How to use MySQL binlogs to undo a DROP statement
This post is for people who are trying to roll back unwanted modifications to their MySQL database.
You cannot use the binary logs to undo unwanted changes to your data. The binary logs are for redoing statements, not undoing them. If you have a backup, you may be able to restore the backup and then replay binary logs to roll forward to the desired state. But you cannot roll backwards with the binary logs.
I say “may be able to” because depending on how you take the backup, even your backups and binary logs may not be enough to fully recover your data. If you don’t know how to do backups right, my advice is to hire someone who knows. It’s not something you should leave to chance.
I wrote this post because of all the people familiar with other databases, who do not know that their chosen backup strategy leads to a situation where it’s impossible to recover their data. Hopefully Google will lead them to it.
Hopefully one would notice such a malicious or accidental drop or truncate table statement quickly but perhaps its just a few records gone missing which took several days to realize. One way to deal with this is use mk-slave-delay from maatkit (slave lag by 2-5 days). And to audit what accts are making the changes and sequence of events, one could use log-slave-updates on a slave.
Sean
2 Jan 09 at 12:46 pm