本博客的目的在于簡述MySQL和PostgreSQL之間如何跨數(shù)據(jù)庫進行復制堰乔。涉及跨數(shù)據(jù)庫復制的databases一般被稱作異構databases。這是將數(shù)據(jù)從一種RDBMS server復制到另一種server的一種很好的方法贡羔。
PostgreSQL和MySQL都是傳統(tǒng)的RDBMS數(shù)據(jù)庫陨帆，但是他們也提供了NoSQL的能力。本文主要從RDBMS的角度討論PostgreSQL和MySQL之間的復制問題二蓝。不對復制內(nèi)部機制做詳細介紹引镊，只對一些基本元素朦蕴、如何配置、有點弟头、限制以及一些使用案例進行闡述吩抓。
通常情況下，兩個種類相同的主備之間使用binary模式或者query模式進行復制亮瓷。復制的目的在于琴拧，在備上能夠得到主的實時備份數(shù)據(jù)，從而形成一個active-passive模式（因為復制只配置單向復制）嘱支。當然，也可以配置成向同步挣饥，構建active-active模式除师。
可以在兩個不同數(shù)據(jù)庫server之間配置上面的兩種模式，其中一個數(shù)據(jù)庫server可以配置從另外一個完全不同的數(shù)據(jù)庫server上接收副本數(shù)據(jù)并維護副本數(shù)據(jù)的實時快照扔枫。MySQL和PostgreSQL通過原生機制或者第三方插件（包括binlog方法汛聚、磁盤塊方法、基于語句和行的方法）完成上面提到的模式短荐。
由于MySQL和PostgreSQL使用不同的復制協(xié)議瘾婿，所以他們之間不能互相交互套硼。為了達到通信流的目的，可以使用一個開源軟件pg_chameleon。
pg_chameleon是由python3開發(fā)的MySQL to PG的復制工具恳邀。該插件也會使用一個mysql-replication的開源庫，該庫也是由Python3開發(fā)傍衡。從MySQL表中拉取行鏡像并存儲成JSONB形式蠢壹，然后同步到PG數(shù)據(jù)庫。PG數(shù)據(jù)庫通過pl/pgsql進行解析并回放入宦。
1哺徊、同一個集群中多個MySQL schema可以復制到一個PG database，形成many-to-one復制模式乾闰。
2落追、源和目的schema名可以不一樣
3、復制數(shù)據(jù)可以從mysql級聯(lián)副本中拉取涯肩。
4轿钠、會排除復制失敗的表及復制過程中產(chǎn)生錯誤的表巢钓。
5、每個復制功能通過守護進程進行管理
6谣膳、配置參數(shù)和配置文件以yaml結構進行控制竿报。
HostVm1Vm2
操作系統(tǒng)Centos linux release 7.6 x86_64Centos linux release 7.5 x86_64
數(shù)據(jù)庫版本MySQL5.7.26PostgreSQL10.5
數(shù)據(jù)庫端口號33065433
IP地址192.168.56.102192.168.56.106
首先需要安裝Python，他在創(chuàng)建虛擬環(huán)境以及激活的時候會用到继谚。
$> 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
安裝成功后需要創(chuàng)建并激活虛擬環(huán)境烈菌。另外需要將pip模塊升級到最新版本。pg_chameleon最新版本是2.0.10花履，為了不引入新的bug芽世，建議先使用2.0.9版本。
$> 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诡壁，并創(chuàng)建默認路徑以及配置文件：
(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
此時济瓢，創(chuàng)建一個config-example.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提供的樣例文件改造過的妹卿，以適應源和目標環(huán)境旺矾。下面是配置文件改造的摘要。
默認情況下.yml文件有“global settings”段夺克，用以控制詳細信息比如鎖文件位置箕宙、日志位置、日志保留期等铺纽。接著是“type override”段柬帕，這部分是在復制期間重寫類型的集合。默認情況下使用樣本類型重寫規(guī)則狡门，即將tinyint(1)轉換成布爾值陷寝。然后是“pg_conn”，是目標數(shù)據(jù)庫連接的詳細信息其馏。最后一部分是源數(shù)據(jù)庫信息凤跑，控制源數(shù)據(jù)庫的連接、源和目標直接的schema映射尝偎、需要跳過不復制的表饶火、時間超時、內(nèi)存等配置致扯。注意肤寝，“sources”表示可以有多個源。
本文使用的demo中有一個“world_x”database抖僵，包括4個表鲤看，MySQL社區(qū)提供了下載位置：https://dev.mysql.com/doc/index-other.html。
在MySQL和PostgreSQL中都需要創(chuàng)建一個專用用戶“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段創(chuàng)建一個“db_replica”database用以接收MySQL數(shù)據(jù)找筝。PG中的“usr_replica”用戶自動配置成兩個schemas（pgworld_x和sch_chameleon）的擁有者。這兩個schema包含實際復制表和catalog表慷吊。通過create_replica_schema參數(shù)自動配置：
postgres=#CREATEUSERusr_replicaWITHPASSWORD'pass123';
CREATEROLE
postgres=#CREATEDATABASEdb_replicaWITHOWNER usr_replica;
CREATEDATABASE
MySQL配置如下袖裕，需重啟服務才能生效：
$> vi?/etc/my.cnf
binlog_format= ROW
binlog_row_image=FULL
log-bin = mysql-bin
server-id?= 1
此時需要測試下連接是否正常，保證執(zhí)行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個命令時搭建環(huán)境時執(zhí)行溉瓶，添加源并初始化一個備急鳄。“create_replica_schema”創(chuàng)建默認的schema（sch_chameleon）以及復制的schema（pgworld_x）堰酿〖埠辏“add_source”通過讀取配置文件信息添加source database，本文中是“mysql”触创】裁辏“init_replica”基于配置文件進行初始化。
$> chameleon create_replica_schema --debug
$> chameleon add_source --config default --sourcemysql --debug
$> chameleon init_replica --config default --sourcemysql --debug
上面的三個命令執(zhí)行成功后哼绑，會分別輸出明顯的執(zhí)行成功信息岩馍。任何錯誤和語法錯誤都會清晰的輸出。
最后一步是通過“start_replica”啟動復制：
$> chameleon start_replica --config default --sourcemysql
output: Starting the replica processforsourcemysql
通過show_status顯示復制狀態(tài)：
$> chameleon show_status --sourcemysql
OUTPUT:
Source id? Source name??? Type??? Status??? Consistent??? Read lag??? LastreadReplay lag??? Last replay
-----------? -------------? ------? --------? ------------? ----------? -----------? ------------? -------------
??????????1? mysql????????? mysql?? running?? No??????????? N/A????????????????????? N/A
== Schema mappings ==
Origin schema??? Destination schema
---------------? --------------------
world_x????????? pgworld_x
== Replica status ==
---------------------? ---
Tables not replicated? 0
Tables replicated????? 4
All tables???????????? 4
Last maintenance?????? N/A
Next maintenance?????? N/A
Replayed rows
Replayed DDL
Skipped rows
---------------------? ---
$> chameleon show_errors --config default
output: There are no errorsinthelog
通過ps命令查看守護進程：
$>? ps?-ef|grep?chameleon
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 --sourcemysql
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 --sourcemysql
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 --sourcemysql
直到“real-time 回放”搭建復制才能完成抖韩。涉及創(chuàng)建表兼雄、向MySQL數(shù)據(jù)庫中插入數(shù)據(jù)；PG的sync_tables命令更新守護進程并將表記錄復制到PG：
mysql>createtablet1 (n1intprimarykey, n2varchar(10));
Query OK, 0 rows?affected (0.01 sec)
mysql>insertintot1values(1,'one');
Query OK, 1 row affected (0.00 sec)
mysql>insertintot1values(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.
測試確認復制正常：
$> psql -p 5433 -U usr_replica -d db_replica -c "select*frompgworld_x.t1";
?n1 |? n2
----+-------
??1 | one
??2 | two
如果是一個遷移需求帽蝶，執(zhí)行下面命令標記遷移結束。在所有需要復制的表復制完成后執(zhí)行這些命令：
$> chameleon stop_replica --configdefault--source?mysql
$> chameleon detach_replica --configdefault--source?mysql --debug
下面的命令可選：
$> chameleon drop_source --config default --sourcemysql --debug
$> chameleon drop_replica_schema --config default --sourcemysql --debug
安裝并配置比較簡單
錯誤日志易看懂
無需更改任何配置块攒，初始化完成后可以添加額外的復制表
可配置成多源復制
可以指定不復制哪些表
僅支持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
