本文主要給大家介紹MySQL和PostgreSQL之間跨數(shù)據(jù)庫(kù)復(fù)制講義，希望可以給大家補(bǔ)充和更新些知識(shí)，如有其它問(wèn)題需要了解的可以持續(xù)在創(chuàng)新互聯(lián)行業(yè)資訊里面關(guān)注我的更新文章的。
PostgreSQL和MySQL都是傳統(tǒng)的RDBMS數(shù)據(jù)庫(kù)，但是他們也提供了NoSQL的能力。本文主要從RDBMS的角度討論P(yáng)ostgreSQL和MySQL之間的復(fù)制問(wèn)題。不對(duì)復(fù)制內(nèi)部機(jī)制做詳細(xì)介紹，只對(duì)一些基本元素、如何配置、有點(diǎn)、限制以及一些使用案例進(jìn)行闡述。
通常情況下，兩個(gè)種類(lèi)相同的主備之間使用binary模式或者query模式進(jìn)行復(fù)制。復(fù)制的目的在于，在備上能夠得到主的實(shí)時(shí)備份數(shù)據(jù)，從而形成一個(gè)active-passive模式（因?yàn)閺?fù)制只配置單向復(fù)制）。當(dāng)然，也可以配置成向同步，構(gòu)建active-active模式。
可以在兩個(gè)不同數(shù)據(jù)庫(kù)server之間配置上面的兩種模式，其中一個(gè)數(shù)據(jù)庫(kù)server可以配置從另外一個(gè)完全不同的數(shù)據(jù)庫(kù)server上接收副本數(shù)據(jù)并維護(hù)副本數(shù)據(jù)的實(shí)時(shí)快照。MySQL和PostgreSQL通過(guò)原生機(jī)制或者第三方插件（包括binlog方法、磁盤(pán)塊方法、基于語(yǔ)句和行的方法）完成上面提到的模式。
由于MySQL和PostgreSQL使用不同的復(fù)制協(xié)議，所以他們之間不能互相交互。為了達(dá)到通信流的目的，可以使用一個(gè)開(kāi)源軟件pg_chameleon。
pg_chameleon是由python3開(kāi)發(fā)的MySQL to PG的復(fù)制工具。該插件也會(huì)使用一個(gè)mysql-replication的開(kāi)源庫(kù)，該庫(kù)也是由Python3開(kāi)發(fā)。從MySQL表中拉取行鏡像并存儲(chǔ)成JSONB形式，然后同步到PG數(shù)據(jù)庫(kù)。PG數(shù)據(jù)庫(kù)通過(guò)pl/pgsql進(jìn)行解析并回放。
1、同一個(gè)集群中多個(gè)MySQL schema可以復(fù)制到一個(gè)PG database，形成many-to-one復(fù)制模式。
2、源和目的schema名可以不一樣
3、復(fù)制數(shù)據(jù)可以從mysql級(jí)聯(lián)副本中拉取。
4、會(huì)排除復(fù)制失敗的表及復(fù)制過(guò)程中產(chǎn)生錯(cuò)誤的表。
5、每個(gè)復(fù)制功能通過(guò)守護(hù)進(jìn)程進(jìn)行管理
6、配置參數(shù)和配置文件以yaml結(jié)構(gòu)進(jìn)行控制。
Host | Vm1 | Vm2 |
操作系統(tǒng) | Centos linux release 7.6 x86_64 | Centos linux release 7.5 x86_64 |
數(shù)據(jù)庫(kù)版本 | MySQL5.7.26 | PostgreSQL10.5 |
數(shù)據(jù)庫(kù)端口號(hào) | 3306 | 5433 |
IP地址 | 192.168.56.102 | 192.168.56.106 |
首先需要安裝Python，他在創(chuàng)建虛擬環(huán)境以及激活的時(shí)候會(huì)用到。
$> 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安裝成功后需要?jiǎng)?chuàng)建并激活虛擬環(huán)境。另外需要將pip模塊升級(jí)到最新版本。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下一步需要通過(guò)set_configuration_files配置啟用pg_chameleon，并創(chuàng)建默認(rèn)路徑以及配置文件：
(venv) $> chameleon set_configuration_filescreating directory /root/.pg_chameleoncreating 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此時(shí)，創(chuàng)建一個(gè)config-example.yml文件作為默認(rèn)的配置文件。一個(gè)簡(jiǎn)單的配置例子如下所示：
$> cat default.yml---#global settingspid_dir: '~/.pg_chameleon/pid/'log_dir: '~/.pg_chameleon/logs/'log_dest: filelog_level: infolog_days_keep: 10rollbar_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 connectionpg_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提供的樣例文件改造過(guò)的，以適應(yīng)源和目標(biāo)環(huán)境。下面是配置文件改造的摘要。
默認(rèn)情況下.yml文件有“global settings”段，用以控制詳細(xì)信息比如鎖文件位置、日志位置、日志保留期等。接著是“type override”段，這部分是在復(fù)制期間重寫(xiě)類(lèi)型的集合。默認(rèn)情況下使用樣本類(lèi)型重寫(xiě)規(guī)則，即將tinyint(1)轉(zhuǎn)換成布爾值。然后是“pg_conn”，是目標(biāo)數(shù)據(jù)庫(kù)連接的詳細(xì)信息。最后一部分是源數(shù)據(jù)庫(kù)信息，控制源數(shù)據(jù)庫(kù)的連接、源和目標(biāo)直接的schema映射、需要跳過(guò)不復(fù)制的表、時(shí)間超時(shí)、內(nèi)存等配置。注意，“sources”表示可以有多個(gè)源。
本文使用的demo中有一個(gè)“world_x”database，包括4個(gè)表，MySQL社區(qū)提供了下載位置：https://dev.mysql.com/doc/index-other.html。
在MySQL和PostgreSQL中都需要?jiǎng)?chuàng)建一個(gè)專(zhuān)用用戶(hù)“usr_replica”，用以復(fù)制。在MySQL中該用戶(hù)需要賦予額外的權(quán)限用以訪問(wèn)需要復(fù)制表：
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段創(chuàng)建一個(gè)“db_replica”database用以接收MySQL數(shù)據(jù)。PG中的“usr_replica”用戶(hù)自動(dòng)配置成兩個(gè)schemas（pgworld_x和sch_chameleon）的擁有者。這兩個(gè)schema包含實(shí)際復(fù)制表和catalog表。通過(guò)create_replica_schema參數(shù)自動(dòng)配置：
postgres=# CREATE USER usr_replica WITH PASSWORD 'pass123';CREATE ROLEpostgres=# CREATE DATABASE db_replica WITH OWNER usr_replica;CREATE DATABASEMySQL配置如下，需重啟服務(wù)才能生效：
$> vi /etc/my.cnfbinlog_format= ROWbinlog_row_image=FULLlog-bin = mysql-binserver-id = 1此時(shí)需要測(cè)試下連接是否正常，保證執(zhí)行pg_chameleon命令時(shí)不出問(wèn)題：
PostgreSQL端：
$> mysql -u usr_replica -Ap'admin123' -h 192.168.56.102 -D world_xMySQL端：
psql -p 5433 -U usr_replica -h 192.168.56.106 db_replica下面pg_chameleon的3個(gè)命令時(shí)搭建環(huán)境時(shí)執(zhí)行，添加源并初始化一個(gè)備。“create_replica_schema”創(chuàng)建默認(rèn)的schema（sch_chameleon）以及復(fù)制的schema（pgworld_x）?！癮dd_source”通過(guò)讀取配置文件信息添加source database，本文中是“mysql”?！癷nit_replica”基于配置文件進(jìn)行初始化。
$> chameleon create_replica_schema --debug$> chameleon add_source --config default --source mysql --debug$> chameleon init_replica --config default --source mysql --debug上面的三個(gè)命令執(zhí)行成功后，會(huì)分別輸出明顯的執(zhí)行成功信息。任何錯(cuò)誤和語(yǔ)法錯(cuò)誤都會(huì)清晰的輸出。
最后一步是通過(guò)“start_replica”啟動(dòng)復(fù)制：
$> chameleon start_replica --config default --source mysqloutput: Starting the replica process for source mysql通過(guò)show_status顯示復(fù)制狀態(tài)：
$> chameleon show_status --source mysql OUTPUT: Source id Source name Type Status Consistent Read lag Last read Replay 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 0Tables replicated 4All tables 4Last maintenance N/ANext maintenance N/AReplayed rowsReplayed DDLSkipped rows--------------------- ---$> chameleon show_errors --config defaultoutput: There are no errors in the log通過(guò)ps命令查看守護(hù)進(jìn)程：
$> ps -ef|grep chameleonroot 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 mysqlroot 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 mysqlroot 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 回放”搭建復(fù)制才能完成。涉及創(chuàng)建表、向MySQL數(shù)據(jù)庫(kù)中插入數(shù)據(jù)；PG的sync_tables命令更新守護(hù)進(jìn)程并將表記錄復(fù)制到PG：
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 mysqlSync tables process for source mysql started.測(cè)試確認(rèn)復(fù)制正常：
$> psql -p 5433 -U usr_replica -d db_replica -c "select * from pgworld_x.t1"; n1 | n2----+------- 1 | one 2 | two如果是一個(gè)遷移需求，執(zhí)行下面命令標(biāo)記遷移結(jié)束。在所有需要復(fù)制的表復(fù)制完成后執(zhí)行這些命令：
$> 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安裝并配置比較簡(jiǎn)單
錯(cuò)誤日志易看懂
無(wú)需更改任何配置，初始化完成后可以添加額外的復(fù)制表
可配置成多源復(fù)制
可以指定不復(fù)制哪些表
僅支持MySQL5.5及其以上的版本到Pg9.5及其以上之間進(jìn)行復(fù)制
每個(gè)復(fù)制表需要有主鍵或唯一鍵
只能MySQL到PG
看了以上關(guān)于MySQL和PostgreSQL之間跨數(shù)據(jù)庫(kù)復(fù)制講義，希望能給大家在實(shí)際運(yùn)用中帶來(lái)一定的幫助。本文由于篇幅有限，難免會(huì)有不足和需要補(bǔ)充的地方，如有需要更加專(zhuān)業(yè)的解答，可在官網(wǎng)聯(lián)系我們的24小時(shí)售前售后，隨時(shí)幫您解答問(wèn)題的。
另外有需要云服務(wù)器可以了解下創(chuàng)新互聯(lián)cdcxhl.cn，海內(nèi)外云服務(wù)器15元起步，三天無(wú)理由+7*72小時(shí)售后在線，公司持有idc許可證，提供“云服務(wù)器、裸金屬服務(wù)器、高防服務(wù)器、香港服務(wù)器、美國(guó)服務(wù)器、虛擬主機(jī)、免備案服務(wù)器”等云主機(jī)租用服務(wù)以及企業(yè)上云的綜合解決方案，具有“安全穩(wěn)定、簡(jiǎn)單易用、服務(wù)可用性高、性?xún)r(jià)比高”等特點(diǎn)與優(yōu)勢(shì)，專(zhuān)為企業(yè)上云打造定制，能夠滿(mǎn)足用戶(hù)豐富、多元化的應(yīng)用場(chǎng)景需求。
重慶分公司
重慶分公司
