Why server id important in MySQL Replication

Set server id in my.cnf is one of the necessary steps to implement or start replication. The general rule is, server id must be unique across the environment where we are implementing MySQL replication.

Generally while implementing multi-master/circular replication, we are setting option –log-slave-updates= 1. so when any statement of master will be executed on the slave, it will be logged in binary log of that server.

For example, we have 3 servers A, B and C. Their server ids are accordingly 11, 22 and 33. They are replicating in circular manner (multi-master) like A is master of B server, B is master of C server and C is master of A server.

Now when any mysql client will execute insert/update/delete statement on A server, it will be logged in the binary of A server with server id 11.

As that statement logged in binary of A server, it will go to B server (because B is slave of A), executed there and logged in binlog of B server with the same server id 11.  (why same server id? because originally that statement initiated by A server with server id 11 and on B server its executed by sql thread not regular mysql client).

Now again this statement will go to C server (because C is slave of B), executed there and logged in the binlog of C server with the same server id 11.

As C is master for A server, when the statement will come to A server via binlog of C server, sql thread of A server will compare the server id and will find that its same 11 means this statement is originally initiated by the server itself and no need to execute so finally sql thread will skip that statement.

With the help of server id, we can avoid the endless loop kind of circumstances in master-master/circular replication.

3 thoughts on “Why server id important in MySQL Replication

Leave a Reply to Cédric Cancel reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.