发布于 由admin

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

好的，没问题。我们直接进入正题。

MySQL复制过滤：利用binlog_do_db与binlog_ignore_db实现精确复制

大家好，今天我们要深入探讨MySQL复制中的一个关键概念：复制过滤，特别是如何利用binlog_do_db和binlog_ignore_db来实现精确的数据库复制。在复杂的生产环境中，我们通常不需要复制所有数据库或者数据库中的所有表。复制过滤允许我们只复制我们需要的数据，这可以显著降低网络带宽占用，减少Slave服务器的存储压力，并且提高复制效率。

复制过滤的必要性

在开始深入技术细节之前，先来理解一下为什么需要复制过滤。以下是一些常见的场景：

  • 数据隔离： 某些数据可能包含敏感信息，出于安全或合规性考虑，我们不希望这些数据被复制到其他服务器。

  • 降低资源消耗： 只复制需要的数据库可以减少网络带宽，磁盘空间和Slave服务器的CPU资源消耗。

  • 简化维护： 如果Slave服务器只需要关注特定数据库，维护和管理工作会大大简化。

  • 测试环境： 我们可能只需要复制生产环境的部分数据到测试环境进行测试，而不是完整的数据副本。

复制过滤的类型

MySQL提供了多种复制过滤机制，主要可以分为以下几类：

  1. 基于数据库的过滤： 使用binlog_do_db和binlog_ignore_db参数，这是我们今天要重点讨论的内容。
  2. 基于表的过滤： 使用replicate_do_table和replicate_ignore_table参数。
  3. 基于通配符的过滤： 使用replicate_wild_do_table和replicate_wild_ignore_table参数。

今天我们主要关注基于数据库的过滤，其他的过滤方式以后有机会再详细讨论。

binlog_do_db 和 binlog_ignore_db

binlog_do_db 和 binlog_ignore_db 是 Master 服务器上的配置，用于控制哪些数据库的更改会被记录到二进制日志(binary log)中。 重要的是要理解 这些参数只影响 Master 服务器写入二进制日志的行为，并不直接影响 Slave 服务器的复制行为。 Slave服务器的复制行为由replicate_do_db和replicate_ignore_db等参数控制，我们稍后会讨论。

  • binlog_do_db: 指定需要写入二进制日志的数据库列表。 只有列出的数据库的更改才会被记录。

  • binlog_ignore_db: 指定不需要写入二进制日志的数据库列表。 列出的数据库的更改不会被记录。

注意： 如果同时设置了 binlog_do_db 和 binlog_ignore_db， 那么 binlog_ignore_db 优先于 binlog_do_db。

配置 binlog_do_db 和 binlog_ignore_db

可以通过以下方式配置这两个参数：

  1. 配置文件 (my.cnf/my.ini): 这是推荐的方式，因为配置会在服务器重启后仍然生效。

    [mysqld]
binlog_do_db = db1,db2
binlog_ignore_db = db3

  2. 命令行: 这种方式只在当前会话有效，服务器重启后配置会丢失。

    SET GLOBAL binlog_do_db = 'db1,db2';
SET GLOBAL binlog_ignore_db = 'db3';

    注意： 修改全局参数需要 SUPER 权限。

示例 1: 使用 binlog_do_db

假设我们有两个数据库 users 和 products， 我们只想复制 users 数据库的更改。

  1. 配置 Master 服务器的 my.cnf：

    [mysqld]
log_bin = mysql-bin  # 启用二进制日志
server-id = 1
binlog_do_db = users

  2. 重启 Master 服务器：

    sudo systemctl restart mysqld

    或者

    sudo service mysql restart

  3. 验证配置：

    SHOW GLOBAL VARIABLES LIKE 'binlog_do_db';

    应该看到 Value 为 users。

现在，只有对 users 数据库的更改才会被记录到二进制日志中。

示例 2: 使用 binlog_ignore_db

假设我们有三个数据库 users, products 和 logs， 我们不想复制 logs 数据库的更改。

  1. 配置 Master 服务器的 my.cnf：

    [mysqld]
log_bin = mysql-bin  # 启用二进制日志
server-id = 1
binlog_ignore_db = logs

  2. 重启 Master 服务器：

    sudo systemctl restart mysqld

  3. 验证配置：

    SHOW GLOBAL VARIABLES LIKE 'binlog_ignore_db';

    应该看到 Value 为 logs。

现在，对 logs 数据库的更改不会被记录到二进制日志中。

示例 3: 同时使用 binlog_do_db 和 binlog_ignore_db

假设我们有三个数据库 users, products 和 logs。 我们只想复制 users 和 products 数据库，但是 products 数据库中有一个表 products.temp_table，我们不想复制这个表。 这种情况比较复杂，binlog_do_db 和 binlog_ignore_db 无法直接实现。 我们需要结合其他复制过滤参数，例如 replicate_ignore_table，在 Slave 服务器上进行配置。 这里为了演示 binlog_do_db 和 binlog_ignore_db 的优先级，我们先假设只复制 users 和 products，并忽略 logs。

  1. 配置 Master 服务器的 my.cnf：

    [mysqld]
log_bin = mysql-bin  # 启用二进制日志
server-id = 1
binlog_do_db = users,products
binlog_ignore_db = logs

  2. 重启 Master 服务器：

    sudo systemctl restart mysqld

  3. 验证配置：

    SHOW GLOBAL VARIABLES LIKE 'binlog_do_db';
SHOW GLOBAL VARIABLES LIKE 'binlog_ignore_db';

    应该看到 Value 分别为 users,products 和 logs。

由于 binlog_ignore_db 优先于 binlog_do_db， 因此 logs 数据库的更改不会被记录到二进制日志中，即使它没有在 binlog_do_db 列表中。

Slave服务器上的复制过滤

仅仅在 Master 服务器上配置 binlog_do_db 和 binlog_ignore_db 是不够的。 Slave 服务器也需要配置复制过滤参数，才能实现精确复制。

Slave 服务器上常用的复制过滤参数包括：

  • replicate_do_db: 指定 Slave 服务器需要复制的数据库列表。
  • replicate_ignore_db: 指定 Slave 服务器不需要复制的数据库列表。
  • replicate_do_table: 指定 Slave 服务器需要复制的表列表。
  • replicate_ignore_table: 指定 Slave 服务器不需要复制的表列表。
  • replicate_wild_do_table: 使用通配符指定 Slave 服务器需要复制的表列表。
  • replicate_wild_ignore_table: 使用通配符指定 Slave 服务器不需要复制的表列表。

注意： Slave 服务器的复制过滤参数只影响 Slave 服务器的行为，与 Master 服务器无关。

replicate_do_db 和 replicate_ignore_db

这两个参数的功能与 binlog_do_db 和 binlog_ignore_db 类似，但是作用对象不同。 replicate_do_db 和 replicate_ignore_db 控制的是 Slave 服务器从 Master 服务器接收到的二进制日志中，哪些数据库的更改会被应用到 Slave 服务器上。

  • replicate_do_db: 指定 Slave 服务器需要复制的数据库列表。只有列出的数据库的更改才会被应用到 Slave 服务器。

  • replicate_ignore_db: 指定 Slave 服务器不需要复制的数据库列表。列出的数据库的更改不会被应用到 Slave 服务器。

注意： 如果同时设置了 replicate_do_db 和 replicate_ignore_db， 那么 replicate_ignore_db 优先于 replicate_do_db。

配置 replicate_do_db 和 replicate_ignore_db

可以通过以下方式配置这两个参数：

  1. 配置文件 (my.cnf/my.ini): 这是推荐的方式，因为配置会在服务器重启后仍然生效。

    [mysqld]
replicate_do_db = db1,db2
replicate_ignore_db = db3

  2. 命令行: 这种方式只在当前会话有效，服务器重启后配置会丢失。

    SET GLOBAL replicate_do_db = 'db1,db2';
SET GLOBAL replicate_ignore_db = 'db3';

    注意： 修改全局参数需要 SUPER 或 REPLICATION SLAVE ADMIN 权限。

示例 4: Master/Slave 复制过滤的完整配置

假设我们有三个数据库 users, products 和 logs。 我们只想在 Slave 服务器上复制 users 数据库。

  1. 配置 Master 服务器的 my.cnf：

    [mysqld]
log_bin = mysql-bin  # 启用二进制日志
server-id = 1
binlog_do_db = users

  2. 重启 Master 服务器：

    sudo systemctl restart mysqld

  3. 配置 Slave 服务器的 my.cnf：

    [mysqld]
server-id = 2
replicate_do_db = users

  4. 重启 Slave 服务器：

    sudo systemctl restart mysqld

  5. 启动 Slave 复制线程：

    START SLAVE;

现在，只有对 users 数据库的更改才会被记录到 Master 服务器的二进制日志中， 并且只有 users 数据库的更改才会被应用到 Slave 服务器上。

示例 5: binlog_ignore_db 和 replicate_ignore_db 的配合使用

假设我们有三个数据库 users, products 和 logs。 我们不想复制 logs 数据库。

  1. 配置 Master 服务器的 my.cnf：

    [mysqld]
log_bin = mysql-bin  # 启用二进制日志
server-id = 1
binlog_ignore_db = logs

  2. 重启 Master 服务器：

    sudo systemctl restart mysqld

  3. 配置 Slave 服务器的 my.cnf：

    [mysqld]
server-id = 2
replicate_ignore_db = logs

  4. 重启 Slave 服务器：

    sudo systemctl restart mysqld

  5. 启动 Slave 复制线程：

    START SLAVE;

现在，对 logs 数据库的更改不会被记录到 Master 服务器的二进制日志中， 并且 logs 数据库的更改也不会被应用到 Slave 服务器上。

复制过滤的优先级

当同时使用多种复制过滤参数时，理解它们的优先级非常重要。

| 参数类型 | 参数名 | 优先级 | 说明
of course, and the rest of the world.
| 参数类型 | 参数名 | 优先级 | 说明

发表回复

您的邮箱地址不会被公开。 必填项已用 * 标注