Restore MySQL database using binlog
Suppose you take hourly backups and you loose data in between backups. You can restore the most recent backup, but you will still miss data... in this article I'll explain how you can use the binlogs to restore what you lost.
The problem
Suppose your sysadmins have configured your system in such a way that they take backups every 4 hours and at specific requests (i.e. before a release).
Suppose that you are 10 hours after a release and discover a serious issue where someone added a delete without a where that is only executed every once in a while.
You now have a problem: your data is wiped every X minutes, and your 4-hour backups can only help so much. If you have setup replication, there is a solution though… the binlogs!
Binlogs
I always download the binlogs to a safe environment. I don't like messing with them in production, so I like to copy them to my local machine or a dev server. That way I can safely run mysqlbinlog on the logs, mess with them and test what I'm about to do without doing anything nasty on production.
After you have done this, execute mysqlbinlog like this:
mysqlbinlog --start-datetime="YYYY-MM-DD HH:MM:SS" --stop-datetime="YYYY-MM-DD HH:MM:SS" \
mysql-bin.000XXX | grep -B 1 - A 1 `table_name`' > tempfile.sql
You specify the start and end time you want to search in using --start-datetime and --stop-datetime respecively. You specify the binlog you want to explore and grep it. In this case I grepped on the table name, but I could have grepped on insert statements alone. This is entirely up to you. Then you send it to a text file that you will use as a base for your restore script.
Example data:
--
SET TIMESTAMP=1319535998/*!*/;
INSERT INTO `table_name` (`fld1`, `key`, `value`) VALUES ('5064785', 'blah', 'no')
/*!*/;
--
SET TIMESTAMP=1319535998/*!*/;
INSERT INTO `table_name` (`fld1`, `key`, `value`) VALUES ('5064785', 'blib', 'no')
/*!*/;
--
SET TIMESTAMP=1319535998/*!*/;
INSERT INTO `table_name` (`fld1`, `key`, `value`) VALUES ('5064786', 'blub', 'no')
/*!*/;
--
SET TIMESTAMP=1319535998/*!*/;
DELETE FROM `table_name` WHERE (`fld1` = 5064820) AND (`key` = 'blah')
/*!*/;
--
SET TIMESTAMP=1319535998/*!*/;
DELETE FROM `table_name` WHERE (`fld1` = 5064820) AND (`key` = 'blib')
/*!*/;
--
SET TIMESTAMP=1319536002/*!*/;
DELETE FROM `table_name` WHERE (`fld1` = 5064820) AND (`key` = 'blub')
/*!*/;
--
SET TIMESTAMP=1319536013/*!*/;
DELETE FROM `table_name`
/*!*/;
When grepping I add a line before and a line after the query. That way I include the TIMESTAMP statement aswell as the ";" at the end.
As you can notice, you will get all insert/update/delete/replace/... queries for the table, not the SELECT queries. In my example, all queries where one-line queries, if you have multi-line queries, you probably have to modify your grep statements to accommodate this.
SQL File
I usually replace the INSERT INTO statements, by REPLACE INTO statements… just in case. I also filter out the bad queries (deletes without where).
After all this is done, you have an SQL file that you can use to update your missing data.
Things to note:
- If you have queries using "NOW()", grep in usch a way that you include the previous line, this has the TIMESTAMP in there. (see above)
- Your autonumber fields, will not take their original number, but a new number. This can cause problems, when you reference to this from other tables.
- Use with care! Check the data and filter wisely!
Conclusion
Binlogs offer you a way to retrieve missing data, but it's not the holy grail. You can still run into problems with autonumber fields for instance. When using this method, be careful. :)
+32 475 62.42.64