5.9.3.1. Specifying Times for Recovery
To indicate the start and end times for recovery, specify the
--start-date and --stop-date
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-date="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-date 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-date="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. 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 file with a text editor to examine it.
