MySQL Replication – Master and Slave Same Server-ID Issue

MySQLreplication

I've been setting up a master->slave_a->slave_b relationship and having a little trouble. I currently have the master->slave_a replication working fine.

Each of the two slaves have exactly the same configuration, other than the hostname, server-id setting. When ever I try and start the slave on slave_b I have the following error pop up:

Fatal error: The slave I/O thread stops because master and slave have equal MySQL server ids; these ids must be different for replication to work (or the --replicate-same-server-id option must be used on slave but this does not always make sense; please check the manual before using it).

I really don't understand, because I have all of the server IDs miles apart 1->2626->2629 (m->s->s).

I'm setting them in my /etc/mysql/conf.d/replication.cnf file – and it seems to be being included, but I have no idea how I can check which server id mysql is picking up.

Thanks in advance 🙂 Tom.

Best Answer

The server_id of the Master is recorded in its binary logs along with each query it executed to completion. To see those server_id values in the binary log, you run mysqlbinlog against any binary log.

Since Master -> Slave_A works fine, here is what you can do to clean things up between Slave_A and Slave_B:

On Slave_A, run the following in mysql:

mysql> STOP SLAVE;
mysql> RESET MASTER;

On Slave_A, run the following in the OS:

$ echo "STOP SLAVE;" > /root/MyData.sql
$ mysqldump -u... -p... --master-data=1 --routines --triggers --all-databases >> /root/MyData.sql
$ echo "START SLAVE;" >> /root/MyData.sql
$ mysql -hSlave_B -u... -p... -A < /root/MyData.sql

On Slave_A, run the following in mysql:

mysql> START SLAVE;

This will get Slave_B to stare at the correct values for server_id, and also reestablish MySQL Replication.

BTW You need to make sure binary logging is disabled on Slave_B since it is not necessary for a Slave unless Slave_B is going to a Master to some other Slave.

Related Question