7.5.1. Point-in-Time Recovery Using Event Times

To indicate the start and end times for recovery, specify the --start-datetime and --stop-datetime options for mysqlbinlog, in DATETIME format. As an example, suppose that exactly at 10:00 a.m. on April 20, 2005 an SQL statement was executed that deleted a large table. To restore the table and data, you could restore the previous night's backup, and then execute the following command:

shell> mysqlbinlog --stop-datetime="2005-04-20
        9:59:59" \         /var/log/mysql/bin.123456 | mysql -u root
        -p

This command recovers all of the data up until the date and time given by the --stop-datetime option. If you did not detect the erroneous SQL statement that was entered until hours later, you will probably also want to recover the activity that occurred afterward. Based on this, you could run mysqlbinlog again with a start date and time, like so:

shell> mysqlbinlog --start-datetime="2005-04-20
        10:01:00" \         /var/log/mysql/bin.123456 | mysql -u root
        -p

In this command, the SQL statements logged from 10:01 a.m. on will be re-executed. The combination of restoring of the previous night's dump file and the two mysqlbinlog commands restores everything up until one second before 10:00 a.m. and everything from 10:01 a.m. on.

To use this method of point-in-time recovery, you should examine the log to be sure of the exact times to specify for the commands. To display the log file contents without executing them, use this command:

shell> mysqlbinlog /var/log/mysql/bin.123456 >
        /tmp/mysql_restore.sql

Then open the /tmp/mysql_restore.sql file with a text editor to examine it.

Excluding specific changes by specifying times for mysqlbinlog does not work well if multiple statements executed at the same time as the one to be excluded.