这篇文章主要介绍“MySQL和PostgreSQL之间如何跨数据库进行复制”，在日常操作中，相信很多人在MySQL和PostgreSQL之间如何跨数据库进行复制问题上存在疑惑，小编查阅了各式资料，整理出简单好用的操作方法，希望对大家解答”MySQL和PostgreSQL之间如何跨数据库进行复制”的疑惑有所帮助！接下来，请跟着小编一起来学习吧！

涉及跨数据库复制的databases一般被称作异构databases。这是将数据从一种RDBMS server复制到另一种server的一种很好的方法。

PostgreSQL和MySQL都是传统的RDBMS数据库，但是他们也提供了NoSQL的能力。本文主要从RDBMS的角度讨论PostgreSQL和MySQL之间的复制问题。不对复制内部机制做详细介绍，只对一些基本元素、如何配置、有点、限制以及一些使用案例进行阐述。

通常情况下，两个种类相同的主备之间使用binary模式或者query模式进行复制。复制的目的在于，在备上能够得到主的实时备份数据，从而形成一个active-passive模式（因为复制只配置单向复制）。当然，也可以配置成向同步，构建active-active模式。

可以在两个不同数据库server之间配置上面的两种模式，其中一个数据库server可以配置从另外一个完全不同的数据库server上接收副本数据并维护副本数据的实时快照。MySQL和PostgreSQL通过原生机制或者第三方插件（包括binlog方法、磁盘块方法、基于语句和行的方法）完成上面提到的模式。

由于MySQL和PostgreSQL使用不同的复制协议，所以他们之间不能互相交互。为了达到通信流的目的，可以使用一个开源软件pg_chameleon。

pg_chameleon是由python3开发的MySQL to PG的复制工具。该插件也会使用一个mysql-replication的开源库，该库也是由Python3开发。从MySQL表中拉取行镜像并存储成JSONB形式，然后同步到PG数据库。PG数据库通过pl/pgsql进行解析并回放。

1、同一个集群中多个MySQL schema可以复制到一个PG database，形成many-to-one复制模式。

2、源和目的schema名可以不一样

3、复制数据可以从mysql级联副本中拉取。

4、会排除复制失败的表及复制过程中产生错误的表。

5、每个复制功能通过守护进程进行管理

6、配置参数和配置文件以yaml结构进行控制。

Host

Vm1

Vm2

操作系统

Centos linux release 7.6 x86_64

Centos linux release 7.5 x86_64

数据库版本

MySQL5.7.26

PostgreSQL10.5

数据库端口号

3306

5433

IP地址

192.168.56.102

192.168.56.106

首先需要安装Python，他在创建虚拟环境以及激活的时候会用到。

$>wget$>tar-xJfPython-3.6.8.tar.xz$>cdPython-3.6.8$>./configure--enable-optimizations$>makealtinstall

安装成功后需要创建并激活虚拟环境。另外需要将pip模块升级到最新版本。pg_chameleon最新版本是2.0.10，为了不引入新的bug，建议先使用2.0.9版本。

$>python3.6-mvenvvenv$>sourcevenv/bin/activate(venv)$>pipinstallpip--upgrade(venv)$>pipinstallpg_chameleon==2.0.9

下一步需要通过set_configuration_files配置启用pg_chameleon，并创建默认路径以及配置文件：

(venv)$>chameleonset_configuration_filescreatingdirectory/root/.pg_chameleoncreatingdirectory/root/.pg_chameleon/configuration/creatingdirectory/root/.pg_chameleon/logs/creatingdirectory/root/.pg_chameleon/pid/copyingconfigurationexamplein/root/.pg_chameleon/configuration//config-example.yml

此时，创建一个config-example.yml文件作为默认的配置文件。一个简单的配置例子如下所示：

$>catdefault.yml---#globalsettingspid_dir:'~/.pg_chameleon/pid/'log_dir:'~/.pg_chameleon/logs/'log_dest:filelog_level:infolog_days_keep:10rollbar_key:''rollbar_env:''#type_overrideallowstheusertooverridethedefaulttypeconversionintoadifferentone.type_override:"tinyint(1)":override_to:booleanoverride_tables:-"*"#postgresdestinationconnectionpg_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:10schema_mappings:world_x:pgworld_xlimit_tables:#-delphis_mediterranea.fooskip_tables:#-delphis_mediterranea.bargrant_select_to:-usr_readonlylock_timeout:"120s"my_server_id:100replica_batch_size:10000replay_max_rows:10000batch_retention:'1day'copy_max_memory:"300M"copy_mode:'file'out_dir:/tmpsleep_loop:1on_error_replay:continueon_error_read:continueauto_maintenance:"disabled"gtid_enable:Notype:mysqlskip_events:insert:-delphis_mediterranea.foo#skipsinsertsonthetabledelphis_mediterranea.foodelete:-delphis_mediterranea#skipsdeletesonschemadelphis_mediterraneaupdate:

本文使用的配置文件是pg_chameleon提供的样例文件改造过的，以适应源和目标环境。下面是配置文件改造的摘要。

默认情况下.yml文件有“global settings”段，用以控制详细信息比如锁文件位置、日志位置、日志保留期等。接着是“type override”段，这部分是在复制期间重写类型的集合。默认情况下使用样本类型重写规则，即将tinyint(1)转换成布尔值。然后是“pg_conn”，是目标数据库连接的详细信息。最后一部分是源数据库信息，控制源数据库的连接、源和目标直接的schema映射、需要跳过不复制的表、时间超时、内存等配置。注意，“sources”表示可以有多个源。

本文使用的demo中有一个“world_x”database，包括4个表，MySQL社区提供了下载位置：https://dev.mysql.com/doc/index-other.html。

在MySQL和PostgreSQL中都需要创建一个专用用户“usr_replica”，用以复制。在MySQL中该用户需要赋予额外的权限用以访问需要复制表：

mysql>CREATEUSERusr_replica;mysql>SETPASSWORDFORusr_replica='pass123';mysql>GRANTALLONworld_x.*TO'usr_replica';mysql>GRANTRELOADON*.*to'usr_replica';mysql>GRANTREPLICATIONCLIENTON*.*to'usr_replica';mysql>GRANTREPLICATIONSLAVEON*.*to'usr_replica';mysql>FLUSHPRIVILEGES;

PostgreSQL段创建一个“db_replica”database用以接收MySQL数据。PG中的“usr_replica”用户自动配置成两个schemas（pgworld_x和sch_chameleon）的拥有者。这两个schema包含实际复制表和catalog表。通过create_replica_schema参数自动配置：

postgres=#CREATEUSERusr_replicaWITHPASSWORD'pass123';CREATEROLEpostgres=#CREATEDATABASEdb_replicaWITHOWNERusr_replica;CREATEDATABASE

MySQL配置如下，需重启服务才能生效：

$>vi/etc/my.cnfbinlog_format=ROWbinlog_row_image=FULLlog-bin=mysql-binserver-id=1

此时需要测试下连接是否正常，保证执行pg_chameleon命令时不出问题：

PostgreSQL端：

$>mysql-uusr_replica-Ap'admin123'-h192.168.56.102-Dworld_x

MySQL端：

psql-p5433-Uusr_replica-h192.168.56.106db_replica

下面pg_chameleon的3个命令时搭建环境时执行，添加源并初始化一个备。“create_replica_schema”创建默认的schema（sch_chameleon）以及复制的schema（pgworld_x）。“add_source”通过读取配置文件信息添加source database，本文中是“mysql”。“init_replica”基于配置文件进行初始化。

$>chameleoncreate_replica_schema--debug$>chameleonadd_source--configdefault--sourcemysql--debug$>chameleoninit_replica--configdefault--sourcemysql--debug

上面的三个命令执行成功后，会分别输出明显的执行成功信息。任何错误和语法错误都会清晰的输出。

最后一步是通过“start_replica”启动复制：

$>chameleonstart_replica--configdefault--sourcemysqloutput:Startingthereplicaprocessforsourcemysql

通过show_status显示复制状态：

$>chameleonshow_status--sourcemysqlOUTPUT:SourceidSourcenameTypeStatusConsistentReadlagLastreadReplaylagLastreplay------------------------------------------------------------------------------------------------1mysqlmysqlrunningNoN/AN/A==Schemamappings==OriginschemaDestinationschema-----------------------------------world_xpgworld_x==Replicastatus==------------------------Tablesnotreplicated0Tablesreplicated4Alltables4LastmaintenanceN/ANextmaintenanceN/AReplayedrowsReplayedDDLSkippedrows------------------------$>chameleonshow_errors--configdefaultoutput:Therearenoerrorsinthelog

通过ps命令查看守护进程：

$>ps-ef|grepchameleonroot7631019:20?00:00:00/u01/media/mysql_samp_dbs/world_x-db/venv/bin/python3.6/u01/media/mysql_samp_dbs/world_x-db/venv/bin/chameleonstart_replica--configdefault--sourcemysqlroot764763019:20?00:00:01/u01/media/mysql_samp_dbs/world_x-db/venv/bin/python3.6/u01/media/mysql_samp_dbs/world_x-db/venv/bin/chameleonstart_replica--configdefault--sourcemysqlroot765763019:20?00:00:00/u01/media/mysql_samp_dbs/world_x-db/venv/bin/python3.6/u01/media/mysql_samp_dbs/world_x-db/venv/bin/chameleonstart_replica--configdefault--sourcemysql

直到“real-time 回放”搭建复制才能完成。涉及创建表、向MySQL数据库中插入数据；PG的sync_tables命令更新守护进程并将表记录复制到PG：

mysql>createtablet1(n1intprimarykey,n2varchar(10));QueryOK,0rowsaffected(0.01sec)mysql>insertintot1values(1,'one');QueryOK,1rowaffected(0.00sec)mysql>insertintot1values(2,'two');QueryOK,1rowaffected(0.00sec)

$>chameleonsync_tables--tablesworld_x.t1--configdefault--sourcemysqlSynctablesprocessforsourcemysqlstarted.

测试确认复制正常：

$>psql-p5433-Uusr_replica-ddb_replica-c"select*frompgworld_x.t1";n1|n2----+-------1|one2|two

如果是一个迁移需求，执行下面命令标记迁移结束。在所有需要复制的表复制完成后执行这些命令：

$>chameleonstop_replica--configdefault--sourcemysql$>chameleondetach_replica--configdefault--sourcemysql--debug

下面的命令可选：

$>chameleondrop_source--configdefault--sourcemysql--debug$>chameleondrop_replica_schema--configdefault--sourcemysql--debugPros of Using pg_chameleon

安装并配置比较简单

错误日志易看懂

无需更改任何配置，初始化完成后可以添加额外的复制表

可配置成多源复制

可以指定不复制哪些表

仅支持MySQL5.5及其以上的版本到Pg9.5及其以上之间进行复制

每个复制表需要有主键或唯一键

只能MySQL到PG

到此，关于“MySQL和PostgreSQL之间如何跨数据库进行复制”的学习就结束了，希望能够解决大家的疑惑。理论与实践的搭配能更好的帮助大家学习，快去试试吧！若想继续学习更多相关知识，请继续关注亿速云网站，小编会继续努力为大家带来更多实用的文章！