標(biāo)簽

PostgreSQL , MySQL , rds_dbsync , mysql , mysqldump , copy , mysql_fdw

---

背景

將MySQL數(shù)據(jù)不落地的方式導(dǎo)入PostgreSQL。

1 rds_dbsync (推薦使用)

《MySQL準(zhǔn)實(shí)時(shí)同步到PostgreSQL, Greenplum的方案之一 - rds_dbsync》

這個(gè)效率最高，支持不落地，支持流式導(dǎo)入，支持單表并發(fā)（通過(guò)配置文件，寫(xiě)WHERE條件，拆成多個(gè)并發(fā)導(dǎo)同一張表）。

用法

以CentOS 7.x x64為例。

mysql2pgsql已打包所有依賴包，可以不安裝pgsql和mysql。不過(guò)你如果想連接數(shù)據(jù)庫(kù)做一些管理工作、或者排錯(cuò)等，還是有必要安裝一下。

1、pgsql

《PostgreSQL on Linux 最佳部署手冊(cè) - 珍藏級(jí)》

《PostgreSQL 10 on ECS 實(shí)施 流復(fù)制備庫(kù)鏡像+自動(dòng)快照備份+自動(dòng)備份驗(yàn)證+自動(dòng)清理備份與歸檔 - 珍藏級(jí)》

《PostgreSQL 10 + PostGIS + Sharding(pg_pathman) + MySQL(fdw外部表) on ECS 部署指南(適合新用戶) - 珍藏級(jí)》

su - digoal vi .bash_profile export PS1="$USER@`/bin/hostname -s`-> " export PGPORT=1921 export PGDATA=/data01/pg/pg_root$PGPORT export LANG=en_US.utf8 export PGHOME=/home/digoal/pgsql11 export LD_LIBRARY_PATH=$PGHOME/lib:/lib64:/usr/lib64:/usr/local/lib64:/lib:/usr/lib:/usr/local/lib:$LD_LIBRARY_PATH export PATH=$PGHOME/bin:$PATH:. export DATE=`date +"%Y%m%d%H%M"` export MANPATH=$PGHOME/share/man:$MANPATH export PGHOST=$PGDATA export PGUSER=postgres export PGDATABASE=postgres alias rm='rm -i' alias ll='ls -lh' unalias vi

2、mysql

https://dev.mysql.com/downloads/repo/yum/

https://dev.mysql.com/doc/mysql-yum-repo-quick-guide/en/

su - root vi /etc/yum.repos.d/mysql.repo [mysql57-community] name=MySQL 5.7 Community Server baseurl=http://repo.mysql.com/yum/mysql-5.7-community/el/7/$basearch/ enabled=1 gpgcheck=0 gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql yum install -y mysql-community-server.x86_64 mysql-community-devel.x86_64

3、rds_dbsync

詳細(xì)配置文檔參考

./mysql2pgsql --help ignore copy error count 0 each table Unsupported option: -Usage: -l <table list file> -j <thread number> -d -n -f -s -b -h -l specifies a file with table listed; -j specifies number of threads to do the job; -d means get DDL only without fetching data; -n means no partion info in DDLs; -f means taking first column as distribution key; -s specifies the target schema; -b specifies the buffer size in KB used to sending copy data to target db, the default is 0

https://github.com/aliyun/rds_dbsync/blob/master/doc/mysql2pgsql_ch.md

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

wget https://github.com/aliyun/rds_dbsync/files/1555186/mysql2pgsql.bin.el7.20171213.zip unzip mysql2pgsql.bin.el7.20171213.zip

4、rds_dbsync 將mysql遷移到pgsql

確保執(zhí)行mysql2pgsql的機(jī)器，可以同時(shí)連接到mysql, pgsql.

如果無(wú)法直接互聯(lián)，可以使用SSH打通隧道進(jìn)行互相訪問(wèn)。

《使用 ssh -R 建立反向/遠(yuǎn)程TCP端口轉(zhuǎn)發(fā)代理》

1、配置my.cfg文件，源(mysql)、目標(biāo)(pgsql)

cd mysql2pgsql.bin.el7.20171213 cd bin vi my.cfg [src.mysql] host = "數(shù)據(jù)庫(kù)IP" port = "數(shù)據(jù)庫(kù)PORT" user = "數(shù)據(jù)庫(kù)user" password = "數(shù)據(jù)庫(kù)user密碼" db = "數(shù)據(jù)庫(kù)名" encodingdir = "share" encoding = "utf8" [desc.pgsql] connect_string = "host=127.0.0.1 port=1921 dbname=postgres user=postgres password=pgsql" target_schema = "public"

如果postgresql在本地，可以使用unix socket連接，導(dǎo)入性能比tcp要快一點(diǎn)。例如

connect_string = "host=/tmp dbname=postgres port=1921 user=postgres password=pgsql"

unix socket dir配置可從配置文件讀取

postgres=# show unix_socket_directories ; unix_socket_directories ------------------------- /tmp,. (1 row)

2、生成mysql 轉(zhuǎn)換為pgsql 的建表 DDL

./mysql2pgsql -d > ddl.sql

3、執(zhí)行輸出的DDL文件

在pgsql對(duì)應(yīng)的數(shù)據(jù)庫(kù)中，執(zhí)行第二步生成的DDL語(yǔ)句，創(chuàng)建目標(biāo)表。

psql -f ./ddl.sql -1

如果有問(wèn)題，需要手工修復(fù)一下。

遷移例子

1、全量遷移

cd mysql2pgsql.bin.el7.20171213/bin nohup ./mysql2pgsql >./load.log 2>&1 &

2、選擇性遷移

如果不想遷移所有表的數(shù)據(jù)，或者某些表只想遷移部分?jǐn)?shù)據(jù)，可以寫(xiě)配置文件。

2.1、甚至可以多個(gè)源寫(xiě)入單個(gè)表，例如多個(gè)MYSQL節(jié)點(diǎn)數(shù)據(jù)，匯入單個(gè)PG節(jié)點(diǎn)。

2.2、如果源表與PG的目標(biāo)表名字不一樣，可以在配置文件中映射表名。(冒號(hào)分隔：第一列為mysql里面的表名，第二列為MYSQL里面的表名，或者QUERY)

vi lo.txt tbl1 tbl2 : select * from tbl_from_mysql where id<10000; tbl2 : select * from tbl_from_mysql where id >= 100000 and id< 10000000; tbl3 : tbl_from_mysql_1 tbl3 : tbl_from_mysql_2

然后執(zhí)行

cd mysql2pgsql.bin.el7.20171213/bin nohup ./mysql2pgsql -l ./lo.txt >./load.log 2>&1 &

3、并行遷移

默認(rèn)為5個(gè)遷移線程操作（每個(gè)線程COPY一張表），通過(guò)-j參數(shù)指定。

cd mysql2pgsql.bin.el7.20171213/bin nohup ./mysql2pgsql -l ./lo.txt -j 8 >./load.log 2>&1 &

4、單表如何支持并行遷移

單表，通過(guò)where條件分段，可以實(shí)現(xiàn)單表的并行遷移（但是幾個(gè)SQL分開(kāi)執(zhí)行，他們的SNAPSHOT不一樣，不滿足全局一致性）

vi lo.txt tbl2 : select * from tbl_from_mysql where id < 1000000; tbl2 : select * from tbl_from_mysql where id >= 1000000 and id < 2000000; tbl2 : select * from tbl_from_mysql where id >= 2000000 and id < 3000000; tbl2 : select * from tbl_from_mysql where id >= 3000000; cd mysql2pgsql.bin.el7.20171213/bin nohup ./mysql2pgsql -l ./lo.txt -j 4 >./load.log 2>&1 &

2 mysql_fdw

《PostgreSQL 10 + PostGIS + Sharding(pg_pathman) + MySQL(fdw外部表) on ECS 部署指南(適合新用戶) - 珍藏級(jí)》

https://github.com/EnterpriseDB/mysql_fdw

http://blog.163.com/digoal@126/blog/static/1638770402011111233524987/

http://blog.163.com/digoal@126/blog/static/163877040201493145214445/

3 mysql

通過(guò)管道導(dǎo)入

export PGHOST= export PGPORT= export PGDATABASE= export PGUSER= export PGPASSWORD= nohup mysql -C -h主機(jī) -P端口 -u用戶 -p密碼 庫(kù) -B -e "select * from 表" | psql -c "copy 表 from stdin with (format csv, HEADER true, null 'NULL', DELIMITER E'\t')" > /dev/null 2>&1 &

如果表很大，可能OOM，因?yàn)樾枰獙?shù)據(jù)完全HOLD到mysql客戶端后，才開(kāi)始輸出。暫不清楚mysql客戶端有沒(méi)有流式輸出的功能。

4 mysqldump

通過(guò)管道導(dǎo)入

export PGHOST= export PGPORT= export PGDATABASE= export PGUSER= export PGPASSWORD= mysqldump 庫(kù)名 -t -h主機(jī) -P端口 -u用戶 -p密碼 --no-create-db --skip-quote-names --skip-add-locks --skip-lock-tables --skip-tz-utc -y --default-character-set=UTF8 -C --compact --compatible=postgresql --tables 表 | psql -f - >/dev/null 2>&1 &

如果在mysql服務(wù)器上運(yùn)行，可以dump CSV格式。

MySQL沒(méi)有像PostgreSQL這樣的COPY to stdout或COPY from stdin這樣的COPY協(xié)議，只有服務(wù)端COPY。

mysqldump有一些格式問(wèn)題(即使使用--compatible=postgresql)，可能導(dǎo)致數(shù)據(jù)導(dǎo)入到PG時(shí)出錯(cuò)。

參考

man mysql

man mysqldump

https://github.com/aliyun/rds_dbsync

https://github.com/EnterpriseDB/mysql_fdw

總結(jié)

以上是生活随笔為你收集整理的MySQL 不落地迁移、导入 PostgreSQL - 推荐 rds_dbsync的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問(wèn)題。

如果覺(jué)得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。