This section explains the general idea of using the binary log to perform a point-in-time-recovery. The next section, Section 1.5.2, “Point-in-Time Recovery Using Event Positions”, explains the operation in details with an example.
Many of the examples in this and the next section use the
mysql client to process binary log output
produced by mysqlbinlog. If your binary log
\0 (null) characters, that output
cannot be parsed by mysql unless you invoke
it with the
The source of information for point-in-time recovery is the set of binary log files generated subsequent to the full backup operation. Therefore, to allow a server to be restored to a point-in-time, binary logging must be enabled on it, which is the default setting for MySQL 8.0 (see 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. 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:
mysql> SHOW MASTER STATUS;
The mysqlbinlog utility converts the events in the binary log files from binary format to text so that they can be viewed or applied. mysqlbinlog has options for selecting sections of the binary log based on event times or position of events within the log. See mysqlbinlog — Utility for Processing Binary Log Files.
Applying events from the binary log causes the data modifications they represent to be reexecuted. This enables recovery of data changes for a given span of time. To apply events from the binary log, process mysqlbinlog output using the mysql client:
$> mysqlbinlog binlog_files | mysql -u root -p
If binary log files have been encrypted, which can be done from
MySQL 8.0.14 onwards, mysqlbinlog cannot read
them directly as in the above example, but can read them from
the server using the
-R) option. For example:
$> mysqlbinlog --read-from-remote-server --host=host_name --port=3306 --user=root --password --ssl-mode=required binlog_files | mysql -u root -p
Here, the option
--ssl-mode=required has been
used to ensure that the data from the binary log files is
protected in transit, because it is sent to
mysqlbinlog in an unencrypted format.
VERIFY_IDENTITY are better choices than
REQUIRED for the SSL mode, because they
help prevent man-in-the-middle attacks. To implement one of
these settings, you must first ensure that the CA certificate
for the server is reliably available to all the clients that
use it in your environment, otherwise availability issues will
result. See Command Options for Encrypted Connections.
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:
$> mysqlbinlog binlog_files | more
Alternatively, save the output in a file and view the file in a text editor:
$> mysqlbinlog binlog_files > tmpfile $> ... edit tmpfile ...
After editing the file, apply the contents as follows:
$> mysql -u root -p < tmpfile
If you have more than one binary log to apply on the MySQL server, use a single connection to apply the contents of all binary log files that you want to process. Here is one way to do so:
$> mysqlbinlog binlog.000001 binlog.000002 | mysql -u root -p
Another approach is to write the whole log to a single file and then process the file:
$> mysqlbinlog binlog.000001 > /tmp/statements.sql $> mysqlbinlog binlog.000002 >> /tmp/statements.sql $> mysql -u root -p -e "source /tmp/statements.sql"