Connecting MySQL to PostgreSQL Using mysql_fdw for Cross-DB Queries

In many environments, PostgreSQL and MySQL co-exist due to specific use cases that favor one database over the other. The need to connect and interact with both databases in a seamless manner can arise. PostgreSQL offers an excellent solution through the Foreign Data Wrapper (FDW) feature, which allows PostgreSQL to connect to other databases, including MySQL, via the mysql_fdw extension.

In this blog, we’ll dive into the mysql_fdw extension, explain how it works, and guide you through setting it up to integrate MySQL with PostgreSQL.

What is mysql_fdw?

mysql_fdw is a Foreign Data Wrapper (FDW) that allows PostgreSQL to interact with MySQL databases. 

With mysql_fdw, you can query, insert, update, and delete MySQL data from PostgreSQL using SQL, making cross-database operations easier without needing to replicate data.

Why Use mysql_fdw?

• Unified Access: With mysql_fdw, you can access MySQL data directly from PostgreSQL using standard SQL queries.
• Cross-Platform Integration: It simplifies the integration between MySQL and PostgreSQL applications, making it easier to migrate data or query both databases.
• Data Federation: You can join data from MySQL tables with PostgreSQL tables in real-time, effectively "federating" the data without needing full-scale replication.
• Minimal Overhead: Unlike database replication, mysql_fdw doesn't require setting up or maintaining an entirely separate database system; it allows PostgreSQL to access MySQL data directly.

Prerequisites

Before getting started, ensure that:

• We need to have a PostgreSQL instance .
• MySQL is already installed and running.
• Have the necessary privileges to create extensions and access MySQL from PostgreSQL.

Installing mysql_fdw

Step 1: Install Required Packages.

Step 2 : Install the mysql_fdw Extension

CREATE EXTENSION mysql_fdw;

Step 3 : Configure Connection to MySQL

You need to create a foreign server and user mapping to connect PostgreSQL to MySQL. Use the following SQL commands:

Create a Foreign Server

postgres=# CREATE SERVER mysql_server FOREIGN DATA WRAPPER mysql_fdw OPTIONS (host '172.17.0.9', port '3306');

Create a User Mapping

postgres=# CREATE USER MAPPING FOR postgres SERVER mysql_server OPTIONS (username 'mysql_user', password 'Mysql@1234');

Accessing MySQL Data

Once the foreign server and user mapping are set up, you can create foreign tables that represent MySQL tables in PostgreSQL.

Creating Foriegn tables:

Create foreign tables in PostgreSQL that map to MySQL tables:

CREATE FOREIGN TABLE mysql_users ( id INT, name VARCHAR(255), email VARCHAR(255), created_at TIMESTAMP ) SERVER mysql_server OPTIONS (table_name 'users', dbname 'mafiree');

Query MySQL Data

postgres=# SELECT * FROM mysql_users;
id | name | email | created_at
----+---------------+---------------------+---------------------
1 | Alice Smith | alice@example.com | 2025-01-16 09:47:22
2 | Bob Johnson | bob@example.com | 2025-01-16 09:47:22
3 | Charlie Brown | charlie@example.com | 2025-01-16 09:47:22
(3 rows)

We can also perform INSERT, UPDATE, DELETE the tables here.

Advantages of mysql_fdw

1. Seamless Data Integration:

• mysql_fdw allows PostgreSQL to directly query MySQL data using SQL, enabling seamless integration between PostgreSQL and MySQL without the need for complex synchronization mechanisms.

2. Minimal Overhead:

• Since mysql_fdw does not require full-scale replication between PostgreSQL and MySQL, it avoids the overhead associated with replicating large datasets across systems.

3. Data Federation:

• It helps in data federation scenarios where data from MySQL and PostgreSQL can be accessed together without physically replicating the data from MySQL to PostgreSQL.

Disadvantages of mysql_fdw

1. Network Dependency:

• Since mysql_fdw relies on network communication between PostgreSQL and MySQL, the performance and availability of the network can affect the responsiveness of queries on foreign tables.

2. Lack of Index Support:

• While mysql_fdw allows queries to be run on MySQL data, it doesn’t provide direct support for PostgreSQL indexes on foreign tables, which can further degrade performance for complex queries.

Conclusion

The mysql_fdw extension provides an easy and powerful way to integrate MySQL and PostgreSQL, enabling seamless cross-database querying and data federation. By following the steps in this blog, you should now have a working setup where PostgreSQL can interact with MySQL databases through SQL.

If you need further assistance with setting up or troubleshooting mysql_fdw for your PostgreSQL and MySQL integration, feel free to reach out to our support team.

Contact us at: sales@mafiree.com We're here to help you streamline your database integration and ensure smooth operations. Don't hesitate to reach out!