通过mysql_fdw读写MySQL数据 - 云数据库 RDS

RDS PostgreSQL提供mysql_fdw插件，可以读写RDS MySQL实例或自建MySQL数据库里的数据。

前提条件

实例为RDS PostgreSQL 10或以上版本的云盘实例。
说明
RDS PostgreSQL 14需要内核小版本至少为20221030，如何查看和升级内核小版本，请参见升级内核小版本。
将RDS PostgreSQL的专有网络网段（例如172.xx.xx.xx/16）添加到MySQL实例的白名单中，允许RDS PostgreSQL访问。
说明
您可以在RDS PostgreSQL实例的数据库连接中查看专有网络网段。

背景信息

PostgreSQL从9.6开始就支持并行计算，到11的时候并行计算性能得到巨大提升，10亿数据量的join查询可以实现秒级完成。所以很多用户会使用PostgreSQL作为小的数据仓库使用，同时又能提供高并发访问。

使用mysql_fdw插件能够将PostgreSQL和MySQL连接，同步MySQL数据进行数据分析。

操作步骤

新建mysql_fdw插件。
```
postgres=> create extension mysql_fdw;  
CREATE EXTENSION  
```
说明
仅高权限账号可以执行此命令。

创建MySQL服务器定义。

postgres=> CREATE SERVER <server名称>  
postgres->      FOREIGN DATA WRAPPER mysql_fdw
postgres->      OPTIONS (host '<连接地址>', port '<连接端口>');  
CREATE SERVER

说明

服务器定义中的host必须配置为MySQL的内网地址，port必须配置为MySQL的内网端口。

示例

postgres=> CREATE SERVER mysql_server  
postgres->      FOREIGN DATA WRAPPER mysql_fdw
postgres->      OPTIONS (host 'rm-xxx.mysql.rds.aliyuncs.com', port '3306');  
CREATE SERVER

创建用户映射，将MySQL服务器定义映射到PostgreSQL的某个用户上，将来使用这个用户访问MySQL的数据。

postgres=> CREATE USER MAPPING FOR <PostgreSQL用户名>   
SERVER <server名称>  
OPTIONS (username '<MySQL用户名>', password '<MySQL用户对应密码>');  
CREATE USER MAPPING

示例

postgres=> CREATE USER MAPPING FOR pgtest 
SERVER mysql_server  
OPTIONS (username 'mysqltest', password 'Test1234!');  
CREATE USER MAPPING

使用上一步骤的PostgreSQL用户创建MySQL的外部表。

说明

外部表的字段名要与MySQL数据库中表的字段名相同，同时可以仅创建您想要查询的字段。例如MySQL数据库中的表有3个字段ID、NAME、AGE，您可以仅创建其中2个字段ID、NAME。

postgres=> CREATE FOREIGN TABLE <表名> (<字段名> <数据类型>,<字段名> <数据类型>...) server <server名称> options (dbname '<MySQL数据库名>', table_name '<MySQL表名>');  
CREATE FOREIGN TABLE

示例

postgres=> CREATE FOREIGN TABLE ft_test (id1 int, name1 text) server mysql_server options (dbname 'test123', table_name 'test');  
CREATE FOREIGN TABLE

测试读写

您可以通过外部表读写MySQL数据。

说明

MySQL对应的表必须有主键才可以写入数据，否则会报如下错误：

ERROR:  first column of remote table must be unique for INSERT/UPDATE/DELETE operation.

postgres=> select * from ft_test ;  

postgres=> insert into ft_test values (2,'abc');  
INSERT 0 1  

postgres=> insert into ft_test select generate_series(3,100),'abc';  
INSERT 0 98  
postgres=> select count(*) from ft_test ;  
 count   
-------  
    99  
(1 row)

检查执行计划，即PostgreSQL查询MySQL数据的请求在MySQL中是如何执行的。

postgres=> explain verbose select count(*) from ft_test ;  
                                  QUERY PLAN                                     
-------------------------------------------------------------------------------  
 Aggregate  (cost=1027.50..1027.51 rows=1 width=8)  
   Output: count(*)  
   ->  Foreign Scan on public.ft_test  (cost=25.00..1025.00 rows=1000 width=0)  
         Output: id, info  
         Remote server startup cost: 25  
         Remote query: SELECT NULL FROM `test123`.`test`  
(6 rows)  

postgres=> explain verbose select id from ft_test where id=2;  
                               QUERY PLAN                                  
-------------------------------------------------------------------------  
 Foreign Scan on public.ft_test  (cost=25.00..1025.00 rows=1000 width=4)  
   Output: id  
   Remote server startup cost: 25  
   Remote query: SELECT `id` FROM `test123`.`test` WHERE ((`id` = 2))  
(4 rows)