第22期 MySQL延迟复制

  • pxbibm

    2024-12-26 10:07:56
  • MySQL
  • 原创

1.MySQL 延迟复制简介

在网络通信正常的情况下，通常 MySQL 复制很快，即时很快，但 MySQL 缺省的复制也会多少存在延迟的，并且用户无法缩短延迟时间，用户无法控制。另一方面，有时却需要特意增加复制的延迟。假设有这样一种场景，用户在主库上误删除了一个表，并且该操作很快被复制到从库。当用户发现这个错误时，从库早就完成了该事件重放，重放后从库上也把这个表删除了。此时主库、从库都没有那个被误删的表了。如何恢复？如果有备份，可以幸运地从备份中进行恢复，但是有时会丢失数据的，丢失的数据量取决于备份的新旧和从备份时间点到表被删除时间点之间该表上数据的变化量。如果没有备份呢？这种情况下，延迟复制或许可以帮上忙，作为一种恢复数据的备选方案。如果在发现问题时，从库还没有来得及重放相应的中继日志，那么就有机会在从库获得该表，继而进行恢复。

这里忽略一些其它数据恢复方案，比如可以在 MySQL 上，通过实现解析相应的二进制日志事件，根据日志事件生成反向的 SQL 语句，来进行恢复，这些程序一般都是个人自己开发的，程序的稳定性，可靠性比较差。

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秒才在从库上执行。每个事务发生延迟，实际延迟仅强制在 gtid_log_event或 anonymous_gtid_log_event事件上。二进制日志中的每个 GTID 事务始终都以 Gtid_log_event开头，匿名事务没有分配 GTID，MySQL 确保日志中的每个匿名事务都以 Anonymous_gtid_log_event开头。对于事务中的其它事件，不会对它们施加任何等待时间，而是立即执行。

2 实验

例如，下面将实验环境中将从库设置为延迟 60 秒复制：

# 首先你要一个主从复制的环境
# 从库上执行
MySQL [(none)]> change master to master_delay = 60;
ERROR 3085 (HY000): This operation cannot be performed with a running slave sql thread; run STOP SLAVE SQL_THREAD FOR CHANNEL '' first.
以上命令直接执行会报错，需要先停掉sql_thread线程，在开启延迟复制。
如下面的操作
MySQL [(none)]> stop slave sql_thread;
Query OK, 0 rows affected (0.00 sec)
MySQL [(none)]> change master to master_delay = 60;
Query OK, 0 rows affected (0.01 sec)
 
MySQL [(none)]> start slave sql_thread;
Query OK, 0 rows affected (0.00 sec)

注意：联机设置延迟复制时，需要先停止 sql_thread线程。

现在主库执行一个事务，观察从库的变化：

主库：
MySQL [(none)]> create table pxb.t1(id int);
Query OK, 0 rows affected (0.01 sec)
观察从库：
MySQL [(none)]>desc pxb.t1;
ERROR 1146 (42S02): Table 'pxb.t1' doesn't exist
或者使用show create table命令
MySQL [(none)]> show create table pxb.t1;
ERROR 1146 (42S02): Table 'pxb.t1' doesn't exist
MySQL [(none)]> 
过60秒之后再观察.................
MySQL [(none)]> desc pxb.t1;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(11) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
1 row in set (0.00 sec)

主库上执行完成创建表的ddl语句， 60 秒后，从库上才出现该表。

观察主从延迟的设置

1.从库上 performance_schema模式下的 replication_applier_configuration.desired_delay表列显示使用 master_delay选项配置的延迟。

2.从库上 performance_schema模式下的 replication_applier_status.remaining_delay表列显示剩余的延迟秒数。

从库上执行：
MySQL [(none)]> select desired_delay from performance_schema.replication_applier_configuration;
+---------------+
| desired_delay |
+---------------+
|            60 |
+---------------+
1 row in set (0.00 sec) 
MySQL [(none)]> select remaining_delay from performance_schema.replication_applier_status;
+-----------------+
| remaining_delay |
+-----------------+
|            NULL |
+-----------------+
1 row in set (0.00 sec) 
mysql> 
我们在主库上删除表pxb.t1
MySQL [(none)]> drop table pxb.t1;
Query OK, 0 rows affected (0.02 sec) 
mysql>  
这时，我们快速切换到从库上，在从库上快速执行命令
可以多执行几次，我们发现秒数在一点一点减少，直到减少为null时，
表pxb.t1就删除了。
MySQL [(none)]> select remaining_delay from performance_schema.replication_applier_status;
+-----------------+
| remaining_delay |
+-----------------+
|              50 |
+-----------------+
1 row in set (0.00 sec) 
MySQL [(none)]> select remaining_delay from performance_schema.replication_applier_status;
+-----------------+
| remaining_delay |
+-----------------+
|              31 |
+-----------------+
1 row in set (0.00 sec) 
MySQL [(none)]> desc pxb.t1;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(11) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
1 row in set (0.00 sec)
MySQL [(none)]> select remaining_delay from performance_schema.replication_applier_status;
+-----------------+
| remaining_delay |
+-----------------+
|              15 |
+-----------------+
1 row in set (0.00 sec) 
MySQL [(none)]> select remaining_delay from performance_schema.replication_applier_status;
+-----------------+
| remaining_delay |
+-----------------+
|            NULL |
+-----------------+
1 row in set (0.00 sec)
MySQL [(none)]> desc pxb.t1;
ERROR 1146 (42S02): Table 'test.t3' doesn't exist


2 如何使用延迟复制恢复误删的表

下面演示一个恢复案例

在主库上创建测试数据
MySQL [(none)]> create database pxb;
Query OK, 1 row affected (0.01 sec)
MySQL [(none)]> create table pxb.t1(id int);
Query OK, 0 rows affected (0.02 sec)
MySQL [(none)]> insert into pxb.t1 values(1),(2),(3);
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0
MySQL [(none)]> select * from pxb.t1;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
+------+
3 rows in set (0.00 sec)
在从库上检查是否同步过来了。
MySQL [(none)]> select * from pxb.t1;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
+------+
3 rows in set (0.00 sec)
从库开启延迟复制
MySQL [(none)]> stop slave;
MySQL [(none)]> change master to master_delay=3600;
MySQL [(none)]> start slave;
延迟3600秒，既1小时。
主库上新增一条记录，并删除pxb.t1 表。
MySQL [(none)]> insert into pxb.t1 values(4);
Query OK, 1 row affected (0.01 sec)
MySQL [(none)]> drop table pxb.t1;
Query OK, 0 rows affected (0.02 sec)
此时查询从库，会发现t1表依然存在，还是只有3条记录。
MySQL [(none)]> select * from pxb.t1;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
+------+
3 rows in set (0.00 sec)
在主库中，查看drop操作在binlog 中的位置点信息。
MySQL [(none)]> show master status;
+------------------+----------+--------------+------------------+------------------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                              |
+------------------+----------+--------------+------------------+------------------------------------------------+
| mysql-bin.362784 |     2323 |              |                  | 55bbb9d6-293a-11eb-9b8b-fa163ec9502d:1-1335803 |
+------------------+----------+--------------+------------------+------------------------------------------------+
1 row in set (0.00 sec)
MySQL [(none)]> 
MySQL [(none)]> pager grep -iB 5 drop
PAGER set to 'grep -iB 5 drop'
MySQL [(none)]> show binlog events in 'mysql-bin.362784';
| mysql-bin.362784 | 1968 | Rows_query     | 1529910326 |        2020 | # insert into pxb.t1 values(4)                                                                                                                                                                                                                                                                                                                                       |
| mysql-bin.362784 | 2020 | Table_map      | 1529910326 |        2065 | table_id: 283 (pxb.t1)                                                                                                                                                                                                                                                                                                                                               |
| mysql-bin.362784 | 2065 | Write_rows_v1  | 1529910326 |        2111 | table_id: 283 flags: STMT_END_F                                                                                                                                                                                                                                                                                                                                      |
| mysql-bin.362784 | 2111 | Xid            | 1529910326 |        2142 | COMMIT /* xid=3245255102 */                                                                                                                                                                                                                                                                                                                                          |
| mysql-bin.362784 | 2142 | Gtid           | 1529910326 |        2207 | SET @@SESSION.GTID_NEXT= '55bbb9d6-293a-11eb-9b8b-fa163ec9502d:1335803'                                                                                                                                                                                                                                                                                              |
| mysql-bin.362784 | 2207 | Query          | 1529910326 |        2323 | DROP TABLE `pxb`.`t1` /* generated by server */                                                                                                                                                                                                                                                                                                                      |
32 rows in set (0.00 sec)
MySQL [(none)]> 
由于binlog中的事务较多，直接查看不太方便，这里我们使用了grep命令进行过滤，
命令行中的-i 表示忽略大小写，-B 5会答应匹配文本的前5行。
基于binlog的输出可以看到，drop操作前一个事务的结束位置点为2142
从库恢复到指定位置点2142。
MySQL [(none)]> stop slave
MySQL [(none)]> change master to master_delay=0;
MySQL [(none)]> start slave until master_log_file='mysql-bin.362784',master_log_pos=2142;
检查show slave status 的输出，确保SQL线程已经执行到该位置点。
确定依据，
Relay_Master_Log_File==Until_Log_File并且Exec_Master_log_pos==Until_log_pos并且Slave_SQL_Running=='NO'
如下面的输出。
MySQL [(none)]>  show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.10.23.37
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.362784
          Read_Master_Log_Pos: 2142
               Relay_Log_File: mysql-relay-bin.362711
                Relay_Log_Pos: 423
        Relay_Master_Log_File: mysql-bin00000057
             Slave_IO_Running: Yes
            Slave_SQL_Running: No
              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: 2142
              Relay_Log_Space: 61
              Until_Condition: None
               Until_Log_File: mysql-bin.362784
                Until_Log_Pos: 2142
           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: 0
                  Master_UUID: d056e231-616f-11ec-89e7-fa163e153c3c
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read 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: 
                Auto_Position: 0
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
1 row in set (0.00 sec)
再查看pxb.t1表的内容
MySQL [(none)]> select * from pxb.t1;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
+------+
4 rows in set (0.00 sec)
新增的记录已经复制过来了。此时我们可以导出t1表，并将其导入到主库上。


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。


请使用浏览器的分享功能分享到微信等