mysql_fdw: Foreign Data Wrapper for MySQL

January 3, 2024

Summary: mysql_fdw is a PostgreSQL extension, which implements a Foreign Data Wrapper (FDW) for MySQL.

Table of Contents

Usage

The following parameters can be set on a MySQL foreign server object:

  • host: Address or hostname of the MySQL server. Defaults to 127.0.0.1
  • port: Port number of the MySQL server. Defaults to 3306
  • secure_auth: Enable or disable secure authentication. Default is true
  • init_command: SQL statement to execute when connecting to the MySQL server.
  • use_remote_estimate: Controls whether mysql_fdw issues remote EXPLAIN commands to obtain cost estimates. Default is false.
  • reconnect: Enable or disable automatic reconnection to the MySQL server if the existing connection is found to have been lost. Default is false.
  • sql_mode: Set MySQL sql_mode for established connection. Default is ANSI_QUOTES.
  • ssl_key: The path name of the client private key file.
  • ssl_cert: The path name of the client public key certificate file.
  • ssl_ca: The path name of the Certificate Authority (CA) certificate file. This option, if used, must specify the same certificate used by the server.
  • ssl_capath: The path name of the directory that contains trusted SSL CA certificate files.
  • ssl_cipher: The list of permissible ciphers for SSL encryption.
  • fetch_size: This option specifies the number of rows mysql_fdw should get in each fetch operation. It can be specified for a foreign table or a foreign server. The option specified on a table overrides an option specified for the server. The default is 100.
  • character_set: The character set to use for MySQL connection. Default is auto which means autodetect based on the operating system setting. Before the introduction of the character_set option, the character set was set similar to the PostgreSQL database encoding. To get this older behavior set the character_set to special value PGDatabaseEncoding.

The following parameters can be set on a MySQL foreign table object:

  • dbname: Name of the MySQL database to query. This is a mandatory option.
  • table_name: Name of the MySQL table, default is the same as foreign table.
  • max_blob_size: Max blob size to read without truncation.
  • fetch_size: Same as fetch_size parameter for foreign server.

The following parameters need to supplied while creating user mapping.

  • username: Username to use when connecting to MySQL.
  • password: Password to authenticate to the MySQL server with.

The following parameters can be set on IMPORT FOREIGN SCHEMA command:

  • import_default: This option controls whether column DEFAULT expressions are included in the definitions of foreign tables imported from a foreign server. The default is false.
  • import_not_null: This option controls whether column NOT NULL constraints are included in the definitions of foreign tables imported from a foreign server. The default is true.
  • import_enum_as_text: This option can be used to map MySQL ENUM type to TEXT type in the definitions of foreign tables, otherwise emit a warning for type to be created. The default is false.
  • import_generated: This option controls whether GENERATED column expressions are included in the definitions of foreign tables imported from a foreign server or not. The default is true. The IMPORT will fail altogether if an imported generated expression uses a function or operator that does not exist on PostgreSQL.

Examples

-- load extension first time after install
CREATE EXTENSION mysql_fdw;

-- create server object
CREATE SERVER mysql_server
	FOREIGN DATA WRAPPER mysql_fdw
	OPTIONS (host '127.0.0.1', port '3306');

-- create user mapping
CREATE USER MAPPING FOR postgres
	SERVER mysql_server
	OPTIONS (username 'foo', password 'bar');

-- create foreign table
CREATE FOREIGN TABLE warehouse
	(
		warehouse_id int,
		warehouse_name text,
		warehouse_created timestamp
	)
	SERVER mysql_server
	OPTIONS (dbname 'db', table_name 'warehouse');

-- insert new rows in table
INSERT INTO warehouse values (1, 'UPS', current_date);
INSERT INTO warehouse values (2, 'TV', current_date);
INSERT INTO warehouse values (3, 'Table', current_date);

-- select from table
SELECT * FROM warehouse ORDER BY 1;

warehouse_id | warehouse_name | warehouse_created
-------------+----------------+-------------------
           1 | UPS            | 10-JUL-20 00:00:00
           2 | TV             | 10-JUL-20 00:00:00
           3 | Table          | 10-JUL-20 00:00:00

-- delete row from table
DELETE FROM warehouse where warehouse_id = 3;

-- update a row of table
UPDATE warehouse set warehouse_name = 'UPS_NEW' where warehouse_id = 1;

-- explain a table with verbose option
EXPLAIN VERBOSE SELECT warehouse_id, warehouse_name FROM warehouse WHERE warehouse_name LIKE 'TV' limit 1;

                                                    QUERY PLAN
--------------------------------------------------------------------------------------------------------------------
Limit  (cost=10.00..11.00 rows=1 width=36)
	Output: warehouse_id, warehouse_name
	->  Foreign Scan on public.warehouse  (cost=10.00..1010.00 rows=1000 width=36)
		Output: warehouse_id, warehouse_name
		Local server startup cost: 10
		Remote query: SELECT `warehouse_id`, `warehouse_name` FROM `db`.`warehouse` WHERE ((`warehouse_name` LIKE BINARY 'TV'))

See more

mysql_fdw Project

Search

Categories

Tags