mysql全量同步到postgresql中

最新推荐文章于 2024-07-17 12:49:12 发布
最新推荐文章于 2024-07-17 12:49:12 发布
阅读量2.1k 收藏 4
点赞数
分类专栏： 工具相关 文章标签： mysql postgresql greenplum
原文链接：https://yq.aliyun.com/articles/698104
版权

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

哈哈哈哈哈哈哈111
关注
  • 0
    点赞
  • 踩
  • 4
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
mysql postgresql Greenplum 实时同步
06-28
mysql postgresql Greenplum 实时同步，步骤清晰，代码完整。
MySQL数据迁移到postgresql必备手册.pdf
09-01
在本例中，它被用作MySQL到PostgreSQL迁移的主要工具。 2. **迁移步骤详解** - **步骤1：选择数据库** 右键点击Navicat Premium中的MySQL数据库，选择“数据传输”功能，这将启动迁移过程。 - **步骤2：配置源与...
MySQL 不落地迁移、导入 PostgreSQL - 推荐 rds_dbsync
weixin_34087301的博客
04-14 772
标签 PostgreSQL , MySQL , rds_dbsync , mysql , mysqldump , copy , mysql_fdw 背景 将MySQL数据不落地的方式导入PostgreSQL。 1 rds_dbsync (推荐使用) 《MySQL准实时同步到PostgreSQL, Greenplum的方案之一 - rds_dbsyn...
最全MySQL 到 PostgreSQL 实时数据同步实操分享，4个改变你编程技能的小技巧
2301_78399550的博客
05-13 966
针对最近很多人都在面试，我这边也整理了相当多的面试专题资料，也有其他大厂的面经。希望可以帮助到大家。下面的面试题答案都整理成文档笔记。也还整理了一些面试资料&最新2021收集的一些大厂的面试真题（都整理成文档，小部分截图）最新整理电子书本文已被CODING开源项目：【一线大厂Java面试题解析+核心总结学习笔记+最新讲解视频+实战项目源码】收录需要这份系统化的资料的朋友，可以点击这里获取。
Oracle 数据怎么实时同步到 DM DB 达梦数据库 | 亲测干货建议收藏
java李阳勇的博客
09-02 4068
摘要 很多 DBA 同学经常会遇到要从一个数据库实时同步到另一个数据库的问题，同构数据还相对容易，遇上异构数据、表多、数据量大等情况就难以同步。我自己亲测了一种方式，可以很方便地完成 Oracle 数据实时同步到 DM DB 达梦数据库，跟大家分享一下，希望对你有帮助。 Oracle 数据实时同步到 DM DB 达梦数据库 ，目前支持的工具还比较少，一款免费的数据同步工具 Tapdata Cloud是我发现的第一个支持国产数据库的。跟前面分享到的其他数据库同步的操作方式类似、用起来感觉还不错、话不多..
MySQL 到 PostgreSQL 实时数据同步实操分享，Python中高级面试题
2401_83974173的博客
04-03 818
Python崛起并且风靡，因为优点多、应用领域广、被大牛们认可。学习 Python 门槛很低，但它的晋级路线很多，通过它你能进入机器学习、数据挖掘、大数据，CS等更加高级的领域。Python可以做网络应用，可以做科学计算，数据分析，可以做网络爬虫，可以做机器学习、自然语言处理、可以写游戏、可以做桌面应用…Python可以做的很多，你需要学好基础，再选择明确的方向。这里给大家分享一份全套的 Python 学习资料，给那些想学习 Python 的小伙伴们一点帮助！
MySQL准实时同步到PostgreSQL, Greenplum的方案之一 - rds_dbsync
weixin_34197488的博客
10-28 2117
标签 PostgreSQL , Greenplum , rds_dbsync , binlog 背景 rds_dbsync是阿里云数据库内核组开源的一个数据实时同步工具。 可以解析MySQL的binlog，或者PostgreSQL的WAL日志，实现增量的实时同步。同时支持全量不落地迁移功能。 rds_dbsync功能介绍 1、全量不落地迁移(从my...
使用Kettle同步mysql数据，增量同步教程执行步骤
10-24
以上步骤提供了使用Kettle进行MySQL增量同步的基本框架，但实际操作中可能需要根据具体业务需求进行调整。例如，可能需要处理复杂的业务规则，或者使用特定的版本控制策略来处理并发更新。 通过Kettle，你可以灵活...
NIFI1.21.0-Mysql和Postgresql到MysqlHbase-全量指定库和表同步到Mysql和Hbase.zip
06-20
NIFI1.21.0_Mysql和Postgresql到MysqlHbase_...是一个nifi的模板,实现了从mysql以及postgresql中同时读取数据,读取的时候支持,指定数据库和数据表进行同步,数据全量同步,可以同时同步到mysql数据库以及hbase数据库中.
Datax实现增量同步数据到Postgres
06-14
2. **增量同步**：与全量同步不同，增量同步只迁移自上次同步以来发生变化的数据，减少不必要的计算和网络传输，提高效率。Datax 支持基于时间戳、自增ID等多种方式实现增量同步。 3. **PostgreSQL 数据库**：...
mysql、posrgresql转成clickhouse db工具sync-master
02-07
然而，在实际业务中，我们往往需要将已有的MySQL或PostgreSQL数据库的数据迁移到ClickHouse，这时就需要借助特定的工具来实现。sync-master就是这样一款专为数据迁移而设计的开源工具，它使得从关系型数据库到...
pgsync：将数据从一个Postgres数据库同步到另一个数据库
02-05
pgsync 将数据从一个Postgres数据库同步到另一个数据库（例如pg_dump / pg_restore ）。 设计用于： 速度-表格并行传输 安全性-防止敏感数据离开服务器的内置方法 灵活性-优雅地处理架构差异，例如缺少列和多余的列 便利性-同步部分表，表组和相关记录 :tangerine: 经过战斗测试 安装 pgsync是一个命令行工具。 要安装，请运行： gem install pgsync 这将为您提供pgsync命令。 如果安装失败，则可能需要安装。 建立 在您的项目目录中，运行： pgsync --init 这将创建.pgsync.yml供您自定义。 我们建议将其检查到版本控件
mysql2psql
01-15
mysql2psql 是一个 Ruby 的扩展用来将 MySQL 数据迁移到 PostgreSQL 数据库。 安装方法：gem install mysql2psql
从MySQL迁移到PostgreSQL的完整指南
最新发布
分享关于Java编程、数据库管理和信息安全方面的最佳实践
07-17 1068
从MySQL迁移到PostgreSQL是一个复杂但必要的过程，它能够为系统带来更高的性能、丰富的功能集和更强的扩展能力。整个迁移过程包括准备工作、迁移表结构、迁移数据、迁移存储过程和函数以及数据完整性验证。在每个步骤中都需要细致的规划和执行，以确保数据的一致性和完整性。在迁移过程中，需要仔细考虑停机时长、应用改造以及生产环境中的实际验证。停机时长的规划直接影响业务的连续性，建议在业务低峰期进行迁移，并使用增量备份和恢复的方法以缩短停机时间。
mysql迁移到postgresql,通过chameleon工具迁移问题合集
CXNSHUAI的专栏
03-01 481
建议先执行一次drop_replica_schema命令，如果直接执行过init_replica命令，不执行drop_replica_schema而直接再次执行init_replica，会出现命令执行成功但数据没有同步的情况。schema_mappings参数，前面为mysql数据库，后面为cmdb的schema，些schema会自动创建。新建数据库后不需要创建schema，同步工具会自动创建schema，只需要新建数据库即可。1.2.1推荐使用源码安装方法，使用工具安装可能会出现如下报错。
使用canal和canal-adapter实时同步Mysql数据到postgres数据库中
归期的博客
10-25 2970
canal简介 canal，译意为水道/管道/沟渠，主要用途是基于 MySQL 数据库增量日志解析，提供增量数据订阅和消费 基于日志增量订阅和消费的业务包括 数据库镜像 数据库实时备份 索引构建和实时维护(拆分异构索引、倒排索引等) 业务 cache 刷新 带业务逻辑的增量数据处理 当前的 canal 支持源端 MySQL 版本包括 5.1.x , 5.5.x , 5.6.x , 5.7.x , 8.0.x canal和canal-adapter下载 官方下载地址: https://github.c
MySQL 增量同步到 PostgreSQL
weixin_34219944的博客
10-22 872
MySQL 增量同步到 PostgreSQL 作者 digoal 日期 2016-10-20 标签 PostgreSQL , MySQL , 数据同步 , binlog 背景 接触到越来越多MySQL的数据库迁移到PostgreSQL的需求，整理了一些从MySQL增量迁移到PostgreSQL的开源项目。 有一些是基于binlog的，增量效果比...
MySQL迁移到pgsql中
weixin_43804233的博客
07-02 162
我是用centos7.9才实现的，公司arm架构的弄了半天没弄起来，最后虚拟机搞了个centos就好了。最后开始执行命令迁移。
MySQL迁移到PostgreSQL操作指南
日拱一卒无有尽，功不唐捐终入海
08-05 9217
请注意，这只是一个简单的指南，MySQL到PostgreSQL的迁移实际上可能涉及更多的细节和步骤，具体取决于您的数据库和应用程序的特定情况。迁移完成后，确保进行充分的测试和验证，以确保数据的一致性和应用程序的正常运行。如果您的应用程序与数据库直接交互，并使用了特定于MySQL的查询语法或函数，那么您需要相应地修改这些代码，以适应PostgreSQL的语法和函数。请注意，MySQL和PostgreSQL之间有一些语法和数据类型的差异，您可能需要根据具体情况进行一些调整和转换。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值