Rows already DELETEd on MySQL slave; How to DELETE on master?

Question

I have a handful of rows that exist in my MySQL master but not in my slave.

Replication is running ok and these rows are unlikely to ever be referenced as it is a store-and-forget type of table. However, I want to delete these orphaned rows in order to have everything in sync and prevent any future problems.

If I issue a DELETE statement on the master to remove the offending rows, the slave obviously won't be able to replicate it as those rows don't exist at the slave.

What are the implications of doing this?

• Will this break replication or will the slave simply ignore the problem and carry on?
• What is the recommended way to resolve this?

Background

A DELETE statement was interrupted on the master. Replication stopped as a result. I believe the same DELETE statement was then executed on the slave before restarting replication. However, it ran to completion on the slave, resulting in more rows being deleted there.

Answer 1

Here is something quick and dirty you can do

Run the DELETE like this on the Master:

SET SQL_LOG_BIN=0;
DELETE FROM ... ;

The first line tells the DB Session not to record the SQL that follows. Thus, when you run the DELETE on the Master, the SQL will not be written in the Master's binary logs. Consequently, the Slave will never receive the DELETE in its relay logs.

This will only affect the session you use to run these two lines. All other DB Connections will replicate as usual. Once you disconnect, any new session will replicate properly.

Give it a Try !!!

Answer 2

Just run the DELETEs on the master. Worst case - it will execute and be a no-op on the slave. I recommend against disabling binary logging for this on the master, as it will prevent you from using your binary logs for point in time recovery.

Answer 3

I find option(2) given by @aaron-brown more suitable than the option(1) by @rolandomysqldba in case you have master - slave - hot standby setup and DELETES have been performed only on the slave.

That is because if you delete on master using (1) your hot standby won't get the deletes and will become out of sync with master, whereas using (2) you would seamlessly keep slave and hot standby both in sync.

I encountered this problem in production environment and used (1) and it just worked fine.

Answer 4

I'm several years late to the party but I would like to propose the following solution:

If

• You know exactly which rows have been deleted on the follower AND
• You have permissions to write to the follower

You can export the rows from the leader and import them into the follower, then do the delete from the master. This should make a clean delete for the leader and all followers.