« Favorite Open Source Project? | Main | Backstage »

January 22, 2003

MySQL Recovery

Today's urgent message . . . "Um, I accidentally deleted 500 rows from db_x.table_y". Amazingly, the first time in my 2 years at Tufts. Also the first time our backup/restore protocol was exercised in production (well tested in dev).

When I first started here we did a mysqldump of modified records every hour, but it began to be noticable to the users so I redesigned our backup plan.

Our backup/restore protocol:
Each night we mysqlhotcopy the database into a directory named with date. Prior to the backup we move the binlog into the previous days dir, keeping the snapshot and all the changes for the day in one place. We keep 4 days worth of backups.

To restore, the db is stopped long enough to copy the necessary table files from that morning's snapshot into the mysql data directory. Once the database is started we use a perl script I wrote to run the logs:

hot_restore --file=<mysql log file> [--database=<database>] [--table=<table>] [--stoptime=<mysql log timestamp>]

This script outputs the relevant updates from the log file(s), allowing the user to specify a timestamp where the script will end printing statements (most likely immediately before the statement that did the damage).

Today's issues were resolved with the database being down just long enough to copy the table files over and the few seconds it took to run the day's log file against the table.

Doesn't mean I wasn't nervous through the whole thing.

Posted by mike at January 22, 2003 10:02 PM