An Overview of PostgreSQL & MySQL Cross Replication

本文涉及的产品
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
RDS MySQL Serverless 高可用系列,价值2615元额度,1个月
简介: An Overview of PostgreSQL & MySQL Cross Replication

本博客的目的在于简述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背景


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


pg_chameleon特性


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

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

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

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

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

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


Demo


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,他在创建虚拟环境以及激活的时候会用到。

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

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

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

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

1. (venv) $> chameleon set_configuration_files
2. creating directory /root/.pg_chameleon
3. creating directory /root/.pg_chameleon/configuration/
4. creating directory /root/.pg_chameleon/logs/
5. creating directory /root/.pg_chameleon/pid/
6. copying configuration  example in /root/.pg_chameleon/configuration//config-example.yml

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

1. $> cat default.yml
2. ---
3. #global settings
4. pid_dir: '~/.pg_chameleon/pid/'
5. log_dir: '~/.pg_chameleon/logs/'
6. log_dest: file
7. log_level: info
8. log_days_keep: 10
9. rollbar_key: ''
10. rollbar_env: ''
11. 
12. # type_override allows the user to override the default type conversion into a different one.
13. type_override:
14.   "tinyint(1)":
15.     override_to: boolean
16.     override_tables:
17.       - "*"
18. 
19. #postgres  destination connection
20. pg_conn:
21.   host: "192.168.56.106"
22.   port: "5433"
23.   user: "usr_replica"
24.   password: "pass123"
25.   database: "db_replica"
26.   charset: "utf8"
27. 
28. 
29. sources:
30.   mysql:
31.     db_conn:
32.       host: "192.168.56.102"
33.       port: "3306"
34.       user: "usr_replica"
35.       password: "pass123"
36.       charset: 'utf8'
37.       connect_timeout: 10
38.     schema_mappings:
39.       world_x: pgworld_x
40.     limit_tables:
41. #      - delphis_mediterranea.foo
42.     skip_tables:
43. #      - delphis_mediterranea.bar
44.     grant_select_to:
45.       - usr_readonly
46.     lock_timeout: "120s"
47.     my_server_id: 100
48.     replica_batch_size: 10000
49.     replay_max_rows: 10000
50.     batch_retention: '1 day'
51.     copy_max_memory: "300M"
52.     copy_mode: 'file'
53.     out_dir: /tmp
54.     sleep_loop: 1
55.     on_error_replay: continue
56.     on_error_read: continue
57.     auto_maintenance: "disabled"
58.     gtid_enable: No
59.     type: mysql
60.     skip_events:
61.       insert:
62.         - delphis_mediterranea.foo #skips inserts on the table delphis_mediterranea.foo
63.       delete:
64.         - delphis_mediterranea #skips deletes on schema delphis_mediterranea
65.       update:


本文使用的配置文件是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中该用户需要赋予额外的权限用以访问需要复制表:


1. mysql> CREATE USER usr_replica ;
2. mysql> SET PASSWORD FOR usr_replica='pass123';
3. mysql> GRANT ALL ON world_x.* TO 'usr_replica';
4. mysql> GRANT RELOAD ON *.* to 'usr_replica';
5. mysql> GRANT REPLICATION CLIENT ON *.* to 'usr_replica';
6. mysql> GRANT REPLICATION SLAVE ON *.* to 'usr_replica';
7. mysql> FLUSH PRIVILEGES;

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

1. postgres=# CREATE USER usr_replica WITH PASSWORD 'pass123';
2. CREATE ROLE
3. postgres=# CREATE DATABASE db_replica WITH OWNER usr_replica;
4. CREATE DATABASE

MySQL配置如下,需重启服务才能生效:

1. $> vi /etc/my.cnf
2. binlog_format= ROW
3. binlog_row_image=FULL
4. log-bin = mysql-bin
5. 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的3个命令时搭建环境时执行,添加源并初始化一个备。“create_replica_schema”创建默认的schema(sch_chameleon)以及复制的schema(pgworld_x)。“add_source”通过读取配置文件信息添加source database,本文中是“mysql”。“init_replica”基于配置文件进行初始化。

1. $> chameleon create_replica_schema --debug
2. $> chameleon add_source --config default --source mysql --debug
3. $> chameleon init_replica --config default --source mysql --debug

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

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

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

通过show_status显示复制状态:

1. $> chameleon show_status --source mysql 
2. OUTPUT:
3.   Source id  Source name    Type    Status    Consistent    Read lag    Last read    Replay lag    Last replay
4. 
5. -----------  -------------  ------  --------  ------------  ----------  -----------  ------------  -------------
6. 
7.           1  mysql          mysql   running   No            N/A                      N/A
8. 
9. 
10. == Schema mappings ==
11. Origin schema    Destination schema
12. ---------------  --------------------
13. world_x          pgworld_x
14. 
15. == Replica status ==
16. ---------------------  ---
17. Tables not replicated  0
18. Tables replicated      4
19. All tables             4
20. Last maintenance       N/A
21. Next maintenance       N/A
22. Replayed rows
23. Replayed DDL
24. Skipped rows
25. ---------------------  ---
26. $> chameleon show_errors --config default
27. output: There are no errors in the log

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

1. $>  ps -ef|grep chameleon
2. root       763     1  0 19:20 ?        00:00:00 /u01/media/mysql_samp_dbs/world_x-db/venv/bin/python3.6 /u01/media/mysq l_samp_dbs/world_x-db/venv/bin/chameleon start_replica --config default --source mysql
3. root       764   763  0 19:20 ?        00:00:01 /u01/media/mysql_samp_dbs/world_x-db/venv/bin/python3.6 /u01/media/mysq l_samp_dbs/world_x-db/venv/bin/chameleon start_replica --config default --source mysql
4. root       765   763  0 19:20 ?        00:00:00 /u01/media/mysql_samp_dbs/world_x-db/venv/bin/python3.6 /u01/media/mysq l_samp_dbs/world_x-db/venv/bin/chameleon start_replica --config default --source mysql

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

1. mysql> create table t1 (n1 int primary key, n2 varchar(10));
2. Query OK, 0 rows affected (0.01 sec)
3. mysql> insert into t1 values (1,'one');
4. Query OK, 1 row affected (0.00 sec)
5. mysql> insert into t1 values (2,'two');
6. Query OK, 1 row affected (0.00 sec)
1. $> chameleon sync_tables --tables world_x.t1 --config default --source mysql
2. Sync tables process for source mysql started.

测试确认复制正常:

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

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

1. $> chameleon stop_replica --config default --source mysql
2. $> chameleon detach_replica --config default --source mysql --debug

下面的命令可选:

1. $> chameleon drop_source --config default --source mysql --debug
2. $> chameleon drop_replica_schema --config default --source mysql --debug


Pros of Using pg_chameleon


安装并配置比较简单

错误日志易看懂

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

可配置成多源复制

可以指定不复制哪些表


Cons of Using pg_chameleon


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

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

只能MySQL到PG


总结


pg_chameleon工具提供从MySQL向PG迁移的方法。然而只能单向复制。这个缺点可以使用另外一个工具SymmetricDS来弥补。文档:https://pgchameleon.org/documents/;命令行说明:https://pgchameleon.org/documents/usage.html#command-line-reference


原文


https://severalnines.com/blog/overview-postgresql-mysql-cross-replication

目录
相关文章
|
2月前
|
NoSQL 关系型数据库 MySQL
微服务架构下的数据库选择:MySQL、PostgreSQL 还是 NoSQL?
在微服务架构中,数据库的选择至关重要。不同类型的数据库适用于不同的需求和场景。在本文章中,我们将深入探讨传统的关系型数据库(如 MySQL 和 PostgreSQL)与现代 NoSQL 数据库的优劣势,并分析在微服务架构下的最佳实践。
|
22天前
|
存储 关系型数据库 MySQL
一个项目用5款数据库?MySQL、PostgreSQL、ClickHouse、MongoDB区别,适用场景
一个项目用5款数据库?MySQL、PostgreSQL、ClickHouse、MongoDB——特点、性能、扩展性、安全性、适用场景比较
|
2月前
|
Oracle NoSQL 关系型数据库
主流数据库对比:MySQL、PostgreSQL、Oracle和Redis的优缺点分析
主流数据库对比:MySQL、PostgreSQL、Oracle和Redis的优缺点分析
150 2
|
22天前
|
存储 关系型数据库 MySQL
四种数据库对比MySQL、PostgreSQL、ClickHouse、MongoDB——特点、性能、扩展性、安全性、适用场景
四种数据库对比 MySQL、PostgreSQL、ClickHouse、MongoDB——特点、性能、扩展性、安全性、适用场景
|
3月前
|
关系型数据库 MySQL Linux
在Linux中,如何配置数据库服务器(如MySQL或PostgreSQL)?
在Linux中,如何配置数据库服务器(如MySQL或PostgreSQL)?
|
3月前
|
SQL 关系型数据库 MySQL
SQL Server、MySQL、PostgreSQL:主流数据库SQL语法异同比较——深入探讨数据类型、分页查询、表创建与数据插入、函数和索引等关键语法差异,为跨数据库开发提供实用指导
【8月更文挑战第31天】SQL Server、MySQL和PostgreSQL是当今最流行的关系型数据库管理系统,均使用SQL作为查询语言,但在语法和功能实现上存在差异。本文将比较它们在数据类型、分页查询、创建和插入数据以及函数和索引等方面的异同,帮助开发者更好地理解和使用这些数据库。尽管它们共用SQL语言,但每个系统都有独特的语法规则,了解这些差异有助于提升开发效率和项目成功率。
248 0
|
3月前
|
关系型数据库 MySQL 数据库
postgresql使用mysql_fdw连接mysql
通过以上步骤,你可以在PostgreSQL中访问和查询远程MySQL服务器的数据,这对于数据集成和多数据库管理非常有用。
177 0
|
3月前
|
关系型数据库 MySQL 数据库
探究数据库开源协议:PostgreSQL vs MySQL
探究数据库开源协议:PostgreSQL vs MySQL
|
5月前
|
SQL 关系型数据库 数据库
nacos 2.2.3版本 查看配置文件的历史版本的接口 是针对MySQL数据库的sql 改成postgresql后 sql语句报错 该怎么解决
在Nacos 2.2.3中切换到PostgreSQL后,执行配置文件历史版本分页查询出错,因`LIMIT 0, 10`语法不被PostgreSQL支持,需改为`LIMIT 10 OFFSET 0`。仅当存在历史版本时报错。解决方案是调整查询SQL以兼容PostgreSQL语法。
|
6月前
|
关系型数据库 MySQL Java
项目从 MySQL 切换 PostgreSQL,踩了太多的坑!!!
底层自己操作数据库可能就无法修改源码了,只能修改数据库表字段类型了
282 1