MySQL的复制过滤：如何利用binlog_do_db与binlog_ignore_db实现精确复制？

MySQL 复制过滤：binlog_do_db 与 binlog_ignore_db 精确复制实战

大家好，今天我们深入探讨 MySQL 复制中一个至关重要但又容易混淆的环节：复制过滤。特别是 binlog_do_db 和 binlog_ignore_db 这两个参数，它们是实现精确复制的关键。精确复制意味着你可以控制哪些数据库和表的数据变更会被复制到从库，这对于异构环境、数据备份、读写分离等场景至关重要。

1. 复制过滤的必要性

在默认情况下，MySQL 的复制会同步主库的所有二进制日志 (binlog) 事件到从库。 这在某些场景下是不可取的：

• 资源消耗: 从库可能不需要主库的所有数据，同步所有数据会浪费网络带宽和存储空间。
• 安全考虑: 某些敏感数据不应该复制到从库，例如包含用户个人信息的数据库。
• 异构环境: 主从库的用途不同，从库可能只需要主库的部分数据进行分析或报表生成。
• 维护方便: 当需要对主库进行特定数据库的维护或升级时，可以暂时停止对该数据库的复制，避免影响从库。

2. binlog_do_db 和 binlog_ignore_db 的作用

binlog_do_db 和 binlog_ignore_db 是 MySQL 中用于控制二进制日志写入的关键参数。它们影响的是主库的 binlog 的内容，进而影响复制到从库的数据。

• binlog_do_db: 指定需要记录 binlog 的数据库。只有指定的数据库的事件才会被写入 binlog。
• binlog_ignore_db: 指定不需要记录 binlog 的数据库。指定的数据库的事件不会被写入 binlog。

这两个参数是互斥的，不能同时使用。 使用其中任何一个，都会覆盖默认行为（即记录所有数据库）。

3. 配置位置与生效范围

这两个参数需要在主库的 my.cnf (或 my.ini Windows 环境下) 配置文件中设置。 修改配置文件后，需要重启 MySQL 服务才能生效。

示例 my.cnf 配置:

[mysqld]
# 只记录 database1 和 database2 的 binlog
binlog_do_db = database1,database2

# 或者，忽略 database3 和 database4 的 binlog
# binlog_ignore_db = database3,database4

注意: 这两个参数影响的是全局的 binlog 写入行为。 任何连接到主库的客户端，对这些数据库的操作都会受到影响。

4. 配置优先级与冲突解决

当存在多个复制过滤参数时，MySQL 有一套优先级规则：

1. binlog_do_db 和 binlog_ignore_db 互斥，不能同时使用。如果同时设置，MySQL 会启动失败。
2. 如果在命令行或会话级别设置了 binlog_do_db 或 binlog_ignore_db，它们会覆盖 my.cnf 中的设置，但只对当前会话有效。这种方式不推荐用于复制过滤，因为无法保证复制的稳定性。
3. replicate-do-db 和 replicate-ignore-db (从库参数，后面会讲到) 会进一步过滤从主库接收到的 binlog 事件。

5. 复制过滤的详细规则与示例

我们现在深入探讨 binlog_do_db 和 binlog_ignore_db 的具体使用规则和示例。

5.1. binlog_do_db 的使用

当使用 binlog_do_db 时，只有完全匹配指定数据库名称的事件才会被写入 binlog。 这意味着：

• 只有 USE database_name; 选择了的数据库的事件才会被记录。
• 跨数据库的语句，例如 UPDATE other_database.table1 SET ... WHERE ... 即使涉及到 binlog_do_db 指定的数据库，也不会被记录。

示例:

假设我们有三个数据库：db_a, db_b, db_c。 db_a 包含表 table1, table2； db_b 包含表 table3, table4； db_c 包含表 table5, table6。

我们的 my.cnf 配置如下：

[mysqld]
binlog_do_db = db_a

现在，我们在主库上执行以下 SQL 语句：

USE db_a;
INSERT INTO table1 (col1) VALUES (1); -- 会被记录

USE db_b;
INSERT INTO table3 (col1) VALUES (2); -- 不会被记录

USE db_a;
UPDATE table2 SET col1 = 3 WHERE id = 1; -- 会被记录

USE db_c;
INSERT INTO table5 (col1) VALUES (4); -- 不会被记录

UPDATE db_b.table3 SET col1 = 5 WHERE id = 1; -- 不会被记录，即使更新了 db_b 数据库中的表

UPDATE db_a.table1 SET col1 = 6 WHERE id = 1; -- 不会被记录，因为没有先 USE db_a;

结论: 只有在 USE db_a; 语句之后执行的针对 db_a 数据库的操作才会被记录到 binlog 中。

5.2. binlog_ignore_db 的使用

binlog_ignore_db 的行为与 binlog_do_db 相反。 所有完全匹配指定数据库名称的事件都不会被写入 binlog。

示例:

假设我们有三个数据库：db_a, db_b, db_c。

我们的 my.cnf 配置如下：

[mysqld]
binlog_ignore_db = db_b,db_c

现在，我们在主库上执行以下 SQL 语句：

USE db_a;
INSERT INTO table1 (col1) VALUES (1); -- 会被记录

USE db_b;
INSERT INTO table3 (col1) VALUES (2); -- 不会被记录

USE db_a;
UPDATE table2 SET col1 = 3 WHERE id = 1; -- 会被记录

USE db_c;
INSERT INTO table5 (col1) VALUES (4); -- 不会被记录

UPDATE db_b.table3 SET col1 = 5 WHERE id = 1; -- 不会被记录

UPDATE db_a.table1 SET col1 = 6 WHERE id = 1; -- 不会被记录，因为没有先 USE db_a;

结论: 凡是 USE db_b; 或 USE db_c; 语句之后执行的操作，都不会被记录到 binlog 中。

6. 从库的复制过滤：replicate-do-db 和 replicate-ignore-db

即使主库已经通过 binlog_do_db 或 binlog_ignore_db 进行了过滤，从库仍然可以通过 replicate-do-db 和 replicate-ignore-db 参数进行进一步的过滤。 这两个参数配置在从库的 my.cnf 文件中，用于控制从主库接收到的 binlog 事件在从库上的执行。

• replicate-do-db: 只复制指定的数据库到从库。
• replicate-ignore-db: 忽略复制指定的数据库到从库。

注意: replicate-do-db 和 replicate-ignore-db 影响的是从库执行 binlog 事件的行为，而不是主库 binlog 的生成。

示例:

假设主库的 my.cnf 配置如下：

[mysqld]
binlog_do_db = db_a,db_b

从库的 my.cnf 配置如下：

[mysqld]
replicate-do-db = db_a

在这种情况下，主库会记录 db_a 和 db_b 的 binlog 事件。 但是，从库只会执行与 db_a 相关的事件，而会忽略 db_b 的事件。 db_b 在从库上不会被更新。

7. 表级别的复制过滤

除了数据库级别的过滤，MySQL 还支持表级别的复制过滤，主要通过以下参数：

• replicate-do-table: 只复制指定的表。
• replicate-ignore-table: 忽略复制指定的表。
• replicate-wild-do-table: 使用通配符指定需要复制的表。
• replicate-wild-ignore-table: 使用通配符指定需要忽略的表。

这些参数也配置在从库的 my.cnf 文件中。

示例:

[mysqld]
replicate-do-table = db_a.table1
replicate-ignore-table = db_b.table3
replicate-wild-do-table = db_c.table%
replicate-wild-ignore-table = db_d.%_temp

• replicate-do-table = db_a.table1：只复制 db_a 数据库中的 table1 表。
• replicate-ignore-table = db_b.table3：忽略复制 db_b 数据库中的 table3 表。
• replicate-wild-do-table = db_c.table%：复制 db_c 数据库中所有以 table 开头的表（例如 table1, table_abc）。
• replicate-wild-ignore-table = db_d.%_temp：忽略复制 db_d 数据库中所有第二个字符是 _ 且以 temp 结尾的表（例如 a_temp, b_temp）。

8. 复制过滤参数的优先级总结

为了更清晰地理解复制过滤参数的优先级，我们总结如下表：

参数	位置	作用范围	优先级	说明
binlog_do_db	主库 my.cnf	全局 (主库 binlog)	高	影响主库 binlog 的生成。只有指定的数据库的事件才会被写入 binlog。
binlog_ignore_db	主库 my.cnf	全局 (主库 binlog)	高	影响主库 binlog 的生成。指定的数据库的事件不会被写入 binlog。
replicate-do-db	从库 my.cnf	从库执行	中	从库只执行与指定数据库相关的 binlog 事件。
replicate-ignore-db	从库 my.cnf	从库执行	中	从库忽略执行与指定数据库相关的 binlog 事件。
replicate-do-table	从库 my.cnf	从库执行	低	从库只执行与指定表相关的 binlog 事件。
replicate-ignore-table	从库 my.cnf	从库执行	低	从库忽略执行与指定表相关的 binlog 事件。
replicate-wild-do-table	从库 my.cnf	从库执行	低	从库只执行与符合通配符规则的表相关的 binlog 事件。
replicate-wild-ignore-table	从库 my.cnf	从库执行	低	从库忽略执行与符合通配符规则的表相关的 binlog 事件。

9. 实战案例：读写分离与数据备份

下面我们通过两个实际案例来演示复制过滤的应用。

9.1. 读写分离

假设我们有一个电商应用，需要实现读写分离。 主库负责处理写操作（例如订单创建、商品更新），从库负责处理读操作（例如商品查询、报表生成）。 为了减轻主库的压力，我们只将商品相关的数据库复制到从库。

主库配置：

[mysqld]
binlog_do_db = product_db

从库配置：

[mysqld]
replicate-do-db = product_db

这样，只有 product_db 数据库的事件会被复制到从库，从库可以专注于处理商品相关的读请求。

9.2. 数据备份

假设我们需要定期备份某个敏感数据库，但又不想将所有数据都备份到外部存储。 我们可以创建一个专门用于备份的从库，只复制需要备份的数据库。

主库配置：

[mysqld]
binlog_do_db = sensitive_db

备份从库配置：

[mysqld]
replicate-do-db = sensitive_db

然后，定期备份该从库的数据，从而实现对敏感数据的安全备份。

10. 常见问题与注意事项

• 忘记重启 MySQL 服务: 修改 my.cnf 文件后，必须重启 MySQL 服务才能使配置生效。
• 混淆主从库参数: binlog_do_db 和 binlog_ignore_db 是主库参数，replicate-do-db 和 replicate-ignore-db 是从库参数。
• 忽略了系统数据库: 如果需要复制系统数据库（例如 mysql, performance_schema, sys），需要显式地包含它们，否则会被忽略。
• 复制中断: 不正确的复制过滤配置可能导致复制中断。 需要仔细检查配置，并查看 MySQL 的错误日志。
• DDL 语句的影响: 当执行 DDL 语句（例如 CREATE TABLE, ALTER TABLE）时，需要特别注意复制过滤的设置，确保 DDL 语句能够正确地复制到从库。 建议在执行 DDL 语句之前，先停止复制，然后执行 DDL 语句，最后再启动复制。
• GTID 模式: 在使用 GTID 模式时，复制过滤的配置更加复杂，需要仔细阅读 MySQL 的官方文档。

11. 总结与建议

binlog_do_db 和 binlog_ignore_db 是 MySQL 复制过滤的重要工具，掌握它们可以帮助你实现精确的数据复制，优化资源利用，提高数据安全性。在配置复制过滤时，需要仔细考虑业务需求，并充分理解各个参数的作用和优先级。

通过配置来控制复制，确保数据同步的精准与高效

希望今天的分享对大家有所帮助。 谢谢！