digoal
2018-08-15
PostgreSQL , MySQL , rds_dbsync , mysql , mysqldump , copy , mysql_fdw
将MySQL数据不落地的方式导入PostgreSQL。
《MySQL准实时同步到PostgreSQL, Greenplum的方案之一 - rds_dbsync》
这个效率最高，支持不落地，支持流式导入，支持单表并发（通过配置文件，写WHERE条件，拆成多个并发导同一张表）。
以CentOS 7.x x64为例。
mysql2pgsql已打包所有依赖包，可以不安装pgsql和mysql。不过你如果想连接数据库做一些管理工作、或者排错等，还是有必要安装一下。
《PostgreSQL on Linux 最佳部署手册 - 珍藏级》
《PostgreSQL 10 on ECS 实施 流复制备库镜像+自动快照备份+自动备份验证+自动清理备份与归档 - 珍藏级》
《PostgreSQL 10 + PostGIS + Sharding(pg_pathman) + MySQL(fdw外部表) on ECS 部署指南(适合新用户) - 珍藏级》
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
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
详细配置文档参考
./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
确保执行mysql2pgsql的机器，可以同时连接到mysql, pgsql.
如果无法直接互联，可以使用SSH打通隧道进行互相访问。
1、配置my.cfg文件，源(mysql)、目标(pgsql)
cd mysql2pgsql.bin.el7.20171213
cd bin
vi my.cfg
[src.mysql]
host = "数据库IP"
port = "数据库PORT"
user = "数据库user"
password = "数据库user密码"
db = "数据库名"
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连接，导入性能比tcp要快一点。例如
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 转换为pgsql 的建表 DDL
./mysql2pgsql -d > ddl.sql
3、执行输出的DDL文件
在pgsql对应的数据库中，执行第二步生成的DDL语句，创建目标表。
psql -f ./ddl.sql -1
如果有问题，需要手工修复一下。
1、全量迁移
cd mysql2pgsql.bin.el7.20171213/bin
nohup ./mysql2pgsql >./load.log 2>&1 &
2、选择性迁移
如果不想迁移所有表的数据，或者某些表只想迁移部分数据，可以写配置文件。
2.1、甚至可以多个源写入单个表，例如多个MYSQL节点数据，汇入单个PG节点。
2.2、如果源表与PG的目标表名字不一样，可以在配置文件中映射表名。(冒号分隔：第一列为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
然后执行
cd mysql2pgsql.bin.el7.20171213/bin
nohup ./mysql2pgsql -l ./lo.txt >./load.log 2>&1 &
3、并行迁移
默认为5个迁移线程操作（每个线程COPY一张表），通过-j参数指定。
cd mysql2pgsql.bin.el7.20171213/bin
nohup ./mysql2pgsql -l ./lo.txt -j 8 >./load.log 2>&1 &
4、单表如何支持并行迁移
单表，通过where条件分段，可以实现单表的并行迁移（但是几个SQL分开执行，他们的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 &
《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/
通过管道导入
export PGHOST=
export PGPORT=
export PGDATABASE=
export PGUSER=
export PGPASSWORD=
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 &
如果表很大，可能OOM，因为需要将数据完全HOLD到mysql客户端后，才开始输出。暂不清楚mysql客户端有没有流式输出的功能。
通过管道导入
export PGHOST=
export PGPORT=
export PGDATABASE=
export PGUSER=
export PGPASSWORD=
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
您的愿望将传达给PG kernel hacker、数据库厂商等, 帮助提高数据库产品质量和功能, 说不定下一个PG版本就有您提出的功能点. 针对非常好的提议，奖励限量版PG文化衫、纪念品、贴纸、PG热门书籍等，奖品丰富，快来许愿。开不开森.
