MySQL – Will Changing server_id on Master Break Replication?

MySQLreplication

I have few MySql absolutely isolated trees, each one of them has one master and few slaves.

For purposes of automation I need to set the same server_id to all masters (this will simplify the usage of pt-heartbeat which needs server_id). This should be perfectly fine since all trees are isolated.

I am wondering, if I change server_id on master, restart master, do I damage the replication? Of course there are will be a short brake of replication, but will this break replication completely?

The question is does changing server_id break replication completely, does it change positions in bin-log?

UPDATE: actually I don't have to restart mysql, I can just change it in console with SET GLOBAL server_id=xxx, in this case I don't need restart.

Best Answer

It should not break replication provided you do not choose a bad server_id. Here is why:

Given this server_id setup

  • Master with server_id 1
  • Slave with server_id 2

Here is what happens with Replication

  • Master completes and SQL command
  • Master records its server_id along with SQL
  • Slave reads Binary Logs of Master and stores them in Relay Logs
  • Slave reads reads Relay Logs and sees server_id 1
  • Since the Master server_id and Slave server_id do not match, Slaves run the SQL

What happens if you set the Master's server_id to 2 ?

  • Master completes and SQL command
  • Master records its server_id along with SQL
  • Slave reads Binary Logs of Master and stores them in Relay Logs
  • Slave reads reads Relay Logs and sees server_id 1
  • Since the Master server_id and Slave server_id match, Slaves does not run the SQL

The only bad server_id to assign is the same server_id as the Slave. Other than that, Replication should not break.

Related Question