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.
- Stop replication
- Flush logs
- if you can do the processing on the server wonderful, other wise copy the binlogs to a recovery system.
mysqlbinlog -d mydb mysql-binlog.[0-9]* | sed '/table/,/;/!d' > recovery_file- 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