Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
In today’s article, I will tell you how to Access MySQL From PostgreSQL With mysql_fdw.
To access the MySQL database from the PostgreSQL database, we need to do the following steps. Transactions were performed using Centos 7 and PostgreSQL 13.
1 2 | MySQL Database : 192.168.1.60 PostgreSQL Database : 192.168.1.61 |
1. We create a user in the MySQL database and give the created user privileges on which database will be processed.
1 2 3 | mysql> CREATE USER 'user_fdw'@'%' IDENTIFIED BY '<password>' mysql> grant select,insert,update,delete on test to user_fdw@'%'; mysql> FLUSH PRIVILEGES; |
2. We install the MySQL client on the PostgreSQL server.
1 | $ yum -y install mysql |
3. After installation, we test connection to MySQL database.
1 | $ mysql -u user_fdw -p -h 192.168.1.60 |
4. Install the mysql_fdw package on the PostgreSQL server.
1 | $ yum -y install mysql_fdw |
5. We create the extension installed in the PostgreSQL database.
1 | testdb=# create extension mysql_fdw; |
6. We are creating a foreign server.
1 2 | testdb=# CREATE SERVER mysql_svr FOREIGN DATA WRAPPER mysql_fdw OPTIONS (host '192.168.1.60',port '3306'); |
7. We create mapping for MySQL database user.
1 2 | testdb=# CREATE USER MAPPING FOR public SERVER mysql_svr OPTIONS (username 'user_fdw',password '<password>'); |
8. Finally, we create a foreign table.
1 2 3 4 5 6 7 | testdb=# CREATE FOREIGN TABLE ftbl_test ( id int OPTIONS (key 'true') NOT NULL, name varchar(30), lastname varchar(40) ) SERVER mysql_svr OPTIONS (SCHEMA 'test', TABLE 'customers'); |
