还在用mysqlbinlog恢复binlog日志吗

MySQL 的binlog 日志除了用于主从同步外，也是数据恢复的重要手段，基于binlog日志的恢复方式称为Point-in-time recovery，可最大程度保障RPO。

当发生了误操作场景，通常会考虑全量备份+binlog日志恢复误删的数据，先进行全量备份的恢复，再使用binlog恢复增量部分。

利用mysqlbinlog筛选解析全量备份点之后到故障点之前的日志重定向到mysql，但单线程的恢复方式如果日志量较大恢复效率一定不佳。是否有更快的恢复方式？

time mysqlbinlog mysql-bin.* | mysql

如果利用SQL线程回放binlog，相对mysqlbinlog恢复效率会如何？

实验步骤

---

我们部署一个MySQL实例，为了更快的验证，把binlog文件设置小一点。为了能在原节点回放binlog日志，需增加另外三个参数，使用sysbench压测生成一些binlog文件。

dbdeployer deploy single 5.7.25 -c max_binlog_size=536870912 -c replicate-same-server-id=1 -c skip-slave-start -c log_slave_updates=off
./use -e "set global max_binlog_size=512*1024*1024;"
sysbench oltp_write_only  --table-size=100000 --tables=10 --mysql-db=test --mysql-port=5725 --mysql-host=127.0.0.1 --mysql-user=msandbox --mysql-password=msandbox --threads=100 --time=1000 --report-interval=1 --events=0 prepare
sysbench oltp_write_only  --table-size=100000 --tables=10 --mysql-db=test --mysql-port=5725 --mysql-host=127.0.0.1 --mysql-user=msandbox --mysql-password=msandbox --threads=100 --time=600 --report-interval=1 --events=0 run

校验数据用于恢复后的对比。

for i in `seq 1 10`; do ./use test -BNe "checksum table sbtest$i"; done

备份一下binlog日志，并清空数据。

mkdir binlog && mv data/mysql-bin.0* ./binlog
./use -e "drop database test;create database test;reset master;stop slave;reset slave all;"

这里我们生成了两个binlog文件。

hongbin@MBP ~/s/msb_5_7_25> ll binlog/
total 2131000
-rw-r-----  1 hongbin  staff   512M  4 30 18:36 mysql-bin.000001
-rw-r-----  1 hongbin  staff   512M  4 30 18:38 mysql-bin.000002

把binlog日志更名为relay log，拷贝到数据目录下。

for i in `seq -f "%06g" 1 2`; do cp binlog/mysql-bin.$i data/MBP-relay-bin.$i; done 
cd data && ls  ./MBP-relay-bin.0* > ./MBP-relay-bin.index

我们只利用SQL线程进行回放，并不需要IO线程工作，所以指定一个未知的master_host，指定SQL线程回放relay log文件和位置，启用多线程回放，启动SQL线程，记录起始和结束时间，观测耗时。

./use -e "CHANGE MASTER TO RELAY_LOG_FILE='MBP-relay-bin.000001',RELAY_LOG_POS=1, MASTER_HOST='nohost';"
./use -e "SET GLOBAL SLAVE_PARALLEL_TYPE='LOGICAL_CLOCK';SET GLOBAL SLAVE_PARALLEL_WORKERS=4;
./use -e "START SLAVE SQL_THREAD;select sysdate();" |tee start_time
while true; do sleep 1;  ./use -Be 'show slave statusG' | awk '/Slave has read all relay log/ {print | "date"}'; done

如果指定了until从句，则无法使用多线程回放。

./use -e "START SLAVE SQL_THREAD UNTIL RELAY_LOG_FILE = 'MBP-relay-bin.000002', RELAY_LOG_POS = 3625;"

基于这种情况，如果有多个binlog文件需要回放，除最后一个binlog文件之外，并行回放前面一批binlog，最后一个binlog文件顺序回放至指定until位置。

测试结果

---

使用自身的binlog回放需要禁用log_slave_udpate，不会写binlog文件，所以sync_binlog参数不影响。

	mysqlbinlog	单线程SQL回放	多线程SQL回放
sync_binlog=1	154.18 m	N/A	N/A
sync_binlog=0	7m 58s	3 min	1m 16s

无论是单线程还是多线程回放，都比使用mysqlbinlog回放效率高。

注意事项

---

若启用多线程SQL回放，可能会遇到类似"Transaction's sequence number is inconsistent with that of a preceding one: sequence_number (1) <= previous sequence_number (207922)"报错。

 

last_committed和sequence_number是多线程SQL的参考依据。

 

当binlog达到max_binlog_size会在binlog文件尾写入Rotate事件，生成新的binlog文件，last_committed和sequence_number从新计数。

 

IO线程读到binlog的Rotate事件，在relay log文件头写入Rotate事件，新的relay log文件last_committed和sequence_number与binlog保持一致，从新计数。

 

如果在relay log达到max_binlog_size或max_relay_log_size时，IO线程还未读到binlog的Rotate事件，新的relay log会继续之前的last_committed和sequence_number，relay log文件头也不会写入Rotate事件。

该事务对应binlog文件的某位置

 

在SQL线程回放多个binlog文件时，binlog文件头是没有Rotate binlog 事件的，但last_committed和sequence_number却是从新计数的，所以在读到后续的binlog时，就会产生"Transaction's sequence number is inconsistent with that of a preceding one: sequence_number (1) <= previous sequence_number (207922)"这样的报错。

恢复方法可重新change master 指定新的relay log文件名。

 

转自https://www.modb.pro/db/61751