RDS PostgreSQL成为RDS MySQL的实时分析库_云数据库 RDS(RDS)-阿里云帮助中心

本文介绍如何将RDS MySQL数据实时迁移到RDS PostgreSQL，使RDS PostgreSQL成为RDS MySQL的实时分析库。

背景信息

当需要使用RDS PostgreSQL数据库特有的功能来分析MySQL中的数据，使用GIS处理时空数据或进行用户画像分析时，可以使用DTS功能将RDS MySQL中的数据实时迁移到RDS PostgreSQL数据库中，使PostgreSQL数据库作为MySQL的实时分析库。

前提条件

已创建源RDS MySQL实例，创建方法，请参见创建RDS MySQL实例。
已创建目标RDS PostgreSQL实例，创建方法，请参见创建RDS PostgreSQL实例。
目标RDS PostgreSQL实例存储空间须大于源RDS MySQL实例占用的存储空间。

准备源数据库测试数据

连接源数据库RDS MySQL。

mysql -h <连接地址> -u <用户名> -P <端口> -p

创建测试数据库db1。
```
CREATE DATABASE db1;
```
进入db1数据库。
```
USE db1;
```

创建测试数据表test_mm和test_innodb。

CREATE TABLE `test_mm` (
`id` INT (11) NOT NULL AUTO_INCREMENT,
`user_id` VARCHAR (20) NOT NULL,
`group_id` INT (11) NOT NULL,
`create_time` datetime NOT NULL,
PRIMARY KEY (`id`),KEY `index_user_id` (`user_id`) USING HASH
) ENGINE = innodb AUTO_INCREMENT = 1 
DEFAULT CHARSET = utf8;

CREATE TABLE `test_innodb` (
`id` INT (11) NOT NULL AUTO_INCREMENT,
`user_id` VARCHAR (20) NOT NULL,
`group_id` INT (11) NOT NULL,
`create_time` datetime NOT NULL,
PRIMARY KEY (`id`),
KEY `index_user_id` (`user_id`) USING HASH
) ENGINE = innodb AUTO_INCREMENT = 1 
DEFAULT CHARSET = utf8;

创建随机字符串函数。

delimiter $$
CREATE FUNCTION rand_string(n int) RETURNS varchar(255)
begin
declare chars_str varchar(100)
default "abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789";
declare return_str varchar(255) default "";
declare i int default 0;
while i < n do
set return_str=concat(return_str,substring(chars_str,floor(1+rand()*62),1));
set i= i+1;
end while;
return return_str;
end $$
delimiter ;

创建插入测试数据的存储过程。

delimiter $$
CREATE PROCEDURE `insert_data`(IN n int)
BEGIN
DECLARE i INT DEFAULT 1;
WHILE (i <= n ) DO
INSERT into test_mm (user_id,group_id,create_time ) VALUEs
(rand_string(20),FLOOR(RAND() * 100) ,now() );
set i=i+1;
END WHILE;
END $$
delimiter ;

调用存储过程。

CALL insert_data(1000000);
INSERT INTO test_innodb SELECT * FROM test_mm;

创建迁移任务

创建迁移任务前，需要在RDS PostgreSQL控制台创建数据库，用于从RDS MySQL接收数据。创建方法，请参见创建数据库。

说明本示例中创建的数据库名为db2。
登录新版DTS同步任务的列表页面。

说明您也可以登录DMS数据管理服务。在顶部菜单栏中，选择传输与加工（DTS） > 数据同步。
在左侧导航栏，单击数据迁移。
在页面顶部的迁移任务列表中选择目标实例所属地域。
在页面右上角，单击创建迁移任务。

配置源库及目标库信息。


类别	配置	说明
无	任务名称	DTS会自动生成一个任务名称，建议配置具有业务意义的名称（无唯一性要求），便于后续识别。
源库信息	数据库类型	选择RDS实例。
	实例地区	选择源RDS MySQL实例所属地域。
	实例ID	择源RDS MySQL实例ID。
	数据库账号	填入源RDS MySQL实例的数据库账号。账号需要具备以下权限：库表结构迁移：SELECT权限。全量迁移：SELECT权限。增量迁移：REPLICATION CLIENT、REPLICATION SLAVE、SHOW VIEW和SELECT权限。
	数据库密码	填入该数据库账号对应的密码。
	连接方式	根据需求选择非加密连接或SSL安全连接。如果设置为SSL安全连接，您需要提前开启RDS MySQL实例的SSL加密功能，详情请参见设置SSL加密。
目标库信息	数据库类型	选择RDS实例。
	实例地区	选择目标RDS PostgreSQL实例所属地域。
	RDS实例ID	选择目标RDS PostgreSQL实例ID。
	数据库账号	填入目标RDS PostgreSQL实例的数据库账号，需具备以下权限： LOGIN权限。目标库的CONNECT、CREATE权限。目标Schema的CREATE权限。
	数据库密码	填入该数据库账号对应的密码。

配置完成后，需要分别单击源库和目标库数据库密码参数后的测试连接，测试成功后，再进行下一步。
单击页面右下角授权白名单并进入下一步。

配置迁移类型及列表。


配置	说明
迁移类型	选择结构迁移、全量数据迁移和增量数据迁移。
迁移对象	选择待迁移的表。本示例为test_innodb和test_mm。
已选择对象	此处展示已选择待迁移的表。
映射名称更改	默认为不进行库表名称批量修改，如果需要对库表名称进行批量修改，可在选择要进行库表名称批量更改后，单击页面右下角高级设置，批量修改数据库表名称。
源库、目标库无法连接后的重试时间	默认为720分钟，无需修改。

上述配置完成后，单击页面下方的预检查并启动。
说明
- 在迁移作业正式启动之前，会先进行预检查。只有预检查通过后，才能成功启动迁移作业。
- 如果预检查失败，单击具体检查项后的，查看失败详情。
  
  您可以根据提示修复后重新进行预检查。
  
  如无需修复告警检测项，您也可以选择确认屏蔽、忽略告警项并重新进行预检查，跳过告警检测项重新进行预检查。
当预检查页面中显示为预检查通过100%时，单击下一步。
在购买配置确认页面，选择数据迁移实例的链路规格。

说明 DTS为您提供了不同性能的迁移规格，迁移链路规格的不同会影响迁移速率，您可以根据业务场景进行选择，详情请参见数据迁移链路规格说明。
勾选《数据传输（按量付费）服务条款》。
单击购买并启动，迁移任务正式开始，您可在任务列表查看具体任务进度。

MySQL数据实时迁移至PostgreSQL

查看当前全量迁移状态。

连接RDS PostgreSQL数据库。
```
psql -h <数据库连接地址> -U <用户名> -p <端口号> -d db2
```
说明由于在创建迁移任务步骤中创建了数据库db2来接收MySQL迁移的数据，因此，连接PostgreSQL数据库时，数据库名直接配置成db2。
执行\dn命令，查看是否将MySQL数据库db1映射为PostgreSQL中的Schema。
```
   List of schemas
  Name  |   Owner
--------+------------
 db1    | test1
 public | pg*******
(2 rows)
```
说明 MySQL中的数据库名称，在迁移到PostgreSQL数据库后，将会映射到PostgreSQL中的Schema。

执行\dt+ db1.*命令，查看db1中的表状态。

                            List of relations
 Schema |    Name     | Type  | Owner | Persistence | Size  | Description
--------+-------------+-------+-------+-------------+-------+-------------
 db1    | test_innodb | table | test1 | permanent   | 65 MB |
 db1    | test_mm     | table | test1 | permanent   | 65 MB |
(2 rows)

执行如下命令，分别查询test_innodb和test_mm数据表记录数。
- 查询test_innodb记录数。
```
SELECT COUNT(*) FROM db1.test_innodb;
```
```
  count
---------
 1000000
(1 row)
```
- 查询test_mm记录数。
```
SELECT COUNT(*) FROM db1.test_mm;
```
```
  count
---------
 1000000
(1 row) 
```
说明
由于MySQL数据库在PostgreSQL中被映射为Schema，在PostgreSQL中查询db1中数据时，需要指定Schema。
如果您不想每次查询db1数据时都指定Schema，可以设置search_path参数。

查看search_path。
show search_path;
search_path ----------------- "$user", public (1 row)

设置search_path。
set search_path = db1, "$user", public;
SET

查看修改结果。
show search_path;
search_path ---------------------- db1, "$user", public (1 row)

新增数据实时迁移测试。

RDS MySQL中新增数据。

INSERT INTO test_innodb (user_id, group_id, `create_time`) VALUES ('testuser', 1, '2021-07-29 12:00:00');

执行结果

SELECT * FROM test_Innodb WHERE user_id = 'testuser';

+---------+----------+----------+---------------------+
| id      | user_id  | group_id | create_time         |
+---------+----------+----------+---------------------+
| 1000001 | testuser |        1 | 2021-07-29 12:00:00 |
+---------+----------+----------+---------------------+
1 row in set (0.03 sec)

RDS PostgreSQL中查看新增的记录是否迁移。

SELECT * FROM test_Innodb WHERE user_id = 'testuser';

   id    | user_id  | group_id |     create_time
---------+----------+----------+---------------------
 1000001 | testuser |        1 | 2021-07-29 12:00:00
(1 row)

更新数据实时迁移测试。

RDS MySQL中更新数据。

UPDATE test_innodb set group_id = 2 WHERE user_id = 'testuser';

执行结果：

SELECT * FROM test_innodb WHERE user_id = 'testuser';

+---------+----------+----------+---------------------+
| id      | user_id  | group_id | create_time         |
+---------+----------+----------+---------------------+
| 1000001 | testuser |        2 | 2021-07-29 12:00:00 |
+---------+----------+----------+---------------------+
1 row in set (0.03 sec)

RDS PostgreSQL中查看更新的记录是否迁移。

SELECT * FROM test_innodb WHERE user_id = 'testuser';

   id    | user_id  | group_id |     create_time
---------+----------+----------+---------------------
 1000001 | testuser |        2 | 2021-07-29 12:00:00
(1 row)

删除数据实时迁移测试。

RDS MySQL中删除数据。

DELETE FROM test_innodb WHERE user_id = 'testuser';

执行结果：

查询user_id为testuser的记录。

SELECT * FROM test_innodb WHERE user_id = 'testuser';

Empty set (0.03 sec)

查询MAX(id)。

SELECT MAX(id) FROM test_innodb;

+---------+
| MAX(id) |
+---------+
| 1000000 |
+---------+
1 row in set (0.03 sec)

说明未新增记录时，id最大为1000000，新增数据后，id自增到1000001，删除后，id又减少至1000000。

RDS PostgreSQL中查看删除记录后数据是否迁移。

SELECT * FROM test_innodb WHERE user_id = 'testuser';

 id | user_id | group_id | create_time
----+---------+----------+-------------
(0 rows)

SELECT MAX(id) FROM test_innodb;

   max
---------
 1000000
(1 row)