847 阿裏雲技術社區[雲棲]

MySQL準實時同步到PostgreSQL, Greenplum的方案之一 - rds_dbsync

標簽

PostgreSQL , Greenplum , rds_dbsync , binlog

背景

rds_dbsync是阿裏雲數據庫內核組開源的一個數據實時同步工具。

可以解析MySQL的binlog，或者PostgreSQL的WAL日誌，實現增量的實時同步。同時支持全量不落地遷移功能。

rds_dbsync功能介紹

1、全量不落地遷移(從mysql到pgsql，從pgsql到pgsql，從mysql或pgsql到Greenplum)。

支持並行。

2、DDL轉換

3、增量實時同步(從mysql到pgsql，從pgsql到pgsql，從mysql或pgsql到Greenplum)

注意，rds_dbsync增量實時同步功能不支持DDL，僅支持DML。因此如果有DDL操作，建議保持目標端處於源端的超集即可。

例如

1、用戶需要將字段改長。

先加目標端，再加源端。

2、用戶需要刪字段。

先刪源端，再刪目標端。

3、用戶需要加字段。

先加目標端，再加源端。

rds_dbsync功能模塊

1、mysql2pgsql，全量遷移命令。

2、binlog_miner，解析mysql binlog，並寫入臨時PG庫的命令。

3、binlog_loader，從臨時PG庫將BINLOG取出，並在目標庫回放的命令（會合並多條為一條，提高效率）。

下載rds_dbsync

建議下載二進製，CentOS 6 x64 選擇el6，CentOS 7 x64選擇el7。

https://github.com/aliyun/rds_dbsync/releases

編譯rds_dbsync

1、安裝mysql mysql-5.7.3-m13

https://downloads.mysql.com/archives/get/file/MySQL-5.7.3_m13-1.linux_glibc2.5.src.rpm

https://downloads.mysql.com/archives/community/

2、安裝PostgreSQL

參考《PostgreSQL 10 + PostGIS + Sharding(pg_pathman) + MySQL(fdw外部表) on ECS 部署指南(適合新用戶)》

3、修改makefile

# Minimal requriement: Mysql >= 5.6, Pgsql >= 9.3  
mysql_source_dir=mysql-5.7.3-m13  
mysql_install_dir=/usr/local/mysql  
mysql_include_dir=$(mysql_install_dir)/include  
mysql_lib_dir=$(mysql_install_dir)/lib  
  
pgsql_install_dir=/usr/pgsql-9.4

4、make

5、打包依賴動態庫，拷貝到對應目錄

《Linux 批量依賴庫拷貝(ldd)》

6、拷貝mysql share到對應目錄

cp -r /usr/local/mysql/share ....

7、最終目錄結構

# ls -la mysql2pgsql.bin.el7.20171025/  
  
drwxr-xr-x 2 59147 users 4096 Oct 25 12:49 bin  
drwxr-xr-x 2 59147 users 4096 Oct 25 12:48 lib  
drwxr-xr-x 2 59147 users 4096 Oct 25 12:48 share  
  
  
# cd bin  
[root@izuf64ame76453g49tfw8pz bin]# ll  
total 10612  
-rwxr-xr-x 1 root  root    209352 Oct 25 12:05 binlog_loader  
-rwxr-xr-x 1 root  root  10268656 Oct 25 12:05 binlog_miner  
-rw-r--r-- 1 59147 users        4 Jan 18  2017 loader_table_list.txt  
-rw-r--r-- 1 59147 users      646 Apr 24  2017 my.cfg  
-rwxr-xr-x 1 root  root    135416 Oct 25 12:05 mysql2pgsql  
-rwxr-xr-x 1 root  root    236736 Oct 25 12:05 pgsql2pgsql

一、全量遷移說明

mysql2pgsql

工具 mysql2pgsql 支持不落地的把 MYSQL 中的表遷移到 HybridDB/Greenplum Database/PostgreSQL/PPAS。

此工具的原理是，同時連接源端 mysql 數據庫和目的端數據庫，從 mysql 庫中通過查詢得到要導出的數據，然後通過 COPY 命令導入到目的端。

此工具支持多線程導入（每個工作線程負責導入一部分數據庫表）。

參數配置

修改配置文件 my.cfg、配置源和目的庫連接信息。

源庫 mysql 的連接信息如下：

**注意：**源庫 mysql 的連接信息中，用戶需要有對所有用戶表的讀權限。

[src.mysql]  
host = "192.168.1.1"  
port = "3306"  
user = "test"  
password = "test"  
db = "test"  
encodingdir = "share"  
encoding = "utf8"

encodingdir，指mysql軟件的share目錄，例如，建議將/usr/local/mysql/share拷貝到存放mysql2pgsql二進製文件的上一層目錄下，與bin平級。

encoding，指目標端pgsql數據庫的字符集（即我們從mysql庫讀取的數據，將轉換為這個編碼，目的是方便寫入目標端的PGSQL）。

目的庫 pgsql （包括 PostgreSQL、PPAS 和 HybridDB for PostgreSQL ）的連接信息如下：

**注意：**目的庫 pgsql 的連接信息，用戶需要對目標表有寫的權限。

[desc.pgsql]  
connect_string = "host=192.168.1.1 dbname=test port=5888  user=test password=pgsql"

mysql2pgsql 用法

mysql2pgsql 的用法如下所示：

./mysql2pgsql -l <tables_list_file> -d -n -j <number of threads> -s <schema of target able>

參數說明：

-l：可選參數，指定一個文本文件，文件中含有需要同步的表；如果不指定此參數，則同步配置文件中指定數據庫下的所有表。<tables_list_file>為一個文件名，裏麵含有需要同步的表集合以及表上查詢的條件，其內容格式示例如下：

不帶任何東西的冒號，會忽略。帶有條件的，將使用後麵的SQL拖取對應的數據。

table1 : select * from table_big where column1 < '2016-08-05'  
table2 :   
table3  
table4 : select column1, column2 from tableX where column1 != 10  
table5 : select * from table_big where column1 >= '2016-08-05'

-d：可選參數，表示隻生成目的表的建表 DDL 語句，不實際進行數據同步。
-n：可選參數，需要與-d一起使用，指定在 DDL 語句中不包含表分區定義。
-j：可選參數，指定使用多少線程進行數據同步；如果不指定此參數，會使用 5 個線程並發。
-s：可選參數，指定目標表的schema，一次命令隻能指定一個schema。如果不指定此參數，則數據會導入到public下的表。

典型用法

全庫遷移

全庫遷移的操作步驟如下所示：

1. 通過如下命令，獲取目的端對應表的 DDL。

./mysql2pgsql -d

2. 根據這些 DDL，再加入 distribution key 等信息，在目的端創建表。

3. 執行如下命令，同步所有表：

./mysql2pgsql

此命令會把配置文件中所指定數據庫中的所有 mysql 表數據遷移到目的端。過程中使用 5 個線程（即缺省線程數為 5），讀取和導入所有涉及的表數據。

部分表遷移

1. 編輯一個新文件 tab_list.txt，放入如下內容：

t1   
t2 : select * from t2 where c1 > 138888

2. 執行如下命令，同步指定的 t1 和 t2 表（注意 t2 表隻遷移符合 c1 > 138888 條件的數據）：

./mysql2pgsql -l tab_list.txt

mysql2pgsql 二進製安裝包下載

下載地址：單擊這裏。

mysql2pgsql 源碼編譯說明

查看源碼編譯說明，單擊這裏。

二、增量同步說明

增量同步前，建議先進行一次全量同步，並記錄下全量同步的開始時間點。這個時間點之後的binlog都需要抽取。

rds_dbsync實現了增量的冪等操作，因此我們僅需要一個全量同步開始的大致時間即可（比如10點開始全量同步，10點以前的時間都可以作為增量同步的binlog解析開始時間）。

mysql2gp(包含了binlog_miner和binlog_loader) 使用和部署說明

一 mysql2gp 介紹

mysql2gp 實現了從 MySQL 中遷移增量數據到 PostgreSQL 或 Greenplum

其中增量數據來自於 MySQL 的 binlog, 結合全量數據遷移工具 mysql2pgsql 可以把 MySQL 中的數據完整的遷移到 PostgreSQL Greenplum 中，且保持準實時同步。

1.1 支持特性和限製

1、支持拉取 MySQL 5.1 5.5 5.6 5.7 版本的 binlog，需要設置源端mysql數據庫的 binlog 相關參數(RDS MYSQL無需設置，已默認開啟)

	binlog_format = ROW  
	binlog_row_image = FULL

2、支持同步指定表的各類數據變化到目標DB中，包括對應行的 insert update delete。

3、數據同步的表需要有單列主鍵。

4、支持對主鍵進行修改。

5、暫時不支持異構數據庫的 DDL 同步。

6、支持指定表鏡像方式同步到 PostgreSQL 或 Greenplum（配置文件方式）。

7、支持指定模式的表同步。

1.2 mysql2gp 實現架構

mysql2gp 的實現方式：

1、在客戶端主機（也可以部署在其他主機）上啟動一個臨時 PG 數據庫，用於臨時存放從 MySQL 拉去到的 binlog 數據。

2、 binlog_miner 從源 MySQL 的一個 binlog 文件開始，拉取和解析 binlog 並存放到臨時 PG 中。

3、 binlog_loader 從臨時 PG 中讀取增量數據，並做適當的處理(主要是合並單條記錄的多次操作，減少目標端的壓力，提高性能)，最終批量寫入到目標 PostgreSQL 或 Greenplum 中去。

1.3 mysql2gp 模塊介紹

mysql2gp 分為5個部分

1、 binlog_miner 用於拉取MySQL 目標庫中的 binlog, 並保存到臨時 PGSQL DB 中。

2、 binlog_loader 用於讀取臨時 PGSQL DB 中的 binlog 數據並加載到目標庫中。

3、 my.cfg 配置文件，設置需要同步數據的源和目標數據庫的鏈接信息和相關參數。

4、 loader_table_list.txt 配置文件，設置需要同步的表名列表，用回車符隔開。

5、臨時 PGSQL DB，用戶保存增量數據的臨時數據庫。這個DB建議和 binlog_miner binlog_loader 部署在同一個主機。

二 mysql2gp 部署

建議臨時 PGSQL DB 和客戶端二進製部署在同主機

部署步驟：

2.1 部署臨時 PGSQL DB

在目標主機部署一個臨時 PGSQL DB 用戶存放臨時數據，主機需要為臨時數據預留足夠的保存增量數據的空間。部署完成後獲得一個連接臨時 PG DB 的連接串，如

dbname=test port=5432 user=test password=pgsql

2.2 配置文件

2.2.1 MySQL 相關

my.cnf

[src.mysql]  
host = "192.168.1.1"  
port = "3301"  
user = "test"  
password = "123456"  
db = "test"  
encodingdir = "share"  
encoding = "utf8"  
binlogfile = "mysql-bin.000001"

注意：

1、連接MySQL 的數據庫用戶需要有 select 權限和拉取 binlog 的權限。

2、 binlogfile 為讀取 binlog 的啟始文件，必須設置。該配置和全量數據同步工具配合使用。

通常在開始同步全量 MySQL 數據時記錄當前正在寫的 binlog 文件名，並配置到增量同步的 my.cnf 中。

下次啟動，不需要解析binlogfile，解析到的binlog偏移信息會記錄在臨時 PGSQL DB中。

如果要刪除增量任務，需要清空臨時 PGSQL DB庫。每個任務，需要一個臨時 PGSQL DB庫。

2.2.2 臨時PGSQL DB數據庫

my.cnf

[local.pgsql]  
connect_string = "dbname=test host=127.0.0.1 port=5432 user=test password=pgsql"

注意：

1、連接本地數據庫可以不指定 host 信息(默認使用/tmp下的unix socket連，這個需要在臨時PGSQL DB庫的postgresql.conf中設置它的unix_socket_directories參數，並選擇/tmp目錄)，這樣的鏈接模式效率較高。

2.2.3 目的數據庫

my.cnf

[desc.pgsql]  
connect_string = "host=192.167.1.2 dbname=postgres port=5432 user=test password=pgsql"  
target_schema = "test"

注意:

1、 target_schema 用於指定目標表存在的 schema，也可以不指定，不指時默認 schema 為 public。

2.2.4 設置需要同步的表

1、 my.cnf

[binlogloader]  
loader_table_list = "loader_table_list.txt"

2、 loader_table_list.txt

a  
b

2.3 啟動同步進程

2.3.1 啟動 binlog 拉取進程

推薦命令行：

	nohup ./binlog_miner 1>minner.log 2>&1 &

2.3.2 啟動 binlog 寫入進程

推薦命令行：

	nohup ./binlog_loader 1>loader.log 2>&1 &

定時任務和監控

監控binlog_miner, binlog_loader的進程狀態，如果進程異常，自動重啟。

crontab -e  
  
* * * * * 包含監控+啟動邏輯的shell腳本

下載

https://downloads.mysql.com/archives/community/

https://github.com/aliyun/rds_dbsync/releases

https://www.postgresql.org/ftp/source/

最後更新：2017-10-29 00:04:43

MySQL準實時同步到PostgreSQL, Greenplum的方案之一 - rds_dbsync

標簽

背景

rds_dbsync功能介紹

rds_dbsync功能模塊

下載rds_dbsync

編譯rds_dbsync

一、全量遷移說明

mysql2pgsql

參數配置

mysql2pgsql 用法

典型用法

全庫遷移

部分表遷移

mysql2pgsql 二進製安裝包下載

mysql2pgsql 源碼編譯說明

二、增量同步說明

mysql2gp(包含了binlog_miner和binlog_loader) 使用和部署說明

一 mysql2gp 介紹

1.1 支持特性和限製

1.2 mysql2gp 實現架構

1.3 mysql2gp 模塊介紹

二 mysql2gp 部署

2.1 部署臨時 PGSQL DB

2.2 配置文件

2.2.1 MySQL 相關

2.2.2 臨時PGSQL DB數據庫

2.2.3 目的數據庫

2.2.4 設置需要同步的表

2.3 啟動同步進程

2.3.1 啟動 binlog 拉取進程

2.3.2 啟動 binlog 寫入進程

定時任務和監控

下載

上一篇：速成網站和虛擬主機的區別？

下一篇： MySQL,Oracle,SQL Server等準實時同步到PostgreSQL的方案之一 - FDW外部訪問接口

相關內容

熱門內容

最新內容

MySQL準實時同步到PostgreSQL, Greenplum的方案之一 - rds_dbsync

標簽

背景

rds_dbsync功能介紹

rds_dbsync功能模塊

下載rds_dbsync

編譯rds_dbsync

一、全量遷移說明

mysql2pgsql

參數配置

mysql2pgsql 用法

典型用法

全庫遷移

部分表遷移

mysql2pgsql 二進製安裝包下載

mysql2pgsql 源碼編譯說明

二、增量同步說明

mysql2gp(包含了binlog_miner和binlog_loader) 使用和部署說明

一 mysql2gp 介紹

1.1 支持特性和限製

1.2 mysql2gp 實現架構

1.3 mysql2gp 模塊介紹

二 mysql2gp 部署

2.1 部署臨時 PGSQL DB

2.2 配置文件

2.2.1 MySQL 相關

2.2.2 臨時PGSQL DB數據庫

2.2.3 目的數據庫

2.2.4 設置需要同步的表

2.3 啟動同步進程

2.3.1 啟動 binlog 拉取進程

2.3.2 啟動 binlog 寫入進程

定時任務和監控

下載

上一篇： 速成網站和虛擬主機的區別？

下一篇： MySQL,Oracle,SQL Server等準實時同步到PostgreSQL的方案之一 - FDW外部訪問接口

相關內容

熱門內容

最新內容

上一篇：速成網站和虛擬主機的區別？