云扬のBlog

与你共享美好生活

【MySQL】主从复制过滤配置全解析：库与表级同步控制实战

在实际生产环境中，MySQL 主从复制架构几乎是标配，但默认的全量同步往往会带来不必要的资源消耗 —— 比如日志表、测试表的数据同步，不仅占用从库磁盘空间，还会增加网络传输压力。因此，精细化控制同步范围（只同步核心数据、忽略非必要表 / 库）就显得尤为重要。

最近刚好在优化公司的主从架构，整理了一套完整的过滤配置实验方案，从环境准备到六种核心场景测试，全程实操验证，今天就把这些干货分享给大家～

一、实验环境准备（基础铺垫不能少）

首先得有一个正常运行的 MySQL 主从架构（主从已实现基础同步），我这里用的是 MySQL 8.0.25 版本，操作系统是 CentOS 7。核心操作分为主库执行（创建测试数据）和从库执行（配置过滤规则与验证）两部分。

1.1 主库：创建测试库与表

为了验证不同过滤规则，我特意创建了 2 个库、4 张表，包含业务表、日志表，方便后续区分测试：

-- 1. 创建测试数据库

create database db1;

create database db2;

-- 2. db1库：业务表+日志表（模拟真实场景）

use db1;

create table table_01(

   id int not null auto_increment primary key,

   name varchar(10)  -- 存储随机字符串，用于验证数据同步

);

create table table_02(

  id int not null auto_increment primary key,

  name varchar(10)

);

create table log_01(  -- 模拟日志表，后续用于测试"忽略同步"

   id int not null auto_increment primary key,

   name varchar(10)

);

-- 3. db2库：普通测试表

use db2;

create table test_01(

   id int not null auto_increment primary key,

   name varchar(10)

);

1.2 主库：写入测试数据

通过SUBSTRING(MD5(RAND()), 1, 10)生成随机 10 位字符串，模拟业务数据写入：

-- 向db1的3张表和db2的1张表插入数据

insert into db1.table_01(name) values (SUBSTRING(MD5(RAND()), 1, 10));

insert into db1.table_02(name) values (SUBSTRING(MD5(RAND()), 1, 10));

insert into db1.log_01(name) values (SUBSTRING(MD5(RAND()), 1, 10));

insert into db2.test_01(name) values (SUBSTRING(MD5(RAND()), 1, 10));

1.3 从库：验证初始同步

在从库执行查询，确认基础主从同步正常（所有表均能查询到主库插入的数据）：

-- 从库查询4张测试表数据

select * from db1.table_01;

select * from db1.table_02;

select * from db1.log_01;

select * from db2.test_01;

若均能返回数据，说明主从架构基础正常，可进入过滤配置实验。

二、六种核心过滤方案（场景化实操）

所有过滤规则均在从库配置，核心逻辑是通过CHANGE REPLICATION FILTER命令临时生效，或修改 MySQL 配置文件实现持久化（需重启服务）。每个实验均遵循 “停止SQL线程 → 配置规则 → 重启SQL线程 → 测试效果 → 清理规则” 流程，避免规则残留影响后续实验。

2.1 方案 1：只复制某一个库（REPLICATE_DO_DB）

适用场景：只需要同步核心业务库（比如 db1），非核心库（如 db2）无需同步。

步骤 1：从库配置过滤规则

-- 1. 停止从库SQL线程（避免配置修改时同步异常）

STOP SLAVE SQL_THREAD;

-- 2. 配置"只复制db1"（临时生效，重启从库后失效）

CHANGE REPLICATION FILTER REPLICATE_DO_DB=(db1);

-- 3. 重启SQL线程，应用配置

start slave sql_thread;

-- 4. 验证配置状态（查看Filter相关参数）

show slave status\G

如果需要长期生效，就要改配置文件（记得重启服务）：

# 编辑MySQL配置文件

vim /data/mysql/conf/my.cnf

# 新增配置（指定只复制db1）

replicate-do-db=db1

# 重启MySQL服务生效

systemctl restart mysqld

步骤 2：测试同步效果

  1. 主库插入新数据：
insert into db1.table_01(name) values (SUBSTRING(MD5(RAND()), 1, 10));  -- db1表

insert into db2.test_01(name) values (SUBSTRING(MD5(RAND()), 1, 10));  -- db2表

2. 从库验证：

  • db1.table_01：新增数据同步成功（符合过滤规则）
  • db2.test_01：新增数据未同步（被过滤）

测试结果：主库往 db1 插数据同步成功，db2 插数据从库无反应，符合预期～

步骤 3：取消过滤规则

STOP SLAVE SQL_THREAD;

CHANGE REPLICATION FILTER REPLICATE_DO_DB=();  -- 空值清空规则

start slave sql_thread;

2.2 方案 2：忽略某个库的复制（REPLICATE_IGNORE_DB）

场景：需同步除db1（日志库 / 测试库）外的所有库。

步骤 1：从库配置过滤规则

STOP SLAVE SQL_THREAD;

-- 配置"忽略db1"（临时生效）

CHANGE REPLICATION FILTER REPLICATE_IGNORE_DB=(db1);

start slave sql_thread;

show slave status\G

持久化配置：

vim /data/mysql/conf/my.cnf

replicate-ignore-db=db1  # 新增配置

systemctl restart mysqld

步骤 2：测试同步效果

  1. 主库插入新数据：
insert into db1.log_01(name) values (SUBSTRING(MD5(RAND()), 1, 10));  -- db1表

insert into db2.test_01(name) values (SUBSTRING(MD5(RAND()), 1, 10));  -- db2表

2. 从库验证：

  • db1.log_01：新增数据未同步（被忽略）
  • db2.test_01：新增数据同步成功

测试感悟：这个规则适合把测试库、日志库排除在外，减少从库压力，亲测好用！

步骤 3：取消过滤规则

STOP SLAVE SQL_THREAD;

CHANGE REPLICATION FILTER REPLICATE_IGNORE_DB=();

start slave sql_thread;

2.3 方案 3：只复制指定的表（REPLICATE_DO_TABLE）

适用场景：同一库下只需要同步核心表（比如 db1.table_01），其他表忽略。

步骤 1：从库配置过滤规则

STOP SLAVE SQL_THREAD;

-- 配置"只复制db1.table_01"（需指定库名.表名）

CHANGE REPLICATION FILTER REPLICATE_DO_TABLE=(db1.table_01);

start slave sql_thread;

show slave status\G

注意：如果只写表名会失效！之前踩过这个坑，大家一定要注意～

持久化配置：

vim /data/mysql/conf/my.cnf

replicate-do-table=db1.table_01  # 新增配置

systemctl restart mysqld

步骤 2：测试同步效果

  1. 主库插入新数据：
insert into db1.table_01(name) values (SUBSTRING(MD5(RAND()), 1, 10));  -- 目标表

insert into db1.table_02(name) values (SUBSTRING(MD5(RAND()), 1, 10));  -- 同库其他表

2. 从库验证：

  • db1.table_01：同步成功
  • db1.table_02：未同步

步骤 3：取消过滤规则

STOP SLAVE SQL_THREAD;

CHANGE REPLICATION FILTER REPLICATE_DO_TABLE=();

start slave sql_thread;

2.4 方案 4：忽略复制指定的表（REPLICATE_IGNORE_TABLE）

适用场景：同步某库大部分表，但忽略日志表、临时表（比如 db1.log_01）。

步骤 1：从库配置过滤规则

STOP SLAVE SQL_THREAD;

-- 配置"忽略db1.log\_01"

CHANGE REPLICATION FILTER REPLICATE_IGNORE_TABLE=(db1.log_01);

start slave sql_thread;

show slave status\G

持久化配置：

vim /data/mysql/conf/my.cnf

replicate-ignore-table=db1.log_01  # 新增配置

systemctl restart mysqld

步骤 2：测试同步效果

  1. 主库插入新数据：
insert into db1.log_01(name) values (SUBSTRING(MD5(RAND()), 1, 10));  -- 被忽略表

insert into db1.table_01(name) values (SUBSTRING(MD5(RAND()), 1, 10));  -- 其他表

2. 从库验证：

  • db1.log_01：未同步
  • db1.table_01：同步成功

步骤 3：取消过滤规则

STOP SLAVE SQL_THREAD;

CHANGE REPLICATION FILTER REPLICATE_IGNORE_TABLE=();

start slave sql_thread;

实操心得：日志表数据量大且无需备份，用这个规则能节省大量磁盘空间～

2.5 方案 5：指定同步某些表（通配符，REPLICATE_WILD_DO_TABLE）

适用场景：同步某库下符合前缀 / 后缀规则的表（比如 db1 下所有 table 开头的表）。

步骤 1：从库配置过滤规则

STOP SLAVE SQL_THREAD;

-- 通配符%匹配任意字符，配置"只复制db1.table%"

CHANGE REPLICATION FILTER REPLICATE_WILD_DO_TABLE=('db1.table%');

start slave sql_thread;

show slave status\G

持久化配置：

vim /data/mysql/conf/my.cnf

replicate-wild-do-table=db1.table%  # 新增配置（无需引号）

systemctl restart mysqld

步骤 2：测试同步效果

  1. 主库插入新数据：
insert into db1.table_01(name) values (SUBSTRING(MD5(RAND()), 1, 10));  -- 匹配前缀

insert into db1.table_02(name) values (SUBSTRING(MD5(RAND()), 1, 10));  -- 匹配前缀

insert into db1.log_01(name) values (SUBSTRING(MD5(RAND()), 1, 10));    -- 不匹配前缀
  1. 从库验证：
  • table_01、table_02：同步成功
  • log_01：未同步

测试效果：table_01、table_02 都能同步，log_01 被忽略，批量匹配超高效！

步骤 3：取消过滤规则

STOP SLAVE SQL_THREAD;

CHANGE REPLICATION FILTER REPLICATE_WILD_DO_TABLE=();

start slave sql_thread;

2.6 方案 6：忽略某些表的同步（通配符，REPLICATE_WILD_IGNORE_TABLE）

适用场景：忽略某库下符合规则的表（比如 db1 下所有 table 开头的表）。

步骤 1：从库配置过滤规则

STOP SLAVE SQL_THREAD;

-- 配置"忽略db1.table%"

CHANGE REPLICATION FILTER REPLICATE_WILD_IGNORE_TABLE=('db1.table%');

start slave sql_thread;

show slave status\G

持久化配置：

vim /data/mysql/conf/my.cnf

replicate-wild-ignore-table=db1.table%  # 新增配置

systemctl restart mysqld

步骤 2：测试同步效果

  1. 主库插入新数据：
insert into db1.table_01(name) values (SUBSTRING(MD5(RAND()), 1, 10));  -- 被忽略前缀

insert into db1.log_01(name) values (SUBSTRING(MD5(RAND()), 1, 10));    -- 其他表

2. 从库验证：

  • table_01：未同步
  • log_01：同步成功

步骤 3：取消过滤规则

STOP SLAVE SQL_THREAD;

CHANGE REPLICATION FILTER REPLICATE_WILD_IGNORE_TABLE=();

start slave sql_thread;

使用建议：如果需要批量排除同类表，这个规则比逐个写 REPLICATE_IGNORE_TABLE 更方便。

2.7 实验后数据清理

由于过滤配置会导致主从数据不一致（部分表未同步），实验结束后建议在主库和从库同时删除测试库，避免残留数据干扰：

-- 主库和从库均执行

drop database db1;

drop database db2;

三、实验总结（快速选型指南）

为方便快速选型，将 7 种配置方案整理为对比表，涵盖核心参数、适用场景及配置方式：

配置方案核心参数控制粒度适用场景临时配置命令（从库）持久化配置（my.cnf）
只复制某库REPLICATE_DO_DB库级仅同步核心业务库CHANGE REPLICATION FILTER REPLICATE_DO_DB=(库名);replicate-do-db = 库名
忽略某库REPLICATE_IGNORE_DB库级排除非核心库（如测试库、日志库）CHANGE REPLICATION FILTER REPLICATE_IGNORE_DB=(库名);replicate-ignore-db = 库名
只复制指定表REPLICATE_DO_TABLE表级仅同步单库下的核心表CHANGE REPLICATION FILTER REPLICATE_DO_TABLE=(库名.表名);replicate-do-table = 库名.表名
忽略指定表REPLICATE_IGNORE_TABLE表级排除单库下的非核心表（如日志表）CHANGE REPLICATION FILTER REPLICATE_IGNORE_TABLE=(库名.表名);replicate-ignore-table = 库名.表名
通配符匹配同步表REPLICATE_WILD_DO_TABLE通配符级同步单库下符合前缀 / 后缀规则的表CHANGE REPLICATION FILTER REPLICATE_WILD_DO_TABLE=(‘ 库名.前缀 %’);replicate-wild-do-table = 库名.前缀 %
通配符匹配忽略表REPLICATE_WILD_IGNORE_TABLE通配符级排除单库下符合前缀 / 后缀规则的表CHANGE REPLICATION FILTER REPLICATE_WILD_IGNORE_TABLE=(‘ 库名.前缀 %’);replicate-wild-ignore-table = 库名.前缀 %
清理过滤规则––实验后或规则变更前，清空所有过滤配置CHANGE REPLICATION FILTER 对应参数 =();删除对应配置项并重启服务

四、避坑指南（实操经验分享）

  1. 配置生效顺序：临时配置（CHANGE REPLICATION FILTER）优先级高于配置文件，且无需重启；若需长期生效，需同步修改配置文件。
  2. 多规则冲突：若同时配置 “只复制” 和 “忽略” 规则，“忽略” 规则优先级更高（即先判断是否被忽略，再判断是否需复制）。
  3. 表名大小写：MySQL 在 Linux 环境下默认区分表名大小写，配置时需与实际表名大小写一致，避免规则失效。
  4. 重启影响：临时配置在从库重启后失效，生产环境需优先使用配置文件持久化，避免服务重启后规则丢失。

最后

以上就是 MySQL 主从复制过滤配置的全部实操内容啦～ 其实过滤规则不难，关键是根据业务场景选对方案，并且注意避坑。如果大家有其他场景的过滤需求，或者在实操中遇到问题，欢迎在评论区交流探讨！

后续还会分享更多 MySQL 主从复制的优化技巧，比如并行复制、延迟同步等，感兴趣的朋友可以关注一波～

Tags:
Previous Article
Next Article

发表回复

Your email address will not be published. Required fields are marked *.

*
*

近期文章

站点统计

  • 0
  • 20
  • 55
  • 0
  • 59

归档

分类

Java (4) Linux (2) MySQL (44) 网站建设 (5) 读书笔记 (4)