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'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.

share|improve this question
    
We need to set server id in my.cnf file. Could you please check this link hackosis.com/how-to-setup-mysql-replication for more references. –  Praveen Prasannan Dec 27 '11 at 9:59
    
I've already configured this. Other settings are being taken into account from that same config file so I'm sure it must be picking it up... "because I have all of the server IDs miles apart 1->2626->2629 (m->s->s)." –  tarnfeld Dec 27 '11 at 16:10

2 Answers 2

up vote 2 down vote accepted

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.

share|improve this answer
    
Thanks! I'll try that. –  tarnfeld Dec 27 '11 at 20:48

I was struggling with this issue for sometime where master shows error

Master & Slave were set up correctly with different IPs & different "server-id" in my.ini


Relay_Master_Log_File: log-bin.000001
Slave_IO_Running: No
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:   
.
.
.    
Last_IO_Errno: 1593
Last_IO_Error: 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).
Last_SQL_Errno: 0

ISSUE:
Slave SQL_Thread was running on master server as well...(Not sure how :( )

FIX:
run "stop slave;" on MASTER

POST ACTIONS
Make sure replication is still working by verify slave status (on SLAVE)

In my case, above fixed the issue and master-slave replication also worked fine after that.

share|improve this answer

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.