On more than one occasion I have had to figure out how to restore data from a mysql-binlog and I do it so infrequently that I decided to put the command here so I know where to find it in the future. The first thing to do is to export the binlog. Since it is in binary, you will need to use the mysqlbinlog command.
Let’s assume we have a file called mysql-binlog.000111. There are a number of ways this can be exported, based on your needs, and those choices are available via command line flags (man mysqlbinlog for details). Let’s say you need data from a certain start time up to a certain end time. This can be specified on the command line. You can also specify the database that you wish to export the data for. This is because if you run multiple databases, they are all logged to the same set of binlogs. A command to do all of this would look like (it doesn’t show, but the flags are all preceeded by double dashes):
mysqlbinlog --start-datetime="2009-07-31 05:00:00" --stop-datetime="2009-07-31 10:05:00" --database mydatabase mysql-binlog.000111 > output.txt
That command says pull all of the transactions from the mysql-binlog.000111 file for the ‘mydatabase’ database starting from 7-31-2009 at 5am up until 7-31-2009 at 10:05am and output them to the output.txt file.
This file can simply be input directly into the database using the following command:
mysql < output.txt
Some notes here. First, there is a ‘use ’ statement in the output file. If you are restoring a database to a backup database and not the original you will want to remove the ‘use’ statement from that file (or all of the use statements if there is more than one). Otherwise, you will run the binlog right into the original database. One easy way to do this is run grep -v and remove any line that starts with use, outputting that to a new file.
grep -v ^use output.txt >newout.txt
Finally, you would modify your mysql statement to use the backup database name and the file without the ‘use’ statements.
mysql backupdatabase < newout.txt
Assuming no errors, this will run all of your queries into the database.
Final notes: This binlog replay assumes that you have restored the original database up until the 5am time from some backup file you had. The binlog will then recreate all of the transactions from the time the backup ends until 10:05am. If you try to replay from the binlog and there is already data in the database for those transactions, you could potentially run into duplicate key errors, etc.