MySQL的延迟复制

 一、什么是延迟复制

延迟复制就是将 Slave节点与Master节点保持指定时间的复制间隔。

所谓的延迟，只是对SQL_Thread的线程的延迟。IO_Thread 主库发生的任何操作的日志都会同步到 slave，也就是说 IO_Thread 线程和主库是没有延迟的。只是 SQL_Thread 与主库有延迟。只是执行时间延迟，而不是读取 binlog 时间延迟。

MySQL （需5.6以上版本）延迟复制配置，通过设置Slave上的MASTER TO MASTER_DELAY参数实现：

CHANGE MASTER TO MASTER_DELAY = N；
复制

N为多少秒，该语句设置从数据库延时N秒后，再与主数据库进行数据同步复制。

二、延迟复制好处

（1）利用延迟复制做误操作恢复

提供 Master 节点意外错误的快速恢复机制，若 Master 节点出现误改、误删等操作，造成数据丢失的情况，由于Slave节点有延迟因素的存在，DBA可以通过 Slave 节点仍然保存的数据，快速地恢复。不过通常延迟时间不会太长，如果出现误操作，而且Slave节点恰好还没有应用这些Master传来的操作，在延迟时间范围内进行恢复即可，可先直接备份误操作的表（延迟复制期间没有操作过这个表）或者关闭slave复制，恢复到误操作前的时间再导出表（详见五）。

（2）把延迟从库作为专用的备份节点，这样可以设置延迟的时间长点。虽然有一定的延迟，但并不影响利用该节点作为备份角色，也不影响生产节点数据库。

（3）测试复制环境出现延迟时，评估对系统应用可能造成的影响。

（4）也还可以把延迟从库当做一些问题、案例研究的对象。

三、开启和关闭延迟复制

开启的具体操作：

登陆到Slave数据库服务器

mysql>stop slave;
mysql>CHANGE MASTER TO MASTER_DELAY = 1200;
mysql>start slave;
mysql>show slave status \G;
                   SQL_Delay: 1200
         SQL_Remaining_Delay: NULL
     Slave_SQL_Running_State: Slave has read all relay log; waiting for theslave I/O thread to update it
复制

查看SQL_Delay的值为1200，表示设置成功。

设置后，Slave 节点接收到 Master 节点生成的二进制日志，不会马上应用，而是等待，直到时间符合设定的延迟条件后才开始应用。

关闭的具体操作：

mysql>stop slave;
mysql>CHANGE MASTER TO MASTER_DELAY = 0;
mysql>start slave;
mysql>show slave status \G;
复制

注：从 MySQL 5.7 起，修改 MASTER_DELAY 选项可以在线立即生效，而无需重启 Slave 线程。

四、延迟复制状态查看

Slave 开通延迟复制后，Slave 节点应用状态如何，我们可以通过 SHOW SLAVE STATUS\G 查看以下 3 个状态值即可。

·SQL_Delay：显示当前设定的延迟时间，以秒为单位。

·SQL_Remaining_Delay: 当 Slave_SQL_Running_State 列的状态是“Waiting until MASTER_DELAY seconds after master executed event''时，本列显示的值就是距离延迟阈值的时间，也就是说还有多长时间才能开始应用，否则本列值应该是 NULL。复制完成后没有可复制的 events 时，这个值就是 NULL。

·Slave_SQL_Running_State: 该值显示的是当前 SQL_THREAD 的状态；

该值一般会有两种情况：

当 SQL_THREAD 没有处于延迟等待阶段时显示：Slave has read all relay log; waiting formore updates。

当SQL_THREAD 处于延迟等待阶段，SHOW SLAVE STATUS\G 或者 SHOW PROCESSLIST 显示该进程的状态将会是：Waiting until MASTER_DELAY seconds aftermaster executed event。

Master 上执行了建库操作后，查看 slave 状态

mysql> show slave status\G;
                   SQL_Delay: 1200
         SQL_Remaining_Delay: 1157
     Slave_SQL_Running_State: Waiting until MASTER_DELAY seconds after masterexecuted event
复制

设置的是1200秒延迟复制，master_delay=1200。所以上面显示 SQL_Delay：1200

SQL_Remaining_Delay: 1157 ，表示还有1157秒后才可以应用Master上执行的语句。

复制完成后 slave 状态：

mysql> show slave status\G;
                   SQL_Delay: 1200
         SQL_Remaining_Delay: NULL
     Slave_SQL_Running_State: Slave has read all relay log; waiting for theslave I/O thread to update it
复制

五、延迟复制恢复数据方法和步骤

（1）主库出现误操作后，首先我们把 sql_thread 线程停止执行，并把延迟复制 master_delay 这个值改为 0，表示取消延迟复制；

mysql> stop slave sql_thread;
mysql> change master to master_delay=0;
复制

（2）让 sql_thread 执行到某个时间点停止；

start slave  sql_thread until master_log_file='xxxx',master_log_pos='xxxxx';
复制

注：

START SLAVE  UNTIL {

#1.直到指定的 GTID 位置停下    {SQL_BEFORE_GTIDS | SQL_AFTER_GTIDS} =gtid_set       

#2.直到指定的 Binlog 位置停下    |   MASTER_LOG_FILE = 'log_name', MASTER_LOG_POS = log_pos       

#3.直到指定的 Relay Log 位置停下    |   RELAY_LOG_FILE = 'log_name', RELAY_LOG_POS = log_pos

#4.直到 Slave上多个并行线程之前没有延迟差距了就停下    #因为多线程复制，不同线程的复制进度不一样，因此有差距    |  SQL_AFTER_MTS_GAPS  

}

（3）然后把 slave 库上恢复的数据使用 mysqldump 备份出来；

（4）把 mysqldump 备份的数据导入到 Master 库上；

数据恢复完成后恢复slave延迟时间等；