AnalyticDB:Use the mysql_fdw extension to access MySQL data

Usage notes

When you perform DML operations, such as UPDATE, DELETE, and INSERT, on a MySQL foreign table, the source table must use the first column as the unique primary key column or a part of the composite primary key. The primary key constraint limits do not apply to the SELECT operation.

Prerequisites

• The mysql_fdw extension is installed. For more information, see Install, update, and uninstall extensions.

• A database account is created for an AnalyticDB for PostgreSQL instance. For more information, see Create a database account.

Getting started

To use the mysql_fdw extension to access data on a remote MySQL database, perform the following steps:

1. Log on to the AnalyticDB for PostgreSQL console. Find the AnalyticDB for PostgreSQL instance that you want to manage and click the instance ID to go to the Basic Information page of the instance.

2. In the upper-right corner of the page, click Log On to Database. In the Log On to Database Instance dialog box, configure the Database Account and Database Password parameters and click Login.

3. Enter a database and execute the CREATE SERVER statement to create a foreign server as the MySQL database that you want to access. Sample statements:

   -- Create a foreign server named mysql_svr.
   CREATE SERVER mysql_svr FOREIGN DATA WRAPPER mysql_fdw
     OPTIONS (host '127.0.0.1', port '3306');
   
   -- Create a user mapping for the mysql_svr server by specifying the username and the password that are used to access the MySQL database.
   CREATE USER MAPPING FOR public SERVER mysql_svr
     OPTIONS (username '$MYSQL_USER_NAME', password '$MYSQL_PASS');
   
   -- Use the mysql_svr server to create a 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');

   Parameters:

   • CREATE SERVER: creates a foreign server. The foreign server specifies the MySQL database that you want to access.

   • CREATE USER MAPPING: creates a user mapping. You must specify the username and the password that are used to access the MySQL database.

   • CREATE FOREIGN TABLE: creates a foreign table. Column names of the foreign table must match the column names of the remote table that you want to access. You can also execute the IMPORT FOREIGN SCHEMA statement to allow the mysql_fdw extension to automatically create a foreign table on the AnalyticDB for PostgreSQL instance for the MySQL table that you want to access.

4. Perform the query, insert, add, and remove operations on data of the MySQL database. Only the preceding operations are supported.

Syntax

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' [, ... ])]

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

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' [, ... ] ) ]

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

Compatibility

The mysql_fdw extension of AnalyticDB for PostgreSQL is compatible with the following MySQL instances and databases:

ApsaraDB RDS for MySQL

Self-managed MySQL database

MySQL versions earlier than V5.7 are no longer maintained. In most cases, MySQL V5.7 and later are used.

PolarDB for MySQL

Features and data types

Features

Connection pool

All queries in the same session use the same MySQL database connection instead of a new MySQL connection.

WHERE pushdown

The WHERE clause of a foreign table is pushed down to MySQL. This way, the WHERE conditions related to the foreign table are executed in MySQL, and fewer rows of data are transmitted to the AnalyticDB for PostgreSQL instance.

Projection pushdown

Not all data of a MySQL table is queried and transmitted to the AnalyticDB for PostgreSQL instance. The mysql_fdw extension returns only the columns in the table that you want to query by executing the SELECT statement. This way, less data is transmitted to the AnalyticDB for PostgreSQL instance to improve performance.

Prepared statement

SELECT queries are performed by using prepared statements instead of simple SELECT statements.

JOIN pushdown

Join operations between two foreign tables in the same MySQL database are pushed down to a remote MySQL database for execution. This improves performance compared with the method of obtaining all rows of the two tables and performing join operations on the AnalyticDB for PostgreSQL instance.

Aggregate pushdown

Aggregate functions are pushed down to a remote MySQL database for execution. This improves performance compared with the method of obtaining all rows and performing aggregate operations on the AnalyticDB for PostgreSQL instance.

ORDER BY pushdown

ORDER BY clauses are pushed down to a remote MySQL database for execution to obtain an ordered result set from MySQL. MySQL databases perform NULL-related operations in the opposite manner compared with AnalyticDB for PostgreSQL. To obtain a result that is the same as the result in AnalyticDB for PostgreSQL, add the expression IS NULL predicate to the beginning of each ORDER BY clause. This way, MySQL places NULL values after a result set that is sorted in ascending order.

LIMIT and OFFSET pushdown

The LIMIT and OFFSET clauses are pushed down to a remote MySQL database for execution. This reduces the amount of data transmitted between the AnalyticDB for PostgreSQL instance and the MySQL database compared with the method of transmitting all data to the AnalyticDB for PostgreSQL instance.

Data types