1

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.

Improve this question

2 Answers 2

Reset to default
1

You didn't setup the server-id variable, 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.

Improve this answer
4
  • Both servers have been restarted several times, I've been banging my head on this for a day or so. At the moment the next thing I'm going to do is purge binary logs on the master, sync with pt-sync-tables and see if replication can start from there.
    – Simon Greenwood
    Commented Apr 13, 2015 at 12:54
  • @SimonGreenwood pt-table-sync will not work (usually, at least as intended) unless replication is running. Cloning the server again and using mysql_upgrade may be an option, though.
    – jynus
    Commented Apr 13, 2015 at 13:01
  • The original migration was actually done through cPanel (I know, I know) so I would have thought it was converted at that point. Of course, to make it more fun time is short and I have a limited window to do dumps in. At the moment, getting the servers in sync without a full dump would be useful, is that possible by running mysqlbinlog on the binary logs on the master?
    – Simon Greenwood
    Commented Apr 13, 2015 at 15:03
  • @SimonGreenwood Theoretically, yes.
    – jynus
    Commented Apr 13, 2015 at 15:38
1

As mentioned, the slave is set up to do two way replication, and there was actually some advice in the error log:

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

In this case, adding replicate-same-server-id = 1 to the slave my.cfg worked and may be all I need unless the client insists that they need two way replication, at which point I might be in trouble

Thanks for the help @jynus, especially about the low likelihood of getting two-way replication working, and I'll accept about restarting as it's perfectly valid.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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