MySQL复制过滤：利用binlog_do_db与binlog_ignore_db实现精确复制
大家好！今天我们来深入探讨MySQL复制中的过滤机制，重点讲解如何利用binlog_do_db和binlog_ignore_db这两个参数实现精确的数据库复制。在复杂的生产环境中，我们往往不需要复制整个数据库实例的所有数据，而是只需要复制特定的数据库或者表，甚至需要排除某些数据库或表。掌握复制过滤技术对于优化复制效率、节省存储空间以及保障数据安全至关重要。
1. 复制过滤的必要性
在深入了解具体的配置之前，我们首先要明白为什么需要复制过滤。以下是一些常见的应用场景：
- 资源限制： Slave节点的存储空间有限，不需要复制所有数据。
- 安全考虑： 某些敏感数据不需要同步到Slave节点，例如用户密码、财务信息等。
- 简化管理： 只需要复制特定的数据库，可以降低Slave节点的维护成本。
- 测试环境： 在测试环境中只需要复制生产环境的部分数据用于测试。
- 数据分析： 将部分数据复制到专门用于分析的Slave节点，避免影响生产环境的性能。
2. binlog_do_db与binlog_ignore_db的工作原理
binlog_do_db和binlog_ignore_db是MySQL Server启动参数，用于指定哪些数据库的更改会被写入二进制日志（binary log）。Master节点根据这两个参数决定是否将某个数据库的操作记录到binlog中。Slave节点从Master节点接收binlog，并根据自己的配置决定是否应用这些更改。
binlog_do_db： 指定需要记录到二进制日志的数据库列表。只有位于列表中的数据库的更改才会被记录。如果设置了该参数，则只有指定的数据库会被复制。未指定的数据库的更改不会被记录到binlog中，因此也不会被复制。binlog_ignore_db： 指定不需要记录到二进制日志的数据库列表。位于列表中的数据库的更改不会被记录。如果设置了该参数，则除了指定的数据库以外，所有其他的数据库都会被复制。
优先级： binlog_do_db 和 binlog_ignore_db 同时存在时，binlog_ignore_db 优先级更高。也就是说，如果一个数据库同时出现在 binlog_do_db 和 binlog_ignore_db 列表中，那么这个数据库的更改将不会被记录到 binlog 中。
注意： 这两个参数是在 Master 节点上配置的，用于控制哪些数据库的更改会被写入二进制日志。
3. 配置binlog_do_db与binlog_ignore_db
配置这两个参数需要修改MySQL的配置文件（通常是my.cnf或my.ini）。
步骤：
-
停止MySQL服务： 在修改配置文件之前，需要先停止MySQL服务。
sudo systemctl stop mysql -
编辑配置文件： 使用文本编辑器打开MySQL的配置文件。
sudo nano /etc/mysql/my.cnf -
添加或修改参数： 在
[mysqld]部分添加或修改binlog_do_db和binlog_ignore_db参数。-
只复制数据库
db1和db2：[mysqld] binlog_do_db=db1 binlog_do_db=db2 -
忽略数据库
db3和db4：[mysqld] binlog_ignore_db=db3 binlog_ignore_db=db4 -
同时使用
binlog_do_db和binlog_ignore_db(不推荐，容易出错):[mysqld] binlog_do_db=db1 binlog_do_db=db2 binlog_ignore_db=db3
-
-
保存并关闭配置文件。
-
启动MySQL服务： 修改完成后，启动MySQL服务。
sudo systemctl start mysql -
检查配置是否生效： 可以使用
SHOW VARIABLES LIKE 'binlog_%';命令检查配置是否生效。mysql> SHOW VARIABLES LIKE 'binlog_%'; +---------------------------------+---------------------+ | Variable_name | Value | +---------------------------------+---------------------+ | binlog_cache_size | 32768 | | binlog_checksum | CRC32 | | binlog_direct_non_transactional_updates | OFF | | binlog_error_action | ABORT_SERVER | | binlog_format | ROW | | binlog_group_commit_sync_delay | 0 | | binlog_group_commit_sync_no_delay_count | 0 | | binlog_gtid_simple_recovery | ON | | binlog_ignore_db | db3,db4 | | binlog_max_flush_queue_time | 60 | | binlog_optimize_multi_table_update | ON | | binlog_row_image | FULL | | binlog_rows_query_log_events | OFF | | binlog_stmt_cache_size | 32768 | | binlog_transaction_dependency_history_size | 25000 | | binlog_transaction_dependency_tracking | COMMIT_ORDER | | binlog_unsafe_clustering | OFF | | binlog_verify_checksum | ON | +---------------------------------+---------------------+ 18 rows in set (0.00 sec)注意观察
binlog_ignore_db的值是否与配置文件中的一致。 如果是binlog_do_db，也会显示出来。 如果配置没有生效，尝试重启MySQL服务。
4. 复制过滤的细节与注意事项
-
区分大小写： 数据库名在
binlog_do_db和binlog_ignore_db中是区分大小写的。 因此，务必确保数据库名的大小写与实际数据库名一致。 -
字符集： 确保配置文件中的字符集与数据库的字符集一致，避免出现乱码问题。
-
动态修改：
binlog_do_db和binlog_ignore_db只能在MySQL启动时指定，不能动态修改。 修改后必须重启MySQL服务才能生效。 -
binlog格式： 建议使用
ROW格式的二进制日志。 在STATEMENT格式下，某些语句可能会导致复制失败，例如使用UUID()函数。 使用ROW格式可以更准确地记录数据的更改，提高复制的可靠性。 -
与其他过滤规则的配合：
binlog_do_db和binlog_ignore_db只是Master节点的过滤规则。 Slave节点也有自己的过滤规则，例如replicate-do-db和replicate-ignore-db。 需要综合考虑Master和Slave节点的过滤规则，才能实现期望的复制效果。 -
DDL语句： DDL语句（例如
CREATE DATABASE、ALTER TABLE）也会被记录到二进制日志中。 如果只想复制数据，可以考虑使用replicate-ignore-table来排除DDL语句。 -
flush logs： 修改配置后，建议执行
FLUSH LOGS;命令，强制MySQL切换到新的二进制日志文件。 这样可以确保新的配置立即生效。 -
gtid_mode: 如果开启了 GTID （Global Transaction ID），
binlog_do_db和binlog_ignore_db的使用会受到一些限制。 建议在启用GTID时，尽量避免使用这两个参数，而是使用replicate-do-db和replicate-ignore-db在Slave节点进行过滤。
5. Slave节点的复制过滤
除了在Master节点上进行过滤，还可以在Slave节点上进行过滤。 Slave节点的过滤参数主要有：
replicate-do-db： 指定需要复制的数据库列表。只有位于列表中的数据库的更改才会被应用到Slave节点。replicate-ignore-db： 指定不需要复制的数据库列表。位于列表中的数据库的更改不会被应用到Slave节点。replicate-do-table： 指定需要复制的表列表。只有位于列表中的表的更改才会被应用到Slave节点。格式为database.table。replicate-ignore-table： 指定不需要复制的表列表。位于列表中的表的更改不会被应用到Slave节点。格式为database.table。replicate-wild-do-table： 使用通配符指定需要复制的表列表。 例如db%.table%表示复制所有以db开头的数据库中，以table开头的表。replicate-wild-ignore-table： 使用通配符指定不需要复制的表列表。
示例：
在Slave节点的配置文件中添加以下参数，表示只复制db1.table1和db2.table2表：
[mysqld]
replicate-do-table=db1.table1
replicate-do-table=db2.table2Slave节点过滤的优势：
- 灵活性： 可以在Slave节点上根据需要灵活地调整复制规则，而无需重启Master节点。
- 安全性： 即使Master节点配置错误，也不会影响Slave节点的数据安全。
- 性能： 可以降低Slave节点的负载，提高复制效率。
Slave节点过滤的缺点：
- 数据一致性风险： 如果Master和Slave节点的过滤规则不一致，可能会导致数据不一致。
- 维护成本： 需要维护Master和Slave两套过滤规则，增加了维护成本。
6. 常见问题与解决方案
-
配置不生效： 检查配置文件是否正确，MySQL服务是否重启，以及配置参数的名称是否正确。
-
复制中断： 检查Master和Slave节点的网络连接是否正常，二进制日志是否损坏，以及复制线程是否正常运行。
-
数据不一致： 检查Master和Slave节点的过滤规则是否一致，以及是否存在未同步的事务。
-
性能问题： 优化SQL语句，调整MySQL参数，以及考虑使用更快的存储设备。
-
GTID相关问题: 如果开启了GTID，尽量避免在Master节点使用
binlog_do_db和binlog_ignore_db。 推荐在Slave节点使用replicate-do-db和replicate-ignore-db。 如果必须使用 Master 节点的过滤功能，务必了解 GTID 的工作原理，并进行充分的测试。
7. 案例分析：安全优先的复制策略
假设我们有一个生产环境的MySQL数据库，其中包含多个数据库，包括一个存储用户敏感信息的sensitive_db数据库。 我们需要将数据复制到一个用于数据分析的Slave节点，但出于安全考虑，不能将sensitive_db数据库复制到Slave节点。
解决方案：
-
Master节点配置： 在Master节点的
my.cnf文件中添加以下参数：[mysqld] binlog_ignore_db=sensitive_db -
Slave节点配置： 在Slave节点的
my.cnf文件中添加以下参数（可选，但推荐）：[mysqld] replicate-ignore-db=sensitive_db在Slave节点也配置
replicate-ignore-db可以作为额外的安全保障，即使Master节点配置错误，也能防止sensitive_db被复制到Slave节点。 -
重启Master和Slave节点。
通过以上配置，我们可以确保sensitive_db数据库的更改不会被记录到Master节点的二进制日志中，因此也不会被复制到Slave节点。
8. 表格总结参数
| 参数名称 | 作用范围 | 作用 |
|---|---|---|
binlog_do_db |
Master | 指定需要记录到二进制日志的数据库列表。 |
binlog_ignore_db |
Master | 指定不需要记录到二进制日志的数据库列表。 |
replicate-do-db |
Slave | 指定需要复制的数据库列表。 |
replicate-ignore-db |
Slave | 指定不需要复制的数据库列表。 |
replicate-do-table |
Slave | 指定需要复制的表列表，格式为 database.table。 |
replicate-ignore-table |
Slave | 指定不需要复制的表列表，格式为 database.table。 |
replicate-wild-do-table |
Slave | 使用通配符指定需要复制的表列表。 |
replicate-wild-ignore-table |
Slave | 使用通配符指定不需要复制的表列表。 |
9. 精准控制复制范围，保障数据安全
今天的分享就到这里，我们深入探讨了MySQL复制过滤的原理和配置方法，重点讲解了binlog_do_db和binlog_ignore_db参数的使用。通过合理配置这些参数，我们可以实现精确的数据库复制，优化复制效率，节省存储空间，并保障数据安全。 记住，在配置复制过滤时，一定要仔细考虑实际需求，并进行充分的测试，以确保复制配置能够满足需求，并且不会导致数据不一致或其他问题。