Posted by attriel
May 18th, 2010
Well, the server blewout … a bout a day after my last post, honestly. Doesn’t that just suck.
So, I spent one day (well, morning, before work for a couple hours) trying to get it back up to limp through the day for the folks who host off my system. Got it up, but it died again before I got to the office, so obviously THAT wasn’t working.
So over the weekend I divided my time between taking my son around (my wife was out of town and it’s the first time he was away from her by more than a car trip), and migrating everything to the new server I had that I had been slowly working on. Actually, one of the last things I was waiting for was the 10.04 update, so I was ready to do the migration, at least. I had just been planning to do it on my own terms 
So, I couldn’t copy over the mysql files directly as they’d been corrupted. My dumps from day of had a bunch of blank tables (oops, guess that’s what happens when you dump AFTER catastrophic failure). But! luckily I had a dump from the previous weekend. And since I had binary logging running, I had a full log of everything that had happened in between.
So after a full restore from the dump, the following command gave me the commands that had been issued in the meantime:
mysqlbinlog -r ~/fullout –start-position 5938222 psychotomy-bin.000056 psychotomy-bin.00006[23]
The “-r filename” is the output file. heckofalot nicer than having to redirect output, IMO.
“–start-position ###” tells it where to start the log dumping. This let me get the logs from after my full dump, to eliminate duplicate key errors by inserting things that were already there (as it turned out I had the wrong number and had to do it again)
“-d db” (not pictured above) tells it which database to show the results for.
Then the filenames of the binlogs that you want the replay out of. In this case I had a bunch of files that were just restarts without content, so I skipped them.
This didn’t show the -d flag because what I did was two steps:
1) I replayed the logs for my primary users, being where we noticed the corrupted data first when I tried restoring the day-of dump. Got them back up
2) I dumped everything and then extracted that system out of the dump so I could replay all the other databases.
But mysqlbinlog SERIOUSLY nice.
On an unrelated note, I think I’m going to set up a weekly process to dump the db and then archive the binlogs with the dump from the previous week. Then keep the last, say, month. Basically “logrotate” for my database