Oracle 5.0, Reference Manual

Page: 652 / 3232

Point-in-Time Recovery Using Event Times
632
If you have more than one binary log to execute on the MySQL server, the safe method is to process
them all using a single connection to the server. Here is an example that demonstrates what may be
unsafe:
shell> mysqlbinlog binlog.000001 | mysql -u root -p # DANGER!!
shell> mysqlbinlog binlog.000002 | mysql -u root -p # DANGER!!
Processing binary logs this way using different connections to the server causes problems if the
first log file contains a
CREATE TEMPORARY TABLE
statement and the second log contains a
statement that uses the temporary table. When the first
mysql
process terminates, the server drops
the temporary table. When the second
mysql
process attempts to use the table, the server reports
“unknown table.”
To avoid problems like this, use a single connection to execute the contents of all binary logs that you
want to process. Here is one way to do so:
shell> mysqlbinlog binlog.000001 binlog.000002 | mysql -u root -p
Another approach is to write all the logs to a single file and then process the file:
shell> mysqlbinlog binlog.000001 > /tmp/statements.sql
shell> mysqlbinlog binlog.000002 >> /tmp/statements.sql
shell> mysql -u root -p -e "source /tmp/statements.sql"
7.5.1. Point-in-Time Recovery Using Event Times
To indicate the start and end times for recovery, specify the
--start-datetime
[346] and
--stop-
datetime
[347] 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
[347] 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.
7.5.2. Point-in-Time Recovery Using Event Positions
Instead of specifying dates and times, the
--start-position
[346] and
--stop-position
[347]
options for
mysqlbinlog
can be used for specifying log positions. They work the same as the
start and stop date options, except that you specify log position numbers rather than dates. Using