Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
add comment

2 Answers

up vote 2 down vote accepted

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

share|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
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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