mysql5.6开始支持延时复制，默认master_delay为0秒，
CHANGE MASTER TO MASTER_DELAY = N;
表示延时N秒
原理：延时复制的本质是sql_thread需要等待延时时间之后才能执行。
延时复制适用场景：
（1）防止主库误操作，在复制同步之前，可以停止同步
（2）用作测试
（3）用来检查数据库以前的数据
reset slave会把master_delay清零；
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
|
创建一个延时复制：
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 |
+
----+------+
|
本文转自 Darren_Chen 51CTO博客，原文链接：http://blog.51cto.com/darrenmemos/1921702，如需转载请自行联系原作者
