Lectura y escritura de datos de MySQL (mysql_fdw) - ApsaraDB RDS - Centro de documentación de Alibaba Cloud

RDS PostgreSQL incluye el plugin mysql_fdw, que permite leer y escribir datos en instancias de RDS MySQL o en bases de datos MySQL autogestionadas.

Requisitos previos

La instancia debe ejecutar RDS PostgreSQL 10 o una versión posterior y usar discos en la nube.
Nota
- En RDS PostgreSQL 14, la versión secundaria del motor debe ser 20221030 o posterior.
- En RDS PostgreSQL 17, la versión secundaria del motor debe ser 20241030 o posterior.
Para consultar y actualizar la versión secundaria del motor, consulte Upgrade the minor engine version.
Agregue el bloque CIDR de la VPC de su instancia de RDS PostgreSQL (por ejemplo, 172.xx.xx.xx/16) a la lista blanca de la instancia de MySQL.

Nota
En la página Database Connection de su instancia de RDS PostgreSQL, ubique el bloque CIDR de la VPC (por ejemplo, 172.xx.xx.xx/16) junto a Network Type y anote el valor de Internal Port (por ejemplo, 5432).

Contexto

PostgreSQL admite el procesamiento paralelo desde la versión 9.6. La versión 11 mejoró significativamente este rendimiento y permitió completar consultas join con miles de millones de filas en segundos. Por esta razón, muchos usuarios utilizan PostgreSQL como un pequeño almacén de datos que también admite accesos con alta concurrencia.

Con el plugin mysql_fdw puede conectar PostgreSQL a MySQL y sincronizar los datos de MySQL para analizarlos.

Procedimiento

Cree el plugin mysql_fdw.
```
postgres=> create extension mysql_fdw;  
CREATE EXTENSION  
```
Nota
Solo una cuenta con privilegios puede ejecutar este comando.

Cree una definición de servidor de MySQL.

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

Nota

En la definición del servidor, establezca el parámetro host con el endpoint interno de la instancia de MySQL y el parámetro port con su puerto interno.

Ejemplo:

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

Cree una asignación de usuarios para vincular la definición del servidor con el usuario de PostgreSQL que accederá a la base de datos MySQL.

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

Ejemplo:

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

Use el usuario de PostgreSQL del paso anterior para crear una tabla externa correspondiente a la tabla de MySQL.
Nota
Los nombres de las columnas de la tabla externa deben coincidir con los nombres de las columnas correspondientes en la tabla de MySQL. Solo es necesario definir las columnas que desea consultar. Por ejemplo, si la tabla de MySQL contiene tres columnas (ID, NAME y AGE), puede crear una tabla externa únicamente con las columnas ID y NAME.
```
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  
```
Ejemplo:
```
postgres=> CREATE FOREIGN TABLE ft_test (id1 int, name1 text) server mysql_server options (dbname 'test123', table_name 'test');  
CREATE FOREIGN TABLE  
```

Prueba de operaciones de lectura y escritura

Mediante la tabla externa puede leer y escribir los datos de MySQL.

Nota

La tabla de MySQL debe tener una clave primaria para las operaciones de escritura. De lo contrario, la operación falla y devuelve el siguiente 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)

Revise el plan de ejecución para comprobar cómo se envían las consultas de la tabla externa a 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)