Connect to MySQL and MariaDB from Amazon Aurora and Amazon RDS for PostgreSQL using the mysql_fdw extension

When dealing with multiple database engines or schemas that share data with each other, you’re faced with the difficulty of establishing cross-engine database connectivity. To overcome this challenge, PostgreSQL offers foreign data wrappers (FDW), which are libraries that facilitate communication with external data sources by abstracting the complexities of data source connection and data retrieval.

The PostgreSQL mysql_fdw extension is a FDW implementation for MySQL, facilitating direct connectivity between a PostgreSQL database and a MySQL or a MariaDB database. The mysql_fdw extension is supported for Amazon Aurora PostgreSQL-Compatible Edition version 15.4, 14.9, 13.12, 12.16 and higher releases and Amazon RDS for PostgreSQL version 14.2, 13.6 and higher releases.

In this post, we demonstrate the steps involved in setting up and using the mysql_fdw PostgreSQL extension on Amazon RDS for PostgreSQL, enabling you to interact with your MySQL database data. The examples in this blog post also work for Amazon Aurora PostgreSQL-compatible edition.

Solution overview

By using the mysql_fdw extension in Amazon RDS for PostgreSQL, it enables data retrieval from Amazon RDS for MySQL, Amazon Aurora MySQL-Compatible Edition, MariaDB and other MySQL-compatible databases.

Suppose you have a MySQL database installed on an Amazon Elastic Compute Cloud (Amazon EC2) instance, containing several tables. Your RDS for PostgreSQL users need to query and modify data directly from that MySQL database using SELECT, INSERT, UPDATE, and DELETE commands.

To enable direct access to the MySQL tables, we will configure mysql_fdw extension in RDS for PostgreSQL database.

All the commands required for this post is issued from a Linux Bastion Host, removing the need to connect to the database host directly. If you’re using your own MySQL or PostgreSQL database host, update the connection credentials accordingly.

The following diagram illustrates our solution architecture

In this post we use MySQL as the target database. The examples in this blog post will also work for Amazon Aurora MySQL-compatible edition, and MariaDB.

Prerequisites

To install the mysql_fdw extension, make sure that you have the following prerequisites in place

• MySQL Instance (for this post we use MySQL on Amazon EC2)
• Amazon Aurora PostgreSQL-Compatible Edition or RDS for PostgreSQL (for this post we use RDS for PostgreSQL) database. If you don’t already have a RDS for PostgreSQL instance, you can create one. For instructions, refer to Creating and connecting to a PostgreSQL DB instance.
• Configure a security group to allow traffic between your source and the target databases.

If you’re planning to connect your on-premises network to Amazon RDS, it’s recommended that you have at least one of the following network connectivity options to establish a connection between your on-premises network and Amazon RDS. The choice of connectivity option will depend on your specific requirements and use case, but having one of these options in place will ensure that you can establish a secure and reliable connection to your RDS instance:

• VPN connection
• AWS Direct Connect
• VPC peering
• AWS Transit Gateway
• AWS managed VPN

Ensuring that you have these prerequisites in place will enable you to proceed with the task successfully.

Build sample tables

For this post, we use a MySQL database on Amazon EC2 as a target database and create a sample database. We use a bastion host to access the MySQL database and utilize the MySQL command-line tool to run commands.

If you prefer to use your own database, then you can skip setting up your target instance. Just make sure to replace your database name, tables names and user account name accordingly.

For this post, we connect with SSH. See Connect to your instance , for more information about how to connect to your EC2 instance.

1. Connect to the bastion host with the following code:

ssh -i /path/key-pair-name.pem <instance-user-name>@<instance-public-dns-name>

For example:

ssh -i "ec2-key-mykey.pem" ec2-user@ec2-X-XX-XXX-XX.eu-central-1.compute.amazonaws.com

2. Connect to the database with the user that has privilege to create a database

mysql -u root -p

3. Create a new database called mysql_classicmodels by running the following command:

CREATE DATABASE mysql_classicmodels;

4. Select the newly created database using the following command:

USE mysql_classicmodels;

5. Create three new tables named mysql_offices, mysql_employees, and mysql_customers by running the following script:

/*Table structure for table offices */
CREATE TABLE mysql_offices (
  officeCode varchar(10) NOT NULL,
  city varchar(50) NOT NULL,
  phone varchar(50) NOT NULL,
  addressLine1 varchar(50) NOT NULL,
  addressLine2 varchar(50) DEFAULT NULL,
  state varchar(50) DEFAULT NULL,
  country varchar(50) NOT NULL,
  postalCode varchar(15) NOT NULL,
  territory varchar(10) NOT NULL,
  PRIMARY KEY (officeCode)
);

/*Data for the table mysql_offices */
INSERT INTO mysql_offices(officeCode,city,phone,addressLine1,addressLine2,state,country,postalCode,territory) VALUES  
('1','Anytown','212-555-0100','7 W 34Th Street','Suite 200','NY','10001','USA','NA'),
('2','Anywhere','571-555-0101','525 14th St S','Suite 5','VA','22202','USA','NA'),
('3','Nowhere','206-555-0111','100 Main Street','','WA','98109','USA','NA'),
('4','Any Town','936-555-0122','301 W 125Th St','Apt 3','NY','10026','USA','NA');


/*Table structure for table mysql_employees */
CREATE TABLE mysql_employees (
  employeeNumber int(11) NOT NULL,
  lastName varchar(50) NOT NULL,
  firstName varchar(50) NOT NULL,
  extension varchar(10) NOT NULL,
  email varchar(100) NOT NULL,
  officeCode varchar(10) NOT NULL,
  reportsTo int(11) DEFAULT NULL,
  jobTitle varchar(50) NOT NULL,
  PRIMARY KEY (employeeNumber),
  KEY reportsTo (reportsTo),
  KEY officeCode (officeCode),
  CONSTRAINT employees_ibfk_1 FOREIGN KEY (reportsTo) REFERENCES mysql_employees (employeeNumber),
  CONSTRAINT employees_ibfk_2 FOREIGN KEY (officeCode) REFERENCES mysql_offices (officeCode)
);

/*Data for the table mysql_employees */
INSERT INTO  mysql_employees(employeeNumber,lastName,firstName,extension,email,officeCode,reportsTo,jobTitle) VALUES  
(1002,'Rosalez','Alejandro','x5800','alejandro_rosalez@example.com','1',NULL,'President'),
(1056,'Mansa','Akua','x4611','akua_mansa@example.com','1',1002,'VP Sales'),
(1088,'Silva','Ana Carolina','x4871','anacarolina_silva@example.com','3',1056,'Sales Manager (APAC)'),
(1102,'Desai','Arnav','x5408','arnav_desai@example.com','4',1056,'Sale Manager (EMEA)'),
(1143,'Salazar','Carlos','x5428','carlos_salazar@example.com','1',1056,'Sales Manager (NA)'),
(1165,'Ramirez','Diego','x3291','diego_ramirez@example.com','1',1143,'Sales Rep'),
(1166,'Owusu','Efua','x4065','efua_owusu@example.com','1',1143,'Sales Rep'),
(1337,'Doe','Jane','x6493','jane_doe@example.com','4',1102,'Sales Rep'),
(1370,'Doe','John','x2028','john_doe@example.com','4',1102,'Sales Rep'),
(1401,'Stiles','John','x2759','john_stiles@example.com','4',1102,'Sales Rep'),
(1504,'Souza','Jorge','x102','jorge_souza@example.com','3',1102,'Sales Rep'),
(1611,'Mensah','Kwaku','x101','kwaku_mensah@example.com','3',1088,'Sales Rep');


/*Table structure for table mysql_customers */
CREATE TABLE mysql_customers (
  customerNumber int(11) NOT NULL,
  customerName varchar(50) NOT NULL,
  contactLastName varchar(50) NOT NULL,
  contactFirstName varchar(50) NOT NULL,
  phone varchar(50) NOT NULL,
  addressLine1 varchar(50) NOT NULL,
  addressLine2 varchar(50) DEFAULT NULL,
  city varchar(50) NOT NULL,
  state varchar(50) DEFAULT NULL,
  postalCode varchar(15) DEFAULT NULL,
  country varchar(50) NOT NULL,
  salesRepEmployeeNumber int(11) DEFAULT NULL,
  creditLimit decimal(10,2) DEFAULT NULL,
  PRIMARY KEY (customerNumber),
  KEY salesRepEmployeeNumber (salesRepEmployeeNumber),
  CONSTRAINT customers_ibfk_1 FOREIGN KEY (salesRepEmployeeNumber) REFERENCES mysql_employees (employeeNumber)
);

/*Data for the table mysql_customers */
INSERT INTO mysql_customers(customerNumber,customerName,contactLastName,contactFirstName,phone,addressLine1,addressLine2,city,state,postalCode,country,salesRepEmployeeNumber,creditLimit) VALUES  
(103,'Atelier graphique','Nikki','Wolf','212-555-0133','100 Main Street',NULL,'Anywhere','NY','44000','USA',1370,'21000.00'),
(112,'Signal Gift Stores','Pat','Candella','936-555-0140','7 W 14th Street',NULL,'Nowhere','NV','83030','USA',1166,'71800.00'),
(114,'Australian Collectors Co.','Paulo','Santos','832-555-0147','2308 Janna Way','Level 3','Any Town','WA','30040','USA',1611,'117300.00'),
(121,'Baane Mini Imports','Richard','Roe','409-555-0154','1152 Indian Run Dr',NULL,'No where','NJ','40110','USA',1504,'81700.00'),
(124,'Mini Gifts Distributors Ltd.','Saanvi','Sarkar','404-555-0161','1 Market Street',NULL,'Everywhere','CA','97562','USA',1165,'210500.00'),
(125,'Havel Zbyszek Co','Shirley','Rodriguez','306-555-0168','123 Any Street',NULL,'Anytown','TX','01012','USA',1370,'0.00'),
(129,'Mini Wheels Co.','Sofía','Martínez','205-555-0175','5557 North Anywhere Street',NULL,'Nowhere','CA','94217','USA',1165,'64600.00'),
(144,'Volvo Model Replicas Co','Terry','Whitlock','832-555-0182','100 Anywhere Ave',NULL,'Any Where','FL','95822','USA',1504,'53100.00');

Set up your target instance

To enable access to MySQL from the PostgreSQL server side, you must configure a user on the MySQL instance. To do this, complete the following steps:

1. Connect to your MySQL DB instance and create a new user with password:

CREATE USER 'mysqlfdwuser' IDENTIFIED BY 'Dbadmin#123';

2. You can verify that the user was created in MySQL by running the following query:

SELECT user FROM mysql.user;

3. Select the database created during sample table creation using the following command:

USE mysql_classicmodels;

4. Grant the required privileges on the tables that will be used as foreign tables in PostgreSQL.

GRANT SELECT,INSERT,UPDATE,DELETE ON mysql_offices TO mysqlfdwuser@'%';
GRANT SELECT,INSERT,UPDATE,DELETE ON mysql_employees TO mysqlfdwuser@'%';
GRANT SELECT,INSERT,UPDATE,DELETE ON mysql_customers TO mysqlfdwuser@'%';

Set up your source RDS for PostgreSQL instance

To configure your RDS for PostgreSQL instance, you must complete three high-level steps on your RDS for PostgreSQL instance:

1. Install the mysql_fdw extension
2. Create a connection point (server)from RDS for PostgreSQL to the MySQL DB instance, and
3. Create a foreign table to query data from the MySQL to the PostgreSQL database

Install the mysql_fdw extension

For this post, we employ a Linux Bastion Host to access the RDS for PostgreSQL database and utilize the psql command-line tool to run commands.

1. Connect to your PostgreSQL DB instance using an account that has the rds_superuser role:

psql --host=<RDS Endpoint> --port=<DB Port> --username=<RDS User>

2. Install the mysql_fdw extension

Note that only users with the rds_superuser permission can execute the following command

postgres=> CREATE EXTENSION mysql_fdw;

3. You can check the list of installed extensions with the following command:

postgres=> \dx

For more information about CREATE EXTENSION command, see the PostgreSQL documentation.

Create and configure a connection point from Amazon RDS for PostgreSQL to the MySQL DB instance

To set up your connection point, complete the following steps:

1. Create a foreign server and configure connection point from Amazon RDS for PostgreSQL to the MySQL database by running the following commands on the RDS for PostgreSQL DB instance:

CREATE SERVER mysql_server  
FOREIGN DATA WRAPPER mysql_fdw
OPTIONS(host '<Host IP Address>', port '<MySQL DB Port>');

The foreign server on the PostgreSQL DB instance acts as a gateway to the MySQL database. You can use either the private IP or the public DNS hostname as the host endpoint. For this post, we use the private IP of the EC2 instance of MySQL database.

2. Verify that the foreign server was created with the following command:

postgres=> \des+

3. For this post, we create a new user named pguser1 run the following commands

CREATE USER pguser1;

\password pguser1;

To establish a connection to a MySQL database via a foreign server in PostgreSQL, the user accounts must be able to create and manage their own connections. To do this, they must have USAGE privileges on the foreign data wrapper.

4. Grant the user access to the USAGE privilege on the foreign data wrapper:

GRANT USAGE ON FOREIGN SERVER mysql_server TO pguser1;

For the user to access the foreign server it must be mapped to the foreign server that was created in this step.

5. To map a MySQL server to pguser1 account instance in the previous step, run the following SQL command:

CREATE USER MAPPING FOR pguser1 
SERVER mysql_server OPTIONS (username 'mysqlfdwuser', password 'Dbadmin#123');

Create a MySQL foreign table in Amazon RDS for PostgreSQL

To create a foreign MySQL table inside Amazon RDS for PostgreSQL using the account that was mapped to the MySQL server in the previous step, follow these steps:

1. Connect to the PostgreSQL instance using the pguser1 account that was created in the previous step:

postgres=>\c postgres pguser1;

2. Create a mapping between the pguser1 account and the MySQL user account:

CREATE FOREIGN TABLE fg_mysql_offices (officeCode varchar(10),city varchar(50), phone varchar(50), addressLine1 varchar(50), addressLine2 varchar(50), state varchar(50), country varchar(50), postalCode varchar(15), territory varchar(10))
SERVER mysql_server options (dbname 'mysql_classicmodels', table_name 'mysql_offices');  

To create a foreign table in PostgreSQL linked to a MySQL table, the column names in the PostgreSQL foreign table must be same as that of the table in MySQL database. However, you can choose to create only those columns that you want to query. For example, if the table in the MySQL database contains columns ID, NAME, and AGE fields, you can create a foreign table with only columns ID and NAME.

3. Grant foreign table ownership to the user:

ALTER FOREIGN TABLE public.fg_mysql_offices OWNER TO pguser1;

Test your MySQL foreign tables in Amazon RDS for PostgreSQL

Testing your MySQL foreign tables in Amazon RDS for PostgreSQL is an important step to ensure that your MySQL data can be accessed and used within a PostgreSQL environment.

The testing process should include verifying that the queried data is same between MySQL and PostgreSQL.

1. Query MySQL data from the PostgreSQL database using foreign table

SELECT * FROM fg_mysql_offices;

2. Now let’s test inserting, updating, and deleting rows in the MySQL database from PostgreSQL:

INSERT INTO fg_mysql_offices(officeCode,city,phone,addressLine1,addressLine2,state,country,postalCode,territory) VALUES
('5','Any City','404-555-0133','200 Market St',NULL,'GA','20015','USA','NA'),
('6','Any City','408-555-0144','200 Main St',NULL,'GA','20123','USA','NA');
INSERT 0 2

UPDATE fg_mysql_offices SET city = 'Any Town' WHERE officeCode = '1';
UPDATE 1

DELETE FROM fg_mysql_offices WHERE officeCode = '2';
DELETE 1

3. Check the results in MySQL database from PostgreSQL:

SELECT * FROM fg_mysql_offices;

Additionally, it’s important to verify that any modifications made to the foreign tables are correctly reflected in the MySQL database.

4. Connect to your MySQL DB instance and validate the data:

SELECT * FROM mysql_classicmodels.mysql_offices;

Troubleshooting common errors

This section covers common errors that can occur due to misconfiguration.

Issue #1: Failed to connect to MySQL server

If you receive failed to connect error message:

ERROR: failed to connect to MySQL: Can't connect to MySQL server on 'ec2-x-xxx-xxx-xx.eu-central-1.compute.amazonaws.com:3306' (110)

In this case, the security group of your MySQL EC2 instance isn’t allowing connection between your RDS for PostgreSQL and MySQL server. Make sure to add an inbound rule in the security group to allow connections for port 3306.

Issue #2: Problems with the user mapping

If you receive user mapping not found error:

postgres=> SELECT * FROM fg_mysql_offices;
ERROR:  user mapping not found for "postgres"

Connect with the user that was created on RDS for PostgreSQL to manage MySQL foreign server connections. To connect, run the following command

postgres=>\c postgres pguser1;

or the user has not been mapped to the foreign server that is being accessed. To fix this, create the required mapping to the foreign server, run the following command

CREATE USER MAPPING FOR pguser1 SERVER mysql_server OPTIONS (SET username 'mysqlfdwuser1', password 'Password123');

Issue #3: Connecting as the wrong user

If you receive access denied error message:

ERROR: failed to connect to MySQL: Access denied for user 'mysqlfdwuser1'@'xxx.xxx.xxx.xxx' (using password YES)

In this case the foreign server was created with incorrect MySQL user or the user does not have proper access to connect to MySQL database. To fix this, correct the user information in the foreign server, run the following command:

ALTER USER MAPPING FOR pguser1 SERVER mysql_server OPTIONS (SET username 'mysqlfdwuser1', password 'Password123');

Issue #4: Incorrect permissions for the PostgreSQL user

If you receive permission denied for foreign table error:

ERROR: permission denied for foreign table fg_mysql_offices

To fix this, grant the user ownership access to the PostgreSQL user configured to manage MySQL and PostgreSQL foreign server access:

ALTER FOREIGN TABLE public.fg_mysql_offices OWNER TO pguser1;

Cleanup

The resources will incur costs as long as they are running. You can perform a cleanup and free up resource.

1. Drop foreign table

DROP FOREIGN TABLE public.fg_mysql_offices;

2. Drop user mapping

DROP USER MAPPING FOR pguser1 SERVER mysql_server;

3. Drop foreign server

DROP SERVER mysql_server;

4. Drop mysql_fdw extension

DROP EXTENSION mysql_fdw;

5. Delete RDS for PostgreSQL instance if it was created for this blog
6. Terminate your EC2 instance for MySQL if it was created for this blog

Conclusion

PostgreSQL foreign data wrappers present a solution to the challenge of establishing cross-engine database connectivity in microservices-based environments. By making use of the mysql_fdw extension available in Amazon Aurora and Amazon RDS for PostgreSQL, you can achieve direct connectivity between PostgreSQL and MySQL-compatible databases such as Amazon Aurora MySQL-Compatible Edition, MySQL, and MariaDB. In this post, we provided a step-by-step guide for setting up and utilizing the mysql_fdw extension on Amazon RDS for PostgreSQL to access MySQL database data. We demonstrated configuring the foreign connection on RDS for PostgreSQL database to support selects, inserts, updates, and deletes across the foreign data wrapper, thereby offering alternative mechanism when cross-database communication is required. If you have any questions or comments about this post, please use the comments section.

About the Authors

Cristiani Santos da Silva is a Database Migration Specialist Solutions Architect on the Amazon Database Migration Accelerator team. In her role as an Amazon DMA Advisor, she guides customers through successful migration and modernization journeys to AWS Cloud, leveraging her expertise to facilitate seamless transitions.

Vanshika Nigam is a Solutions Architect with Database Migration Accelerator team at AWS and has over 5 years of Amazon RDS experience. She works as an Amazon DMA Advisor to help AWS customers accelerate their on-premise data to AWS Cloud database solutions.

Ahmed Virani is a Solutions Architect with Database Migration Accelerator team at AWS, with over 15 years of experience in database administration, modernization, design, development, and integration.