MySQL云原生与分布式之：`MySQL`的`Logical Replication`：`MySQL`与`PostgreSQL`的逻辑复制对比。

MySQL 云原生与分布式：MySQL 的 Logical Replication 与 PostgreSQL 的逻辑复制对比

大家好，今天我们来探讨 MySQL 的 Logical Replication 以及它与 PostgreSQL 逻辑复制的对比。在云原生和分布式架构盛行的今天，数据复制是构建高可用、可扩展系统的关键技术之一。Logical Replication 逻辑复制作为一种高级复制形式，在灵活性和功能性上都超越了传统的物理复制。

1. 什么是逻辑复制？

逻辑复制是一种基于数据变更逻辑含义的复制方法，与物理复制直接复制数据页不同，它复制的是数据变更的逻辑操作，例如 INSERT、UPDATE 和 DELETE 语句。这意味着：

• 解耦性更高： 源数据库和目标数据库可以拥有不同的存储引擎、表结构，甚至不同的数据库版本。
• 灵活性更强： 可以选择性地复制特定的表、列或数据行，实现更精细化的数据同步。
• 可扩展性更好： 可以将数据复制到不同的数据仓库、搜索引擎或分析平台，支持更广泛的应用场景。

2. MySQL 的 Logical Replication

MySQL 从 5.7 版本开始引入了 Logical Replication，它基于二进制日志 (Binary Log) 实现。基本原理如下：

1. 开启 Binary Log： 确保 MySQL 实例启用了 Binary Log，并设置了 binlog_format = ROW。ROW 格式记录了每一行数据的变更，是 Logical Replication 的基础。
2. 创建 Replication 用户： 创建一个拥有 REPLICATION SLAVE 和 REPLICATION CLIENT 权限的用户，用于连接源数据库并读取 Binary Log。
3. 配置源数据库： 设置 server_id，确保每个 MySQL 实例拥有唯一的 ID。
4. 配置目标数据库： 使用 CHANGE MASTER TO 语句连接源数据库，并指定 Binary Log 的起始位置。
5. 启动复制进程： 使用 START SLAVE 命令启动复制进程。

代码示例：MySQL 源数据库配置

-- 1. 检查 Binary Log 是否开启
SHOW VARIABLES LIKE 'log_bin';

-- 2. 设置 binlog_format
SET GLOBAL binlog_format = 'ROW';

-- 3. 创建 Replication 用户
CREATE USER 'repl'@'%' IDENTIFIED BY 'password';
GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'repl'@'%';
FLUSH PRIVILEGES;

-- 4. 设置 server_id
SET GLOBAL server_id = 1;

-- 5. 重新启动 MySQL 服务，使配置生效

代码示例：MySQL 目标数据库配置

-- 1. 停止 Slave (如果正在运行)
STOP SLAVE;

-- 2. 配置连接信息
CHANGE MASTER TO
  MASTER_HOST='source_db_host',
  MASTER_USER='repl',
  MASTER_PASSWORD='password',
  MASTER_LOG_FILE='mysql-bin.000001', -- 找到源数据库最新的 binlog 文件名
  MASTER_LOG_POS=4; -- 找到源数据库最新的 binlog 文件位置

-- 3. 启动 Slave
START SLAVE;

-- 4. 查看 Slave 状态
SHOW SLAVE STATUSG

MySQL Logical Replication 的优点：

• 集成度高： 直接集成在 MySQL 数据库中，无需额外的组件或工具。
• 易于配置： 配置过程相对简单，只需要修改 MySQL 的配置文件和执行 SQL 语句。
• 成熟稳定： 基于 Binary Log 机制，经过长时间的验证，具有较高的稳定性和可靠性。

MySQL Logical Replication 的缺点：

• 版本限制： 5.7 及以上版本才支持。
• DDL 支持有限： 某些 DDL 语句可能无法正确复制，需要手动处理。
• 性能影响： ROW 格式的 Binary Log 会增加磁盘 I/O 和网络传输的开销。
• 过滤功能较弱： 只能基于表进行过滤，无法基于列或数据行进行过滤。
• 不支持冲突检测和解决： 如果目标数据库存在数据冲突，可能会导致复制中断。

3. PostgreSQL 的 Logical Replication

PostgreSQL 从 10 版本开始引入了 Logical Replication，它基于发布/订阅 (Publish/Subscribe) 模式实现。基本原理如下：

1. 创建 Publication： 在源数据库上创建一个 Publication，指定需要复制的表。
2. 创建 Subscription： 在目标数据库上创建一个 Subscription，订阅源数据库的 Publication。
3. 配置复制槽 (Replication Slot)： Replication Slot 用于跟踪源数据库的 WAL (Write-Ahead Logging) 日志，确保数据不会丢失。
4. 启动复制进程： PostgreSQL 会自动启动复制进程，将数据从源数据库复制到目标数据库。

代码示例：PostgreSQL 源数据库配置

-- 1. 修改 postgresql.conf 文件，启用 WAL 逻辑解码
wal_level = logical

-- 2. 允许 replication 连接
-- 修改 pg_hba.conf 文件，添加如下内容
host    replication     repl_user     0.0.0.0/0               md5

-- 3. 创建 Publication
CREATE PUBLICATION my_publication FOR TABLE my_table;

-- 4. 创建 Replication 用户
CREATE USER repl_user WITH REPLICATION PASSWORD 'password';

-- 5. 重新启动 PostgreSQL 服务，使配置生效

代码示例：PostgreSQL 目标数据库配置

-- 1. 创建 Subscription
CREATE SUBSCRIPTION my_subscription
CONNECTION 'host=source_db_host port=5432 dbname=source_db user=repl_user password=password'
PUBLICATION my_publication;

PostgreSQL Logical Replication 的优点：

• 灵活性高： 支持基于表、列和数据行的过滤，可以实现更精细化的数据同步。
• 可扩展性强： 支持多个订阅者，可以将数据复制到不同的目标数据库。
• DDL 支持更好： 可以自动复制大部分 DDL 语句。
• 冲突检测和解决： 提供了一些机制来检测和解决数据冲突。
• 发布/订阅模式： 基于发布/订阅模式，可以实现更复杂的数据复制场景。

PostgreSQL Logical Replication 的缺点：

• 配置相对复杂： 配置过程比 MySQL 稍微复杂一些，需要创建 Publication 和 Subscription。
• 性能影响： WAL 逻辑解码会增加 CPU 的开销。
• 需要额外配置： 需要配置 Replication Slot 来保证数据的完整性。
• 学习曲线较陡峭： 相对于 MySQL，PostgreSQL 的 Logical Replication 概念更多，需要一定的学习成本。

4. MySQL 与 PostgreSQL Logical Replication 对比

下面我们用表格来更清晰地对比 MySQL 和 PostgreSQL 的 Logical Replication：

特性	MySQL Logical Replication	PostgreSQL Logical Replication
实现方式	基于 Binary Log	基于发布/订阅 (WAL 逻辑解码)
版本要求	5.7+	10+
配置复杂度	简单	相对复杂
过滤功能	只能基于表过滤	支持基于表、列和数据行过滤
DDL 支持	有限	更好
冲突检测和解决	不支持	支持
可扩展性	较弱	强
性能影响	磁盘 I/O 和网络传输	CPU 开销
稳定性和可靠性	高	高
学习曲线	简单	相对陡峭
应用场景	简单的数据同步场景	复杂的数据同步和集成场景

5. 如何选择合适的 Logical Replication 方案？

选择哪种 Logical Replication 方案取决于具体的应用场景和需求。

• 如果你的应用场景比较简单，只需要同步整个表的数据，并且对性能要求不高，那么 MySQL 的 Logical Replication 是一个不错的选择。它配置简单，易于使用，并且与 MySQL 数据库集成度高。
• 如果你的应用场景比较复杂，需要精细化的数据同步，例如只同步特定的列或数据行，或者需要将数据复制到多个目标数据库，那么 PostgreSQL 的 Logical Replication 更加适合。它提供了更强大的过滤功能和可扩展性，可以满足更复杂的需求。

6. 最佳实践

无论选择哪种 Logical Replication 方案，都需要注意以下几点：

• 监控复制状态： 定期检查复制状态，确保数据同步正常进行。
• 处理错误和冲突： 及时处理复制过程中出现的错误和冲突，避免数据丢失或不一致。
• 优化性能： 根据实际情况调整配置参数，优化复制性能。
• 备份和恢复： 定期备份源数据库和目标数据库，以便在发生故障时能够快速恢复。
• 权限管理： 合理分配 Replication 用户的权限，避免安全风险。
• 测试验证： 在生产环境部署之前，务必在测试环境中进行充分的测试和验证。

7. 案例分析

• MySQL Logical Replication 案例：

  • 将生产数据库的数据同步到只读副本，用于报表查询和分析。
  • 将数据同步到缓存数据库，提高读取性能。
  • 将数据同步到另一个 MySQL 实例，实现异地容灾。

• PostgreSQL Logical Replication 案例：

  • 将生产数据库的数据同步到数据仓库，用于数据分析和挖掘。
  • 将数据同步到搜索引擎，提供全文搜索功能。
  • 将数据同步到不同的微服务，实现数据共享和集成。

代码示例：MySQL Logical Replication 过滤特定表

假设我们只想复制 users 表和 orders 表，可以这样做：

1. 创建只包含特定表的 Publication： MySQL 的 Logical Replication 本身不支持 Publication 的概念，所以无法像 PostgreSQL 那样直接创建只包含特定表的 Publication。 但是可以通过在目标端设置 replicate-do-db 和 replicate-do-table 选项来实现类似的效果。 需要在目标端 MySQL 的配置文件 (my.cnf 或 my.ini) 中添加如下内容:

replicate-do-db=your_database_name
replicate-do-table=your_database_name.users
replicate-do-table=your_database_name.orders

2. 重启 Slave： 重启 MySQL Slave 服务，使配置生效。

代码示例：PostgreSQL Logical Replication 过滤特定列

假设我们只想复制 users 表的 id、name 和 email 列，可以这样做：

1. 创建只包含特定列的视图：

CREATE VIEW users_view AS
SELECT id, name, email
FROM users;

2. 创建 Publication：

CREATE PUBLICATION my_publication FOR TABLE users_view;

3. 创建 Subscription：

CREATE SUBSCRIPTION my_subscription
CONNECTION 'host=source_db_host port=5432 dbname=source_db user=repl_user password=password'
PUBLICATION my_publication;

代码示例：使用 pglogical 扩展进行更复杂的逻辑复制

PostgreSQL 还有一个强大的扩展 pglogical，它提供了更高级的逻辑复制功能，例如：

• 双向复制： 支持在多个数据库之间进行双向复制。
• 冲突解决： 提供了更灵活的冲突解决机制。
• 更复杂的过滤： 可以基于更复杂的条件进行过滤。

安装 pglogical 扩展:

CREATE EXTENSION pglogical;

使用 pglogical 扩展进行复制的例子比较复杂，需要创建 nodes, subscriptions, replications sets 等等。 可以参考 pglogical 的官方文档来了解详细的用法。

逻辑复制，灵活方案

MySQL 和 PostgreSQL 都提供了强大的逻辑复制功能，可以根据不同的应用场景和需求选择合适的方案。 了解他们的优缺点和最佳实践，可以帮助我们构建高可用、可扩展的云原生和分布式系统。 逻辑复制在保证数据一致性的同时，提供了更大的灵活性和可扩展性。

基于 Binlog 与发布订阅

MySQL 的逻辑复制基于 Binary Log 实现，配置简单，易于使用。 PostgreSQL 的逻辑复制基于发布/订阅模式实现，提供了更强大的过滤功能和可扩展性。

选择与实践，成就卓越

根据实际情况选择合适的 Logical Replication 方案，并遵循最佳实践，可以构建可靠、高效的数据复制系统。 熟练掌握逻辑复制技术，将助力于云原生和分布式架构的成功实施。