content_image/852501.jpg
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 TABLE . You can delete from the file any statements not to be executed before executing its contents. After editing the file, apply the contents as follows: 

shell> mysql -u root -p < tmpfile 

If you have more than one binary log to apply 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 apply the contents of all binary log files 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 the whole log 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" 

When writing to a dump file while reading back from a binary log containing GTIDs (see Section 17.1.3, “Replication with Global Transaction Identifiers” ), use the --skip-gtids  option with mysqlbinlog , like this: 

## 7.5.2 Point-in-Time Recovery Using Event Positions 

shell> mysqlbinlog --skip-gtids binlog.000001 > /tmp/dump.sql shell> mysqlbinlog --skip-gtids binlog.000002 >> /tmp/dump.sql shell> mysql -u root -p -e "source /tmp/dump.sql" 

The last section, Section 7.5.1, “Point-in-Time Recovery Using Binary Log” , explains the general idea of using the binary log to perform a point-in-time-recovery. The section explains the operation in details with an example. 

As an example, suppose that around 20:06:00 on March 11, 2020, an SQL statement was executed that deleted a table. You can perform a point-in-time recovery to restore the server up to its state right before the table deletion. These are some sample steps to achieve that: 

1. Restore the last full backup created before the point-in-time of interest (call it t$_{p}$ , which is 20:06:00 on March 11, 2020 in our example). When finished, note the binary log position up to which you have restored the server for later use, and restart the server. 

<img src='content_image/852501.jpg'>

## Note 

While the last binary log position recovered is also displayed by InnoDB after the restore and server restart, that is not  a reliable means for obtaining the ending log position of your restore, as there could be DDL events and non- InnoDB changes that have taken place after the time reflected by the displayed position. Your backup and restore tool should provide you with the last binary 

