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'); |
