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