mysql延迟复制_MySQL延迟复制

备注:测试数据库版本为MySQL 8.0

这个blog我们来聊聊MySQL 延迟复制

概述

MySQL的复制一般都很快，虽然有时候因为 网络原因、大事务等原因造成延迟，但是这个无法人为控制。

生产中可能会存在主库误操作，导致数据被删除了，Oracle有flashback技术，MySQL官方目前没有退出对应的flashback技术。

此时，我们可以通过设置延迟复制，设置从库比主库慢半个小时，这个时候就可以从从库进行数据恢复到主库了。

MySQL支持延迟复制，以便从库故意执行比主库晚至少在指定时间间隔的事务。在MySQL 8.0中，延迟复制的方法取决于两个时间戳：immediate_commit_timestamp和original_commit_timestamp。如果复制拓扑中的所有服务器都运行MySQL 8.0.1或更高版本，则使用这些时间戳测量延迟复制。如果从库未使用这些时间戳，则执行MySQL 5.7的延迟复制。

复制延迟默认为0秒。使用CHANGE MASTER TO MASTER_DELAY = N语句将延迟设置为N秒。从主库接收的事务比主库上的提交至少晚N秒才在从库上执行。每个事务发生延迟(不是以前MySQL版本中的事件)，实际延迟仅强制在gtid_log_event或anonymous_gtid_log_event事件上。二进制日志中的每个GTID事务始终都以Gtid_log_event开头，匿名事务没有分配GTID，MySQL确保日志中的每个匿名事务都以Anonymous_gtid_log_event开头。对于事务中的其它事件，不会对它们施加任何等待时间，而是立即执行。注意，START SLAVE和STOP SLAVE立即生效并忽略任何延迟，RESET SLAVE将延迟重置为0。

一.延迟复制测试

1.环境要求

服务器类别

IP

主库

10.31.1.112

从库

10.31.1.113

此时已经搭建了异步复制的主从

2.开启延迟复制

stop slave sql_thread;

CHANGE MASTER TO MASTER_DELAY = 300;

start slave sql_thread;

主库

create table t6 as select * from t4;

select * from t6;

mysql> create table t6 as select * from t4;

Query OK, 1 row affected (0.02 sec)

Records: 1 Duplicates: 0 Warnings: 0

mysql>

mysql> select * from t6;

+------+------+

| id | name |

+------+------+

| 1 | aaa |

+------+------+

1 row in set (0.00 sec)

从库

mysql> select remaining_delay from performance_schema.replication_applier_status;

+-----------------+

| remaining_delay |

+-----------------+

| 300 |

+-----------------+

1 row in set (0.01 sec)

mysql> select current_timestamp;

+---------------------+

| current_timestamp |

+---------------------+

| 2020-07-29 11:00:44 |

+---------------------+

1 row in set (0.00 sec)

mysql> select * from t6;

ERROR 1146 (42S02): Table 'test.t6' doesn't exist

mysql>

mysql> select current_timestamp;

+---------------------+

| current_timestamp |

+---------------------+

| 2020-07-29 11:03:30 |

+---------------------+

1 row in set (0.00 sec)

mysql> select * from t6;

ERROR 1146 (42S02): Table 'test.t6' doesn't exist

mysql>

mysql> select current_timestamp;

+---------------------+

| current_timestamp |

+---------------------+

| 2020-07-29 11:05:29 |

+---------------------+

1 row in set (0.00 sec)

mysql> select * from t6;

+------+------+

| id | name |

+------+------+

| 1 | aaa |

+------+------+

1 row in set (0.00 sec)

mysql> select remaining_delay from performance_schema.replication_applier_status;

+-----------------+

| remaining_delay |

+-----------------+

| NULL |

+-----------------+

1 row in set (0.01 sec)

3.监控延迟复制

在MySQL 8之前的老版本中，监控复制的延迟(滞后)最常用的方法之一是依赖于show slave status输出中的seconds_behind_master字段。但是，当使用比传统主从复制更复杂的复制拓扑，例如组复制时，此度量标准不再适用。MySQL 8中添加的immediate_commit_timestamp和original_commit_timestamp可提供有关复制延迟的更精细的信息。监控支持这些时间戳的复制延迟的推荐方法是使用以下performance_schema模式中的表。

replication_connection_status：与主服务器连接的当前状态，提供有关连接线程排队到中继日志中的最后和当前事务的信息。

replication_applier_status_by_coordinator：协调器线程的当前状态，仅在使用多线程复制时显示该信息，提供有关协调器线程缓冲到工作队列的最后一个事务的信息，以及当前正在缓冲的事务。

replication_applier_status_by_worker：应用从主服务器接收事务的线程的当前状态，提供有关应用程序线程或使用多线程复制时每个工作线程应用的事务信息。

使用这些表，可以监控相应线程处理的最后一个事务以及该线程当前正在处理的事务的信息，包括：

事务的GTID。

从库中继日志中检索的事务的original_commit_timestamp和immediate_commit_timestamp。

线程开始处理事务的时间。

对于上次处理的事务，线程完成处理它的时间。

除Performance Schema表之外，show slave status的输出还有三个字段与延迟复制有关：

SQL_Delay：非负整数，表示使用CHANGE MASTER TO MASTER_DELAY = N配置的复制延迟，以秒为单位。与performance_schema.replication_applier_configuration.desired_delay值相同。

SQL_Remaining_Delay：当Slave_SQL_Running_State等待主执行事件后的MASTER_DELAY秒时，该字段包含一个整数，表示延迟剩余的秒数。在它他时候，此字段为NULL。与performance_schema.replication_applier_status.remaining_delay值相同。

Slave_SQL_Running_State：一个字符串，指示SQL线程的状态(类似于Slave_IO_State)。该值与SHOW PROCESSLIST显示的SQL线程的State值相同。

当从库的SQL线程在执行事件之前等待延迟时，SHOW PROCESSLIST将其状态值显示为：Waiting until MASTER_DELAY seconds after master executed event。

参考文献: