MySQL云原生与分布式之：`MySQL`的`Logical Replication`：`MySQL`与`PostgreSQL`的逻辑复制对比。 - 智猿学院-前后端，数据库，人工智能，云计算等领域前沿技术讲座

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

各位同学，大家好。今天我们来聊聊MySQL的逻辑复制，并将其与PostgreSQL的逻辑复制进行对比，以便大家更好地理解和选择合适的复制方案。

一、逻辑复制的概念与优势

逻辑复制（Logical Replication）是一种数据复制技术，它基于数据库的逻辑变化，如INSERT、UPDATE、DELETE操作，而不是物理存储层面的变化。与基于物理日志的复制（如MySQL的Binlog Replication）相比，逻辑复制具有以下优势：

更精细的数据控制： 可以选择复制特定的表、甚至表中的特定列。
跨版本兼容性更好： 即使主从数据库版本不同，只要逻辑格式兼容，也能进行复制。
更灵活的拓扑结构： 支持扇入、扇出等更复杂的复制拓扑。
减少数据库锁定： 逻辑复制通常对主库的影响较小。
易于数据转换： 可以在复制过程中进行数据转换和过滤。

二、MySQL的Logical Replication

MySQL从5.7.2开始引入了逻辑复制，并在8.0版本进行了增强。它基于Binlog（二进制日志）的GTID（全局事务标识符）机制，将Binlog解析成逻辑变化，并将其传输到订阅者。

2.1 MySQL Logical Replication的架构

MySQL的逻辑复制主要包含以下组件：

Publisher (发布者): 源数据库，负责将逻辑变化写入Binlog。
Binlog: 记录数据库逻辑变化的日志文件。
Replication Filter (复制过滤器): 用于选择需要复制的表和列。
Subscriber (订阅者): 目标数据库，接收并应用逻辑变化。
Replication Channel (复制通道): 用于Publisher和Subscriber之间的通信。

2.2 MySQL Logical Replication的配置

以下是一个简单的MySQL逻辑复制配置示例：

Publisher (源数据库) 配置:

启用Binlog和GTID:
```
# /etc/mysql/mysql.conf.d/mysqld.cnf
[mysqld]
log_bin = mysql-bin
server-id = 1
enforce_gtid_consistency = ON
gtid_mode = ON
binlog_format = ROW
binlog_row_image = FULL
```
- log_bin: 启用二进制日志。
- server-id: 设置服务器ID，确保集群中唯一。
- enforce_gtid_consistency: 强制使用GTID一致性。
- gtid_mode: 启用GTID模式。
- binlog_format: 设置Binlog格式为ROW，这是逻辑复制的必要条件。
- binlog_row_image: 设置为FULL，确保所有列都记录在binlog中。

创建复制用户:

CREATE USER 'repl'@'%' IDENTIFIED BY 'password';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
FLUSH PRIVILEGES;

REPLICATION SLAVE 权限允许用户从主服务器读取binlog。

Subscriber (目标数据库) 配置:

设置服务器ID:

# /etc/mysql/mysql.conf.d/mysqld.cnf
[mysqld]
server-id = 2
enforce_gtid_consistency = ON
gtid_mode = ON

配置复制通道:
```
CHANGE MASTER TO
  MASTER_HOST='publisher_ip',
  MASTER_USER='repl',
  MASTER_PASSWORD='password',
  MASTER_LOG_FILE='mysql-bin.000001',  --  初始 binlog 文件名
  MASTER_LOG_POS=4,                   --  初始 binlog 位置
  MASTER_AUTO_POSITION=1,             --  启用 GTID 自动定位
  GET_MASTER_PUBLIC_KEY=1;            --  如果主服务器启用了基于 RSA 的加密连接

START SLAVE;
```
- MASTER_HOST: Publisher的IP地址。
- MASTER_USER: 复制用户的用户名。
- MASTER_PASSWORD: 复制用户的密码。
- MASTER_LOG_FILE, MASTER_LOG_POS: 初始Binlog文件名和位置，通常在第一次配置时需要指定，之后会自动跟踪。
- MASTER_AUTO_POSITION=1: 启用基于GTID的自动定位，这是逻辑复制的关键。
- GET_MASTER_PUBLIC_KEY=1: 如果主服务器使用了基于 RSA 的加密连接，需要设置此选项。
检查复制状态:
```
SHOW SLAVE STATUSG;
```
- 查看 Slave_IO_Running 和 Slave_SQL_Running 是否为 Yes。

2.3 MySQL Logical Replication的限制

Binlog格式限制： 必须使用ROW格式。
GTID依赖： 依赖于GTID，因此需要启用GTID模式。
不支持DDL复制： 默认情况下，不支持DDL（数据定义语言）语句的复制。MySQL 8.0.23引入了DDL复制的增强功能，但仍需配置。
不支持所有存储引擎： 不支持所有存储引擎，例如MEMORY存储引擎。
性能影响： 开启Binlog和GTID会对性能产生一定影响。
复杂配置： 配置相对复杂，容易出错。
缺少内置的转换和过滤功能： 需要通过自定义开发来实现复杂的数据转换和过滤。

2.4 MySQL 8.0的增强功能

MySQL 8.0对逻辑复制进行了多项增强，包括：

DDL复制： 支持DDL语句的复制，简化了维护工作。
性能提升： 优化了复制性能，减少了延迟。
更灵活的过滤： 支持更灵活的过滤规则。
更完善的错误处理： 提供了更完善的错误处理机制。

三、PostgreSQL的Logical Replication

PostgreSQL从10版本开始引入了逻辑复制，并在此后的版本中不断完善。它基于WAL（预写式日志）的逻辑解码，将WAL日志解析成逻辑变化，并将其传输到订阅者。

3.1 PostgreSQL Logical Replication的架构

PostgreSQL的逻辑复制主要包含以下组件：

Publisher (发布者): 源数据库，负责将逻辑变化写入WAL。
WAL (预写式日志): 记录数据库逻辑变化的日志文件。
Publication (发布): 定义需要发布的表和列。
Subscription (订阅): 定义订阅的内容和连接信息。
Replication Slot (复制槽): 用于跟踪发布者的WAL日志位置，确保订阅者不会丢失数据。
Subscriber (订阅者): 目标数据库，接收并应用逻辑变化。

3.2 PostgreSQL Logical Replication的配置

以下是一个简单的PostgreSQL逻辑复制配置示例：

Publisher (源数据库) 配置:

配置postgresql.conf:
```
wal_level = logical
max_replication_slots = 10
max_wal_senders = 10
```
- wal_level = logical: 启用逻辑解码。
- max_replication_slots: 设置最大复制槽数量。
- max_wal_senders: 设置最大WAL发送者数量。
创建复制用户:
```
CREATE ROLE repl WITH REPLICATION LOGIN PASSWORD 'password';
```
- REPLICATION 属性允许用户进行复制连接。
创建Publication:
```
CREATE PUBLICATION my_publication FOR TABLE my_table;
-- 或者发布所有表
CREATE PUBLICATION all_tables_pub FOR ALL TABLES;
```
- CREATE PUBLICATION: 定义需要发布的表。可以指定单个表，也可以使用 FOR ALL TABLES 发布所有表。

Subscriber (目标数据库) 配置:

创建Subscription:
```
CREATE SUBSCRIPTION my_subscription
CONNECTION 'host=publisher_ip port=5432 dbname=mydb user=repl password=password'
PUBLICATION my_publication;
```
- CREATE SUBSCRIPTION: 定义订阅，指定连接信息和发布的名称。
- CONNECTION: 指定连接到发布者的连接字符串。
- PUBLICATION: 指定要订阅的发布名称。
检查复制状态:
```
SELECT * FROM pg_stat_subscription;
```
- 检查 substate 列，确保其状态为 r (running)。

3.3 PostgreSQL Logical Replication的优势

内置的数据转换和过滤功能： 支持使用转换函数进行数据转换和过滤。
灵活的配置： 配置相对简单，易于管理。
支持DDL复制： 支持DDL语句的复制。
更稳定的性能： 通常具有更稳定的性能。
支持多种数据类型： 支持更广泛的数据类型。
Replication Slot机制： Replication Slot机制保证了数据不会丢失。

3.4 PostgreSQL Logical Replication的限制

需要超级用户权限： 创建和管理复制槽需要超级用户权限。
早期版本限制： 早期版本（<10）不支持逻辑复制。
性能影响： 启用逻辑解码会对性能产生一定影响。
初始同步： 首次同步可能需要较长时间。

四、MySQL与PostgreSQL逻辑复制的对比

特性	MySQL Logical Replication	PostgreSQL Logical Replication
基础	Binlog和GTID	WAL和逻辑解码
配置复杂度	相对复杂	相对简单
DDL复制	MySQL 8.0.23开始支持，需要额外配置	支持
数据转换和过滤	需要自定义开发	内置转换函数
复制槽机制	无	Replication Slot，保证数据不丢失
性能	性能可能不稳定	相对稳定
拓扑结构	支持主从、主主、扇入、扇出	支持主从、扇入、扇出
数据类型支持	有限	更广泛
版本要求	MySQL 5.7.2+ (推荐 8.0+)	PostgreSQL 10+
错误处理	相对复杂	相对简单
易用性	较低	较高
适用场景	需要精细控制复制对象，对数据转换要求不高的场景	需要灵活的数据转换和过滤，对数据一致性要求高的场景
适用场景举例	数据备份、读写分离	数据集成、数据迁移、异构数据库同步

五、代码示例：PostgreSQL的转换函数

PostgreSQL的逻辑复制允许在复制过程中使用转换函数来修改数据。以下是一个简单的转换函数示例：

创建转换函数：

CREATE OR REPLACE FUNCTION mask_email(input_email TEXT)
RETURNS TEXT AS $$
BEGIN
    RETURN regexp_replace(input_email, '(.*)@(.*)', '****@2', 'g');
END;
$$ LANGUAGE plpgsql;

这个函数将Email地址的用户名部分替换为****。

创建Publication时使用转换函数：
```
CREATE PUBLICATION my_publication FOR TABLE users (
    id,
    mask_email(email), --  使用转换函数
    name
);
```
- 在这个例子中，email列在复制到订阅者之前，会经过mask_email函数的处理。

六、代码示例：MySQL的Binlog Event Filter (MEB)

虽然MySQL的逻辑复制本身不提供内置的转换函数，但是可以使用 MySQL Enterprise Backup (MEB) 的 Binlog Event Filter 来实现类似的功能。这需要编写Java代码来实现事件过滤和转换。

编写Java代码进行事件过滤和转换:

import com.mysql.cj.binlog.BinlogEvent;
import com.mysql.cj.binlog.BinlogEventFilter;

public class EmailMaskingFilter implements BinlogEventFilter {

    @Override
    public BinlogEvent apply(BinlogEvent event) {
        // 检查事件类型
        if (event.getType() == BinlogEvent.EventType.UPDATE_ROWS || event.getType() == BinlogEvent.EventType.WRITE_ROWS) {
            // 获取事件数据
            // 遍历每一行数据，找到email字段，进行masking
            // ... (复杂的逻辑，需要解析事件内容) ...
            String originalEmail = "..."; // 假设从事件中获取了email
            String maskedEmail = originalEmail.replaceAll("(.*)@(.*)", "****@$2");
            // ... (更新事件中的email字段) ...
        }
        return event;
    }
}

配置MEB使用该Filter:

需要配置MEB，将编译后的Java类作为插件加载，并配置相应的过滤规则。这部分配置比较复杂，涉及到MEB的安装和配置，以及插件的加载。具体步骤可以参考MySQL Enterprise Backup的官方文档。

七、实际应用场景选择建议

数据备份和读写分离： 如果主要需求是数据备份和读写分离，并且对数据转换没有特殊要求，MySQL的逻辑复制是一个可行的选择，尤其是在已经使用了MySQL的情况下。
数据集成和异构数据库同步： 如果需要将数据集成到其他数据库，或者需要进行复杂的数据转换和过滤，PostgreSQL的逻辑复制通常是更好的选择。
高可用性要求： 如果对数据一致性和高可用性有较高要求，PostgreSQL的Replication Slot机制可以提供更好的保障。
云原生环境： 在云原生环境中，PostgreSQL通常更容易与各种云服务集成，例如云数据库、消息队列等。

总而言之，选择哪种逻辑复制方案取决于具体的业务需求、技术栈和对数据一致性的要求。在选择之前，建议进行充分的测试和评估。

两种方案的差异小结

MySQL逻辑复制依赖于Binlog和GTID，配置相对复杂，数据转换需要自定义开发。PostgreSQL逻辑复制基于WAL，配置简单，内置转换函数，并有Replication Slot保证数据不丢失。选择哪种方案取决于业务需求和技术栈。

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

发表回复 取消回复

发表回复取消回复