PostgreSQL 和 MySQL 之间的交叉复制

我将概述 PostgreSQL 和 MySQL 之间的交叉复制，以及在两个数据库服务器之间设置交叉复制的方法。 通常，交叉复制数据库称为同类数据库，它是从一个 RDBMS 服务器移动到另一个 RDBMS 服务器的便捷方法。

PostgreSQL 和 MySQL 数据库通常被认为是关系数据库，但通过附加扩展，它们提供了 NoSQL 功能。 在这里，我们将从关系 DBMS 的角度讨论 PostgreSQL 和 MySQL 之间的复制。

我们不会描述整个内部工作原理，仅描述基本原理，以便您了解配置数据库服务器之间的复制、优点、限制和用例。

通常，两个相同的数据库服务器之间的复制以二进制模式或使用主服务器（也称为发布者、主服务器或主动服务器）和从服务器（订阅服务器、备用服务器或被动服务器）之间的查询来完成。 复制的目的是在从机端提供主数据库的实时副本。 在这种情况下，数据从master传输到slave，即从主动到被动，因为复制仅在一个方向上进行。 但是您可以在两个数据库之间设置双向复制，以便以主动-主动配置将数据从从数据库传输到主数据库。 所有这些，包括级联复制，都可以在两个或多个相同的数据库服务器之间进行。主动-主动或主动-被动配置取决于需要、初始配置中此类功能的可用性或外部配置解决方案的使用以及现有的权衡。

所描述的配置可以在不同的数据库服务器之间进行。 该服务器可以配置为接受来自另一个数据库服务器的复制数据，并且仍然维护复制数据的实时快照。 MySQL 和 PostgreSQL 在内部或通过第三方扩展提供大部分配置，包括二进制日志方法、磁盘锁定以及基于语句和行的方法。

从一台数据库服务器一次性迁移到另一台数据库服务器需要 MySQL 和 PostgreSQL 之间的交叉复制。 这些数据库使用不同的协议，因此不可能直接链接它们。 要建立数据交换，您可以使用外部开源工具，例如pg_chameleon。

什么是 pg_chameleon

pg_chameleon 是 Python 3 中从 MySQL 到 PostgreSQL 的复制系统。它使用同样用 Python 编写的开源 mysql-replication 库。 行图像从 MySQL 表中提取并作为 JSONB 对象存储在 PostgreSQL 数据库中，然后通过 pl/pgsql 函数解密并在 PostgreSQL 数据库中重现。

pg_chameleon 的特点

可以通过一对多配置将同一集群中的多个 MySQL 模式复制到单个目标 PostgreSQL 数据库
源架构名称和目标架构名称不能相同。
可以从级联 MySQL 副本中检索复制数据。
无法复制或产生错误的表被排除在外。
每个复制功能都由守护进程控制。
通过基于 YAML 的参数和配置文件进行控制。

例子

主持人
vm1
vm2

操作系统版本
CentOS Linux 7.6 x86_64
CentOS Linux 7.5 x86_64

数据库服务器版本
MySQL 5.7.26
PostgreSQL 10.5

数据库端口
3306
5433

IP地址
192.168.56.102
192.168.56.106

首先，准备安装 pg_chameleon 所需的所有组件。 此示例安装 Python 3.6.8，它创建并激活虚拟环境。

$> wget https://www.python.org/ftp/python/3.6.8/Python-3.6.8.tar.xz
$> tar -xJf Python-3.6.8.tar.xz
$> cd Python-3.6.8
$> ./configure --enable-optimizations
$> make altinstall

成功安装Python3.6后，您需要完成剩余的要求，例如创建和激活虚拟环境。 此外，pip模块已更新到最新版本并用于安装pg_chameleon。 下面的命令有意安装 pg_chameleon 2.0.9，尽管最新版本是 2.0.10。 这是避免更新版本中出现新错误所必需的。

$> python3.6 -m venv venv
$> source venv/bin/activate
(venv) $> pip install pip --upgrade
(venv) $> pip install pg_chameleon==2.0.9

然后我们使用 set_configuration_files 参数调用 pg_chameleon （chameleon 是一个命令）来启用 pg_chameleon 并创建默认目录和配置文件。

(venv) $> chameleon set_configuration_files
creating directory /root/.pg_chameleon
creating directory /root/.pg_chameleon/configuration/
creating directory /root/.pg_chameleon/logs/
creating directory /root/.pg_chameleon/pid/
copying configuration  example in /root/.pg_chameleon/configuration//config-example.yml

现在我们创建 config-example.yml 的副本作为 default.yml，使其成为默认配置文件。 下面提供了此示例的示例配置文件。

$> cat default.yml
---
#global settings
pid_dir: '~/.pg_chameleon/pid/'
log_dir: '~/.pg_chameleon/logs/'
log_dest: file
log_level: info
log_days_keep: 10
rollbar_key: ''
rollbar_env: ''

# type_override allows the user to override the default type conversion into a different one.
type_override:
  "tinyint(1)":
    override_to: boolean
    override_tables:
      - "*"

#postgres  destination connection
pg_conn:
  host: "192.168.56.106"
  port: "5433"
  user: "usr_replica"
  password: "pass123"
  database: "db_replica"
  charset: "utf8"

sources:
  mysql:
    db_conn:
      host: "192.168.56.102"
      port: "3306"
      user: "usr_replica"
      password: "pass123"
      charset: 'utf8'
      connect_timeout: 10
    schema_mappings:
      world_x: pgworld_x
    limit_tables:
#      - delphis_mediterranea.foo
    skip_tables:
#      - delphis_mediterranea.bar
    grant_select_to:
      - usr_readonly
    lock_timeout: "120s"
    my_server_id: 100
    replica_batch_size: 10000
    replay_max_rows: 10000
    batch_retention: '1 day'
    copy_max_memory: "300M"
    copy_mode: 'file'
    out_dir: /tmp
    sleep_loop: 1
    on_error_replay: continue
    on_error_read: continue
    auto_maintenance: "disabled"
    gtid_enable: No
    type: mysql
    skip_events:
      insert:
        - delphis_mediterranea.foo #skips inserts on the table delphis_mediterranea.foo
      delete:
        - delphis_mediterranea #skips deletes on schema delphis_mediterranea
      update:

本示例中的配置文件是一个示例 pg_chameleon 文件，经过少量修改以适应源环境和目标环境，下面是配置文件不同部分的概述。

在default.yml配置文件中有一个全局设置部分，您可以在其中管理诸如锁定文件位置、日志位置、日志存储期限等设置。接下来是类型覆盖部分，其中复制期间覆盖类型的一组规则。 该示例默认使用将tinyint(1) 转换为布尔值的类型覆盖规则。 在下一节中，我们指定目标数据库的连接详细信息。 在我们的例子中，这是一个 PostgreSQL 数据库，指定为 pg_conn。 最后一节，我们指明源数据，即源数据库的连接参数、源数据库与目标数据库之间的映射方案、需要跳过的表、等待时间、内存、包大小。 请注意，“源”是复数，这意味着我们可以将多个源数据库添加到单个目标数据库以设置多对一配置。

示例数据库 world_x 包含 4 个表，其中包含 MySQL 社区提供的行作为示例。 可以下载 这里。 示例数据库以 tar 和压缩存档形式提供，其中包含创建和导入行的说明。

在 MySQL 和 PostgreSQL 数据库中，会创建一个具有相同名称 usr_replica 的特殊用户。 在 MySQL 中，它被赋予了对所有复制表的额外读取权限。

mysql> CREATE USER usr_replica ;
mysql> SET PASSWORD FOR usr_replica='pass123';
mysql> GRANT ALL ON world_x.* TO 'usr_replica';
mysql> GRANT RELOAD ON *.* to 'usr_replica';
mysql> GRANT REPLICATION CLIENT ON *.* to 'usr_replica';
mysql> GRANT REPLICATION SLAVE ON *.* to 'usr_replica';
mysql> FLUSH PRIVILEGES;

在 PostgreSQL 端，创建了一个 db_replica 数据库，该数据库将接受来自 MySQL 数据库的更改。 PostgreSQL 中的用户 usr_replica 会自动配置为两个模式 pgworld_x 和 sch_chameleon 的所有者，这两个模式分别包含实际的复制表和复制目录表。 create_replica_schema 参数负责自动配置，如下所示。

postgres=# CREATE USER usr_replica WITH PASSWORD 'pass123';
CREATE ROLE
postgres=# CREATE DATABASE db_replica WITH OWNER usr_replica;
CREATE DATABASE

MySQL 数据库配置了一些参数更改，以准备复制，如下所示。 您需要重新启动数据库服务器才能使更改生效。

$> vi /etc/my.cnf
binlog_format= ROW
binlog_row_image=FULL
log-bin = mysql-bin
server-id = 1

现在，检查与两个数据库服务器的连接非常重要，以便运行 pg_chameleon 命令时不会出现问题。

在 PostgreSQL 节点上：

$> mysql -u usr_replica -Ap'admin123' -h 192.168.56.102 -D world_x

在MySQL节点上：

$> psql -p 5433 -U usr_replica -h 192.168.56.106 db_replica

接下来的三个 pg_chameleon (chameleon) 命令准备环境、添加源并初始化副本。 pg_chameleon 的 create_replica_schema 参数在 PostgreSQL 数据库中创建默认模式 (sch_chameleon) 和复制模式 (pgworld_x)，正如我们已经讨论过的。 add_source参数通过读取配置文件（default.yml）将源数据库添加到配置中，在我们的例子中是mysql，init_replica根据配置文件中的参数初始化配置。

$> chameleon create_replica_schema --debug
$> chameleon add_source --config default --source mysql --debug
$> chameleon init_replica --config default --source mysql --debug

这三个命令的输出清楚地表明它们已成功执行。 任何崩溃或语法错误都会以简单、清晰的消息进行报告，并提供如何解决问题的提示。

最后，我们使用 start_replica 开始复制并收到成功消息。

$> chameleon start_replica --config default --source mysql 
output: Starting the replica process for source mysql

可以使用 show_status 参数查询复制状态，并可以使用 show_errors 参数查看错误。

结果。

正如我们已经说过的，每个复制功能都是由守护进程控制的。 为了查看它们，我们使用 Linux ps 命令查询进程表，如下所示。

结果。

在我们实时测试复制之前，复制并不被视为已配置，如下所示。 我们创建一个表，将几条记录插入 MySQL 数据库，然后调用 pg_chameleon 中的sync_tables 参数来更新守护进程，并将包含记录的表复制到 PostgreSQL 数据库。

mysql> create table t1 (n1 int primary key, n2 varchar(10));
Query OK, 0 rows affected (0.01 sec)
mysql> insert into t1 values (1,'one');
Query OK, 1 row affected (0.00 sec)
mysql> insert into t1 values (2,'two');
Query OK, 1 row affected (0.00 sec)

$> chameleon sync_tables --tables world_x.t1 --config default --source mysql
Sync tables process for source mysql started.

为了确认测试结果，我们从 PostgreSQL 数据库查询表并输出行。

$> psql -p 5433 -U usr_replica -d db_replica -c "select * from pgworld_x.t1";
 n1 |  n2
----+-------
  1 | one
  2 | two

如果我们正在进行迁移，以下 pg_chameleon 命令将结束迁移。 在我们确定所有目标表的行都已复制后，需要执行这些命令，结果将是一个整齐迁移的 PostgreSQL 数据库，而无需引用源数据库或复制方案（sch_chameleon）。

$> chameleon stop_replica --config default --source mysql 
$> chameleon detach_replica --config default --source mysql --debug

如果需要，您可以使用以下命令删除原始配置和复制方案。

$> chameleon drop_source --config default --source mysql --debug
$> chameleon drop_replica_schema --config default --source mysql --debug

pg_chameleon的优点

轻松设置和配置。
通过清晰的错误消息轻松排除故障并识别异常情况。
初始化后可以将其他特殊表添加到复制中，而无需更改其余配置。
可以为单个目标数据库配置多个源数据库，如果您要将一个或多个 MySQL 数据库中的数据组合到单个 PostgreSQL 数据库中，这将非常有用。
您不必复制选定的表。

pg_chameleon 的缺点

仅支持 MySQL 5.5 及以上版本作为源数据库和 PostgreSQL 9.5 及以上版本作为目标数据库。
每个表必须有一个主键或唯一键，否则表会在 init_replica 过程中初始化但不会被复制。
单向复制 - 仅从 MySQL 到 PostgreSQL。 因此，它只适用于“有源-无源”电路。
源只能是 MySQL 数据库，对 PostgreSQL 数据库作为源的支持只是实验性的并且有限制（了解更多 这里)

pg_chameleon 的结果

pg_chameleon 中的复制方法非常适合将数据库从 MySQL 迁移到 PostgreSQL。 显着的缺点是复制只是单向的，因此数据库专业人员不太可能将其用于迁移以外的任何用途。 但单向复制的问题可以通过另一种开源工具——SymmetricDS 来解决。

阅读官方文档了解更多内容 这里。 可以找到命令行帮助 这里.

SymmetricDS 概述

SymmetricDS 是一个开源工具，可以将任何数据库复制到任何其他常见数据库：Oracle、MongoDB、PostgreSQL、MySQL、SQL Server、MariaDB、DB2、Sybase、Greenplum、Informix、H2、Firebird 和其他云数据库实例，例如 Redshift 和Azure等。可用功能：数据库和文件同步、多主数据库复制、过滤同步、转换等。 这是一个 Java 工具，需要标准版本的 JRE 或 JDK（版本 8.0 或更高版本）。 这里，可以记录源数据库中触发器的数据变化，并以批量的形式发送到相应的目标数据库。

对称 DS 功能

该工具是独立于平台的，这意味着两个或多个不同的数据库可以交换数据。
关系数据库使用数据更改记录进行同步，而基于文件系统的数据库则使用文件同步。
基于一组规则使用推式和拉式方法的双向复制。
数据传输可以通过安全和低带宽网络进行。
节点发生故障后恢复运行时的自动恢复以及自动冲突解决。
云兼容且强大的扩展 API。

例子

SymmetricDS 可以通过以下两种方式之一进行配置：
主（父）节点集中协调两个从（子）节点之间的数据复制，子节点之间的通信仅通过父节点进行。
主动节点（节点 1）可以与另一个主动节点（节点 2）进行复制通信，无需中介。

在这两个选项中，数据交换都是使用“推”和“拉”进行的。 在此示例中，我们将考虑主动-主动配置。 描述整个架构需要很长时间，因此请做好研究。 领导了解有关 SymmetricDS 设备的更多信息。

安装 SymmetricDS 非常简单：下载 zip 文件的开源版本 故 并将其取出到任何你想要的地方。 下表提供了有关本示例中 SymmetricDS 的安装位置和版本的信息，以及两个节点的数据库版本、Linux 版本、IP 地址和端口的信息。

主持人
vm1
vm2

操作系统版本
CentOS Linux 7.6 x86_64
CentOS Linux 7.6 x86_64

数据库服务器版本
MySQL 5.7.26
PostgreSQL 10.5

数据库端口
3306
5832

IP地址
192.168.1.107
192.168.1.112

对称DS版本
对称DS 3.9
对称DS 3.9

SymmetricDS安装路径
/usr/local/对称服务器-3.9.20
/usr/local/对称服务器-3.9.20

SymmetricDS 节点名称
corp-000
商店 001

这里我们将SymmetricDS安装在/usr/local/symmetric-server-3.9.20中，其中将存储各种子目录和文件。 我们对样本和引擎子目录感兴趣。 示例目录包含具有节点属性的示例配置文件以及可帮助您快速入门的示例 SQL 脚本。

在示例目录中，我们看到三个具有节点属性的配置文件 - 名称显示了特定方案中节点的性质。

corp-000.properties
store-001.properties
store-002.properties

SymmetricDS 具有基本 3 节点设计（选项 1）所需的所有配置文件，并且相同的文件可用于 2 节点设计（选项 2）。 将所需的配置文件从示例目录复制到 vm1 主机上的引擎。 结果是这样的：

$> cat engines/corp-000.properties
engine.name=corp-000
db.driver=com.mysql.jdbc.Driver
db.url=jdbc:mysql://192.168.1.107:3306/replica_db?autoReconnect=true&useSSL=false
db.user=root
db.password=admin123
registration.url=
sync.url=http://192.168.1.107:31415/sync/corp-000
group.id=corp
external.id=000

SymmetricDS 配置中的该节点称为 corp-000，数据库连接由 mysql jdbc 驱动程序处理，该驱动程序使用上面的连接字符串和登录凭据。 我们连接到replica_db数据库，并且将在模式创建期间创建表。 sync.url 显示联系节点进行同步的位置。

主机 vm2 上的节点 2 配置为 store-001，其余部分在下面的 node.properties 文件中指定。 节点 store-001 运行 PostgreSQL 数据库，pgdb_replica 是复制数据库。 Registration.url 允许主机 vm2 联系主机 vm1 并从中接收配置详细信息。

$> cat engines/store-001.properties
engine.name=store-001
db.driver=org.postgresql.Driver
db.url=jdbc:postgresql://192.168.1.112:5832/pgdb_replica
db.user=postgres
db.password=admin123
registration.url=http://192.168.1.107:31415/sync/corp-000
group.id=store
external.id=001

完成的 SymmetricDS 示例包含用于在两个数据库服务器（两个节点）之间设置双向复制的参数。 以下步骤在主机 vm1 (corp-000) 上执行，这将创建一个包含 4 个表的示例架构。 然后使用 symadmin 命令运行 create-sym-tables 会创建目录表，其中将存储节点之间的复制规则和方向。 最后，样本数据被加载到表中。

vm1$> cd /usr/local/symmetric-server-3.9.20/bin
vm1$> ./dbimport --engine corp-000 --format XML create_sample.xml
vm1$> ./symadmin --engine corp-000 create-sym-tables
vm1$> ./dbimport --engine corp-000 insert_sample.sql

在示例中，item 和 item_looking_price 表自动配置为从 corp-000 复制到 store-001，销售表（sale_transaction 和 sale_return_line_item）自动配置为从 store-001 复制到 corp-000。 现在，我们在主机 vm2 (store-001) 上的 PostgreSQL 数据库中创建一个架构，以准备从 corp-000 接收数据。

vm2$> cd /usr/local/symmetric-server-3.9.20/bin
vm2$> ./dbimport --engine store-001 --format XML create_sample.xml

请务必检查 vm1 上的 MySQL 数据库是否具有示例表和 SymmetricDS 目录表。 请注意，SymmetricDS 系统表（以 sym_ 为前缀）当前仅在节点 corp-000 上可用，因为这是我们运行 create-sym-tables 命令并将管理复制的位置。 在节点 store-001 上的数据库中，只有 4 个没有数据的示例表。

全部。 环境已准备好在两个节点上运行 sym 服务器进程，如下所示。

vm1$> cd /usr/local/symmetric-server-3.9.20/bin
vm1$> sym 2>&1 &

日志条目将发送到 SymmetricDS 安装目录下的日志文件夹中的后台日志文件 (symmetry.log) 以及标准输出。 现在可以在节点 store-001 上启动 sym 服务器。

vm2$> cd /usr/local/symmetric-server-3.9.20/bin
vm2$> sym 2>&1 &

如果您在 vm2 主机上运行 sym 服务器进程，它还将在 PostgreSQL 数据库中创建 SymmetricDS 目录表。 如果您在两个节点上运行 sym 服务器进程，它们将相互协调以将数据从 corp-000 复制到 store-001。 如果几秒钟后我们查询两侧的所有 4 个表，我们将看到复制成功。 或者，您可以使用以下命令将引导程序从 corp-001 发送到节点 store-000。

vm1$> ./symadmin --engine corp-000 reload-node 001

此时，一条新记录已插入到节点 corp-000（主机：vm1）上的 MySQL 数据库的 item 表中，您可以检查其复制到节点 store-001（主机：vm2）上的 PostgreSQL 数据库。 我们看到一个 Pull 操作将数据从 corp-000 移动到 store-001。

mysql> insert into item values ('22000002','Jelly Bean');
Query OK, 1 row affected (0.00 sec)

vm2$> psql -p 5832 -U postgres pgdb_replica -c "select * from item"
 item_id  |   name
----------+-----------
 11000001 | Yummy Gum
 22000002 | Jelly Bean
(2 rows)

为了执行 Push 操作将数据从 store-001 移动到 corp-000，我们将一条记录插入 sale_transaction 表并验证复制是否成功。

结果。

我们看到 MySQL 和 PostgreSQL 数据库之间示例表的双向复制已成功设置。 要为新用户表设置复制，请执行以下步骤： 我们以创建表t1为例，并配置其复制规则如下。 这样，我们仅配置从 corp-000 到 store-001 的复制。

mysql> create table  t1 (no integer);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into sym_channel (channel_id,create_time,last_update_time) 
values ('t1',current_timestamp,current_timestamp);
Query OK, 1 row affected (0.01 sec)

mysql> insert into sym_trigger (trigger_id, source_table_name,channel_id,
last_update_time, create_time) values ('t1', 't1', 't1', current_timestamp,
current_timestamp);
Query OK, 1 row affected (0.01 sec)

mysql> insert into sym_trigger_router (trigger_id, router_id,
Initial_load_order, create_time,last_update_time) values ('t1',
'corp-2-store-1', 1, current_timestamp,current_timestamp);
Query OK, 1 row affected (0.01 sec)

然后，使用带有sync-triggers参数的symadmin命令向配置通知架构更改，即添加新表，这将重新创建触发器以映射表定义。 执行 send-schema 将架构更改发送到节点 store-001，并配置表 t1 的复制。

vm1$> ./symadmin -e corp-000 --node=001 sync-triggers    
vm1$> ./symadmin send-schema -e corp-000 --node=001 t1

SymmetricDS 的优点

易于安装和配置，包括一组现成的文件，其中包含用于创建三节点或两节点电路的参数。
跨平台数据库和平台独立性，包括服务器、笔记本电脑和移动设备。
将任何数据库复制到本地、WAN 或云中的任何其他数据库。
可以与几个或数千个数据库进行最佳工作，以方便复制。
付费版本带有 GUI 和出色的支持。

SymmetricDS 的缺点

您需要在命令行上通过SQL语句手动定义复制的规则和方向来加载目录表，这可能很不方便。
设置许多表进行复制可能会很乏味，除非您使用脚本创建定义复制规则和方向的 SQL 语句。
日志中记录的信息太多，有时您需要整理日志文件，以免占用太多空间。

SymmetricDS 的结果

SymmetricDS 允许您在两个、三个甚至数千个节点之间设置双向复制，以复制和同步文件。 这是一个独特的工具，可以独立执行许多任务，例如节点长时间停机后的自动数据恢复、节点之间通过 HTTPS 进行安全高效的数据交换、基于一组规则的自动冲突管理等。SymmetricDS 执行任何数据库之间的复制，因此可用于多种场景，包括跨平台数据的迁移、迁移、分发、过滤和转换。

该示例基于官方 快速指南 由对称DS。 在 用户手册 详细描述使用 SymmetricDS 设置复制所涉及的各种概念。

来源： habr.com