Mysql replication fail

Today i had an interesting problem.

I have a customer that we have upgraded the running system and database server for. And we have used mysql replication to be able to do this with a minimum of downtime. So we have a spare server that we set up as a database server. Started replication to it – when it was running as is should we stopped the system totally – ended the replication and made that system the master. All well. Then we upgraded the old server with new os and new server software and did the same thing back.

And by misstake the system was writing some data to the new server while we still where replicating from the “old” or “temporary” datbase servern. And this ofcuase made the database replication to stop whit an error message.

The error you will see (when you run mysql command show slave status;) is

So is there a way to fix this. Well it kind of depends of what the problem is.

I see 2 ways to fix this. If you by misstage onli inserted a few entries into one table you could (if you know what you did insert) remove that and start up the slave again. This will work.

Secondly is to skip in the replication – This is something you can do if you know that that insert is not something you will miss in the slave. And in our case it was just one log entry.

So you tell mysql to skipp one onetry in the replication log by the command:

if you are useing and older version of mysql you can skip the global.

And the you start the slave replication again with the command:

And it should be ok – you can check the status by the show slave status command and the seconds behind shoudl cound down to 0.

Now you have the slave replication up and running again.


Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.