工具 mysql2pgsql 支持不落地的把 MYSQL 中的表迁移到 HybridDB for PostgreSQL/Greenplum Database/PostgreSQL/PPAS。此工具的原理是,同时连接源端 mysql 数据库和目的端数据库,从 mysql 库中通过查询得到要导出的数据,然后通过 COPY 命令导入到目的端。此工具支持多线程导入(每个工作线程负责导入一部分数据库表)。
修改配置文件 my.cfg、配置源和目的库连接信息。
源库 mysql 的连接信息如下:
注意:源库 mysql 的连接信息中,用户需要有对所有用户表的读权限。
[src.mysql]
host = "192.168.1.1"
port = "3306"
user = "test"
password = "test"
db = "test"
encodingdir = "share"
encoding = "utf8"
目的库 pgsql (包括 Postgresql、PPAS 和 HybridDB for PostgreSQL )的连接信息如下:
注意:目的库 pgsql 的连接信息,用户需要对目标表有写的权限。
[desc.pgsql]
connect_string = "host=192.168.1.1 dbname=test port=5888 user=test password=pgsql"
mysql2pgsql 的用法如下所示:
./mysql2pgsql -l <tables_list_file> -d -n -j <number of threads> -s <schema of target able>
参数说明:
-l:可选参数,指定一个文本文件,文件中含有需要同步的表;如果不指定此参数,则同步配置文件中指定数据库下的所有表。<tables_list_file>
为一个文件名,里面含有需要同步的表集合以及表上查询的条件,其内容格式示例如下:
table1 : select * from table_big where column1 < '2016-08-05'
table2 :
table3
table4: select column1, column2 from tableX where column1 != 10
table5: select * from table_big where column1 >= '2016-08-05'
-d:可选参数,表示只生成目的表的建表 DDL 语句,不实际进行数据同步。
-n:可选参数,需要与-d 一起使用,指定在 DDL 语句中不包含表分区定义。
-j:可选参数,指定使用多少线程进行数据同步;如果不指定此参数,会使用 5 个线程并发。
-s:可选参数,指定目标表的 schema,一次命令只能指定一个 schema。如果不指定此参数,则数据会导入到 public 下的表。
全库迁移的操作步骤如下所示:
通过如下命令,获取目的端对应表的 DDL。
./mysql2pgsql -d
根据这些 DDL,再加入 distribution key 等信息,在目的端创建表。
执行如下命令,同步所有表:
./mysql2pgsql
此命令会把配置文件中所指定数据库中的所有 mysql 表数据迁移到目的端。过程中使用 5 个线程(即缺省线程数为 5),读取和导入所有涉及的表数据。
编辑一个新文件 tab_list.txt,放入如下内容:
t1
t2 : select * from t2 where c1 > 138888
执行如下命令,同步指定的 t1 和 t2 表(注意 t2 表只迁移符合 c1 > 138888 条件的数据):
./mysql2pgsql -l tab_list.txt
工具 pgsql2pgsql 支持不落地的把 HybridDB for PostgreSQL/Greenplum Database/PostgreSQL/PPAS 中的表迁移到 HybridDB for PostgreSQL/Greenplum Database/PostgreSQL/PPAS。
pgsql2pgsql 支持如下功能:
PostgreSQL/PPAS/Greenplum Database/HybridDB for PostgreSQL 全量数据迁移到 PostgreSQL/PPAS/Greenplum Database/HybridDB for PostgreSQL。
PostgreSQL/PPAS(版本大于9.4)全量 + 增量迁移到 PostgreSQL/PPAS。
修改配置文件 my.cfg、配置源和目的库连接信息。
源库 pgsql 连接信息如下所示:
注意:源库 pgsql 的连接信息中,用户最好是对应 DB 的 owner。
[src.pgsql]
connect_string = "host=192.168.1.1 dbname=test port=5888 user=test password=pgsql"
本地临时 Database pgsql 连接信息如下所示:
[local.pgsql]
connect_string = "host=192.168.1.1 dbname=test port=5888 user=test2 password=pgsql"
目的库 pgsql 连接信息如下所示:
注意:目的库 pgsql 的连接信息,用户需要对目标表有写权限。
[desc.pgsql]
connect_string = "host=192.168.1.1 dbname=test port=5888 user=test3 password=pgsql"
注意:
如果要做增量数据同步,连接源库需要有创建 replication slot 的权限。
由于 PostgreSQL 9.4 及以上版本支持逻辑流复制,所以支持作为数据源的增量迁移。打开下列内核参数才能让内核支持逻辑流复制功能。
wal_level = logical
max_wal_senders = 6
max_replication_slots = 6
进行全库迁移,请执行如下命令:
./pgsql2pgsql
迁移程序会默认把对应 pgsql 库中所有用户的表数据将迁移到 pgsql。
连接本地临时 Database,可以查看到单次迁移过程中的状态信息。这些信息被放在表 db_sync_status 中,包括全量迁移的开始和结束时间、增量迁移的开始时间和增量同步的数据情况。
数据集成(Data Integration) 是阿里云大数据服务提供的数据同步平台。该平台为 20 多种数据源提供不同网络环境下的离线 (全量/增量) 数据进出通道,可跨异构数据存储系统、可弹性扩展、可靠、安全、成本低。查看 支持数据源类型 了解可用的数据源。
本文介绍了使用数据集成向 HybridDB for PostgreSQL 进行 数据导入 和 数据导出 的实现方法,分别提供在 向导模式(即可视化界面引导)下的操作步骤 和 脚本模式(即模板参数配置) 下的代码示例。
使用该文档,您可以了解数据集成在 HybridDB for PostgreSQL 中的 使用场景,熟悉以下操作:
使用数据集成中的同步任务,HybridDB for PostgreSQL 可以:
将数据同步到到其他的数据源里,并对数据进行相应处理。
将处理好的其他数据源数据同步到 HybridDB for PostgreSQL 中。
分别在数据集成和 HybridDB for PostgreSQL 端完成以下准备工作。
依次完成以下操作:
开通阿里云官网实名认证账号,并且创建好账号的访问秘钥,即 AccessKeys。
开通 MaxCompute,系统会自动产生一个默认的 ODPS 的数据源,并使用主账号登录大数据开发套件。
创建项目,用户可以在项目中协作完成工作流,共同维护数据和任务等,因此使用大数据开发套件之前需要先创建一个项目。
如您想通过子账号创建数据集成任务,可以赋予其相应的权限。
完成以下准备工作:
在进行数据导入之前,先通过 PostgreSQL 客户端创建好 HybridDB for PostgreSQL 中待迁入数据的目标数据库和表。
若待迁出数据的源数据库为 HybridDB for PostgreSQL,应在 HybridDB for PostgreSQL 管理控制台进行 IP 白名单设置。
如下图所示,登录 HybridDB for PostgreSQL 控制台,选择相应实例,在 数据安全性 页面的 白名单设置 子页下,单击 添加白名单分组。添加以下 IP 地址:10.152.69.0/24,10.153.136.0/24,10.143.32.0/24,120.27.160.26,10.46.67.156,120.27.160.81,10.46.64.81,121.43.110.160,10.117.39.238,121.43.112.137,10.117.28.203,118.178.84.74,10.27.63.41,118.178.56.228,10.27.63.60,118.178.59.233,10.27.63.38,118.178.142.154,10.27.63.15,100.64.0.0/8
。
注意:若使用自定义资源组调度 HybridDB for PostgreSQL 数据同步任务,必须把自定义资源组的机器 ip 也加到 HybridDB for PostgreSQL 的白名单中。
使用数据集成向 HybridDB for PostgreSQL 同步数据前,项目管理员应在数据集成中新增 HybridDB for PostgreSQL 数据源,具体步骤如下:
以开发者身份登录阿里云数加平台,依次选择 大数据开发套件 > 管理控制台,单击对应项目操作栏中的 进入工作区。
单击顶部菜单栏中数据集成模块的数据源。
单击 新增数据源。
在新建数据源弹出框中,选择 数据源类型 为 PostgreSQL。
选择以 JDBC 形式配置该 PostgreSQL 数据源。
其中,
完成配置后,单击 测试连通性。
测试连通性通过后,单击 确定。
至此,您已完成 HybridDB for PostgreSQL 数据源的添加。
下文提供两种配置同步任务的方法,供您选择:
若使用可视化界面引导操作,请参照 向导模式配置同步任务。包含以下步骤:选择来源,选择目标,字段映射,通道控制,预览保存。不同的数据源之间,每一步的界面可能有不同的内容。向导模式可以转换成脚本模式。
若使用模板参数配置操作,请参照 脚本模式配置同步任务。在脚本界面选择相应的模板,模板包含了同步任务的主要参数,配置参数信息以实现同步任务配置。脚本模式不能转化成向导模式。
已参照 新增数据源 在数据集成中新增 HybridDB for PostgreSQL 数据源。
操作步骤如下:
选择以 向导模式 新建同步任务,如下图所示:
选择数据来源,如下图所示:
其中,
完成后单击 下一步。
选择目标,如下图所示:
其中,
向导模式只允许执行一条 SQL 语句,脚本模式可以支持多条 SQL 语句,例如清除旧数据。
向导模式只允许执行一条 SQL 语句,脚本模式可以支持多条 SQL 语句,例如加上某一个时间戳。
完成后单击 下一步。
字段映射。对字段映射关系进行配置,左侧 源头表字段 和右侧 目标表字段 为一一对应的关系,如下图所示:
说明:
- 可以输入常量,输入的值需要使用英文单引号包括,如 ‘abc’、’123’等;
- 可以配合调度参数使用,如 ${bdp.system.bizdate} 等;
- 可以输入你要同步的分区列,如分区列有 pt 等;
- 如果您输入的值无法解析,则类型显示为 ‘未识别’;
- 不支持配置 odps 函数。
完成后单击 下一步。
通道控制。配置作业速率上限和脏数据检查规则,如下图所示:
其中,
作业速率上限:设置数据同步作业可能达到的最高速率,其最终实际速率受网络环境、数据库配置等的影响。
作业并发数:作业速率上限 = 作业并发数 * 单并发的传输速率
当作业速率上限已选定的情况下,应该如何选择作业并发数?
- 如果你的数据源是线上的业务库,建议您不要将并发数设置过大,以防对线上库造成影响。
- 如果您对数据同步速率特别在意,建议您选择最大作业速率上限和较大的作业并发数。
预览保存。完成以上配置后,上下滚动鼠标可查看任务配置,如若无误,单击 保存,如下图所示:
获取结果。同步任务保存后,
调度系统会按照配置属性在从第二天开始自动定时执行。相关调度的配置请参考 调度配置介绍。
运行结果如下图所示:
至此,您已完成在向导模式下创建数据同步任务向 HybridDB for PostgreSQL 导入数据。
代码样例如下:
{
"configuration": {
"reader": {
"plugin": "odps",
"parameter": {
"partition": "pt=${bdp.system.bizdate}",//分区信息
"datasource": "odps_first",//数据源名,建议数据源都先添加数据源后再配置同步任务,此配置项填写的内容必须要与添加的数据源名称保持一致
"column": [
"id",
"name",
"year",
"birthdate",
"ismarried",
"interest",
"salary"
],
"table": "hpg"//源端表名
}
},
"writer": {
"plugin": "postgresql",
"parameter": {
"postSql": [],//导入后准备语句
"datasource": "l_PostGreSql",//数据源名,建议数据源都先添加数据源后再配置同步任务,此配置项填写的内容必须要与添加的数据源名称保持一致
"column": [
"id",
"name",
"year",
"birthdate",
"ismarried",
"interest",
"salary"
],
"table": "public.person",//目标表名
"preSql": []//导入前准备语句
}
},
"setting": {
"speed": {
"concurrent": 7,//并发数
"mbps": 7//数率最高上限
}
}
},
"type": "job",
"version": "1.0"
}