备注:测试数据库版本为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开头匿名事务没有分配GTIDMySQL确保日志中的每个匿名事务都以Anonymous_gtid_log_event开头。对于事务中的其它事件不会对它们施加任何等待时间而是立即执行。注意START SLAVE和STOP SLAVE立即生效并忽略任何延迟RESET SLAVE将延迟重置为0。

延迟复制测试

环境要求

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.10.102
                  Master_User: repll
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000019
          Read_Master_Log_Pos: 5280
               Relay_Log_File: hadoop103-relay-bin.000003
                Relay_Log_Pos: 1316
        Relay_Master_Log_File: mysql-bin.000019
             Slave_IO_Running: Yes 
            Slave_SQL_Running: Yes

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

开启延迟复制

从库

stop slave sql_thread;
CHANGE MASTER TO MASTER_DELAY = 3000;
start slave sql_thread;

主库

create table t6 as select * from dept;
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;
+--------+------------+----------+
| deptno | dname      | loc      |
+--------+------------+----------+
|     10 | ACCOUNTING | NEW YORK |
|     20 | RESEARCH   | DALLAS   |
|     30 | SALES      | CHICAGO  |
|     34 | faddsf     | fadsf    |
|     40 | OPERATIONS | BOSTON   |
+--------+------------+----------+
5 rows 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)

监控延迟复制

在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。
参考

MySQL 8.0 延迟复制_只是甲的博客-CSDN博客