PolarDB:mysql_fdw

Usage notes

• You need to add the CIDR block of the VPC to which the PolarDB for PostgreSQL cluster belongs to allow the cluster to communicate with the MySQL instance. An example CIDR block is 172.xx.xx.xx/16.

• PolarDB for PostgreSQL does not allow you to specify the host and port keywords in OPTIONS statement. Use channel_name instead. By default, each cluster is configured with a channel named localhost, which is the channel to connect to the local server. If you want to connect to other clusters, create a channel first. If you need to configure the channel, Contact us.

Usage

1. Create the mysql_fdw extension.

   CREATE EXTENSION IF NOT EXISTS mysql_fdw;

   Note

   Only privileged accounts can execute this statement.

2. Define a MySQL server.

   CREATE SERVER <The server name>
       FOREIGN DATA WRAPPER mysql_fdw
       OPTIONS (channel_name 'The channel name'); 

   Note

   PolarDB for PostgreSQL allows you to specify the connection information of the MySQL instance only by using channel_name in the OPTIONS statement.

   In the following sample code, a polar_channel named mysqlchannel is used:

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

3. Create a user mapping that maps the MySQL server to an account in the PolarDB for PostgreSQL cluster. You can use the account to read data from and write data to the MySQL database, which resides on the MySQL server.

   CREATE USER MAPPING FOR <PolarDB for PostgreSQL username>
   SERVER <The name of the MySQL server>
   OPTIONS (username '<The MySQL username>', password '<The password of the MySQL user>'); 

   Example:

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

4. Create a foreign table for the MySQL instance by using the PolarDB for PostgreSQL user that is specified in Step 3.

   Note

   The field names in the foreign MySQL table must be the same as the field names in the table in the MySQL database. You can choose to create only the fields that you want to query. For example, if the table in the MySQL database contains the ID, NAME, and AGE fields, you can create the ID and NAME fields in the foreign MySQL table.

   CREATE FOREIGN TABLE <Table name> (<Field name> <Data type >,< Field name> <Data type>...) server <Server name> options (dbname '<MySQL database name>', table_name '<MySQL table name>');

   Example:

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

5. Check whether you can perform read and write operations to the MySQL database.

   • You can use the foreign MySQL table to check whether you can perform read and write operations on the MySQL database.

     Note

     Data can be written to the table in the MySQL database only when the table is assigned a primary key. If the table is not assigned a primary key, the following error is returned:

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

     SELECT * FROM ft_test ;
     INSERT INTO ft_test VALUES (2,'abc');
     INSERT 0 1
     INSERT INTO ft_test SELECT generate_series(3,100),'abc';
     INSERT 0 98
     SELECT count(*) FROM ft_test ;
      count
     -------
         99
     (1 row)

   • View the execution plan to check how the requests sent by the PolarDB for PostgreSQL cluster to query data from the MySQL database are executed.

     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)