Read and write MySQL data (mysql_fdw) - ApsaraDB RDS - Alibaba Cloud Documentation Center

RDS PostgreSQL includes the mysql_fdw plugin, which lets you read and write data in RDS MySQL instances or self-managed MySQL databases.

Prerequisites

Your instance must be RDS PostgreSQL 10 or later and use cloud disks.
Note
- For RDS PostgreSQL 14, the minor engine version must be 20221030 or later.
- For RDS PostgreSQL 17, the minor engine version must be 20241030 or later.
To view and upgrade the minor engine version, see Upgrade the minor engine version.
Add the VPC CIDR block of your RDS PostgreSQL instance (for example, 172.xx.xx.xx/16) to the MySQL instance's whitelist.

Note
On the Database Connection page of your RDS PostgreSQL instance, locate the VPC CIDR block (for example, 172.xx.xx.xx/16) next to Network Type, and note the Internal Port (for example, 5432).

Background information

PostgreSQL has supported parallel computing since version 9.6, and version 11 significantly enhanced its performance, enabling join queries on a billion rows of data to complete in seconds. As a result, many users use PostgreSQL as a small data warehouse that also supports high-concurrency access.

You can use the mysql_fdw plugin to connect PostgreSQL to MySQL and synchronize data from MySQL for analysis.

Procedure

Create the mysql_fdw plugin.
```
postgres=> create extension mysql_fdw;  
CREATE EXTENSION  
```
Note
Only a privileged account can run this command.

Create a MySQL server definition.

postgres=> CREATE SERVER <server_name>  
postgres->      FOREIGN DATA WRAPPER mysql_fdw
postgres->      OPTIONS (host '<endpoint>', port '<port>');  
CREATE SERVER

Note

In the server definition, set the host parameter to the MySQL instance's internal endpoint and the port parameter to its internal port.

Example

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

Create a user mapping to link the server definition to the PostgreSQL user that will access the MySQL database.

postgres=> CREATE USER MAPPING FOR <postgresql_username>   
SERVER <server_name>  
OPTIONS (username '<mysql_username>', password '<password_of_mysql_user>');  
CREATE USER MAPPING

Example

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

Use the PostgreSQL user from the previous step to create a foreign table for the MySQL table.
Note
The column names in the foreign table must match the corresponding column names in the MySQL table. You need to define only the columns that you want to query. For example, if the MySQL table contains three columns (ID, NAME, and AGE), you can create a foreign table with only the ID and NAME columns.
```
postgres=> CREATE FOREIGN TABLE <table_name> (<column_name> <data_type>,<column_name> <data_type>...) server <server_name> options (dbname '<mysql_database_name>', table_name '<mysql_table_name>');  
CREATE FOREIGN TABLE  
```
Example
```
postgres=> CREATE FOREIGN TABLE ft_test (id1 int, name1 text) server mysql_server options (dbname 'test123', table_name 'test');  
CREATE FOREIGN TABLE  
```

Test read and write operations

You can read and write MySQL data using the foreign table.

Note

The MySQL table must have a primary key for write operations. Otherwise, the operation fails with the following error:

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)

Check the execution plan to see how queries on the foreign table are passed to MySQL for execution.

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)