patternsqlMinor
MySQL replication problem: slave has become out of sync
Viewed 0 times
problemslavereplicationsynchasmysqlbecomeout
Problem
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?
I don't need the updates I accidentally made on the Slave. How can I get it back in sync with the Master?
Solution
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??
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??
Context
StackExchange Database Administrators Q#8236, answer score: 2
Revisions (0)
No revisions yet.