Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
add comment

2 Answers

up vote 2 down vote accepted

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.

share|improve this answer
add comment

@com, the answer is yes, changing only the MySQL Master’s server_id will most likely break the replication. In order to make that change work, follow these steps:

  1. First, stop all Slaves.
  2. Run show slave status and save the results on each Slave.
  3. Change the server_id on your Master in my.cnf and by running SET GLOBAL.
  4. Now, on each Slave, use CHANGE MASTER query to set the Master's new server_id but make sure that all other details remain the same.
  5. Finally, run start slave on all Slaves.

One note, changing the server_id on a Slave only however, should not impact replication.

Good luck :)

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.