mysql同步pg_MySQL准实时同步到PostgreSQL, Greenplum的方案之一 - rds_dbsync

PostgreSQL , Greenplum , rds_dbsync , binlog

rds_dbsync是阿里云数据库内核组开源的一个数据实时同步工具。

可以解析MySQL的binlog，或者PostgreSQL的WAL日志，实现增量的实时同步。同时支持全量不落地迁移功能。

1、全量不落地迁移(从mysql到pgsql，从pgsql到pgsql，从mysql或pgsql到Greenplum)。

支持并行。

2、DDL转换

3、增量实时同步(从mysql到pgsql，从pgsql到pgsql，从mysql或pgsql到Greenplum)

注意，rds_dbsync增量实时同步功能不支持DDL，仅支持DML。因此如果有DDL操作，建议保持目标端处于源端的超集即可。

例如

1、用户需要将字段改长。

先加目标端，再加源端。

2、用户需要删字段。

先删源端，再删目标端。

3、用户需要加字段。

先加目标端，再加源端。

1、mysql2pgsql，全量迁移命令。

2、binlog_miner，解析mysql binlog，并写入临时PG库的命令。

3、binlog_loader，从临时PG库将BINLOG取出，并在目标库回放的命令(会合并多条为一条，提高效率)。

建议下载二进制，CentOS 6 x64 选择el6，CentOS 7 x64选择el7。

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

1、安装mysql mysql-5.7.3-m13

https://downloads.mysql.com/archives/get/file/MySQL-5.7.3_m13-1.linux_glibc2.5.src.rpm

https://downloads.mysql.com/archives/community/

2、安装PostgreSQL

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

3、修改makefile

4、make

5、打包依赖动态库，拷贝到对应目录

《Linux 批量依赖库拷贝(ldd)》

6、拷贝mysql share到对应目录

7、最终目录结构

工具 mysql2pgsql 支持不落地的把 MYSQL 中的表迁移到 HybridDB/Greenplum Database/PostgreSQL/PPAS。

此工具的原理是，同时连接源端 mysql 数据库和目的端数据库，从 mysql 库中通过查询得到要导出的数据，然后通过 COPY 命令导入到目的端。

此工具支持多线程导入(每个工作线程负责导入一部分数据库表)。

修改配置文件 my.cfg、配置源和目的库连接信息。

源库 mysql 的连接信息如下：

**注意：**源库 mysql 的连接信息中，用户需要有对所有用户表的读权限。

encodingdir，指mysql软件的share目录，例如，建议将/usr/local/mysql/share拷贝到存放mysql2pgsql二进制文件的上一层目录下，与bin平级。

encoding，指目标端pgsql数据库的字符集(即我们从mysql库读取的数据，将转换为这个编码，目的是方便写入目标端的PGSQL)。

目的库 pgsql (包括 PostgreSQL、PPAS 和 HybridDB for PostgreSQL )的连接信息如下：

**注意：**目的库 pgsql 的连接信息，用户需要对目标表有写的权限。

mysql2pgsql 的用法如下所示：

参数说明：

-l：可选参数，指定一个文本文件，文件中含有需要同步的表；如果不指定此参数，则同步配置文件中指定数据库下的所有表。为一个文件名，里面含有需要同步的表集合以及表上查询的条件，其内容格式示例如下：

不带任何东西的冒号，会忽略。带有条件的，将使用后面的SQL拖取对应的数据。

-d：可选参数，表示只生成目的表的建表 DDL 语句，不实际进行数据同步。

-n：可选参数，需要与-d一起使用，指定在 DDL 语句中不包含表分区定义。

-j：可选参数，指定使用多少线程进行数据同步；如果不指定此参数，会使用 5 个线程并发。

-s：可选参数，指定目标表的schema，一次命令只能指定一个schema。如果不指定此参数，则数据会导入到public下的表。

全库迁移

全库迁移的操作步骤如下所示：

1. 通过如下命令，获取目的端对应表的 DDL。

2. 根据这些 DDL，再加入 distribution key 等信息，在目的端创建表。

3. 执行如下命令，同步所有表：

此命令会把配置文件中所指定数据库中的所有 mysql 表数据迁移到目的端。过程中使用 5 个线程(即缺省线程数为 5)，读取和导入所有涉及的表数据。

部分表迁移

1. 编辑一个新文件 tab_list.txt，放入如下内容：

2. 执行如下命令，同步指定的 t1 和 t2 表(注意 t2 表只迁移符合 c1 > 138888 条件的数据)：

下载地址：单击这里。

查看源码编译说明，单击这里。

增量同步前，建议先进行一次全量同步，并记录下全量同步的开始时间点。这个时间点之后的binlog都需要抽取。

rds_dbsync实现了增量的幂等操作，因此我们仅需要一个全量同步开始的大致时间即可(比如10点开始全量同步，10点以前的时间都可以作为增量同步的binlog解析开始时间)。

mysql2gp 实现了从 MySQL 中迁移增量数据到 PostgreSQL 或 Greenplum

其中增量数据来自于 MySQL 的 binlog, 结合全量数据迁移工具 mysql2pgsql 可以把 MySQL 中的数据完整的迁移到 PostgreSQL Greenplum 中，且保持准实时同步。

1、 支持拉取 MySQL 5.1 5.5 5.6 5.7 版本的 binlog，需要设置源端mysql数据库的 binlog 相关参数(RDS MYSQL无需设置，已默认开启)

2、 支持同步指定表的各类数据变化到目标DB中，包括对应行的 insert update delete。

3、 数据同步的表需要有单列主键。

4、 支持对主键进行修改。

5、 暂时不支持异构数据库的 DDL 同步。

6、 支持指定表镜像方式同步到 PostgreSQL 或 Greenplum(配置文件方式)。

7、 支持指定模式的表同步。

mysql2gp 的实现方式：

1、 在客户端主机(也可以部署在其他主机)上启动一个临时 PG 数据库，用于临时存放从 MySQL 拉去到的 binlog 数据。

2、 binlog_miner 从源 MySQL 的一个 binlog 文件开始，拉取和解析 binlog 并存放到临时 PG 中。

3、 binlog_loader 从临时 PG 中读取增量数据，并做适当的处理(主要是合并单条记录的多次操作，减少目标端的压力，提高性能)，最终批量写入到目标 PostgreSQL 或 Greenplum 中去。

mysql2gp 分为5个部分

1、 binlog_miner 用于拉取MySQL 目标库中的 binlog, 并保存到临时 PGSQL DB 中。

2、 binlog_loader 用于读取临时 PGSQL DB 中的 binlog 数据并加载到目标库中。

3、 my.cfg 配置文件，设置需要同步数据的源和目标数据库的链接信息和相关参数。

4、 loader_table_list.txt 配置文件，设置需要同步的表名列表，用回车符隔开。

5、 临时 PGSQL DB，用户保存增量数据的临时数据库。这个DB建议和 binlog_miner binlog_loader 部署在同一个主机。

建议临时 PGSQL DB 和客户端二进制部署在同主机

部署步骤：

在目标主机部署一个临时 PGSQL DB 用户存放临时数据，主机需要为临时数据预留足够的保存增量数据的空间。部署完成后获得一个连接临时 PG DB 的连接串，如

2.2.1 MySQL 相关

my.cnf

注意：

1、 连接MySQL 的数据库用户需要有 select 权限和拉取 binlog 的权限。

2、 binlogfile 为读取 binlog 的启始文件，必须设置。该配置和全量数据同步工具配合使用。

通常在开始同步全量 MySQL 数据时记录当前正在写的 binlog 文件名，并配置到增量同步的 my.cnf 中。

下次启动，不需要解析binlogfile，解析到的binlog偏移信息会记录在临时 PGSQL DB中。

如果要删除增量任务，需要清空临时 PGSQL DB库。每个任务，需要一个临时 PGSQL DB库。

2.2.2 临时PGSQL DB数据库

my.cnf

注意：

1、 连接本地数据库可以不指定 host 信息(默认使用/tmp下的unix socket连，这个需要在临时PGSQL DB库的postgresql.conf中设置它的unix_socket_directories参数，并选择/tmp目录)，这样的链接模式效率较高。

2.2.3 目的数据库

my.cnf

注意:

1、 target_schema 用于指定目标表存在的 schema，也可以不指定，不指时默认 schema 为 public。

2.2.4 设置需要同步的表

1、 my.cnf

2、 loader_table_list.txt

2.3.1 启动 binlog 拉取进程

推荐命令行：

2.3.2 启动 binlog 写入进程

推荐命令行：

定时任务和监控

监控binlog_miner, binlog_loader的进程状态，如果进程异常，自动重启。

https://downloads.mysql.com/archives/community/

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

https://www.postgresql.org/ftp/source/