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?


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.


Here is something quick and dirty you can do

Run the DELETE like this on the Master:


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

| improve this answer | |
  • Interesting. Will that only impact statements run in the current connection? i.e. other writes and replication will continue as normal? (It's a very busy DB) – Jan Rutten Mar 6 '12 at 16:33
  • @JanRutten Yes, only the current connection. All other DB Connections will replicate as usual. – RolandoMySQLDBA Mar 6 '12 at 16:35
  • And don't forget to close the connection off before you do anything that you DO want replicated! – Dave Rix Mar 7 '12 at 21:33

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.

| improve this answer | |
  • What is your reference for DELETE without stopping binary logs? – shgnInc Jan 20 '15 at 9:55

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.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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