Replaying Mysql Replication error

0
votes

We're running a standard Mysql Master/Slave replication system which has been working nicely for a couple of years. So nicely in fact that we were able to recover from a server outage (which rendered the Master offline) by using the Slave DB (after turning it into a Master).

Anyway, the issue that is causing our problem occurred after we recovered the server and returned the original Master to being a Master and the original slave back to being a Slave.

It was caused by me being an idiot - not by anything else!

Basically a write occurred on the Slave - meaning that a write on the same table on the Master cannot be replicated across due to a Duplicated Primary Key error.

As I said, my fault.

I can delete the record from the Slave that was erroneously written there - thus removing the conflicting ID from the Slave but I want the record from the Master DB.

My question is this: I know that I can 'fix' the replication by skipping over the relevant query from the Master (by setting the SQL_SLAVE_SKIP_COUNTER to 1) - but that will mean I lose the record from the Master.

So, is there a way of 'replaying' the errored replication write? Basically pointing the replication to be one query back?

Or do I have to go through the whole rigmarole of dropping my Slave, repopulating it from my last successful backup and set it to be a Slave again?

Many thanks in advance.

2
mysqldatabase-replication

2 Answers

0
votes

If it's a simple modification/update/... why don't you simply play it on the master (manually or taking it from the slave binary log if you have one), with slave's slave process off, then set the new replication pointer (file & position) on the slave (show master status; on master), and restart the slave?

0
votes

The answer was actually staring me in the face (isn't it always!).

The duplicate Primary key error was caused by me doing an insert on the Slave and an insert to the same table on the master - meaning both new records had the same ID.

Luckily for me, I did not need the insert on the Slave, so I simply deleted it - meaning that there was only one record with the primary key value.

I just needed a way to get the replication to attempt to replicate the record across - and this was easy.

Just restart the Slave!

So:

mysql root:(none)>START SLAVE;
Query OK, 0 rows affected (0.03 sec)

mysql root:(none)> _