MySQL 8.4 Release Notes
上一节，第 9.5.1 节，“使用二进制日志进行时间点恢复”，解释了使用二进制日志执行时间点恢复的一般概念。本节通过示例详细说明了操作过程。The last section, Section 9.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.
例如，假设在 2020 年 3 月 11 日大约 20:06:00 执行了一条删除表的 SQL 语句。您可以执行时间点恢复，以将服务器恢复到删除表之前的状态。以下是实现此操作的一些示例步骤：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:
恢复在感兴趣的时间点之前创建的最后一次完整备份（称为
tp，例如我们的示例中为 2020 年 3 月 11 日 20:06:00）。完成后，记下你已经恢复服务器的二进制日志位置以备后用，然后重启服务器。Restore the last full backup created before the point-in-time of interest (call ittp, 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.Note在恢复和服务器重启后，InnoDB 也会显示最后恢复的二进制日志位置，但这 不是 获取恢复结束日志位置的可靠方法，因为在显示的位置所反映的时间之后，可能已经发生了 DDL 事件和非 InnoDB 的更改。您的备份和恢复工具应该为您提供恢复所需的最后二进制日志位置：例如，如果您使用 mysqlbinlog 执行此任务，请检查二进制日志重放的停止位置；如果您使用 MySQL Enterprise Backup，最后的二进制日志位置已保存在您的备份中。请参见 时间点恢复。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 log position for your recovery: for example, if you are using mysqlbinlog for the task, check the stop position of the binary log replay; if you are using MySQL Enterprise Backup, the last binary log position has been saved in your backup. See Point-in-Time Recovery.
找到对应于您希望恢复数据库的时间点的精确二进制日志事件位置。在我们的示例中，假设我们知道表删除发生的大概时间（
tp），我们可以通过使用 mysqlbinlog 工具检查该时间附近的日志内容来找到日志位置。使用--start-datetime和--stop-datetime选项来指定tp附近的一小段时间，然后在输出中查找该事件。例如：Find the precise binary log event position corresponding to the point in time up to which you want to restore your database. In our example, given that we know the rough time where the table deletion took place (tp), we can find the log position by checking the log contents around that time using the mysqlbinlog utility. Use the--start-datetimeand--stop-datetimeoptions to specify a short time period aroundtp, and then look for the event in the output. For example:$>
mysqlbinlog --start-datetime="2020-03-11 20:05:00" \ --stop-datetime="2020-03-11 20:08:00" --verbose \/var/lib/mysql/bin.123456 | grep -C 15 "DROP TABLE"/*!80014 SET @@session.original_server_version=80019*//*!*/; /*!80014 SET @@session.immediate_server_version=80019*//*!*/; SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/; # at 232 #200311 20:06:20 server id 1 end_log_pos 355 CRC32 0x2fc1e5ea Query thread_id=16 exec_time=0 error_code=0 SET TIMESTAMP=1583971580/*!*/; SET @@session.pseudo_thread_id=16/*!*/; SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/; SET @@session.sql_mode=1168113696/*!*/; SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/; /*!\C utf8mb4 *//*!*/; SET @@session.character_set_client=255,@@session.collation_connection=255,@@session.collation_server=255/*!*/; SET @@session.lc_time_names=0/*!*/; SET @@session.collation_database=DEFAULT/*!*/; /*!80011 SET @@session.default_collation_for_utf8mb4=255*//*!*/; DROP TABLE `pets`.`cats` /* generated by server */ /*!*/; # at 355 #200311 20:07:48 server id 1 end_log_pos 434 CRC32 0x123d65df Anonymous_GTID last_committed=1 sequence_number=2 rbr_only=no original_committed_timestamp=1583971668462467 immediate_commit_timestamp=1583971668462467 transaction_length=473 # original_commit_timestamp=1583971668462467 (2020-03-11 20:07:48.462467 EDT) # immediate_commit_timestamp=1583971668462467 (2020-03-11 20:07:48.462467 EDT) /*!80001 SET @@session.original_commit_timestamp=1583971668462467*//*!*/; /*!80014 SET @@session.original_server_version=80019*//*!*/; /*!80014 SET @@session.immediate_server_version=80019*//*!*/; SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/; # at 434 #200311 20:07:48 server id 1 end_log_pos 828 CRC32 0x57fac9ac Query thread_id=16 exec_time=0 error_code=0 Xid = 217 use `pets`/*!*/; SET TIMESTAMP=1583971668/*!*/; /*!80013 SET @@session.sql_require_primary_key=0*//*!*/; CREATE TABLE dogs从 mysqlbinlog 的输出中，可以在二进制日志的
# at 232与# at 355行之间找到DROP TABLE `pets`.`cats`语句，这意味着该语句发生在日志位置 232 之后，且在DROP TABLE语句之后日志位于位置 355。From the output of mysqlbinlog, theDROP TABLE `pets`.`cats`statement can be found in the segment of the binary log between the line# at 232and# at 355, which means the statement takes place after the log position 232, and the log is at position 355 after theDROP TABLEstatement.Note仅使用
--start-datetime和--stop-datetime选项来帮助你找到实际感兴趣的事件位置。不推荐使用这两个选项来指定要应用的二进制日志段范围：使用这些选项时，遗漏二进制日志事件的风险较高。请改用--start-position和--stop-position。Only use the--start-datetimeand--stop-datetimeoptions to help you find the actual event positions of interest. Using the two options to specify the range of binary log segment to apply is not recommended: there is a higher risk of missing binary log events when using the options. Use--start-positionand--stop-positioninstead.将二进制日志文件中的事件应用到服务器，从您在步骤 1 中找到的日志位置开始（假设为 155），直到在步骤 2 中找到的、位于您感兴趣的时间点 之前 的位置（即 232）：Apply the events in binary log file to the server, starting with the log position your found in step 1 (assume it is 155) and ending at the position you have found in step 2 that is before your point-in-time of interest (which is 232):
$>
mysqlbinlog --start-position=155 --stop-position=232 /var/lib/mysql/bin.123456 \| mysql -u root -p该命令会从起始位置恢复所有事务直到停止位置之前。因为 mysqlbinlog 的输出在每条记录的 SQL 语句之前都包含
SET TIMESTAMP语句，所以恢复的数据和相关的 MySQL 日志反映了事务执行的原始时间。The command recovers all the transactions from the starting position until just before the stop position. Because the output of mysqlbinlog includesSET TIMESTAMPstatements before each SQL statement recorded, the recovered data and related MySQL logs reflect the original times at which the transactions were executed.您的数据库现在已恢复到感兴趣的时间点
tp，也就是在表pets.cats被删除之前。Your database has now been restored to the point-in-time of interest,tp, right before the tablepets.catswas dropped.在已完成的时间点恢复之外，如果您还想重新执行所有在感兴趣时间点 之后 的语句，请再次使用 mysqlbinlog 将所有在
tp之后的事件应用到服务器上。我们在步骤 2 中注意到，在我们想跳过的语句之后，日志的位置是 355；我们可以将其用于--start-position选项，以便包含位置之后的任何语句：Beyond the point-in-time recovery that has been finished, if you also want to reexecute all the statements after your point-in-time of interest, use mysqlbinlog again to apply all the events aftertpto the server. We noted in step 2 that after the statement we wanted to skip, the log is at position 355; we can use it for the--start-positionoption, so that any statements after the position are included:$>
mysqlbinlog --start-position=355 /var/lib/mysql/bin.123456 \| mysql -u root -p您的数据库已恢复二进制日志文件中记录的最新语句，但已跳过所选事件。Your database has been restored the latest statement recorded in the binary log file, but with the selected event skipped.