延迟复制
MySQL 5.7支持延迟复制，这样从服务器就会故意滞后于主服务器至少一段指定的时间。缺省值为0秒。使用MASTER_DELAY选项更改MASTER TO将 延迟设置为N秒：
CHANGE MASTER TO MASTER_DELAY = N;
从主机接收到的事件至少要比它在主机上的执行晚N秒才执行。例外情况是，格式描述事件或日志文件旋转事件没有延迟，它们只影响SQL线程的 内部状态。
延迟复制可用于以下几个目的：
.防止用户在主机上出错。DBA可以将延迟的从服务器回滚到灾难发生之前的时间
.测试系统在出现延迟时的行为。例如，在应用程序中，延迟可能是由从属服务器上的沉重负载引起的。但是，很难生成这种负载级别。延迟复 制可以模拟延迟，而不必模拟负载。它还可以用于调试与滞后从机相关的条件。
.检查数据库很久以前的样子，而无需重新加载备份。例如，如果延迟是一周，DBA需要查看数据库在最后几天的开发之前是什么样子，那么可以 检查延迟的从属服务器。
START SLAVE和STOP SLAVE立即生效并忽略任何延迟。RESET SLAVE将延迟复位为0。
SHOW SLAVE STATUS有三个字段提供延迟信息：
.SQL_Delay: 一个非负整数，表示从服务器必须滞后于主服务器的秒数
.SQL_Remaining_Delay: 当Slave_SQL_Running_State为Waiting until MASTER_DELAY seconds after master executed event，该字段包含一 个整数，表示延迟剩余的秒数。在其他时候，该字段为NULL。
.Slave_SQL_Running_State:指示SQL线程状态的字符串（类似于Slave_IO_State）。该值与SHOW PROCESSLIST显示的SQL线程的State值相同。
mysql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.138.130.243
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: binlog.000010
Read_Master_Log_Pos: 2099
Relay_Log_File: localhost-relay-bin.000003
Relay_Log_Pos: 877
Relay_Master_Log_File: binlog.000010
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: 2099
Relay_Log_Space: 1689
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: 2
Master_UUID: 684e1f7d-6f47-11ef-a6d5-005056a3a162
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
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: 684e1f7d-6f47-11ef-a6d5-005056a3a162:9-11,
bb8b95d1-6f47-11ef-9592-005056a390e6:9-10,
ca006ef3-6f46-11ef-8203-005056b9a980:1-5
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.01 sec)
mysql> SHOW PROCESSLIST\G
*************************** 1. row ***************************
Id: 38
User: system user
Host:
db: NULL
Command: Connect
Time: 2046
State: Slave has read all relay log; waiting for more updates
Info: NULL
*************************** 2. row ***************************
Id: 39
User: root
Host: localhost
db: jycs
Command: Query
Time: 0
State: starting
Info: SHOW PROCESSLIST
*************************** 3. row ***************************
Id: 40
User: system user
Host:
db: NULL
Command: Connect
Time: 3682
State: Waiting for master to send event
Info: NULL
3 rows in set (0.00 sec)
当从SQL线程在执行事件之前等待延迟结束时，SHOW PROCESSLIST将其State值显示为waiting until MASTER_DELAY seconds after master executed event。
在从服务器上设置延迟复制时间为60秒:
mysql> stop slave;
Query OK, 0 rows affected (0.01 sec)
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
mysql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
Slave_IO_State: Connecting to master
Master_Host: 10.138.130.243
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: binlog.000010
Read_Master_Log_Pos: 2659
Relay_Log_File: localhost-relay-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: binlog.000010
Slave_IO_Running: Connecting
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: 2659
Relay_Log_Space: 154
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: NULL
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: 2
Master_UUID: 684e1f7d-6f47-11ef-a6d5-005056a3a162
Master_Info_File: mysql.slave_master_info
SQL_Delay: 60 --延迟复制时间为60秒
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
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: 684e1f7d-6f47-11ef-a6d5-005056a3a162:9-11,
bb8b95d1-6f47-11ef-9592-005056a390e6:9-10,
ca006ef3-6f46-11ef-8203-005056b9a980:1-5
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
主服务器上插入数据
mysql> select now(); +---------------------+ | now() | +---------------------+ | 2024-12-20 17:22:37 | +---------------------+ 1 row in set (0.00 sec) mysql> insert into t_cs values(6,'ij'); Query OK, 1 row affected (0.03 sec) mysql> select now(); +---------------------+ | now() | +---------------------+ | 2024-12-20 17:22:56 | +---------------------+ 1 row in set (0.00 sec)
从服务器上验证延迟复制
mysql> select now(); +---------------------+ | now() | +---------------------+ | 2024-12-20 17:14:31 | +---------------------+ 1 row in set (0.00 sec) mysql> select * from t_cs; +------+------+ | id | name | +------+------+ | 1 | cs | | 2 | ab | | 3 | cd | | 4 | ef | | 5 | gh | +------+------+ 5 rows in set (0.00 sec) mysql> select now(); +---------------------+ | now() | +---------------------+ | 2024-12-20 17:14:37 | +---------------------+ 1 row in set (0.00 sec) mysql> select * from t_cs; +------+------+ | id | name | +------+------+ | 1 | cs | | 2 | ab | | 3 | cd | | 4 | ef | | 5 | gh | +------+------+ 5 rows in set (0.00 sec) mysql> select now(); +---------------------+ | now() | +---------------------+ | 2024-12-20 17:14:51 | +---------------------+ 1 row in set (0.00 sec)
在延迟1分钟后，主服务器上插入的新记录被复制到从服务器
mysql> select * from t_cs; +------+------+ | id | name | +------+------+ | 1 | cs | | 2 | ab | | 3 | cd | | 4 | ef | | 5 | gh | | 6 | ij | +------+------+ 6 rows in set (0.01 sec)