6.5 Point-in-Time (Incremental) Recovery Using the Binary Log

Point-in-time recovery refers to recovery of data changes made
since a given point in time. Typically, this type of recovery is
performed after restoring a full backup that brings the server to
its state as of the time the backup was made. (The full backup can
be made in several ways, such as those listed in
Section 6.2, “Database Backup Methods”.) Point-in-time recovery then
brings the server up to date incrementally from the time of the
full backup to a more recent time.

Point-in-time recovery is based on these principles:

The source of information for point-in-time recovery is the
set of incremental backups represented by the binary log files
generated subsequent to the full backup operation. Therefore,
the server must be started with the
--log-bin option to enable
binary logging (see Section 5.3.4, “The Binary Log”).

To restore data from the binary log, you must know the name
and location of the current binary log files. By default, the
server creates binary log files in the data directory, but a
path name can be specified with the
--log-bin option to place the
files in a different location. Section 5.3.4, “The Binary Log”.

To see a listing of all binary log files, use this statement:

mysql> SHOW BINARY LOGS;

To determine the name of the current binary log file, issue
the following statement:

Executing events from the binary log causes the data
modifications they represent to be redone. This enables
recovery of data changes for a given span of time. To execute
events from the binary log, process
mysqlbinlog output using the
mysql client:

shell> mysqlbinlog binlog_files | mysql -u root -p

Viewing log contents can be useful when you need to determine
event times or positions to select partial log contents prior
to executing events. To view events from the log, send
mysqlbinlog output into a paging program:

shell> mysqlbinlog binlog_files | more

Alternatively, save the output in a file and view the file in
a text editor:

shell> mysqlbinlog binlog_files > tmpfile
shell> ... edit tmpfile ...

Saving the output in a file is useful as a preliminary to
executing the log contents with certain events removed, such
as an accidental DROP DATABASE.
You can delete from the file any statements not to be executed
before executing its contents. After editing the file, execute
the contents as follows:

shell> mysql -u root -p < tmpfile

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:

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: