Recovering from binary log: possible to delete several, non-continuous transactions?

[Alex Zendel]

I’ve been reading up on MySQL’s capabilities in restoring from dumps (.sql log) and also from the binary log. I’ve been reading Guilhem Bichot’s article (http://dev.mysql.com/tech-resources/articles/recovering-from-crashes.html) which strongly implies that it’s possible to go back and locate an individual transaction (delete, insert or update) after converting the bin log to a .sql back up file. His article then suggests that once you find the bad transaction that you want to delete or ‘undo’, you can remove that transaction and ALL subsequent transactions in the log. That way you have your database as it was just before the bad event.

But what happens when there’s unrecognized bad/corrupting event and people continue to use the database afterwards. Then several days down the road, somebody notices the problem. For example, say somebody did a update and set a field to all one value, such as setting all first names in the database to “Sara”. Database users then continue to insert new names into the database. Three days later, somebody comes to me and points out that 99% of the names in our database are ‘Sara’. So if I have the binary log, I can ‘convert’ to a .sql file. Then I can use a text editor to find the UPDATE …. SET firstname = ‘Sara’ transaction. But if I delete all subsequent transactions, then I’ve lost three days worth of data entry. But could I delete this <<individual>> transaction from the .sql file and then run a restore? Would the corrupt first name field be corrected? Will all of the data entry from the following three days be safely and accurately restored?

What do you think? Is this possible? Are there any potentional problems in doing this? We are a small organization and we’d probably have only 5 people accessing the database, both read and write. So having to stop their activity while we search the binary log and do the restore wouldn’t be that big of a deal – there are other tasks they could be working on. Are there other ways of dealing with this type of problem?

Thanks!

Alex