I'm migrating a server to new hardware and trying to set up replication to keep the site current until we switch DNS. The intention is to enable two way replication to make sure that the servers stay in sync during the changeover but I'm concentrating on getting old (MySQL 5.0.92) to new (5.5.42) working at the moment. Both servers have unique IDs but on starting the slave I'm getting
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). The log position given is the start point from SHOW MASTER STATUS on the master.
There are several search results for the issue but the error seems to come up most for MySQL 5.6, which doesn't quite match, and there are even a couple of bug reports. I've tried stopping the slave on the master as suggested in one,I've purged the logs on the slave, the binlogs have the correct server ids in them.
My suspicion is that it's down to some kind of version mismatch and that it hasn't been reported because no-one is ever stupid enough to try replication between two such disparate versions but if anyone has any clues it would be much appreciated. In the meantime I'm going to work with pt-table-sync but it's not ideal.
Best Answer
You didn't setup the
server-idvariable, or you didn't restart after doing it (it is not a dynamic variable, changes will not be effective until you restart). It may be showing you a good id on both servers, but it will not really work until both servers are restarted, both master and slave.It could be a bug, but the above has happened too many times on support cases that I would bet it is that.
Regarding circular replication: 5.0 -> 5.5 "should work", 5.5 -> 5.0 is possible, but not recommended. I understand why you want that, but you may encounter many incompatibilities in the queries and format.