MySQL主从复制（7）延迟从库的设置与数据恢复

一、延迟从库的作用

MySQL从5.6开始支持延迟复制，通过人为配置延迟复制的目的：

· 用户在主库上误操作，通过延迟从库中的数据来恢复被误删除的数据，虽然通过闪回工具甚至手动找到相应误删的数据进行恢复会更快，但是对于truncate、drop等操作是无法使用闪回工具挽回的，相比使用备份来恢复，用延迟从库恢复的效率会更高

· 经常要求查询时间比较久远的数据进行比对，不需要重新加载备份数据。比如要查看一周前的数据，就配置一个从库延迟一周 

二、延迟从库配置

在需要配置为延迟从库的客户端上执行以下命令

mysql > stop slave sql_thread; #配置前需要停止SQL线程
mysql > change master to master_delay = 3600;  #SQL线程延迟1小时，IO线程不影响
mysql > start slave;mysql > show slave status \G  #查看SQL_Dely的值

三、使用延迟从库进行数据恢复
· 方法1
1、主库执行错误语句，错误的删除了一个数据

delete from student where id=5 ;

2、从库还处于延迟阶段，立即停止SQL_THREAD，防止从库执行了该语句

stop slave sql_thread;

3、使用mysqldump在从库导出正常数据，比如主库执行了一个delete语句

mysqldump -uroot -p school student --where 'id=5' --no-create-db -no-create-info > 5.sql

4、主库执行从库导出的SQL将该数据恢复，然后从库恢复SQL线程

· 方法2
1、如果主库执行了错误的语句，比如drop database linuxe，现在需要使用延迟从库来恢复数据。首先就是在延迟从库上停止SQL线程，让它不再继续执行relay中的SQL

stop slave sql_thread;

2、解析binlog以确定主库执行误删除时的GTID值，这里需要找到其上一个GTID

mysqlbinlog -vvv --base64-output=decode-rows mysql-bin.000001 > 01.sql

# 找到误操作的上一个GTID值

SET @@SESSION.GTID_NEXT= '4b4539dd-2fc1-11ec-949b-70b5e873a570:54230'/*!*/;                         #这里看到删除操作的GTID尾号是54320
# at 15959245
#211103 14:43:25 server id 33145160  end_log_pos 15959316       Query   thread_id=53817 exec_time=0     error_code=0
# at 15959377
#211103 14:43:25 server id 33145160  end_log_pos 15959436       Delete_rows: table id 270 flags: STMT_END_F
### DELETE FROM `test`.`t1`
### WHERE
###   @1=9998 /* INT meta=0 nullable=0 is_null=0 */
###   @2='983' /* VARSTRING(256) meta=256 nullable=1 is_null=0 */
###   @3='xAP9' /* VARSTRING(256) meta=256 nullable=1 is_null=0 */
###   @4='mQeN' /* VARSTRING(256) meta=256 nullable=1 is_null=0 */
###   @5='8Eu2' /* VARSTRING(256) meta=256 nullable=1 is_null=0 */
# at 15959436
#211103 14:43:25 server id 33145160  end_log_pos 15959463       Xid = 163705
COMMIT/*!*/;
# at 15959463

3、从库设置停止时间点，确保执行到删除操作前就不再继续同步

mysql> STOP SLAVE;
mysql> START REPLICA UNTIL SQL_AFTER_GTIDS='4b4539dd-2fc1-11ec-949b-70b5e873a570:2-54229';   #到54229就不再继续同步
mysql> START SLAVE;

4、数据恢复
当延迟从库同步到误删除前的操作后，就需要人工进行数据补齐了，可以把该表导出后再导入到主库，然后再恢复中间的binlog数据