MySQL 主从复制过滤新增库表过滤方案

技术背景

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

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

技术方案

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

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

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

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

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

操作流程

流程概述

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

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

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

1. 2024-10-01 00:00:00 从库同步到 GTID:1-100
2. 2024-10-01 00:01:00 主库开始备份，备份记录的 GTID:1-110
3. 2024-10-01 00:02:00 从库同步到 GTID:1-150

4. 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:1-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.%
...

6 . 从库-启动复制线程

START SLAVE;
SHOW SLAVE STATUS\G

补充说明

备份方式

文档中用的 mysqldump 做的备份恢复示例，实际用 Xtrabackup 也可，只是 Xtrabackup 在恢复时需要结合可传输表空间特性做恢复。

表级别复制过滤

文档中只是演示了库级的复制过滤添加，表级别的操作方式基本相同，只是针对不同库下不同表，不能同时备份（mysqldump 不支持）。