4

I have a Master & Slave setup. Embarrassingly I managed to execute some data changing statements (INSERT and UPDATEs across 3 tables) on the Slave. Obviously this stopped the Slave replication.

I don't need the updates I accidentally made on the Slave. How can I get it back in sync with the Master?

Improve this question

2 Answers 2

Reset to default
1

you run this in slave host (pdb-0001 is remote Master DB)

mysqldump --add-drop-database -umiadmin -pXXX -h qdb-0001 -P3306 --single-transaction \
    --apply-slave-statements=true --include-master-host-port --master-data=1 \
    --databases c0006 | mysql -u miadmin -pXXXX -h 127.0.0.1
Improve this answer
1
  • 1
    This is just duplicating the entire database and re-setting up the slave relationship, right?
    – artfulrobot
    Commented Nov 6, 2012 at 10:26
2

A bit more information would be useful - did you update a single table, or multiple tables? Was it a structure change or a pure data change?

It may be possible to get the original table from the master, dump it and restore to a new table on the slave, rebuild the data on the slave by matching the records in the slave data to that of the master table and updating data fields as necessary using some update queries with joins between the two tables on the primary key.

Otherwise, can you go back to a recent backup and restore that to the slave, including the master and slave locations for that backup, so that you can then play-forward through the relay / master logs on the slave in order to catch up with the master??

Improve this answer
3
  • Thanks @DaveRix I like the idea of finding a shared point in history (i.e. backup) and resetting master log to reply from that place. But not sure how I'd identify that point on the master?
    – artfulrobot
    Commented Nov 22, 2011 at 14:30
  • Crude solution: should I stop the master (rather not!); stop the slave; copy the affected tables from master to slave; restart master; restart slave?
    – artfulrobot
    Commented Nov 22, 2011 at 14:49
  • I wouldn't go for the crude option, as only taking some of the tables may screw up the integrity with other tables... You may be better to use something like Xtrabackup from Percona, which will allow you to do a live backup of the master, including the log positions. You can then restore the backup to the slave, set the replication positions appropriately and away you go. I think what I'm trying to say, is ditch the slave and re-create it to be safe! That is assuming you can afford to loose the slave for the duration! (but having said that, it's currently useless!)
    – Dave Rix
    Commented Nov 22, 2011 at 20:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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