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.