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

首先需要安裝Python，他在建立虛擬環境以及啟用的時候會用到。

$> wget  
$> tar -xJf Python-3.6.8.tar.xz$> cd Python-3.6.8$> ./configure --enable-optimizations$> make altinstall

安裝成功後需要建立並啟用虛擬環境。另外需要將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，並建立預設路徑以及配置檔案：

(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

此時，建立一個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提供的樣例檔案改造過的，以適應源和目標環境。下面是配置檔案改造的摘要。

預設情況下.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中該使用者需要賦予額外的許可權用以訪問需要複製表：

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段建立一個“db_replica”database用以接收MySQL資料。PG中的“usr_replica”使用者自動配置成兩個schemas（pgworld_x和sch_chameleon）的擁有者。這兩個schema包含實際複製表和catalog表。透過create_replica_schema引數自動配置：

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

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

$> vi /etc/my.cnf
binlog_format= ROW
binlog_row_image=FULL
log-bin = mysql-binserver-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”基於配置檔案進行初始化。

$> chameleon create_replica_schema --debug
$> chameleon add_source --config default --source mysql --debug
$> chameleon init_replica --config default --source mysql --debug

上面的三個命令執行成功後，會分別輸出明顯的執行成功資訊。任何錯誤和語法錯誤都會清晰的輸出。

最後一步是透過“start_replica”啟動複製：

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

透過show_status顯示覆制狀態：

$> 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  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 errors in the log

透過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 --source mysql
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
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：

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 mysql
Sync tables process for source mysql started.

測試確認複製正常：

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

如果是一個遷移需求，執行下面命令標記遷移結束。在所有需要複製的表複製完成後執行這些命令：

$> 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

安裝並配置比較簡單

錯誤日誌易看懂

無需更改任何配置，初始化完成後可以新增額外的複製表

可配置成多源複製

可以指定不復制哪些表

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