Mysql – Rows already DELETEd on MySQL slave; How to DELETE on master

MySQLreplication

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.

Best Answer

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 !!!

Related Question