I am trying to recover a few thousand rows that were accidentally deleted from a MySQL database. Luckily replication is enabled, so in the binary logs I have a record of each INSERT, UPDATE and DELETE that was performed on the database since it was created.
Unfortunately in a previous attempt to recover the lost rows, someone had copied the entire INSERTS from the binlogs into the database. This created an even bigger mess by adding a bunch of duplicates and changing the ID value which is set to auto_increment.
I created a new recovery database based on a backup that was made a few weeks prior to the incident. I then found the exact point in the binary logs were the backup ended. I extracted all the INSERTS/UPDATES/DELETES from the binlog file and fed them into the new recovery database up until the point were the rows got deleted; this restored the database back to where it was before all the rows had been deleted.
Because of the first failed attempt to recover the lost rows, a lot of invalid data was added to the original database, along with new legitimate data and because one of the columns in the row is set to auto_increment, this creates a mismatch for the value of the column that auto increments when trying to restore the database. I can't simply COPY/PASTE all the missing legitimate binlog SQL statements, because the value of the auto_increment column has changed due to the invalid data that was inserted, which I don't want.
An example of the binlog file:
INSERT INTO reservations (id, name, date) VALUES (null, "john doe", "2013-03-20"); INSERT INTO reservations (id, name, date) VALUES (null, "jane doe", "2013-03-21"); INSERT INTO reservations (id, name, date) VALUES (null, "garbage", "2013-03-21"); INSERT INTO reservations (id, name, date) VALUES (null, "garbage", "2013-03-22"); INSERT INTO reservations (id, name, date) VALUES (null, "mary jane", "2013-03-23"); UPDATE reservations SET name = "mary janett" WHERE id=5;
id, name, date 1, john doe, 2013-03-20 2, jane doe, 2013-03-21 3, garbage, 2013-03-21 4, garbage, 2013-03-22 5, mary janett, 2013-03-23
id, name, date 1, john doe, 2013-03-20 2, jane doe, 2013-03-21 3, mary jane, 2013-03-23
Now if I were to copy the binlog SQL statements into the recovery database, ignoring the garbage INSERTS which I don't want, the UPDATE statement would fail because the value of `id` would be different. Same goes for DELETE's and INSERT's which rely on the value of `id`.
Is there an "easy" way to recover all the new legitimate data that was introduced to the database after the incident? The only solution I can think of at the moment is manually going through each INSERT/UPDATE/DELETE and adjusting the value of `id`.
I am hoping there is a better solution, because with thousands of records, this approach would take a long time. Any help is greatly appreciated!