mysql5.6开始支持延时复制，默认master_delay为0秒，
CHANGE MASTER TO MASTER_DELAY = N;
表示延时N秒
原理：延时复制的本质是sql_thread需要等待延时时间之后才能执行。
延时复制适用场景：
（1）防止主库误操作，在复制同步之前，可以停止同步；
（2）用作测试,不用模拟负载就可以实现主从延迟；
（3）用来检查数据库以前的数据，如延迟设置为1周，这样不需要备份恢复就可以看到比对一周以前的数据
（4）reset slave会把SQL_delay的值清零，并且还会把Master_Log_File等值清空，但是不影响复制；
创建一个延时复制： slave: root@localhost [testdb]>stop slave; root@localhost [testdb]>change master to master_delay=60; root@localhost [testdb]>start slave; root@localhost [testdb]>show slave status\G ...... SQL_Delay: 60 --延时时间 SQL_Remaining_Delay: 56 --剩余时间 Slave_SQL_Running_State: Waiting until MASTER_DELAY seconds after master executed event --等待延时 ...... master： root@localhost [testdb]>delete from t1 where c1=4; slave： root@localhost [testdb]>select * from t1; +----+------+ | c1 | c2 | +----+------+ | 1 | aaa | | 2 | bbb | | 3 | ccc | | 4 | ddd | +----+------+ root@localhost [testdb]>show processlist; +----+-------------+-----------+--------+---------+------+----------------------------------------------------------------+------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+-------------+-----------+--------+---------+------+----------------------------------------------------------------+------------------+ | 26 | root | localhost | testdb | Query | 0 | starting | show processlist | | 27 | system user | | NULL | Connect | 345 | Waiting for master to send event | NULL | | 28 | system user | | NULL | Connect | 10 | Waiting until MASTER_DELAY seconds after master executed event | NULL | +----+-------------+-----------+--------+---------+------+----------------------------------------------------------------+------------------+ #在没有达到60秒之前查看relay-log日志，发现已经写入relay-lo中，说明延时是阻塞SQL_thread线程 [root@Darren1 data]# mysqlbinlog -vv --base64-output=decode-rows relay-bin.000003 BEGIN /*!*/; # at 452 #170409 22:12:27 server id 330622 end_log_pos 5624 CRC32 0x86f7edf4 Table_map: `testdb`.`t1` mapped to number 147 # at 502 #170409 22:12:27 server id 330622 end_log_pos 5668 CRC32 0x697c52ed Delete_rows: table id 147 flags: STMT_END_F ### DELETE FROM `testdb`.`t1` ### WHERE ### @1=3 /* INT meta=0 nullable=0 is_null=0 */ ### @2='ccc' /* VARSTRING(30) meta=30 nullable=1 is_null=0 */ root@localhost [testdb]>select * from t1; +----+------+ | c1 | c2 | +----+------+ | 1 | aaa | | 2 | bbb | | 3 | ccc | +----+------+
