mysql全量同步到postgresql中

1 rds_dbsync (推荐使用)

《MySQL准实时同步到PostgreSQL, Greenplum的方案之一 - rds_dbsync》

这个效率最高,支持不落地,支持流式导入,支持单表并发(通过配置文件,写WHERE条件,拆成多个并发导同一张表)。

用法

以CentOS 7.x x64为例。

mysql2pgsql已打包所有依赖包,可以不安装pgsql和mysql。不过你如果想连接数据库做一些管理工作、或者排错等,还是有必要安装一下。

1、pgsql

《PostgreSQL on Linux 最佳部署手册 - 珍藏级》

《PostgreSQL 10 on ECS 实施 流复制备库镜像+自动快照备份+自动备份验证+自动清理备份与归档 - 珍藏级》

《PostgreSQL 10 + PostGIS + Sharding(pg_pathman) + MySQL(fdw外部表) on ECS 部署指南(适合新用户) - 珍藏级》

 
  1. su - digoal

  2. vi .bash_profile

  3.  
  4. export PS1="$USER@`/bin/hostname -s`-> "

  5. export PGPORT=1921

  6. export PGDATA=/data01/pg/pg_root$PGPORT

  7. export LANG=en_US.utf8

  8. export PGHOME=/home/digoal/pgsql11

  9. export LD_LIBRARY_PATH=$PGHOME/lib:/lib64:/usr/lib64:/usr/local/lib64:/lib:/usr/lib:/usr/local/lib:$LD_LIBRARY_PATH

  10. export PATH=$PGHOME/bin:$PATH:.

  11. export DATE=`date +"%Y%m%d%H%M"`

  12. export MANPATH=$PGHOME/share/man:$MANPATH

  13. export PGHOST=$PGDATA

  14. export PGUSER=postgres

  15. export PGDATABASE=postgres

  16. alias rm='rm -i'

  17. alias ll='ls -lh'

  18. unalias vi

2、mysql

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

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

 
  1. su - root

  2. vi /etc/yum.repos.d/mysql.repo

  3.  
  4. [mysql57-community]

  5. name=MySQL 5.7 Community Server

  6. baseurl=http://repo.mysql.com/yum/mysql-5.7-community/el/7/$basearch/

  7. enabled=1

  8. gpgcheck=0

  9. 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

详细配置文档参考

 
  1. ./mysql2pgsql --help

  2. ignore copy error count 0 each table

  3. Unsupported option: -Usage: -l <table list file> -j <thread number> -d -n -f -s -b -h

  4. -l specifies a file with table listed;

  5. -j specifies number of threads to do the job;

  6. -d means get DDL only without fetching data;

  7. -n means no partion info in DDLs;

  8. -f means taking first column as distribution key;

  9. -s specifies the target schema;

  10. -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

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

  2.  
  3. unzip mysql2pgsql.bin.el7.20171213.zip

4、rds_dbsync 将mysql迁移到pgsql

确保执行mysql2pgsql的机器,可以同时连接到mysql, pgsql.

如果无法直接互联,可以使用SSH打通隧道进行互相访问。

《使用 ssh -R 建立反向/远程TCP端口转发代理》

1、配置my.cfg文件,源(mysql)、目标(pgsql)

 
  1. cd mysql2pgsql.bin.el7.20171213

  2.  
  3. cd bin

  4.  
  5.  
  6. vi my.cfg

  7.  
  8. [src.mysql]

  9. host = "数据库IP"

  10. port = "数据库PORT"

  11. user = "数据库user"

  12. password = "数据库user密码"

  13. db = "数据库名"

  14. encodingdir = "share"

  15. encoding = "utf8"

  16.  
  17. [desc.pgsql]

  18. connect_string = "host=127.0.0.1 port=1921 dbname=postgres user=postgres password=pgsql"

  19. target_schema = "public"

如果postgresql在本地,可以使用unix socket连接,导入性能比tcp要快一点。例如

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

unix socket dir配置可从配置文件读取

 
  1. postgres=# show unix_socket_directories ;

  2. unix_socket_directories

  3. -------------------------

  4. /tmp,.

  5. (1 row)

2、生成mysql 转换为pgsql 的建表 DDL

./mysql2pgsql -d > ddl.sql  

3、执行输出的DDL文件

在pgsql对应的数据库中,执行第二步生成的DDL语句,创建目标表。

psql -f ./ddl.sql -1   

如果有问题,需要手工修复一下。

迁移例子

1、全量迁移

 
  1. cd mysql2pgsql.bin.el7.20171213/bin

  2.  
  3. nohup ./mysql2pgsql >./load.log 2>&1 &

2、选择性迁移

如果不想迁移所有表的数据,或者某些表只想迁移部分数据,可以写配置文件。

2.1、甚至可以多个源写入单个表,例如多个MYSQL节点数据,汇入单个PG节点。

2.2、如果源表与PG的目标表名字不一样,可以在配置文件中映射表名。(冒号分隔:第一列为mysql里面的表名,第二列为MYSQL里面的表名,或者QUERY)

 
  1. vi lo.txt

  2.  
  3. tbl1

  4. tbl2 : select * from tbl_from_mysql where id<10000;

  5. tbl2 : select * from tbl_from_mysql where id >= 100000 and id< 10000000;

  6. tbl3 : tbl_from_mysql_1

  7. tbl3 : tbl_from_mysql_2

然后执行

 
  1. cd mysql2pgsql.bin.el7.20171213/bin

  2.  
  3. nohup ./mysql2pgsql -l ./lo.txt >./load.log 2>&1 &

3、并行迁移

默认为5个迁移线程操作(每个线程COPY一张表),通过-j参数指定。

 
  1. cd mysql2pgsql.bin.el7.20171213/bin

  2.  
  3. nohup ./mysql2pgsql -l ./lo.txt -j 8 >./load.log 2>&1 &

4、单表如何支持并行迁移

单表,通过where条件分段,可以实现单表的并行迁移(但是几个SQL分开执行,他们的SNAPSHOT不一样,不满足全局一致性)

 
  1. vi lo.txt

  2.  
  3. tbl2 : select * from tbl_from_mysql where id < 1000000;

  4. tbl2 : select * from tbl_from_mysql where id >= 1000000 and id < 2000000;

  5. tbl2 : select * from tbl_from_mysql where id >= 2000000 and id < 3000000;

  6. tbl2 : select * from tbl_from_mysql where id >= 3000000;

 
  1. cd mysql2pgsql.bin.el7.20171213/bin

  2.  
  3. nohup ./mysql2pgsql -l ./lo.txt -j 4 >./load.log 2>&1 &

2 mysql_fdw

《PostgreSQL 10 + PostGIS + Sharding(pg_pathman) + MySQL(fdw外部表) on ECS 部署指南(适合新用户) - 珍藏级》

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

通过管道导入

 
  1. export PGHOST=

  2. export PGPORT=

  3. export PGDATABASE=

  4. export PGUSER=

  5. export PGPASSWORD=

  6.  
  7. nohup mysql -C -h主机 -P端口 -u用户 -p密码 库 -B -e "select * from 表" | psql -c "copy 表 from stdin with (format csv, HEADER true, null 'NULL', DELIMITER E'\t')" > /dev/null 2>&1 &

  8.  

如果表很大,可能OOM,因为需要将数据完全HOLD到mysql客户端后,才开始输出。暂不清楚mysql客户端有没有流式输出的功能。

4 mysqldump

通过管道导入

 
  1. export PGHOST=

  2. export PGPORT=

  3. export PGDATABASE=

  4. export PGUSER=

  5. export PGPASSWORD=

  6.  
  7. mysqldump 库名 -t -h主机 -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服务器上运行,可以dump CSV格式。

MySQL没有像PostgreSQL这样的COPY to stdout或COPY from stdin这样的COPY协议,只有服务端COPY。

mysqldump有一些格式问题(即使使用--compatible=postgresql),可能导致数据导入到PG时出错。

参考

man mysql

man mysqldump

https://github.com/aliyun/rds_dbsync

https://github.com/EnterpriseDB/mysql_fdw

  • 0
    点赞
  • 4
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值