AnalyticDB:Access MySQL data using mysql_fdw

Step 1: Log in to your AnalyticDB for PostgreSQL instance

1. Go to the AnalyticDB for PostgreSQL console and click the instance ID.

2. In the upper-right corner, click Log On to Database.

3. In the Log On to Database Instance dialog box, enter your Database Account and Database Password, then click Login.

Step 2: Create a foreign server

Run CREATE SERVER to define the remote MySQL database as a foreign server:

-- Create a foreign server named mysql_svr
CREATE SERVER mysql_svr
  FOREIGN DATA WRAPPER mysql_fdw
  OPTIONS (host '127.0.0.1', port '3306');

Expected output:

CREATE SERVER

Replace 127.0.0.1 and 3306 with your MySQL host and port. For all available options, see CREATE SERVER.

Step 3: Create a user mapping

Run CREATE USER MAPPING to associate an AnalyticDB for PostgreSQL user with MySQL credentials:

-- Map the public role to MySQL credentials
CREATE USER MAPPING FOR public
  SERVER mysql_svr
  OPTIONS (username '$MYSQL_USER_NAME', password '$MYSQL_PASS');

Expected output:

CREATE USER MAPPING

Replace $MYSQL_USER_NAME and $MYSQL_PASS with actual MySQL credentials.

Step 4: Create a foreign table

Run CREATE FOREIGN TABLE to define a local table that maps to a MySQL table. Column names must match the MySQL table's column names exactly.

-- Create a foreign table mirroring the MySQL table test_foreign_table
CREATE FOREIGN TABLE test_foreign_table (
  c1 INT,
  c2 INT,
  c3 TEXT,
  c4 TEXT
)
SERVER mysql_svr
OPTIONS (dbname 'mysql_fdw_database', table_name 'test_foreign_table');

Expected output:

CREATE FOREIGN TABLE

Alternatively, use IMPORT FOREIGN SCHEMA to create foreign tables automatically for all tables in a MySQL schema:

IMPORT FOREIGN SCHEMA mysql_fdw_database
  FROM SERVER mysql_svr
  INTO public;

Use LIMIT TO (table1, table2) to import only specific tables, or EXCEPT (table1, table2) to import all tables except the listed ones. For all available options, see IMPORT FOREIGN SCHEMA.

Step 5: Query and modify data

After the foreign table is created, run SELECT, INSERT, UPDATE, and DELETE on it as you would on any local table:

-- Query data
SELECT * FROM test_foreign_table;

-- Insert a row
INSERT INTO test_foreign_table (c1, c2, c3, c4) VALUES (1, 2, 'foo', 'bar');

-- Update a row
UPDATE test_foreign_table SET c3 = 'updated' WHERE c1 = 1;

-- Delete a row
DELETE FROM test_foreign_table WHERE c1 = 1;

For INSERT, UPDATE, and DELETE to work, the MySQL source table must use its first column as the unique primary key column or as part of a composite primary key. This constraint does not apply to SELECT.

CREATE SERVER

CREATE SERVER [IF NOT EXISTS] server_name [TYPE 'server_type'] [VERSION 'server_version']
  FOREIGN DATA WRAPPER fdw_name
  [OPTIONS ([mpp_execute 'any | master | all segments'], option 'value' [, ...])]

Options

CREATE USER MAPPING

CREATE USER MAPPING [IF NOT EXISTS] FOR { user_name | USER | CURRENT_USER | PUBLIC }
  SERVER server_name
  [OPTIONS (option 'value' [, ...])]

Options

CREATE FOREIGN TABLE

CREATE FOREIGN TABLE [IF NOT EXISTS] table_name ([
  { column_name data_type [OPTIONS (option 'value' [, ...])] [COLLATE collation] [column_constraint [...]]
    | table_constraint }
  [, ...]
])
[INHERITS (parent_table [, ...])]
  SERVER server_name
[OPTIONS ([mpp_execute 'any | master | all segments'], option 'value' [, ...])]

Options

IMPORT FOREIGN SCHEMA

Use IMPORT FOREIGN SCHEMA to create foreign tables automatically for MySQL tables, instead of defining each with CREATE FOREIGN TABLE.

IMPORT FOREIGN SCHEMA remote_schema
  [{ LIMIT TO | EXCEPT } (table_name [, ...])]
  FROM SERVER server_name
  INTO local_schema
  [OPTIONS (option 'value' [, ...])]

• LIMIT TO (table_name, ...): import only the listed tables.

• EXCEPT (table_name, ...): import all tables except the listed ones.

Options