网络知识 娱乐 云数据库MySQL导入云数据仓库PostgreSQL最佳实践

云数据库MySQL导入云数据仓库PostgreSQL最佳实践

发布: 2023年2月14日 10:17:50

说明

本文描述问题及解决方法基于 腾讯云 云数据仓库 PostgreSQL（CDWPG）。

另外使用到：

腾讯云 云数据库 MySQL（TencentDB for MySQL，CDB）

腾讯云 数据传输服务（Data Transmission Service，DTS）

背景

帮助用户在业务不停服的前提下轻松完成数据库迁移上云，利用实时同步通道轻松构建高可用的数据库容灾架构，通过数据订阅来满足商业数据挖掘、业务异步解耦等场景需求。

构建Mysql基础数据

创建测试库及基础表

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 实例中创建迁移帐号，需要的帐号权限如下：

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 实例中创建迁移帐号，需要的帐号权限如下：

[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"。

[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。

同步任务第二步是导入数据到目标表。

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

相关文章