backups worked

We had an unscheduled test of our backups last night. Point-in-time recovery using a mysqldump and binary log files worked fine (thank goodness).

I’m used to thinking of (Oracle) exports being one thing, and point-in-time recovery (using hot backups) another. Maybe there’s a way to do a “PITR” in Oracle using an export, rolling forward using timestamps rather than SCNs… Don’t know. I know that with MySQL you can do it.

Last night we had some user-generated data corruption on a production server. The database was relatively small (a few Gig), so, after stopping the database and restarting it with ––skip-networking, I imported it from the latest daily mysqldump. Although I didn’t use ––master-data for the mysqldump (we had some locking issues with that in the past) I knew the time that the mysqldump had been started.

I did a little investigation into the binary log files using mysqlbinlog

mysqlbinlog ––start-datetime=”2007-09-17 11:25:00″ ––stop-datetime=”2007-09-17 11:30:00″ binlog.000003 > my_tempfile

and found out the exact time I wanted to start rolling forward from. (I already knew when to stop rolling forward.) Then I ran it

mysql -p our_database < mysqlbinlog ––start-datetime=”2007-09-17 11:26:00 ––stop-datetime=”2007-09-17 20:45:00″

Checked things out, shut down mysql and restarted it normally, and we were once again off and running.

(Luckily for us, in this case I didn’t need to know an exact or down-to-the-second time to start rolling forward from. Activity was very light around the time of the mysqldump. Also, luckily, we didn’t need a starttime more precise than to-the-second. If we had needed to be more precise, I think I would have needed a mysqldump taken with the ––master-data option.)


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: