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

一、延迟从库的作用

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

• · 用户在主库上误操作，通过延迟从库中的数据来恢复被误删除的数据
• · 经常要求查询时间比较久远的数据进行比对，不需要重新加载备份数据。比如要查看一周前的数据，就配置一个从库延迟一周 

二、延迟从库配置

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

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、查看延迟从库relaylog位置点（假设position=482），这个点可以作为数据库恢复的起点
show slave status \G  #查看relay_log_file和relay_log_pos信息
3、查看延迟从库relaylog中误操作语句的位置点（假设position=1402），通常是在最后一个relaylog中，这里需要注意Position信息只需要看左边一列的，它才是relaylog中的位置，而relaylog中的End_log_pos是主库中的位置，不需要关注
mysql > show  relaylog events in 'relay-bin.000005';
4、生成用于恢复数据的SQL，这步操作将把从库停止SQL_THREAD之后和误操作之前的SQL进行导出
mysqlbinlog --start-position=482  --stop-position=1402 /data/mysqllog/relay-bin.00005 > /tmp/relay.sql
5、需要注意补齐上一步之后产生的数据