MySQL 5.6 已经支持延迟的流复制， 可设置备节点的延迟时间， 延迟复制是有意义的，例如防止主节点数据误删，查看数据库历史状态等。
重点：延迟复制实在原有的主从复制基础上、所以先要有主从复制的环境，然后配置延迟复制.
一、 语法：CHANGEMASTERTOMASTER_DELAY=30;---表示延迟30s
备注：设置备节点延迟的时间，单位秒。
二、 配置延迟复制
登录从节点服务器进行设置：root@localhost:mysql>stopslave;
QueryOK,0rowsaffected(0.21sec)
root@localhost:mysql>CHANGEMASTERTOMASTER_DELAY=30;
QueryOK,0rowsaffected(0.17sec)
root@localhost:mysql>startslave;
QueryOK,0rowsaffected(0.27sec)
三、查看从节点状态root@localhost:mysql>select*fromslave_relay_log_info\G
***************************1.row***************************
Number_of_lines:7
Relay_log_name:./db2-relay-bin.000007
Relay_log_pos:281
Master_log_name:bin-log.000041
Master_log_pos:361
Sql_delay:30
Number_of_workers:0
Id:1
1rowinset(0.00sec)
root@localhost:mysql>showslavestatus\G
***************************1.row***************************
Slave_IO_State:Waitingformastertosendevent
Master_Host:192.168.2.37
Master_User:rep1
Master_Port:3306
Connect_Retry:60
Master_Log_File:bin-log.000040
Read_Master_Log_Pos:604
Relay_Log_File:db2-relay-bin.000002
Relay_Log_Pos:281
Relay_Master_Log_File:bin-log.000040
Slave_IO_Running:Yes
Slave_SQL_Running:Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno:0
Last_Error:
Skip_Counter:0
Exec_Master_Log_Pos:604
Relay_Log_Space:452
Until_Condition:None
Until_Log_File:
Until_Log_Pos:0
Master_SSL_Allowed:No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master:0
Master_SSL_Verify_Server_Cert:No
Last_IO_Errno:0
Last_IO_Error:
Last_SQL_Errno:0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id:1
Master_UUID:0c130d47-22bb-11e4-aaaa-000c2986ac80
Master_Info_File:mysql.slave_master_info
SQL_Delay:30
SQL_Remaining_Delay:NULL
Slave_SQL_Running_State:Slavehasreadallrelaylog;waitingfortheslaveI/Othreadtoupdateit
Master_Retry_Count:86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position:0
1rowinset(0.00sec)
四、主节点创建表测试root@localhost:mysql>createtabletest_delay(idint4primarykey,
create_timedatetimedefaultcurrent_timestamp);
QueryOK,0rowsaffected(0.59sec)
root@localhost:mysql>insertintotest_delay(id)values(1);
QueryOK,1rowaffected(0.07sec)
五、备节点查询root@localhost:mysql>select*fromtest_delay;
Emptyset(0.00sec)
root@localhost:mysql>select*fromtest_delay;
Emptyset(0.00sec)
这时在备节点上还查不到表。
root@localhost:mysql>showslavestatus\G
***************************1.row***************************
Slave_IO_State:Waitingformastertosendevent
Master_Host:192.168.2.37
Master_User:rep1
Master_Port:3306
Connect_Retry:60
Master_Log_File:bin-log.000040
Read_Master_Log_Pos:1013
Relay_Log_File:db2-relay-bin.000002
Relay_Log_Pos:281
Relay_Master_Log_File:bin-log.000040
Slave_IO_Running:Yes
Slave_SQL_Running:Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno:0
Last_Error:
Skip_Counter:0
Exec_Master_Log_Pos:604
Relay_Log_Space:861
Until_Condition:None
Until_Log_File:
Until_Log_Pos:0
Master_SSL_Allowed:No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master:15
Master_SSL_Verify_Server_Cert:No
Last_IO_Errno:0
Last_IO_Error:
Last_SQL_Errno:0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id:1
Master_UUID:0c130d47-22bb-11e4-aaaa-000c2986ac80
Master_Info_File:mysql.slave_master_info
SQL_Delay:30
SQL_Remaining_Delay:14
Slave_SQL_Running_State:WaitinguntilMASTER_DELAYsecondsaftermasterexecutedevent
Master_Retry_Count:86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position:0
1rowinset(0.00sec)
root@localhost:mysql>select*fromtest_delay;
+----+---------------------+
|id|create_time|
+----+---------------------+
|1|-12-1214:38:47|
+----+---------------------+
1rowinset(0.00sec)
六、备注: 发现备节点在 30 秒后才能查到新建的表，这里有三个字段显示延迟的信息：
SQL_Delay: 显示已设置的主备延迟的时间，单位秒;
SQL_Remaining_Delay: 显示剩余的主备延迟时间，单位秒;
Slave_SQL_Running_State: 表示 SQL thread 状态;
