使用事件位置进行时间点恢复

来自泡泡学习笔记
跳到导航 跳到搜索

假设在2020年3月11日的20:06:00左右执行了一个删除表的SQL语句。您可以执行时间点恢复，将服务器恢复到在删除表之前的状态。以下是一些示例步骤：

  • 在感兴趣的时间点（我们的示例中为2020年3月11日的20:06:00，记为tp）之前还原最后一个完整备份。完成后，记录已恢复服务器的二进制日志位置以备后用，并重新启动服务器。
注意
尽管在恢复和服务器重新启动后，InnoDB也会显示最后恢复的二进制日志位置，但这并不是可靠获取恢复结束日志位置的方法，因为在显示位置之后可能发生了DDL事件和非InnoDB更改。您的备份和恢复工具应该为您提供恢复的最后一个二进制日志位置：例如，如果您使用mysqlbinlog执行任务，请检查二进制日志重放的停止位置；如果您使用MySQL Enterprise Backup，则最后一个二进制日志位置已保存在您的备份中。请参阅时间点恢复。


  • 查找与您想要恢复数据库的时间点相对应的精确二进制日志事件位置。在我们的示例中，我们知道表删除发生的大致时间（tp），我们可以使用mysqlbinlog实用程序检查该时间附近的日志内容来查找日志位置。使用--start-datetime和--stop-datetime选项指定tp附近的短时间段，然后在输出中寻找该事件。例如：


$> 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的输出，DROP TABLE `pets`.`cats`语句可以在二进制日志的第232行和第355行之间找到，这意味着该语句在日志位置232之后发生，并且在DROP TABLE语句之后，日志位置为355。 

请注意
只有在查找感兴趣的实际事件位置时才使用 --start-datetime 和 --stop-datetime 选项。不推荐使用这两个选项来指定要应用的二进制日志段的范围，这样可能会导致错过二进制日志事件的风险增加。请改为使用 --start-position 和 --stop-position 选项。


  • 将二进制日志文件中的事件应用到服务器上，从步骤1中找到的日志位置开始（假设为155），到步骤2中找到的在你感兴趣的时间点之前的位置（即232）为止：
$> mysqlbinlog --start-position=155 --stop-position=232 /var/lib/mysql/bin.123456 \
        | mysql -u root -p


该命令将从开始位置恢复所有事务，直至停止位置之前。由于mysqlbinlog的输出中包含了每个SQL语句之前的SET TIMESTAMP语句，因此恢复的数据和相关的MySQL日志会反映事务执行的原始时间。


  • 你的数据库现在已经恢复到你感兴趣的时间点 tp 前，也就是表 pets.cats 被删除之前的状态。


  • 在完成了点时间恢复后，如果你还想重新执行感兴趣时间点之后的所有语句，可以再次使用mysqlbinlog将所有事件应用到服务器上。我们在步骤2中注意到，在我们想要跳过的语句之后，日志位置为355；我们可以将其用作 --start-position 选项，以便包含该位置之后的所有语句：
$> mysqlbinlog --start-position=355 /var/lib/mysql/bin.123456 \
         | mysql -u root -p


这样，你的数据库就恢复到了二进制日志文件中记录的最新语句，但跳过了选择的事件。

取自“http://bubblestudy.info/wiki/index.php?title=使用事件位置进行时间点恢复&oldid=1640”