说明
本文描述问题及解决方法基于 腾讯云 云数据仓库 PostgreSQL（CDWPG）。
另外使用到：
腾讯云 云数据库 MySQL（TencentDB for MySQL，CDB）
腾讯云 数据传输服务（Data Transmission Service，DTS）
背景
帮助用户在业务不停服的前提下轻松完成数据库迁移上云，利用实时同步通道轻松构建高可用的数据库容灾架构，通过数据订阅来满足商业数据挖掘、业务异步解耦等场景需求。
构建Mysql基础数据
创建测试库及基础表
代码语言：javascript复制MySQL [(none)]> CREATE DATABASE dts_demo;
Query OK, 1 row affected (0.00 sec)
MySQL [(none)]> USE dts_demo;
Database changed
MySQL [dts_demo]> CREATE TABLE `user_info` (
-> `id` int(11) NOT NULL AUTO_INCREMENT,
-> `c_user_id` varchar(36) NOT NULL DEFAULT '',
-> `c_name` varchar(22) NOT NULL DEFAULT '',
-> `c_province_id` int(11) NOT NULL,
-> `c_city_id` int(11) NOT NULL,
-> `create_time` datetime NOT NULL,
-> PRIMARY KEY (`id`),
-> KEY `idx_user_id` (`c_user_id`)
-> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.01 sec)构建测试数据
这里我们快速构建一份测试数据，用来模拟实际生产中量级在100万的一张数据表，参考 快速构建Mysql百万级测试数据。
创建专用于数据同步的账号
创建Mysql同步账号
根据腾讯云DTS官方文档的要求，需要在源端 MySQL 实例中创建迁移帐号，需要的帐号权限如下：
代码语言：javascript复制MySQL [dts_demo]> GRANT SHOW VIEW,PROCESS,RELOAD,LOCK TABLES,REPLICATION CLIENT,REPLICATION SLAVE,SELECT ON *.* TO 'dts_user'@'%' IDENTIFIED BY 'dts_admin';
Query OK, 0 rows affected, 1 warning (0.00 sec)
MySQL [dts_demo]> GRANT ALL PRIVILEGES ON `__tencentdb__`.* TO 'dts_user'@'%';
Query OK, 0 rows affected (0.00 sec)
MySQL [dts_demo]> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)这里使用了超级用户创建了一个mysql普通用户"dts_user"，设置了密码为"dts_admin"，允许所有主机来源登录，并赋予了相应的权限。
其中"__tencentdb__"是DTS所需要用到的用户，这条授权不能省略，最后不要忘记刷新权限。
创建CDWPG同步账号及目标数据库
根据腾讯云DTS官方文档的要求，目标端 CDWPG 实例中创建迁移帐号，需要的帐号权限如下：
代码语言：javascript复制[root@VM-5-48-centos ~]# psql -d postgres -U cdwadmin -h 10.0.5.18 -p 5436
Password for user cdwadmin:
psql (9.5.25, server 9.4.24)
Type "help" for help.
postgres=> CREATE USER dts_user WITH PASSWORD 'dts_admin';
CREATE ROLE
postgres=> CREATE DATABASE dts_demo;
CREATE DATABASE
postgres=> c dts_demo
You are now connected to database "dts_demo" as user "cdwadmin".
dts_demo=> ALTER database dts_demo SET search_path TO dts_demo;
ALTER DATABASE
dts_demo=> GRANT ALL PRIVILEGES ON DATABASE dts_demo TO dts_user;
GRANT
dts_demo=> GRANT Delete, Truncate, Insert, References, Select, Update, TRIGGER ON all tables in schema public TO dts_user;
GRANT这里使用了超级用户创建了一个mysql普通用户"dts_user"，设置了密码为"dts_admin"，并赋予了相应的权限。另外由于Mysql和CDWPG的关系是Mysql的DB对应CDWPG的SCHEMA，所以这里是先创建了目标数据库"dts_demo"并将数据库默认SCHEMA设置为"dts_demo"。
创建CDWPG目标表
字段类型对照表
创建CDWPG表需要根据Postgresql的字段类型来匹配源表字段类型，否则会出现字段类型不对应的问题。
Mysql字段类型 | Postgresql字段类型 | 是否支持 |
|---|---|---|
INTMDEIUMINT | INTEGER | 支持 |
SMALLINT/TINYINT | SMALLINT | 支持 |
BIGINT | BIGINT | 支持 |
TINYINT UNSIGNED | SMALLINT | 支持 |
SMALLINT UNSIGNED | INTEGER | 支持 |
MEDIUMINT UNSIGNED | INTEGER | 支持 |
INT UNSIGNED | BIGINT | 支持 |
BIGINT UNSIGNED | NUMERIC | 支持 |
BOOLEAN | BOOLEAN | 不支持 |
FLOAT(UNSIGNED) | REAL | 支持 |
DOUBLE(UNSIGNED) | DOUBLE PRECISION | 支持 |
DECIMAL | NUMERIC | 支持 |
CHAR | CHARACTER | 支持 |
VARCHAR | TEXT VARCHAR | 支持 |
DATE | DATE | 支持 |
TIME | time without time zone | 支持 |
DATETIME | timestamp without time zone | 支持 |
TIMESTAMP | timestamp without time zone | 支持 |
LONGTEXTMEDIUMTEXT/TINYTEXT/TEXT | TEXT | 支持 |
LONGTEXTMEDIUMTEXT/TINYTEXT/TEXT | JSON(不推荐) | 仅限字符串是json类型 |
BLOB/MEDIUMBLOB/TINYBLOB/BINARYNARBINARYALONGBLOB | BYTEA | 支持 |
ENUM | CHAR或者VARCHAR | 支持 |
YEAR | SMALLINT | 支持 |
创建目标表
使用普通用户"dts_user"创建SCHEMA，并创建目标表"user_info"。
代码语言：javascript复制[root@VM-5-48-centos ~]# psql -d dts_demo -U dts_user -h 10.0.5.18 -p 5436
Password for user dts_user:
psql (9.5.25, server 9.4.24)
Type "help" for help.
dts_demo=> CREATE SCHEMA dts_demo;
CREATE SCHEMA
dts_demo=> CREATE TABLE user_info (
dts_demo(> id integer NOT NULL,
dts_demo(> c_user_id varchar NOT NULL,
dts_demo(> c_name varchar NOT NULL,
dts_demo(> c_province_id integer NOT NULL,
dts_demo(> c_city_id integer NOT NULL,
dts_demo(> create_time timestamp without time zone NOT NULL
dts_demo(> );
CREATE TABLE
dts_demo=> d
List of relations
Schema | Name | Type | Owner
---------- ----------- ------- ----------
dts_demo | user_info | table | dts_user
(1 row)创建DTS数据同步任务
特别注意
- 云Mysql、DTS数据同步产品、CDWPG云数据仓库，这三个实例都需要购买在同于VPC下，否则网络不通，无法做数据同步。
- 配置云数据库 MySQL 到 CDWPG 数据同步任务，在任务启动前，需要进行前置检查，主要检查内容和检查点如下：
检查内容 | 检查点 |
|---|---|
校验目标数据库 schema 和 table是否存在 | schema 和 table 必须提前创建好，如果没有创建好，则会报错 |
校验当前用户是否拥有目标数据表权限 | 针对要同步的表，首先判断当前用户是否是该表的 owner（owner 拥有所有权限），如果不是，则查看 information_schema.table_privilege 表中的授权信息，必须保证拥有：Delete、Truncate、Insert、References、Select、Update、TRIGGER 的授权权限，否则会报错 |
校验目标端磁盘空间是否充足 | 目标库的可用空间和源端需要的空间进行对比 |
校验源端数据库权限 | 对源实例检查是否有权限：Reload、LockTable、ReplClient、ReplSlave、Select、REPLICATION CLIENT |
校验源端 MySQL connect_timeout 参数 | 校验 MySQL 侧的 connect_timeout 参数是否小于10，如果小于则会报错 |
校验源端和目标端数据库连接 | 校验 MySQL 和 CDWPG 是否能正确连接 |
校验源端数据库版本 | MySQL 版本须是 MySQL 5.6或 MySQL 5.7 |
校验源端优化参数 | innodb_stats_on_metadata 指标需要关闭 |
校验源端 binlog 参数 | binlog_format 须为 ROW；binlog_row_image 须为 FULL；log_bin 须为 ON；gtid_mode 须为ON |
校验主键约束 | 源端需要同步的表必须有主键 |
校验源数据库编码 | 源端必须是 utf8 或 utf8mb4 |
校验 MySQL 表名大小写配置是否配置正确 | 校验 lower_case_table_names 参数是否为0，如果为0则配置不正确 |
校验 MySQL 数据库表名和列名是否含有" | CDWPG 不支持"作为列名 |
创建数据同步任务
配置同步任务
测试Mysql连通性
测试CDWPG连通性
可以看到，CDWPG连通性测试不通过。这是由于DTS数据同步工具也是一个访问端，他在对CDWPG访问时也需要在CDWPG端授权相应的白名单访问权限。这里需要移步CDWPG集群实例页面，点击"配置"标签，新建一个访问白名单，其中所需要授权的IP白名单即为上图所示的IP段：
白名单配置完成
再次尝试测试连通性
至此，连通性测试全部完成。
这里简单选择一下需要同步的库表即可。
在校验检测中，遇到一个Mysql参数问题，这个需要移步Mysql实例端，修改一下对应参数。
再次校验检测。
校验全部通过，可以进行数据同步了。
数据同步
同步任务第一步是导出源数据到DTS。
同步任务第二步是导入数据到目标表。
代码语言：javascript复制dts_demo=> SELECT COUNT(1) FROM user_info;
count
--------
206000
(1 row)
dts_demo=> SELECT COUNT(1) FROM user_info;
count
--------
601500
(1 row)
dts_demo=> SELECT COUNT(1) FROM user_info;
count
---------
1000000
(1 row)
dts_demo=> SELECT * FROM user_info ORDER BY id LIMIT 20;
id | c_user_id | c_name | c_province_id | c_city_id | create_time
---- -------------------------------------- ---------------------- --------------- ----------- ---------------------
1 | 1afd2630-88bc-11eb-9c30-0c42a125994e | oxlXASuDAQhIAEmDVAZ4 | 8 | 33 | 2022-03-19 22:05:05
2 | 1afd300e-88bc-11eb-9c30-0c42a125994e | Nj27hTrqAwIQUPiO0qXo | 727 | 95 | 2028-03-19 22:05:05
3 | 1afd4041-88bc-11eb-9c30-0c42a125994e | J9rzo41MCC2dM5Whp4Zy | 482 | 22 | 2026-03-19 22:05:05
4 | 1afd4562-88bc-11eb-9c30-0c42a125994e | RX3eSuFHkqXmNJ8hSoas | 517 | 67 | 2023-03-19 22:05:05
5 | 1afd4a49-88bc-11eb-9c30-0c42a125994e | YcVRm6gPdssI6cxUMZs9 | 54 | 31 | 2023-03-19 22:05:05
6 | 1afd4ebd-88bc-11eb-9c30-0c42a125994e | ydfrgRm1VlPX8FLFSeo5 | 968 | 3 | 2027-03-19 22:05:05
7 | 1afd530c-88bc-11eb-9c30-0c42a125994e | rsMpwgyPk0TiBXO2AFr3 | 585 | 25 | 2027-03-19 22:05:05
8 | 1afd574a-88bc-11eb-9c30-0c42a125994e | H5aqu0qT4xgB06i1341J | 293 | 73 | 2027-03-19 22:05:05
9 | 1afd5cf9-88bc-11eb-9c30-0c42a125994e | Y10PZgc4AzTDjxyY5ke0 | 31 | 60 | 2025-03-19 22:05:05
10 | 1afd61a8-88bc-11eb-9c30-0c42a125994e | 761DXGqU7GUjHpKns2E0 | 732 | 12 | 2022-03-19 22:05:05
11 | 1afd662c-88bc-11eb-9c30-0c42a125994e | AVIBJG21NLi00PX8HS7O | 384 | 97 | 2022-03-19 22:05:05
12 | 1afd6ace-88bc-11eb-9c30-0c42a125994e | RK0E38ooDO0r1CSn6dz6 | 474 | 53 | 2022-03-19 22:05:05
13 | 1afd6f01-88bc-11eb-9c30-0c42a125994e | pNCyKUaVYVyQqowgB3kl | 370 | 31 | 2028-03-19 22:05:05
14 | 1afd7332-88bc-11eb-9c30-0c42a125994e | CvwX2bCq4VhshQeuy9Yf | 960 | 55 | 2024-03-19 22:05:05
15 | 1afd775f-88bc-11eb-9c30-0c42a125994e | 3YzKT2oEXGmAIDRdo9on | 383 | 26 | 2024-03-19 22:05:05
16 | 1afd7bcf-88bc-11eb-9c30-0c42a125994e | j8zjGigivtHUhwDq2OK9 | 172 | 90 | 2025-03-19 22:05:05
17 | 1afd800c-88bc-11eb-9c30-0c42a125994e | 9pqJfSuEE8AlMKdHHeTD | 130 | 24 | 2025-03-19 22:05:05
18 | 1afd842c-88bc-11eb-9c30-0c42a125994e | 0DZUqdFwtEGifda3AA4p | 480 | 67 | 2028-03-19 22:05:05
19 | 1afd886b-88bc-11eb-9c30-0c42a125994e | 6SRyZ7v0mCP981zBaSIL | 374 | 5 | 2022-03-19 22:05:05
20 | 1afd8c9f-88bc-11eb-9c30-0c42a125994e | jKFUparzjJAyRrv4DMST | 530 | 43 | 2024-03-19 22:05:05
(20 rows)在同步的过程，可以看到数据量在不断增加，最后简单核对了一下数据量和记录。
再看一下数据同步任务，发现已经完成了，整体使用下来还是很方便的。
更多方案
使用py-mysql2pgsql同步Mysql数据至Greenplum
使用rds_dbsync同步Mysql数据至Greenplum
