mysql全量同步到postgresql中

忘是亡心i 2023-06-17 12:59 2阅读 0赞

## 1 rds\_dbsync (推荐使用) ##

[《MySQL准实时同步到PostgreSQL, Greenplum的方案之一 - rds\_dbsync》][MySQL_PostgreSQL_ Greenplum_ - rds_dbsync]

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

### 用法 ###

以CentOS 7.x x64为例。

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

1、pgsql

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

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

[《PostgreSQL 10 + PostGIS + Sharding(pg\_pathman) + MySQL(fdw外部表) 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_downloads_repo_yum]

[https://dev.mysql.com/doc/mysql-yum-repo-quick-guide/en/][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_blob_master_doc_mysql2pgsql_ch.md]

[https://github.com/aliyun/rds\_dbsync/releases][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端口转发代理》][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 部署指南(适合新用户) - 珍藏级》][PostgreSQL 10 _ PostGIS _ Sharding_pg_pathman_ _ MySQL_fdw_ on ECS _ -]

[https://github.com/EnterpriseDB/mysql\_fdw][https_github.com_EnterpriseDB_mysql_fdw]

[http://blog.163.com/digoal@126/blog/static/1638770402011111233524987/][http_blog.163.com_digoal_126_blog_static_1638770402011111233524987]

[http://blog.163.com/digoal@126/blog/static/163877040201493145214445/][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_aliyun_rds_dbsync]

[https://github.com/EnterpriseDB/mysql\_fdw][https_github.com_EnterpriseDB_mysql_fdw]

[MySQL_PostgreSQL_ Greenplum_ - rds_dbsync]: https://github.com/digoal/blog/blob/master/201710/20171027_02.md
[PostgreSQL on Linux _ -]: https://github.com/digoal/blog/blob/master/201611/20161121_01.md
[PostgreSQL 10 on ECS _ _ -]: https://github.com/digoal/blog/blob/master/201711/20171129_02.md
[PostgreSQL 10 _ PostGIS _ Sharding_pg_pathman_ _ MySQL_fdw_ on ECS _ -]: https://github.com/digoal/blog/blob/master/201710/20171018_01.md
[https_dev.mysql.com_downloads_repo_yum]: https://dev.mysql.com/downloads/repo/yum/
[https_dev.mysql.com_doc_mysql-yum-repo-quick-guide_en]: https://dev.mysql.com/doc/mysql-yum-repo-quick-guide/en/
[https_github.com_aliyun_rds_dbsync_blob_master_doc_mysql2pgsql_ch.md]: https://github.com/aliyun/rds_dbsync/blob/master/doc/mysql2pgsql_ch.md
[https_github.com_aliyun_rds_dbsync_releases]: https://github.com/aliyun/rds_dbsync/releases
[ssh -R _TCP]: https://github.com/digoal/blog/blob/master/201406/20140614_01.md
[https_github.com_EnterpriseDB_mysql_fdw]: https://github.com/EnterpriseDB/mysql_fdw
[http_blog.163.com_digoal_126_blog_static_1638770402011111233524987]: http://blog.163.com/digoal@126/blog/static/1638770402011111233524987/
[http_blog.163.com_digoal_126_blog_static_163877040201493145214445]: http://blog.163.com/digoal@126/blog/static/163877040201493145214445/
[https_github.com_aliyun_rds_dbsync]: https://github.com/aliyun/rds_dbsync