技术分享 | MySQL 主从复制过滤新增库表过滤方案

爱可生开源社区

发布于 2024-12-03 21:59:45

11100

代码可运行

文章被收录于专栏：爱可生开源社区爱可生开源社区

运行总次数：0

代码可运行

作者：余振兴，爱可生 DBA 团队成员，热衷技术分享、编写技术文档。

爱可生开源社区出品，原创内容未经授权不得随意使用，转载请联系小编并注明来源。

1技术背景

如图所示，需要将原有的主从复制过滤的配置再新增几个库做同步，需要考虑一个效率最高的方式。

主从复制过滤

这里效率主要表示时间、空间成本，如何用更少的时间和空间完成配置变化。

2技术方案

方案1：一并备份需要复制过滤的所有库

优点：操作简单，这个方案和常规的备份恢复没有区别，注意备份时只对指定库做备份即可。
缺点：如果已经在同步的数据库数据量过大的情况下，恢复成本较高，如上图所示，假设 DB1 和 DB2 都是几百 G 的大小，做一次备份的成本相对较高。

方案2：只备份新增的数据库

优点：只需要备份和恢复新增的数据库，备份时间和空间占用都相对较少。
缺点：操作相对方案1而言会繁琐一点，需要对处理流程和细节清晰理解。

本文主要基于【方案 2】展开实际操作流程示例。

3操作流程

流程概述

从库-停止从库 SQL 回放线程
主库-备份 DB4、DB5 数据库
从库-使用 UNTIL SQL_AFTER_GTIDS 回放事务
从库-还原 DB4、DB5 的备份
从库-新增配置 DB4、DB5 的复制过滤策略
从库-启动复制线程

1. 从库-停止从库 SQL 回放线程

说明：这个步骤用于保持从库 IO 线程继续接收主库新增的日志，但不做回放。目的是为了避免在主库做新增库备份时，备份中记录的 GTID 在从库已经被应用过，导致备份还原时，数据重复。举例说明：

2024-10-01 00:00:00 从库同步到 GTID:1-100
2024-10-01 00:01:00 主库开始备份，备份记录的 GTID:1-110
2024-10-01 00:02:00 从库同步到 GTID:1-150
2020-10-01 00:10:00 从库做备份还原，重置 GTID 到备份记录的 GTID:1-110（异常）
- 实际从库已经同步到 GTID:1-150 以上，又被还原到了 GTID:1-110，启动复制报错

本步骤操作只需在从库执行如下命令即可

MySQL> STOP SLAVE SQL_THREAD;

2. 主库-备份 DB4、DB5 数据库

说明：本步骤仅对新增的复制过滤库做备份即可，需要注意的是如果用 mysqldump 做备份，不要加 set-gtid-purged=off，需要让 GTID 信息记录在备份文件中，用于还原时指定。

命令示例如下

shell> mysqldump -h127.0.0.1 -uzhenxing -P3306 -pxxxxx --hex-blob --triggers --routines --events --flush-logs --single-transaction --databases db4 db5  >db4_db5.sql

3. 从库-使用 UNTIL SQL_AFTER_GTIDS 回放事务

说明：本步骤是一个关键操作，需要对从库 SQL 线程指定 UNTIL SQL_AFTER_GTIDS 方式，将从库的事务回放追平到备份文件中记录的事务点，保持从库的回放 GTID 和备份中记录的一致，这样才能正常衔接。

命令示例如下

## 1. 获取备份文件中的 GTID 位点信息（从文件末尾获取即可）
shell> tail -n100 db4_db5.sql | grep GTID_PURGED
-- GTID state at the end of the backup
SET @@GLOBAL.GTID_PURGED='13fc4692-48d2-11ef-8a8f-02000aba382b:1-12756';

## 2. 从库设置回放结束点（注意：这里只需要设置指定GTID值12756，而不是备份中1-12756这个连续区间）
START SLAVE SQL_THREAD UNTIL SQL_AFTER_GTIDS='13fc4692-48d2-11ef-8a8f-02000aba382b:12756';

## 3. 持续观测SQL线程还原状态，直到Executed_Gtid_Set变为指定值12756
MySQL> show slave status\G
  -- 回放过程中的状态
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
      Replicate_Wild_Do_Table: db1.%,db2.%
              Until_Condition: SQL_AFTER_GTIDS
            Executed_Gtid_Set: 13fc4692-48d2-11ef-8a8f-02000aba382b:1-11200
            
MySQL> show slave status\G
    --  SQL_AFTER_GTIDS完成的状态：(Slave_SQL_Running为NO，且GTID结束点和备份文件中结束点一致)
             Slave_IO_Running: Yes
            Slave_SQL_Running: No
      Replicate_Wild_Do_Table: db1.%,db2.%
              Until_Condition: SQL_AFTER_GTIDS
            Executed_Gtid_Set: 13fc4692-48d2-11ef-8a8f-02000aba382b:1-12756

4. 从库-还原 DB4、DB5 的备份

说明：本步骤为实际备份还原过程，直接还原对应数据库即可，SET @@GLOBAL.GTID_PURGED 会在还原最后自动执行，还原后再做一轮确认。 注意：这里还原时最后的 SET @@GLOBAL.GTID_PURGED 可能会报错，可以忽略，是因为从库已经存在GTID_EXECUTED值，不影响。

命令示例如下

## 数据还原
shell> mysql  < db4_db5.sql

## 这个报错可以忽略，其他如果数据报错不可忽略
ERROR 1840 (HY000) at line 424: @@GLOBAL.GTID_PURGED can only be set when @@GLOBAL.GTID_EXECUTED is empty.

5. 从库-新增配置 DB4、DB5 的复制过滤策略

## 动态配置复制过滤策略
MySQL> CHANGE REPLICATION FILTER REPLICATE_WILD_DO_TABLE = ('db1.%','db2.%','db4.%','db5.%');

## 持久化配置到配置文件
shell> cat /data/mysql/3306/my.cnf.3306
...
## Replication Filter Rules
replicate_wild_do_table = db1.%
replicate_wild_do_table = db2.%
replicate_wild_do_table = db4.%
replicate_wild_do_table = db5.%
...