AnOverviewofPG&MySQL-創(chuàng)新互聯(lián)

本博客的目的在于簡述MySQL和PostgreSQL之間如何跨數(shù)據(jù)庫進(jìn)行復(fù)制。涉及跨數(shù)據(jù)庫復(fù)制的databases一般被稱作異構(gòu)databases。這是將數(shù)據(jù)從一種RDBMS server復(fù)制到另一種server的一種很好的方法。

網(wǎng)站建設(shè)哪家好，找創(chuàng)新互聯(lián)！專注于網(wǎng)頁設(shè)計、網(wǎng)站建設(shè)、微信開發(fā)、重慶小程序開發(fā)公司、集團(tuán)企業(yè)網(wǎng)站建設(shè)等服務(wù)項(xiàng)目。為回饋新老客戶創(chuàng)新互聯(lián)還提供了姚安免費(fèi)建站歡迎大家使用！

PostgreSQL和MySQL都是傳統(tǒng)的RDBMS數(shù)據(jù)庫，但是他們也提供了NoSQL的能力。本文主要從RDBMS的角度討論P(yáng)ostgreSQL和MySQL之間的復(fù)制問題。不對復(fù)制內(nèi)部機(jī)制做詳細(xì)介紹，只對一些基本元素、如何配置、有點(diǎn)、限制以及一些使用案例進(jìn)行闡述。

通常情況下，兩個種類相同的主備之間使用binary模式或者query模式進(jìn)行復(fù)制。復(fù)制的目的在于，在備上能夠得到主的實(shí)時備份數(shù)據(jù)，從而形成一個active-passive模式（因?yàn)閺?fù)制只配置單向復(fù)制）。當(dāng)然，也可以配置成向同步，構(gòu)建active-active模式。

可以在兩個不同數(shù)據(jù)庫server之間配置上面的兩種模式，其中一個數(shù)據(jù)庫server可以配置從另外一個完全不同的數(shù)據(jù)庫server上接收副本數(shù)據(jù)并維護(hù)副本數(shù)據(jù)的實(shí)時快照。MySQL和PostgreSQL通過原生機(jī)制或者第三方插件（包括binlog方法、磁盤塊方法、基于語句和行的方法）完成上面提到的模式。

由于MySQL和PostgreSQL使用不同的復(fù)制協(xié)議，所以他們之間不能互相交互。為了達(dá)到通信流的目的，可以使用一個開源軟件pg_chameleon。

pg_chameleon背景

pg_chameleon是由python3開發(fā)的MySQL to PG的復(fù)制工具。該插件也會使用一個mysql-replication的開源庫，該庫也是由Python3開發(fā)。從MySQL表中拉取行鏡像并存儲成JSONB形式，然后同步到PG數(shù)據(jù)庫。PG數(shù)據(jù)庫通過pl/pgsql進(jìn)行解析并回放。

pg_chameleon特性

1、同一個集群中多個MySQL schema可以復(fù)制到一個PG database，形成many-to-one復(fù)制模式。

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

3、復(fù)制數(shù)據(jù)可以從mysql級聯(lián)副本中拉取。

4、會排除復(fù)制失敗的表及復(fù)制過程中產(chǎn)生錯誤的表。

5、每個復(fù)制功能通過守護(hù)進(jìn)程進(jìn)行管理

6、配置參數(shù)和配置文件以yaml結(jié)構(gòu)進(jìn)行控制。

Demo

Host	Vm1	Vm2
操作系統(tǒng)	Centos linux release 7.6 x86_64	Centos linux release 7.5 x86_64
數(shù)據(jù)庫版本	MySQL5.7.26	PostgreSQL10.5
數(shù)據(jù)庫端口號	3306	5433
IP地址	192.168.56.102	192.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)建默認(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

此時，創(chuàng)建一個config-example.yml文件作為默認(rè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提供的樣例文件改造過的，以適應(yīng)源和目標(biāo)環(huán)境。下面是配置文件改造的摘要。

默認(rèn)情況下.yml文件有“global settings”段，用以控制詳細(xì)信息比如鎖文件位置、日志位置、日志保留期等。接著是“type override”段，這部分是在復(fù)制期間重寫類型的集合。默認(rèn)情況下使用樣本類型重寫規(guī)則，即將tinyint(1)轉(zhuǎn)換成布爾值。然后是“pg_conn”，是目標(biāo)數(shù)據(jù)庫連接的詳細(xì)信息。最后一部分是源數(shù)據(jù)庫信息，控制源數(shù)據(jù)庫的連接、源和目標(biāo)直接的schema映射、需要跳過不復(fù)制的表、時間超時、內(nèi)存等配置。注意，“sources”表示可以有多個源。

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

在MySQL和PostgreSQL中都需要創(chuàng)建一個專用用戶“usr_replica”，用以復(fù)制。在MySQL中該用戶需要賦予額外的權(quá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)建一個“db_replica”database用以接收MySQL數(shù)據(jù)。PG中的“usr_replica”用戶自動配置成兩個schemas（pgworld_x和sch_chameleon）的擁有者。這兩個schema包含實(shí)際復(fù)制表和catalog表。通過create_replica_schema參數(shù)自動配置：

postgres=#?CREATE?USER?usr_replica?WITH?PASSWORD?'pass123';CREATE?ROLEpostgres=#?CREATE?DATABASE?db_replica?WITH?OWNER?usr_replica;CREATE?DATABASE

MySQL配置如下，需重啟服務(wù)才能生效：

$>?vi?/etc/my.cnfbinlog_format=?ROWbinlog_row_image=FULLlog-bin?=?mysql-binserver-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í)行，添加源并初始化一個備?！癱reate_replica_schema”創(chuàng)建默認(rèn)的schema（sch_chameleon）以及復(fù)制的schema（pgworld_x）?！癮dd_source”通過讀取配置文件信息添加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

上面的三個命令執(zhí)行成功后，會分別輸出明顯的執(zhí)行成功信息。任何錯誤和語法錯誤都會清晰的輸出。

最后一步是通過“start_replica”啟動復(fù)制：

$>?chameleon?start_replica?--config?default?--source?mysqloutput:?Starting?the?replica?process?for?source?mysql

通過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

通過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ù)庫中插入數(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.

測試確認(rèn)復(fù)制正常：

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

如果是一個遷移需求，執(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

Pros of Using pg_chameleon

安裝并配置比較簡單

錯誤日志易看懂

無需更改任何配置，初始化完成后可以添加額外的復(fù)制表

可配置成多源復(fù)制

可以指定不復(fù)制哪些表

Cons of Using pg_chameleon

僅支持MySQL5.5及其以上的版本到Pg9.5及其以上之間進(jìn)行復(fù)制

每個復(fù)制表需要有主鍵或唯一鍵

只能MySQL到PG

總結(jié)

pg_chameleon工具提供從MySQL向PG遷移的方法。然而只能單向復(fù)制。這個缺點(diǎn)可以使用另外一個工具SymmetricDS來彌補(bǔ)。文檔：https://pgchameleon.org/documents/；命令行說明：https://pgchameleon.org/documents/usage.html#command-line-reference

原文

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

分享標(biāo)題：AnOverviewofPG&MySQL-創(chuàng)新互聯(lián)
文章起源：http://muchs.cn/article0/depgio.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供外貿(mào)網(wǎng)站建設(shè)、微信公眾號、云服務(wù)器、服務(wù)器托管、自適應(yīng)網(wǎng)站、企業(yè)建站

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請盡快告知，我們將會在第一時間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場，如需處理請聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時需注明來源： 創(chuàng)新互聯(lián)