The understanding of Server-id during the MySQL replication process

What does Server-id do for you, you know?

1, MySQL synchronization data is contained Server-id, used to identify which server the statement was originally written from, so Server-id must have

2, each synchronization of the slave on the master has a master thread, the thread is identified by the slave Server-id, each slave on the master side has a maximum master thread, If the two slave have the same server-id, the last one will be kicked off when the connection succeeds. There is at least one such consideration here:

Slave active Connection Master, if the slave above performs a slave stop, the connection is broken, but the corresponding thread on master does not exit, and when slave start, master can no longer create a thread and retain the original thread. There may be problems with that synchronization;

3, in MySQL master synchronization, a number of main needs to form a ring, but when synchronizing to ensure that a data does not fall into the cycle of death, here is to rely on Server-id to achieve

Two. Setting the ID of the server in MY.CNF is a necessary step to implement or start replication. The general rule is that the entire environment where we are implementing the MySQL Replication server ID must be unique.

Generally simultaneous implementation of replication Multi-master/circular, we set the log option from the updated = 1. so any declaration will be performed on the slave of the master and it will be recorded in the binary log of the server.

For example, we have 3 servers, A, B, and C. Its server ID corresponds to 11,22 and 33. They are replicating the loop mode (multi-master), such as a is the primary server b,b is the C Server and the owner of C is the owner of a server.

Now any MySQL client will perform an INSERT/update/Delete declaration on the server and it will be logged in the binary server with server ID 11.

Since the statement is logged in the binary server, it goes to B server (because b is a slave ), where it executes and records Binlog b Server ID 11 on the same server . (why the same server ID?) Because this is the case, it is executed by the SQL thread's irregular MySQL client-initiated server with server ID one and B server).

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

Since c is the primary server, the statement will come to the server through the binary log of the C server, the SQL thread of a server will compare the server ID, will find that the same 11 It is said that this argument is originally initiated by the server itself and there is no need to execute the SQL thread so you will end up skipping this assertion.

With the help of the server ID, we can avoid the dead loop sample in the case of master-master/circular replication.

Three. Ensure that the [Mysqld] section of the my.cnf file on the master server host includes a log-bin option. This section should also have a server-id=master_id option, where master_id must be a positive integer value between 1 and 232–1. For example:

6.            [mysqld]

log-bin=mysql-bin

8.            server-id=1

If you do not provide those options, you should add them and restart the server.

9. Stop the server from the server and add the following line in its my.cnf file:

10.        [mysqld]

11.        server-id=slave_id

The slave_id value, like the master_id value, must be a positive integer value between 1 and 232–1. Also, the ID from the server must not be the same as the ID of the primary server. For example:

[mysqld]

server-id=2

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

If you do not specify a Server-id value, set it to 1 if no master-host is defined, otherwise set to 2. Note that if the Server-id is too long, the primary server rejects all connections from the server and rejects the connection from the server to the primary server. In this way, omitting Server-id is only appropriate for binary log backups.

The understanding of Server-id during the MySQL replication process