PostgreSQL 和 MySQL 之間的交叉複製

我將概述 PostgreSQL 和 MySQL 之間的交叉複製，以及在兩個資料庫伺服器之間設定交叉複製的方法。 通常，交叉複製資料庫稱為同類資料庫，它是從一個 RDBMS 伺服器移動到另一個 RDBMS 伺服器的便捷方法。

PostgreSQL 和 MySQL 資料庫通常被視為關聯式資料庫，但透過附加擴展，它們提供了 NoSQL 功能。 在這裡，我們將從關係式 DBMS 的角度討論 PostgreSQL 和 MySQL 之間的複製。

我們不會描述整個內部工作原理，僅描述基本原理，以便您了解配置資料庫伺服器之間的複製、優點、限制和用例。

通常，兩個相同的資料庫伺服器之間的複製以二進位模式或使用主伺服器（也稱為發布者、主伺服器或主動伺服器）和從伺服器（訂閱伺服器、備用伺服器或被動伺服器）之間的查詢來完成。 複製的目的是在從機端提供主資料庫的即時副本。 在這種情況下，資料從master傳輸到slave，即從主動到被動，因為複製僅在一個方向上進行。 但是您可以在兩個資料庫之間設定雙向複製，以便以主動-主動配置將資料從從資料庫傳輸到主資料庫。 所有這些，包括級聯複製，都可以在兩個或多個相同的資料庫伺服器之間進行。主動-主動或主動-被動配置取決於需要、初始配置中此類功能的可用性或外部配置解決方案的使用以及現有的權衡。

所描述的配置可以在不同的資料庫伺服器之間進行。 該伺服器可以配置為接受來自另一個資料庫伺服器的複製數據，並且仍然維護複製數據的即時快照。 MySQL 和 PostgreSQL 在內部或透過第三方擴充功能提供大部分配置，包括二進位日誌方法、磁碟鎖定以及基於語句和行的方法。

從一台資料庫伺服器一次性遷移到另一台資料庫伺服器需要 MySQL 和 PostgreSQL 之間的交叉複製。 這些資料庫使用不同的協議，因此不可能直接連結它們。 要建立資料交換，您可以使用外部開源工具，例如pg_chameleon。

什麼是 pg_chameleon

pg_chameleon 是 Python 3 中從 MySQL 到 PostgreSQL 的複製系統。它使用同樣用 Python 寫的開源 mysql-replication 函式庫。 行圖像從 MySQL 表中提取並作為 JSONB 物件儲存在 PostgreSQL 資料庫中，然後透過 pl/pgsql 函數解密並在 PostgreSQL 資料庫中重現。

pg_chameleon 的特點

可以透過一對多配置將同一叢集中的多個 MySQL 模式複製到單一目標 PostgreSQL 資料庫
來源架構名稱和目標架構名稱不能相同。
可以從級聯 MySQL 副本中檢索複製資料。
無法複製或產生錯誤的表被排除在外。
每個複製功能都由守護程式控制。
透過基於 YAML 的參數和設定檔進行控制。

例子

主持人
vm1
vm2

操作系統版本
CentOS Linux 7.6 x86_64
CentOS Linux 7.5 x86_64

資料庫伺服器版本
MySQL 5.7.26
PostgreSQL 10.5

資料庫連接埠
3306
5433

IP地址
192.168.56.102
192.168.56.106

首先，準備安裝 pg_chameleon 所需的所有組件。 此範例安裝 Python 3.6.8，它會創建並啟動虛擬環境。

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

成功安裝Python3.6後，您需要完成剩餘的要求，例如建立和啟動虛擬環境。 此外，pip模組已更新至最新版本並用於安裝pg_chameleon。 下面的命令有意安裝 pg_chameleon 2.0.9，儘管最新版本是 2.0.10。 這是避免更新版本中出現新錯誤所必需的。

$> 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 （chameleon 是一個命令）來啟用 pg_chameleon 並建立預設目錄和設定檔。

(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

現在我們建立 config-example.yml 的副本作為 default.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 文件，經過少量修改以適應來源環境和目標環境，以下是設定檔不同部分的概述。

在default.yml設定檔中有一個全域設定部分，您可以在其中管理諸如鎖定檔案位置、日誌位置、日誌儲存期限等設定。接下來是類型覆蓋部分，其中複製期間覆蓋類型的一組規則。 此範例預設使用將tinyint(1) 轉換為布林值的類型覆寫規則。 在下一節中，我們指定目標資料庫的連線詳細資訊。 在我們的範例中，這是一個 PostgreSQL 資料庫，指定為 pg_conn。 最後一節，我們指明來源數據，即來源資料庫的連接參數、來源資料庫和目標資料庫之間的映射方案、需要跳過的表、等待時間、記憶體、套件大小。 請注意，「來源」是複數，這意味著我們可以將多個來源資料庫新增至單一目標資料庫以設定多對一配置。

範例資料庫 world_x 包含 4 個表，其中包含 MySQL 社群提供的行作為範例。 可以下載 這裡。 範例資料庫以 tar 和壓縮存檔形式提供，其中包含建立和匯入行的說明。

在 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 資料庫，該資料庫將接受來自 MySQL 資料庫的變更。 PostgreSQL 中的使用者 usr_replica 會自動配置為兩個模式 pgworld_x 和 sch_chameleon 的擁有者，這兩個模式分別包含實際的複製表和複製目錄表。 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-bin
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 (chameleon) 指令準備環境、新增來源並初始化副本。 正如我們已經討論過的，pg_chameleon 的 create_replica_schema 參數在 PostgreSQL 資料庫中建立預設模式 (sch_chameleon) 和複製模式 (pgworld_x)。 add_source參數透過讀取設定檔（default.yml）將來源資料庫新增至設定中，在我們的範例中是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 mysql 
output: Starting the replica process for source mysql

可以使用 show_status 參數查詢複製狀態，並且可以使用 show_errors 參數查看錯誤。

結果。

正如我們已經說過的，每個複製功能都是由守護程式控制的。 為了查看它們，我們使用 Linux ps 命令查詢進程表，如下所示。

結果。

在我們即時測試複製之前，複製並不被視為已配置，如下所示。 我們建立一個表，將幾筆記錄插入 MySQL 資料庫，然後呼叫 pg_chameleon 中的sync_tables 參數來更新守護進程，並將包含記錄的表複製到 PostgreSQL 資料庫。

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.

為了確認測試結果，我們從 PostgreSQL 資料庫查詢表並輸出行。

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

如果我們正在進行遷移，以下 pg_chameleon 指令將結束遷移。 在我們確定所有目標表的行都已被複製後，需要執行這些命令，結果將是一個整齊遷移的 PostgreSQL 資料庫，而無需引用來源資料庫或複製方案（sch_chameleon）。

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

pg_chameleon的優點

輕鬆設定和配置。
透過清晰的錯誤訊息輕鬆排除故障並識別異常情況。
初始化後可以將其他特殊表新增到複製中，而無需更改其餘配置。
可以為單一目標資料庫配置多個來源資料庫，如果您要將一個或多個 MySQL 資料庫中的資料組合到單一 PostgreSQL 資料庫中，這將非常有用。
您不必複製選定的表。

pg_chameleon 的缺點

僅支援 MySQL 5.5 以上版本作為來源資料庫和 PostgreSQL 9.5 以上版本作為目標資料庫。
每個表必須有一個主鍵或唯一鍵，否則表會在 init_replica 過程中初始化但不會被複製。
單向複製 - 僅從 MySQL 到 PostgreSQL。 因此，它只適用於“有源-無源”電路。
來源只能是 MySQL 資料庫，對 PostgreSQL 資料庫作為來源的支援只是實驗性的並且有限制（了解更多 這裡)

pg_chameleon 的結果

pg_chameleon 中的複製方法非常適合將資料庫從 MySQL 遷移到 PostgreSQL。 顯著的缺點是複製只是單向的，因此資料庫專業人員不太可能將其用於遷移以外的任何用途。 但單向複製的問題可以透過另一個開源工具——SymmetricDS 來解決。

閱讀官方文件以了解更多內容 這裡。 可以找到命令列幫助 這裡.

SymmetricDS 概述

SymmetricDS 是一個開源工具，可以將任何資料庫複製到任何其他常見資料庫：Oracle、MongoDB、PostgreSQL、MySQL、SQL Server、MariaDB、DB2、Sybase、Greenplum、Informix、H2、Firebird 和其他雲端資料庫，例如 Redshift 和Azure等。可用功能：資料庫和檔案同步、多主資料庫複製、過濾同步、轉換等。 這是一個 Java 工具，需要標準版本的 JRE 或 JDK（版本 8.0 或更高版本）。 這裡，可以記錄來源資料庫中觸發器的資料變化，並以批次的形式傳送到對應的目標資料庫。

對稱 DS 功能

該工具是獨立於平台的，這意味著兩個或多個不同的資料庫可以交換資料。
關聯式資料庫使用資料變更記錄進行同步，而基於檔案系統的資料庫則使用檔案同步。
基於一組規則使用推式和拉式方法的雙向複製。
資料傳輸可以透過安全和低頻寬網路進行。
節點發生故障後恢復運行時的自動恢復以及自動衝突解決。
雲端相容且強大的擴充 API。

例子

SymmetricDS 可以透過以下兩個選項之一進行設定：
主（父）節點集中協調兩個從（子）節點之間的資料複製，子節點之間的通訊僅透過父節點進行。
主動節點（節點 1）可以與另一個主動節點（節點 2）進行複製通信，無需中介。

在這兩個選項中，資料交換都是使用「推」和「拉」進行的。 在此範例中，我們將考慮主動-主動配置。 描述整個架構需要很長時間，因此請做好研究。 領導了解有關 SymmetricDS 裝置的更多資訊。

安裝 SymmetricDS 非常簡單：下載 zip 檔案的開源版本 故 並將其取出到任何你想要的地方。 下表提供了有關本範例中 SymmetricDS 的安裝位置和版本的信息，以及兩個節點的資料庫版本、Linux 版本、IP 位址和連接埠的資訊。

主持人
vm1
vm2

操作系統版本
CentOS Linux 7.6 x86_64
CentOS Linux 7.6 x86_64

資料庫伺服器版本
MySQL 5.7.26
PostgreSQL 10.5

資料庫連接埠
3306
5832

IP地址
192.168.1.107
192.168.1.112

對稱DS版本
對稱DS 3.9
對稱DS 3.9

SymmetricDS安裝路徑
/usr/local/對稱伺服器-3.9.20
/usr/local/對稱伺服器-3.9.20

SymmetricDS 節點名稱
corp-000
商店 001

這裡我們將SymmetricDS安裝在/usr/local/symmetric-server-3.9.20中，其中將儲存各種子目錄和檔案。 我們對樣本和引擎子目錄感興趣。 範例目錄包含具有節點屬性的範例設定檔以及可協助您快速入門的範例 SQL 腳本。

在範例目錄中，我們看到三個具有節點屬性的設定檔 - 名稱顯示了特定方案中節點的性質。

corp-000.properties
store-001.properties
store-002.properties

SymmetricDS 具有基本 3 節點設計（選項 1）所需的所有配置文件，並且相同的文件可用於 2 節點設計（選項 2）。 將所需的設定檔從範例目錄複製到 vm1 主機上的引擎。 結果是這樣的：

$> cat engines/corp-000.properties
engine.name=corp-000
db.driver=com.mysql.jdbc.Driver
db.url=jdbc:mysql://192.168.1.107:3306/replica_db?autoReconnect=true&useSSL=false
db.user=root
db.password=admin123
registration.url=
sync.url=http://192.168.1.107:31415/sync/corp-000
group.id=corp
external.id=000

SymmetricDS 配置中的該節點稱為 corp-000，資料庫連接由 mysql jdbc 驅動程式處理，該驅動程式使用上面的連接字串和登入憑證。 我們連接到replica_db資料庫，並且將在模式建立期間建立表格。 sync.url 顯示聯繫節點進行同步的位置。

主機 vm2 上的節點 2 配置為 store-001，其餘部分在下面的 node.properties 檔案中指定。 節點 store-001 運行 PostgreSQL 資料庫，pgdb_replica 是複製資料庫。 Registration.url 允許主機 vm2 聯絡主機 vm1 並從中接收設定詳細資訊。

$> cat engines/store-001.properties
engine.name=store-001
db.driver=org.postgresql.Driver
db.url=jdbc:postgresql://192.168.1.112:5832/pgdb_replica
db.user=postgres
db.password=admin123
registration.url=http://192.168.1.107:31415/sync/corp-000
group.id=store
external.id=001

完成的 SymmetricDS 範例包含用於在兩個資料庫伺服器（兩個節點）之間設定雙向複製的參數。 以下步驟在主機 vm1 (corp-000) 上執行，這將建立一個包含 4 個資料表的範例架構。 然後使用 symadmin 指令執行 create-sym-tables 會建立目錄表，其中將儲存節點之間的複製規則和方向。 最後，樣本資料被載入到表中。

vm1$> cd /usr/local/symmetric-server-3.9.20/bin
vm1$> ./dbimport --engine corp-000 --format XML create_sample.xml
vm1$> ./symadmin --engine corp-000 create-sym-tables
vm1$> ./dbimport --engine corp-000 insert_sample.sql

在範例中，item 和 item_looking_price 表自動配置為從 corp-000 複製到 store-001，銷售表（sale_transaction 和 sale_return_line_item）自動配置為從 store-001 複製到 corp-000。 現在，我們在主機 vm2 (store-001) 上的 PostgreSQL 資料庫中建立一個架構，以準備從 corp-000 接收資料。

vm2$> cd /usr/local/symmetric-server-3.9.20/bin
vm2$> ./dbimport --engine store-001 --format XML create_sample.xml

請務必檢查 vm1 上的 MySQL 資料庫是否具有範例表和 SymmetricDS 目錄表。 請注意，SymmetricDS 系統表（以 sym_ 為前綴）目前僅在節點 corp-000 上可用，因為這是我們執行 create-sym-tables 命令並將管理複製的位置。 在節點 store-001 上的資料庫中，只有 4 個沒有資料的範例表。

全部。 環境已準備好在兩個節點上運行 sym 伺服器進程，如下所示。

vm1$> cd /usr/local/symmetric-server-3.9.20/bin
vm1$> sym 2>&1 &

日誌條目將傳送至 SymmetricDS 安裝目錄下的日誌資料夾中的後台日誌檔案 (symmetry.log) 以及標準輸出。 現在可以在節點 store-001 上啟動 sym 伺服器。

vm2$> cd /usr/local/symmetric-server-3.9.20/bin
vm2$> sym 2>&1 &

如果您在 vm2 主機上執行 sym 伺服器進程，它也會在 PostgreSQL 資料庫中建立 SymmetricDS 目錄表。 如果您在兩個節點上執行 sym 伺服器進程，它們將相互協調以將資料從 corp-000 複製到 store-001。 如果幾秒鐘後我們查詢兩側的所有 4 個表，我們將看到複製成功。 或者，您可以使用下列命令將引導程式從 corp-001 傳送到節點 store-000。

vm1$> ./symadmin --engine corp-000 reload-node 001

此時，新記錄已插入到節點 corp-000（主機：vm1）上的 MySQL 資料庫的 item 表中，您可以檢查其複製到節點 store-001（主機：vm2）上的 PostgreSQL 資料庫。 我們看到一個 Pull 操作將資料從 corp-000 移到 store-001。

mysql> insert into item values ('22000002','Jelly Bean');
Query OK, 1 row affected (0.00 sec)

vm2$> psql -p 5832 -U postgres pgdb_replica -c "select * from item"
 item_id  |   name
----------+-----------
 11000001 | Yummy Gum
 22000002 | Jelly Bean
(2 rows)

為了執行 Push 操作將資料從 store-001 移至 corp-000，我們將一筆記錄插入 sale_transaction 表並驗證複製是否成功。

結果。

我們看到 MySQL 和 PostgreSQL 資料庫之間範例表的雙向複製已成功設定。 若要為新使用者表設定複製，請執行下列步驟： 我們以建立表t1為例，並配置其複製規則如下。 這樣，我們只配置從 corp-000 到 store-001 的複製。

mysql> create table  t1 (no integer);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into sym_channel (channel_id,create_time,last_update_time) 
values ('t1',current_timestamp,current_timestamp);
Query OK, 1 row affected (0.01 sec)

mysql> insert into sym_trigger (trigger_id, source_table_name,channel_id,
last_update_time, create_time) values ('t1', 't1', 't1', current_timestamp,
current_timestamp);
Query OK, 1 row affected (0.01 sec)

mysql> insert into sym_trigger_router (trigger_id, router_id,
Initial_load_order, create_time,last_update_time) values ('t1',
'corp-2-store-1', 1, current_timestamp,current_timestamp);
Query OK, 1 row affected (0.01 sec)

然後，使用帶有sync-triggers參數的symadmin命令向配置通知架構更改，即添加新表，這將重新建立觸發器以映射表定義。 執行 send-schema 將架構變更傳送到節點 store-001，並配置表 t1 的複製。

vm1$> ./symadmin -e corp-000 --node=001 sync-triggers    
vm1$> ./symadmin send-schema -e corp-000 --node=001 t1

SymmetricDS 的優點

易於安裝和配置，包括一組現成的文件，其中包含用於建立三節點或兩節點電路的參數。
跨平台資料庫和平台獨立性，包括伺服器、筆記型電腦和行動裝置。
將任何資料庫複製到本地、WAN 或雲端中的任何其他資料庫。
可以與幾個或數千個資料庫進行最佳工作，以方便複製。
付費版本帶有 GUI 和出色的支援。

SymmetricDS 的缺點

您需要在命令列上透過SQL語句手動定義複製的規則和方向來載入目錄表，這可能很不方便。
設定許多資料表進行複製可能會很乏味，除非您使用腳本建立定義複製規則和方向的 SQL 語句。
日誌中記錄的資訊太多，有時您需要整理日誌文件，以免佔用太多空間。

SymmetricDS 的結果

SymmetricDS 可讓您在兩個、三個甚至數千個節點之間設定雙向複製，以複製和同步檔案。 這是一個獨特的工具，可以獨立執行許多任務，例如節點長時間停機後的自動資料復原、節點之間透過 HTTPS 進行安全且高效的資料交換、基於一組規則的自動衝突管理等。SymmetricDS 執行任何資料庫之間的複製，因此可用於多種場景，包括跨平台資料的遷移、遷移、分發、過濾和轉換。

該範例基於官方 快速指南 由對稱DS。 在 使用者手冊 詳細描述使用 SymmetricDS 設定複製所涉及的各種概念。

來源： www.habr.com