A World Where Database Backups FAIL!

You ever do something that not even your backups can save you from? I have, and it is one of the most gut-wrenching moments you will ever experience in computing.

Backups are only as good as the delay between their creation. For example, if you backup a database at 7:00 am and an issue occurs at 7:00 pm, you still have 12 hours of data to restore. Not something anyone wants to hear.

Thankfully, if you use MySQL, you may be in luck. Have MySQL store binary logs of all updating commands sent to it. Basically this includes anything that isn't a selection of data, but a manipulation of data. You can set this up with mysqld (the MySQL daemon) by providing this option with it:

--log-bin

It will make processes slightly slower, but this is a slight con for an amazing pro.

Let's go back to our example. Let's say you have binary logs storing at 7:00 am right after you create your backups. You can restore your data to 7:00 am from a backup, but you are still missing 12 hours of data. You can use fancy-schmancy monitors, but I merely like looking through the next day's binary log and find the offending piece of code such as:

TRUNCATE foobar;

Then, get the binary data of all data that updates data in table foobar like so:

mysqlbinlog thebinlog | grep ' foobar' >> filteredlog.sql

NOTE: Your command may be "slightly" different from mine.

Then all you do is remove all commands starting at the offending command onto the end of this file (in our example this is "TRUNCATE foobar;"). Once you are done, save this SQL script and run it in a MySQL client.

Voila! You have perfectly restored your database up to the EXACT code that ruined it!

Neat huh?