Twitter is now my all time favorite Web 2.0 app. I know this is totally a childish moment and a bit of hero worship, but it is still great.

October 20th, 2008 | General | No comments

Database backups are not a difficult thing, but they are tedious and only important when there is a crisis at hand. Recently we ran into a situation where backups failed to protect us from some worst practices and we had to scrape together the binlogs and old dumps to resurrect the table.

What follows is a true story although the names and places have been changed.

At 13:45 a developer execute the following query

DELETE FROM table
return 159 rows effected


He intended to execute a SELECT * FROM table, and just remove the WHERE clause from a previous query but it turned out to be a DELETE FROM table WHERE ID = #.

As soon as the trigger was pulled he realized his mistake. I was brought in to resurrect the data because I previously performed all backups.

Off we go.

1. Stop replication
2. Flush logs
3. if you can do the processing on the server wonderful, other wise copy the binlogs to a recovery system.
4. mysqlbinlog -d mydb mysql-binlog.[0-9]* | sed '/table/,/;/!d' > recovery_file
5. start replication

From the recovery_file we had a list of every statement issued against the table and we could replay all transactions to return the table to it’s original state with a little editing.

We are now reviewing our backup and recovery practices. I hope this was educational for you as it was for me.

I found the key point for the sed command in this document, and this tutorial helped greatly.

August 12th, 2008 | Programming | No comments