Server-id during MySQL Replication

The synchronized data of MySQL contains the server-id, which is used to identify the server from which the statement was originally written. Therefore, the server-id must have

The synchronized data of MySQL contains the server-id, which is used to identify the server from which the statement was originally written. Therefore, the server-id must have

1. What is server-id used for? Do you know?
1. the synchronized data of MySQL contains the server-id, which is used to identify the server from which the statement was originally written. Therefore, the server-id must have

2. Each slave in synchronization corresponds to a master thread on the master. This thread is identified by the server-id of the slave. Each slave has a master thread at most on the master, if the server-id of the two slave instances is the same, the first slave instance will be kicked out when the last slave connection is successful. There is at least one such consideration:

After slave actively connects to the master, if slave stop is executed on the slave, the connection is disconnected, but the corresponding thread on the master does not exit. After slave start, the master cannot create another thread and keep the original thread, So synchronization may be faulty;

3. in mysql master synchronization, multiple masters need to form a ring, but during synchronization, ensure that a piece of data is not in an endless loop, this is achieved by server-id.

2. Setting the server ID in my. cnf is a necessary step to implement or start replication. The general rule is that the ID of the MySQL replication server must be unique throughout the environment.

Generally, multi-master/circular replication is performed simultaneously. We set the log option from updated to 1. Therefore, when any declaration is made, the master's slave will be executed and it will be recorded in the binary log of the server.

For example, we have three servers A, B, and C. The server IDs are 11, 22, and 33. They are copying the cyclic mode (Multi-master), for example, A is the master server B, B is the master of the C server and C is the master of A server.

At present, any MySQL client will execute the insert/update/delete server Declaration, which will be recorded in the binary server and server ID 11.

Because the statement is recorded on a binary server, it will go to server B (because B is a slave), where the execution and record of binlog B server are on the same server ID 11. (Why is the same server ID? Because of this statement, the execution is performed on the server and server ID 11 and server B initiated by the mysql client with an irregular SQL thread ).

Now this statement will go to the C server again (because C is the slave of B), where the C server recorded in the binary log executed in the same server ID 11.

Because C is the master server, the C server that comes with this statement will pass the binary log server. The SQL thread of a server will compare the server ID and will find that, as indicated by 11, this is because the server that was initially initiated and there is no need to execute the SQL thread, so this statement will be skipped at last.

With the server ID, we can avoid replication on the master-master/circular in case of endless loops.

3. Make sure that the [mysqld] section of the my. cnf file on the master server host contains a log-bin option. This part should also have a server-id = Master_id option, where master_id must be a positive integer between 1 and 232-1. For example:
6. [mysqld]
7. log-bin = mysql-bin
8. server-id = 1

If no options are provided, add them and restart the server.

9. Stop the server used for the slave server and add the following lines to its my. cnf file:
10. [mysqld]
11. server-id = slave_id

The slave_id value is the same as the Master_id value. It must be a positive integer between 1 and 232-1. In addition, the slave server ID must be different from the master server ID. For example:
[Mysqld]
Server-id = 2

If multiple slave servers are set, each slave server must have a unique server-id value, which must be different from the master server and other slave servers. Server-id values are considered similar to IP addresses: These ID values can uniquely identify each server instance in a replication server cluster.

If no server-id value is specified, if no master-host is defined, set it to 1; otherwise, set it to 2. Note that if the server-id is too long, the master server rejects all connections from the slave server, and the slave server rejects connection to the master server. In this way, the omitted server-id is only applicable to binary log backup.

This article permanently updates the link address: