MySQL bin log: Your friend

2007-01-08 2-minute read

A big thanks to the Debian MySQL server maintainers for enabling bin logging by default.

I just did a big boo boo: dropping the wrong database without a backup. I don’t recommend doing this. But - if you do this, and you are running Debian with a stock configuration, there is hope.

In /var/log/mysql you should see a bunch of files named mysql-bin-00001. If you use the mysqlbinlog program you can convert those files into sql statements. So, you can do:

mysqlbinlog mysql-bin* > dump.sql

Now - you have a giant sql dump. If you want to restore from your mistake you can do something fancy awk’ing - or, if you’re like us, you can open the giant file in vim and by hand edit out the sql statements you don’t need. We did that by searching in vim with:

/^use db-name

To find all sql statements concerning the db that I dumped (lines that start with use db-name with db-name replace with the name of the database). And:

/^use

To find the next set of statements. Then we deleted all statements not related to the db I dropped. We also deleted all the comments (starting with /*) and all the statements that seemed bin log specified, like ones starting with ROLLBACK and setting the timestamp (SET TIMESTAMP).

And, at long last, we had a sql file that, when dumped into the original database, brought the lost Drupal site back to life. Woohoo.

Thanks dkg and the rest of the MFPL tech crew for the help!