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

share|improve this question
add comment

2 Answers

up vote 1 down vote accepted

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
share|improve this answer
1  
This is just duplicating the entire database and re-setting up the slave relationship, right? –  artfulrobot Nov 6 '12 at 10:26
add comment

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

share|improve this answer
    
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 Nov 22 '11 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 Nov 22 '11 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 Nov 22 '11 at 20:40
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.