MySQL: Why Server ID ?

In any MySQL replication  set up, server ID will be a common variable seen in my.cnf. Ans usually it will be unique across the servers.

[mysqld]

server-id = 2

But why its so important?

For any normal Master-Slave setup, it doesn't matter whatever the number you set.

However, when you go for a Multi-master or circular replication, its a basic need.

Consider an  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.

If sever ID wasn't unique, this setup would have entered into  an endless loop state..!