MySQL运维实战之备份和恢复（8.6）将数据库恢复到指定时间点
恢复到指定时间点
使用全量备份和增量备份文件，都只能将数据库恢复到备份结束的时间。通过binlog，可以将数据库恢复到任意时间点（前提是备份和该时间点之间的binlog都存在）。
找到时间点对应的binlog
恢复到时间点，首先需要定位该时间点对应的binlog位点（binlog文件和文件内的偏移量）。每个binlog头部都记录了该binlog产生的时间，我们可以使用mysqlbinlog工具解析binlog，查看binlog的第一个event的时间：
# mysqlbinlog -v binlog.000021 | head /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/; /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/; DELIMITER /*!*/; # at 4 #230625 16:44:06 server id 23480 end_log_pos 126 CRC32 0x245d7ed7 Start: binlog v 4, server v 8.0.32 created 230625 16:44:06
如果我们需要恢复到某个时间点T，那么我们需要找的binlog开始时间不大于T，并且该binlog的下一个binlog的开始时间大于T。
这里提供一个python的脚本，可以批量查看binlog时间：
import sys
import struct
if len(sys.argv) >= 2:
pattern = sys.argv[1]
else:
pattern = 'mysql-bin.[0-9]*'
print ('binlog pattern: %s' % pattern)
def parse_binlog_header(filename):
with open(filename, 'rb') as f:
data = f.read(8)
return struct.unpack('i', data[4:])[0]
def main():
import glob
from datetime import datetime
for f in sorted(glob.glob(pattern), key=lambda x: int(x.split('.')[-1])):
ts = parse_binlog_header(f)
print f, ts, datetime.fromtimestamp(ts)
if __name__ == '__main__':
main()使用脚本，传入binlog匹配模式，显示binlog时间：
# python parse_binlog_time.py 'binlog/binlog.[0-9]*' binlog pattern: binlog/binlog.[0-9]* binlog/binlog.000001 1686640790 2023-06-13 15:19:50 binlog/binlog.000002 1686647377 2023-06-13 17:09:37 binlog/binlog.000003 1686647391 2023-06-13 17:09:51 ...... binlog/binlog.000020 1687682137 2023-06-25 16:35:37 binlog/binlog.000021 1687682646 2023-06-25 16:44:06 binlog/binlog.000022 1687683127 2023-06-25 16:52:07
我们的全量备份binlog位点是binlog.000020：
# cat xtrabackup_binlog_info binlog.000020 610 58224b02-09b7-11ee-90bd-fab81f64ee00:1-13191,7caa9a48-b325-11ed-8541-fab81f64ee00:1-27
假设我们希望将数据库恢复到2023-06-25 16:45:00，那么根据各个binlog的时间信息，我们需要恢复到binlog.000021，从该binlog中找到16:45:00对应的位点：
# mysqlbinlog --stop-datetime="2023-06-25 16:45:01" binlog/binlog.000021 | grep -A 1 "^# at" | tail -2 # at 340009 #230625 16:45:00 server id 23480 end_log_pos 340040 CRC32 0xa1841663 Xid = 88279
我们需要应用binlog.000021偏移量340040之前的binlog。
方法一、使用mysqlbinlog解析binlog并执行
从前面的步骤，我们得到了需要执行的binlog：
binlog开始位点：binlog.000020，偏移量610。
binlog结束位点：binlog.000021，偏移量340040。
依次使用mysqlbinlog解析binlog，并发送给mysql执行。执行第一个binlog时指定参数start-position，执行最后一个binlog时，指定参数stop-position。
执行第一个binlog：
mysqlbinlog --start-position=610 binlog.000020 | mysql -uroot -h127.0.0.1 -P6380 -uroot -pabc123
执行中间的binlog（本测试案例中只有2个binlog）。不需要带start-position和stop-position参数。
执行最后一个binlog：
mysqlbinlog --stop-position=340040 binlog.000021 | mysql -uroot -h127.0.0.1 -P6380 -uroot -pabc123
binlog执行完成后，校验一下数据。
