MySQL的复制过滤：如何利用binlog_do_db与binlog_ignore_db实现精确复制，并处理主从库数据不一致问题？ - 智猿学院-前后端，数据库，人工智能，云计算等领域前沿技术讲座

好的，我们开始。

MySQL复制过滤：利用binlog_do_db与binlog_ignore_db实现精确复制与数据一致性维护

大家好，今天我们来深入探讨MySQL复制过滤，重点讲解如何利用 binlog_do_db 和 binlog_ignore_db 这两个参数实现精确复制，并探讨主从库数据不一致时的处理方法。在实际生产环境中，并非所有数据都需要同步到从库，精确复制可以显著降低从库的存储压力、提升复制性能，并提高数据安全性。

一、复制过滤：必要性与策略

在标准的MySQL主从复制架构中，主库的所有变更默认都会同步到从库。但在以下场景中，我们需要进行复制过滤：

节省从库资源： 从库可能只需要主库的部分数据，例如报表库只需要业务库的统计数据。
提高复制性能： 减少复制的数据量可以降低网络传输的压力，加快同步速度。
安全考虑： 某些敏感数据可能不应该同步到从库，例如用户密码。
多数据中心架构： 不同的数据中心可能只需要特定的数据子集。
测试环境隔离： 在测试环境中，可能只需要同步部分表进行测试，避免影响生产环境。

常见的复制过滤策略包括：

基于数据库的过滤： 只复制或忽略特定的数据库。
基于表的过滤： 只复制或忽略特定的表。
基于行的过滤： (相对复杂，通常使用逻辑复制工具实现) 只复制满足特定条件的行。

今天我们重点讨论基于数据库的过滤，也就是 binlog_do_db 和 binlog_ignore_db 的应用。

二、binlog_do_db 与 binlog_ignore_db：定义与作用

binlog_do_db 和 binlog_ignore_db 都是 MySQL 服务器的启动参数，用于控制二进制日志 (binlog) 中记录哪些数据库的操作。它们只影响主库产生的binlog内容，从而影响从库的复制。

binlog_do_db=db1,db2,...: 指定需要记录到二进制日志中的数据库列表。只有对这些数据库的操作才会被记录到 binlog 中。如果设置了 binlog_do_db，则没有明确列出的数据库的操作将不会被记录。
binlog_ignore_db=db3,db4,...: 指定不需要记录到二进制日志中的数据库列表。对这些数据库的操作不会被记录到 binlog 中。

重要说明：

这两个参数是互斥的。也就是说，不能同时设置 binlog_do_db 和 binlog_ignore_db。只能选择其中一个来配置。
这两个参数只影响主库的 binlog 记录，不影响从库的复制行为。从库的复制行为由从库自己的 replicate_do_db 和 replicate_ignore_db 参数控制 (后面会详细介绍)。
这两个参数只影响后续产生的 binlog 文件。修改参数后，需要重启 MySQL 服务才能生效。

三、配置步骤与示例

停止 MySQL 服务： 在主库上执行 sudo systemctl stop mysql (或者其他停止 MySQL 服务的命令，取决于你的操作系统和 MySQL 安装方式)。
编辑 MySQL 配置文件： 找到 MySQL 的配置文件，通常是 /etc/mysql/mysql.conf.d/mysqld.cnf 或 /etc/my.cnf。
添加或修改配置： 根据你的需求，添加 binlog_do_db 或 binlog_ignore_db 参数。

示例 1：使用 binlog_do_db 只记录 db1 和 db2 数据库的操作。
```
[mysqld]
log_bin=mysql-bin
binlog_format=ROW
server_id=1
binlog_do_db=db1,db2
```
示例 2：使用 binlog_ignore_db 忽略 db3 和 db4 数据库的操作。
```
[mysqld]
log_bin=mysql-bin
binlog_format=ROW
server_id=1
binlog_ignore_db=db3,db4
```
重启 MySQL 服务： 执行 sudo systemctl start mysql (或者其他启动 MySQL 服务的命令)。
验证配置： 登录 MySQL，执行 SHOW VARIABLES LIKE 'binlog_%'; 确认 binlog_do_db 或 binlog_ignore_db 参数已经生效。

四、从库的复制过滤：replicate_do_db 与 replicate_ignore_db

主库的 binlog_do_db 和 binlog_ignore_db 控制的是主库产生的 binlog 内容。为了实现从库的精确复制，还需要在从库上配置 replicate_do_db 和 replicate_ignore_db 参数。

replicate_do_db=db5,db6,...: 指定从库需要复制的数据库列表。从库只会复制 binlog 中对这些数据库的操作。
replicate_ignore_db=db7,db8,...: 指定从库需要忽略的数据库列表。从库会忽略 binlog 中对这些数据库的操作。

重要说明：

replicate_do_db 和 replicate_ignore_db 也互斥，不能同时设置。
这些参数只影响从库的复制行为。
这些参数可以在运行时动态修改，不需要重启 MySQL 服务。

配置步骤与示例：

登录从库 MySQL： 使用具有 SUPER 权限的用户登录到从库 MySQL。
停止 Slave 线程： 执行 STOP SLAVE;。
设置复制过滤参数： 使用 SET GLOBAL 命令设置 replicate_do_db 或 replicate_ignore_db 参数。

示例 1：使用 replicate_do_db 从库只复制 db5 和 db6 数据库的操作。
```
SET GLOBAL replicate_do_db = 'db5,db6';
```
示例 2：使用 replicate_ignore_db 从库忽略 db7 和 db8 数据库的操作。
```
SET GLOBAL replicate_ignore_db = 'db7,db8';
```
启动 Slave 线程： 执行 START SLAVE;。
验证配置： 执行 SHOW SLAVE STATUSG，查看 Replicate_Do_DB 或 Replicate_Ignore_DB 字段是否已经生效。

五、主从数据不一致的处理

即使配置了复制过滤，主从数据不一致的情况仍然可能发生，例如：

配置错误： binlog_do_db、binlog_ignore_db、replicate_do_db 或 replicate_ignore_db 配置不正确。
手动操作： 在从库上直接修改了数据，绕过了复制机制。
网络问题： 复制过程中出现网络中断，导致部分数据未同步。
延迟复制： 从库复制延迟严重，主库已经更新了数据，从库还没有同步。
初始数据不一致： 在配置复制之前，主从库的数据就已经存在差异。

处理主从数据不一致的常用方法：

检查配置： 仔细检查 binlog_do_db、binlog_ignore_db、replicate_do_db 和 replicate_ignore_db 的配置是否正确，确保主从库的过滤规则一致。
数据比对工具： 使用数据比对工具 (例如 pt-table-sync、Data Compare for MySQL) 对比主从库的数据，找出差异的数据。
手动修复： 对于少量的数据差异，可以直接在从库上手动修改数据，使其与主库保持一致。
全量同步： 如果数据差异较大，或者手动修复成本太高，可以考虑进行全量同步。全量同步的步骤如下：
- 停止 Slave 线程：STOP SLAVE;
- 使用 mysqldump 从主库导出数据：mysqldump -u root -p --all-databases --single-transaction --master-data=2 > backup.sql
- 在从库上删除所有数据 (或者只删除需要同步的数据库)：DROP DATABASE IF EXISTS db1; CREATE DATABASE db1; (注意备份从库数据！)
- 在从库上导入数据：mysql -u root -p < backup.sql
- 启动 Slave 线程：START SLAVE;
半同步复制/增强半同步复制： 使用半同步复制或增强半同步复制，可以提高数据一致性，减少数据丢失的风险。
GTID复制： 使用GTID复制能更好的保证数据一致性，方便故障切换。

六、示例：利用复制过滤实现读写分离

假设我们有一个业务数据库 app_db，其中包含 users 表和 orders 表。为了减轻主库的读取压力，我们希望将 users 表的读取操作放到从库上，而 orders 表的读写操作仍然在主库上。

配置步骤：

主库配置：

[mysqld]
log_bin=mysql-bin
binlog_format=ROW
server_id=1
# 不需要特殊的 binlog 过滤，因为默认会记录所有数据库的操作

从库配置：

-- 停止 Slave 线程
STOP SLAVE;

-- 设置从库只复制 app_db 数据库
SET GLOBAL replicate_do_db = 'app_db';

-- 启动 Slave 线程
START SLAVE;

同时，还需要确保客户端连接到从库时，只查询 users 表，而连接到主库时，可以查询 users 和 orders 表。

代码示例 (Python)：

import mysql.connector

# 主库连接信息
master_config = {
    'user': 'your_user',
    'password': 'your_password',
    'host': 'master_host',
    'database': 'app_db'
}

# 从库连接信息
slave_config = {
    'user': 'your_user',
    'password': 'your_password',
    'host': 'slave_host',
    'database': 'app_db'
}

def get_users_from_slave():
    """从从库读取 users 表数据"""
    try:
        cnx = mysql.connector.connect(**slave_config)
        cursor = cnx.cursor()
        query = "SELECT * FROM users"
        cursor.execute(query)
        for row in cursor:
            print(row)
        cursor.close()
        cnx.close()
    except mysql.connector.Error as err:
        print(f"Error: {err}")

def get_orders_from_master():
    """从主库读取 orders 表数据"""
    try:
        cnx = mysql.connector.connect(**master_config)
        cursor = cnx.cursor()
        query = "SELECT * FROM orders"
        cursor.execute(query)
        for row in cursor:
            print(row)
        cursor.close()
        cnx.close()
    except mysql.connector.Error as err:
        print(f"Error: {err}")

# 示例调用
get_users_from_slave()
get_orders_from_master()

七、注意事项与最佳实践

谨慎使用 binlog_do_db 和 binlog_ignore_db： 修改这些参数可能会导致数据丢失或不一致，务必在测试环境中充分验证。
监控复制状态： 定期检查 SHOW SLAVE STATUSG 的输出，确保复制没有出现错误。
备份数据： 在修改复制配置之前，务必备份主从库的数据，以防万一。
文档化配置： 详细记录主从库的复制配置，方便日后维护和排查问题。
考虑使用逻辑复制： 对于更复杂的复制需求 (例如基于行的过滤)，可以考虑使用逻辑复制工具 (例如 Canal、Debezium)。
选择合适的 binlog_format： ROW 格式的 binlog 更加可靠，更适合复制。

表格：参数总结

参数	作用	影响范围	是否互斥	是否需要重启	动态修改
`binlog_do_db`	指定需要记录到 binlog 的数据库列表	主库	与 `binlog_ignore_db` 互斥	是	否
`binlog_ignore_db`	指定不需要记录到 binlog 的数据库列表	主库	与 `binlog_do_db` 互斥	是	否
`replicate_do_db`	指定从库需要复制的数据库列表	从库	与 `replicate_ignore_db` 互斥	否	是
`replicate_ignore_db`	指定从库需要忽略的数据库列表	从库	与 `replicate_do_db` 互斥	否	是

总结：精细控制，数据一致

MySQL复制过滤通过binlog_do_db和binlog_ignore_db在主库控制binlog记录，以及replicate_do_db和replicate_ignore_db在从库控制复制行为，可以实现精确复制。精心配置这些参数，结合数据比对和同步工具，可以有效避免主从数据不一致，确保数据安全和性能。

发表回复 取消回复

发表回复取消回复