数据中台的数据库实时同步：作用、性能消耗与优化策略

码奋 2024-12-30

数据库实时同步在数据中台建设中具有重要作用，主要体现在提高系统的高可用性、负载均衡、灾难恢复能力、数据一致性以及支持地理分布式部署。然而，实时同步也会带来一定的性能消耗，主要包括增加 I/O 操作、网络带宽压力、CPU 使用率和存储需求，同时可能引发复制延迟、锁竞争和管理复杂性等问题。因此，在实施数据库实时同步时，需要根据业务需求选择合适的同步方式，并通过提升硬件性能、优化同步策略、监控同步延迟和采用数据压缩等手段，减少性能负担，确保系统稳定高效运行。

1. MySQL 主从复制（Master-Slave Replication）

主从复制是 MySQL 实现实时同步的经典方式。其基本原理是：一台主服务器（Master）接受写操作，而一台或多台从服务器（Slave）复制主服务器的变动数据。

设置步骤：

主服务器：

配置 my.cnf 文件，启用二进制日志：

[mysqld]
server-id=1
log-bin=mysql-bin

创建复制用户：

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

锁定数据库并获取二进制日志位置：

FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;

在主服务器上备份数据库。

从服务器：

配置 my.cnf 文件：

[mysqld]
server-id=2

将主数据库的备份恢复到从数据库。

配置从服务器连接主服务器：

CHANGE MASTER TO
    MASTER_HOST='master_ip',
    MASTER_USER='replica_user',
    MASTER_PASSWORD='password',
    MASTER_LOG_FILE='mysql-bin.000001',
    MASTER_LOG_POS=1234;

启动从服务器的复制：

START SLAVE;
SHOW SLAVE STATUS\G;

2. MySQL 半同步复制

半同步复制在主从复制的基础上增加了一些保证，确保至少有一个从服务器确认收到数据后，主服务器才会返回响应。

设置步骤：

安装并启用半同步插件：

sudo apt-get install mysql-server-plugin-semi-sync

在主服务器和从服务器的 my.cnf 中启用：

[mysqld]
rpl_semi_sync_master_enabled=1
rpl_semi_sync_slave_enabled=1

重启 MySQL 服务，确保半同步插件已加载。

配置主服务器：

INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';

配置从服务器：

INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';

3. MySQL Group Replication

Group Replication 是 MySQL 5.7 版本之后提供的一种多主复制方式，支持在多个 MySQL 实例间自动同步数据，并确保数据一致性。

设置步骤：

在所有节点的 my.cnf 配置文件中启用 Group Replication：

[mysqld]
server-id=1
log_bin=mysql-bin
binlog_format=row
gtid_mode=ON
enforce-gtid-consistency=ON
group_replication=ON
group_replication_start_on_boot=ON
group_replication_group_name="aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee"
group_replication_local_address="10.0.0.1:33061"
group_replication_group_seeds="10.0.0.2:33061,10.0.0.3:33061"

启动 Group Replication：

START GROUP_REPLICATION;

通过 SHOW STATUS LIKE 'group_replication%' 查看集群状态。

4. MySQL 基于 GTID 的复制

GTID（全局事务标识符）是一种自动化的事务标识方法，可以避免传统复制中的很多问题，简化了主从切换和数据恢复的操作。

设置步骤：

配置主服务器启用 GTID：

[mysqld]
gtid_mode=ON
enforce-gtid-consistency=ON

配置从服务器启用 GTID：

[mysqld]
gtid_mode=ON
enforce-gtid-consistency=ON

在主服务器创建复制用户，并在从服务器配置复制：

CHANGE MASTER TO
    MASTER_HOST='master_ip',
    MASTER_USER='replica_user',
    MASTER_PASSWORD='password',
    MASTER_AUTO_POSITION=1;

PostgreSQL 数据库的实时同步可以通过以下几种方式实现，适用于 Linux 环境：

1. PostgreSQL 主从复制（Streaming Replication）

PostgreSQL 提供了基于流式复制（Streaming Replication）的主从同步机制，主数据库（Primary）将 WAL（Write-Ahead Log）日志流式地传输给从数据库（Standby）。从数据库会实时接收到主数据库的更新，并进行同步。

设置步骤：

主服务器配置：

修改 postgresql.conf 文件，启用流复制：

wal_level = replica
max_wal_senders = 3
hot_standby = on
listen_addresses = '*'

在 pg_hba.conf 文件中添加允许从数据库连接的条目：

host replication all 192.168.1.0/24 md5

创建复制用户：

CREATE ROLE replicator WITH REPLICATION PASSWORD 'password' LOGIN;

重启 PostgreSQL 服务：

sudo systemctl restart postgresql

从服务器配置：

停止从数据库的 PostgreSQL 服务。

使用 pg_basebackup 工具从主数据库创建数据备份：

pg_basebackup -h master_ip -D var/lib/postgresql/13/main -U replicator -v -P --wal-method=stream

创建一个 recovery.conf 文件，配置从服务器连接主服务器：

standby_mode = 'on'
primary_conninfo = 'host=master_ip port=5432 user=replicator password=password'
trigger_file = '/tmp/postgresql.trigger.5432'

启动从数据库：

sudo systemctl start postgresql

验证复制状态：在从数据库上执行：

SELECT * FROM pg_stat_wal_receiver;

如果复制正常，应该能够看到数据流传输信息。

2. PostgreSQL 基于 GTID 的复制

PostgreSQL 不原生支持 GTID（全局事务标识符），但可以通过 pglogical 插件实现类似 GTID 的功能，提供更高的事务一致性，特别适合需要在多个节点之间进行自动切换的高可用性环境。

设置步骤：

安装 pglogical 插件：

sudo apt-get install postgresql-13-pglogical

在主数据库的 postgresql.conf 中加载插件：

shared_preload_libraries = 'pglogical'

在主数据库上启用 pglogical 扩展：

CREATE EXTENSION pglogical;

配置从服务器，安装并加载插件：

CREATE EXTENSION pglogical;

使用 pglogical 配置复制：

SELECT pg_create_logical_replication_subscription('subscription_name', 'host=master_ip user=replicator dbname=postgres');

3. Logical Replication（逻辑复制）

PostgreSQL 10 版本后引入了逻辑复制，允许用户选择特定的表或数据库进行复制。这是基于流复制的另一种方法，适合在单独的数据库或特定表之间进行复制，而不是整个数据库。

设置步骤：

主服务器配置：

修改 postgresql.conf 文件，启用逻辑复制：

wal_level = logical
max_replication_slots = 4
max_wal_senders = 4

在 pg_hba.conf 文件中添加复制用户的访问权限：

host replication replicator 192.168.1.0/24 md5

创建复制用户：

CREATE ROLE replicator WITH REPLICATION PASSWORD 'password' LOGIN;

重启 PostgreSQL 服务：

sudo systemctl restart postgresql

从服务器配置：

创建复制槽和订阅：在主数据库上创建一个逻辑复制槽：

SELECT pg_create_logical_replication_slot('slot_name', 'pgoutput');

在从数据库上创建订阅，订阅主数据库的复制：

CREATE SUBSCRIPTION subscription_name
CONNECTION 'host=master_ip port=5432 user=replicator password=password dbname=postgres'
PUBLICATION publication_name;

从数据库会自动开始接收数据并进行同步。

查看复制状态：在从数据库上，使用以下命令查看复制状态：

SELECT * FROM pg_stat_replication;

4. Barman pgBackRest（备份和灾难恢复工具）

Barman 和 pgBackRest 是两个流行的备份和恢复工具，它们不仅支持备份功能，还可以提供高可用性和实时的主从同步功能。通过这些工具，可以实现备份数据的实时同步和灾难恢复。

Barman： Barman 是一个用于 PostgreSQL 数据库备份和恢复的工具，它支持备份的实时同步。通过配置 Barman，你可以让多个 PostgreSQL 节点保持数据同步。

pgBackRest： pgBackRest 是另一个高性能的备份工具，支持增量备份和备份加密，适合对实时同步和数据备份有较高要求的生产环境。

往期推荐

Zookeeper的介绍和集群安装

数据平台：Ceph介绍和安装

开源的商业智能BI：Apache Superset介绍&安装

postgresql mysql主从配置数据库服务器 mysql创建数据库优化策略

文章转载自码奋，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

数据中台的数据库实时同步：作用、性能消耗与优化策略

1. MySQL 主从复制（Master-Slave Replication）

2. MySQL 半同步复制

3. MySQL Group Replication

4. MySQL 基于 GTID 的复制

1. PostgreSQL 主从复制（Streaming Replication）

2. PostgreSQL 基于 GTID 的复制

3. Logical Replication（逻辑复制）

4. Barman pgBackRest（备份和灾难恢复工具）

评论