Query MySQL Data from PolarDB PostgreSQL via mysql_fdw - PolarDB

mysql_fdw lets PolarDB for PostgreSQL clusters read from and write to MySQL databases without data movement — including PolarDB for MySQL instances, ApsaraDB RDS for MySQL instances, and self-managed MySQL instances.

Supported versions

mysql_fdw is supported on PolarDB for PostgreSQL clusters running the following engine versions:

Engine	Minimum revision version
PostgreSQL 16	2.0.16.6.2.0 or later
PostgreSQL 15	2.0.15.12.4.0 or later
PostgreSQL 14	2.0.14.11.19.0 or later
PostgreSQL 11	2.0.11.9.34.0 or later

Run SHOW polardb_version; to check your revision version, or view it in the console. To upgrade, see Version management.

Usage notes

Network connectivity: Add the CIDR block of the virtual private cloud (VPC) to which the PolarDB for PostgreSQL cluster belongs to the allowlist of the MySQL instance — for example, 172.xx.xx.xx/16.

Channel-based connection: PolarDB for PostgreSQL does not support the host and port keywords in the OPTIONS clause of CREATE SERVER. Use channel_name instead. Each cluster has a default channel named localhost that connects to the local server. To connect to an external MySQL instance, you need a named channel configured for that instance. To create or configure a channel, contact us.

Write operations: INSERT, UPDATE, and DELETE require the MySQL table to have a primary key. Without one, the operation fails with:

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

Set up mysql_fdw

Step 1: Create the extension

Run the following statement using a privileged account:

CREATE EXTENSION IF NOT EXISTS mysql_fdw;

Step 2: Define a foreign server

Create a foreign server that points to the MySQL instance via a channel:

CREATE SERVER <server_name>
    FOREIGN DATA WRAPPER mysql_fdw
    OPTIONS (channel_name '<channel_name>');

Replace <server_name> with a name for the server and <channel_name> with the name of the channel configured for the MySQL instance.

Example — using a polar_channel named mysqlchannel:

CREATE SERVER mysql_server
    FOREIGN DATA WRAPPER mysql_fdw
    OPTIONS (channel_name 'mysqlchannel');

Step 3: Create a user mapping

Map a PolarDB for PostgreSQL user to a MySQL account. The MySQL account determines what data the PolarDB user can read or write.

CREATE USER MAPPING FOR <polardb_username>
    SERVER <server_name>
    OPTIONS (username '<mysql_username>', password '<mysql_password>');

Example:

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

Step 4: Create a foreign table

Create a foreign table that maps to a table in the MySQL database. Use the PolarDB user specified in the user mapping.

CREATE FOREIGN TABLE <table_name> (<field_name> <data_type>, ...)
    SERVER <server_name>
    OPTIONS (dbname '<mysql_database>', table_name '<mysql_table>');

Field names must match the corresponding field names in the MySQL table. You can include only a subset of fields — for example, if the MySQL table has id, name, and age, you can create a foreign table with only id and name.

Example — mapping to table test in database test123:

CREATE FOREIGN TABLE ft_test (id1 int, name1 text)
    SERVER mysql_server
    OPTIONS (dbname 'test123', table_name 'test');

Verify read and write operations

After creating the foreign table, run queries against it to confirm connectivity and permissions.

Read data:

SELECT * FROM ft_test;

Write data:

INSERT INTO ft_test VALUES (2, 'abc');
-- Result: INSERT 0 1

INSERT INTO ft_test SELECT generate_series(3, 100), 'abc';
-- Result: INSERT 0 98

SELECT count(*) FROM ft_test;
--  count
-- -------
--     99
-- (1 row)

Inspect the execution plan:

Use EXPLAIN VERBOSE to see how queries are pushed down to the MySQL instance.

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)

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)

The Remote query line shows the SQL sent to MySQL.