()

Access MySQL database from PostgreSQL using MySql Foreign Data Wrapper (MYSQL_FDW)


The MySQL Foreign Data Wrapper (mysql_fdw) is a Postgres extension that allows you to access data that resides on a MySQL database from Postgres Server. It is a writeable foreign data wrapper that you can use with Postgres functions and utilities, or in conjunction with other data that resides on a Postgres host.

In the previous 2 blog post we saw 1.) Steps to Setup an Oracle Foreign Data Wrapper (oracle_fdw) in PostgreSQL Click here to read more. 2.) Steps to Setup a PostgreSQL Foreign Data Wrapper (postgres_fdw) in PostgreSQL Click here to read more. To access tables present in a remote MySQL database server from a local PostgreSQL database, we can use mysql_fdw Foreign Data Wrapper.

For this demonstration, we’ll use the PostgreSQL DB local_db and MySQL DB remote_db. We will access a table employee that exists in MySQL remote_db from PostgreSQL local_db.

Below are the high-level steps we will follow to set up MYSQL_FDW
1. Setup Remote MySql DB
2. Download the source code for installing mysql_fdw
3. Install mysql-connector package
4. Install mysql-devel package
5. Create Softlink in /usr/include directory
6. Copy files from /usr/include/mysql/mysql into /usr/include/mysql/
7. Compile mysql_fdw using make and make install command

8. Create the MYSQL_FDW extension
9. Create the Foreign Server
10. Create User Mapping
11. Grant the Local User Access to the Foreign Server
12. Create foreign Table

 

Below are setup details and the same will be used in this demonstration.

Sr. No.DB NameUsernameHostnameIPPort
1MySql-remote_dbfdw_usertest-machine01192.168.114.1773306
2PostgreSQL-local_dbapp_usertest-machine02192.168.114.1765432


Step 1. Setup Remote MySql DB: In this step, we will use mysql DB: remote_db and user: fdw_user, And then connect to remote_db and create table employee. We will try to access the newly created table from PostgreSQL Cluster using MYSQL_FDW.

[mysql@test-machine01 bin]$
[mysql@test-machine01 bin]$ mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 70
Server version: 8.0.22 MySQL Community Server - GPL

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create database remote_db;
Query OK, 1 row affected (0.50 sec)

mysql> create user 'fdw_user'@'%' identified by 'secret';
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements
mysql> create user 'fdw_user'@'%' identified by 'Root@1234';
Query OK, 0 rows affected (0.18 sec)

mysql> alter user 'fdw_user'@'%' identified with mysql_native_password by 'Root@1234';
Query OK, 0 rows affected (0.06 sec)

mysql> grant all privileges on remote_db.* to  'fdw_user'@'%' ;
Query OK, 0 rows affected (0.07 sec)

mysql> show grants for 'fdw_user'@'%';
+---------------------------------------------------------+
| Grants for fdw_user@%                                   |
+---------------------------------------------------------+
| GRANT USAGE ON *.* TO `fdw_user`@`%`                    |
| GRANT ALL PRIVILEGES ON `remote_db`.* TO `fdw_user`@`%` |
+---------------------------------------------------------+
2 rows in set (0.07 sec)

mysql> exit
Bye
[mysql@test-machine01 bin]$

[mysql@test-machine01 bin]$
[mysql@test-machine01 bin]$ mysql -h test-machine01 -u fdw_user -p -D remote_db
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 75
Server version: 8.0.22 MySQL Community Server - GPL

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create table employee (id int, first_name varchar(20), last_name varchar(20));
Query OK, 0 rows affected (0.70 sec)

mysql> insert into employee values (1,'jobin','augustine'),(2,'avinash','vallarapu'),(3,'fernando','camargos');
Query OK, 3 rows affected (0.16 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from employee;
+------+------------+-----------+
| id   | first_name | last_name |
+------+------------+-----------+
|    1 | jobin      | augustine |
|    2 | avinash    | vallarapu |
|    3 | fernando   | camargos  |
+------+------------+-----------+
3 rows in set (0.00 sec)

mysql>


Step 2. Download the source code for installing mysql_fdw: Download source code of mysql_fdw from github using OS command wget. This command will directly download the tar file inside your server. After the tar file is download use the command tar -zxvf to extract files. All files will be extracted under folder mysql_fdw-REL-2_6_1.

[root@test-machine02 tmp]#
[root@test-machine02 tmp]# wget https://github.com/EnterpriseDB/mysql_fdw/archive/refs/tags/REL-2_6_1.tar.gz
--2021-11-18 10:36:10--  https://github.com/EnterpriseDB/mysql_fdw/archive/refs/tags/REL-2_6_1.tar.gz
Resolving github.com (github.com)... 140.82.121.3
Connecting to github.com (github.com)|140.82.121.3|:443... connected.
HTTP request sent, awaiting response... 302 Found
Location: https://codeload.github.com/EnterpriseDB/mysql_fdw/tar.gz/refs/tags/REL-2_6_1 [following]
--2021-11-18 10:36:12--  https://codeload.github.com/EnterpriseDB/mysql_fdw/tar.gz/refs/tags/REL-2_6_1
Resolving codeload.github.com (codeload.github.com)... 140.82.121.10
Connecting to codeload.github.com (codeload.github.com)|140.82.121.10|:443... connected.
HTTP request sent, awaiting response... 200 OK
Length: unspecified [application/x-gzip]
Saving to: âREL-2_6_1.tar.gzâ

    [  <=>                                                                                                                                                ] 116,118      438KB/s   in 0.3s

2021-11-18 10:36:13 (438 KB/s) - âREL-2_6_1.tar.gzâ saved [116118]

[root@test-machine02 tmp]#


[root@test-machine02 tmp]# ls -ltr
total 148
drwxr-xr-x. 4 root   root       4096 Nov  7 13:21 pglog
-rw-r--r--. 1 root   root     116118 Nov 18 10:36 REL-2_6_1.tar.gz
[root@test-machine02 tmp]#

[root@test-machine02 tmp]#
[root@test-machine02 tmp]# tar -zxvf REL-2_6_1.tar.gz
mysql_fdw-REL-2_6_1/
mysql_fdw-REL-2_6_1/.gitattributes
mysql_fdw-REL-2_6_1/.gitignore
mysql_fdw-REL-2_6_1/CONTRIBUTING.md
mysql_fdw-REL-2_6_1/LICENSE
mysql_fdw-REL-2_6_1/META.json
mysql_fdw-REL-2_6_1/Makefile
mysql_fdw-REL-2_6_1/README.md
mysql_fdw-REL-2_6_1/sql/connection_validation.sql
mysql_fdw-REL-2_6_1/sql/dml.sql
mysql_fdw-REL-2_6_1/sql/join_pushdown.sql
mysql_fdw-REL-2_6_1/sql/pushdown.sql
mysql_fdw-REL-2_6_1/sql/select.sql
mysql_fdw-REL-2_6_1/sql/server_options.sql
[root@test-machine02 tmp]#
[root@test-machine02 tmp]# ls -ltr
total 152
drwxrwxr-x. 4 root   root       4096 Sep 15 10:02 mysql_fdw-REL-2_6_1
drwxr-xr-x. 4 root   root       4096 Nov  7 13:21 pglog
-rw-r--r--. 1 root   root     116118 Nov 18 10:36 REL-2_6_1.tar.gz
[root@test-machine02 tmp]#


Step 3. Install mysql-connector package: We need to compile the code using make command and then finally install foreign data wrapper using the command make install. We need to ensure that pg_config & mysql_config executable is in path when we run make command. If we try to run the command make USE_PGXS=1 we will receive error “make: mysql_config: Command not found” To fix error regarding mysql_config need to install mysql-connector package. Use OS yum command to install it. After installation is done use the command which mysql_config or whereis mysql_config to get the installation location.

[root@test-machine02 ~]#
[root@test-machine02 ~]# cd /tmp
[root@test-machine02 tmp]#
[root@test-machine02 tmp]# cd mysql_fdw-REL-2_6_1/
[root@test-machine02 mysql_fdw-REL-2_6_1]#
[root@test-machine02 mysql_fdw-REL-2_6_1]# pwd
/tmp/mysql_fdw-REL-2_6_1
[root@test-machine02 mysql_fdw-REL-2_6_1]#
[root@test-machine02 mysql_fdw-REL-2_6_1]# export PATH=/usr/pgsql-13/bin/:$PATH
[root@test-machine02 mysql_fdw-REL-2_6_1]#
[root@test-machine02 mysql_fdw-REL-2_6_1]$ make USE_PGXS=1
make: mysql_config: Command not found
make: mysql_config: Command not found
gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -g -pipe -Wall -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector-strong --param=ssp-buffer-size=4 -grecord-gcc-switches   -m64 -mtune=generic -fPIC  -D _MYSQL_LIBNAME=\"libmysqlclient.so\" -I. -I./ -I/usr/pgsql-13/include/server -I/usr/pgsql-13/include/internal  -D_GNU_SOURCE -I/usr/include/libxml2  -I/usr/include  -c -o connection.o connection.c
Assembler messages:
Fatal error: can't create connection.o: Permission denied
In file included from connection.c:21:0:
mysql_fdw.h:21:19: fatal error: mysql.h: No such file or directory
 #include <mysql.h>
                   ^
compilation terminated.
make: *** [connection.o] Error 2
[root@test-machine02 mysql_fdw-REL-2_6_1]#

[root@test-machine02 tmp]#
[root@test-machine02 tmp]# yum install mysql-connector*
Loaded plugins: langpacks, ulninfo
Package mysql-connector-python-cext is obsoleted by mysql-connector-python, trying to install mysql-connector-python-8.0.23-1.el7.x86_64 instead
Package mysql-connector-python3-cext is obsoleted by mysql-connector-python3, trying to install mysql-connector-python3-8.0.27-1.el7.x86_64 instead
Resolving Dependencies
--> Running transaction check
--> Running transaction check
---> Package unixODBC.x86_64 0:2.3.1-14.0.1.el7 will be installed
--> Finished Dependency Resolution

Dependencies Resolved

==============================================================================================================================================================================================
 Package                                                  Arch                           Version                                     Repository                                          Size
==============================================================================================================================================================================================
Installing:
 mysql-connector-c++                                      x86_64                         8.0.27-1.el7                                mysql-connectors-community                         1.1 M
 mysql-connector-c++-debuginfo                            x86_64                         8.0.27-1.el7                                mysql-connectors-community                          57 M
 mysql-connector-c++-devel                                x86_64                         8.0.27-1.el7                                mysql-connectors-community                         4.0 M
 mysql-connector-c++-jdbc                                 x86_64                         8.0.27-1.el7                                mysql-connectors-community                         1.8 M
 mysql-connector-java                                     noarch                         1:8.0.27-1.el7                              mysql-connectors-community                         2.3 M
 mysql-connector-odbc                                     x86_64                         8.0.27-1.el7                                mysql-connectors-community                         4.3 M
 mysql-connector-odbc-debuginfo                           x86_64                         8.0.27-1.el7                                mysql-connectors-community                          12 M
 mysql-connector-odbc-setup                               x86_64                         8.0.27-1.el7                                mysql-connectors-community                         2.3 M
 mysql-connector-python                                   x86_64                         8.0.23-1.el7                                mysql-connectors-community                          11 M
 mysql-connector-python-debuginfo                         x86_64                         2.1.7-1.el7                                 mysql-connectors-community                         947 k
 mysql-connector-python3                                  x86_64                         8.0.27-1.el7                                mysql-connectors-community                          11 M
Installing for dependencies:
 unixODBC                                                 x86_64                         2.3.1-14.0.1.el7                            ol7_latest                                         413 k

Transaction Summary
==============================================================================================================================================================================================
Install  11 Packages (+1 Dependent package)

Total download size: 108 M
Installed size: 542 M
Is this ok [y/d/N]: y
  Verifying  : mysql-connector-c++-jdbc-8.0.27-1.el7.x86_64                                                                                                                              9/12
  Verifying  : 1:mysql-connector-java-8.0.27-1.el7.noarch                                                                                                                               10/12
  Verifying  : unixODBC-2.3.1-14.0.1.el7.x86_64                                                                                                                                         11/12
  Verifying  : mysql-connector-c++-8.0.27-1.el7.x86_64                                                                                                                                  12/12

Installed:
  mysql-connector-c++.x86_64 0:8.0.27-1.el7                        mysql-connector-c++-debuginfo.x86_64 0:8.0.27-1.el7            mysql-connector-c++-devel.x86_64 0:8.0.27-1.el7
  mysql-connector-c++-jdbc.x86_64 0:8.0.27-1.el7                   mysql-connector-java.noarch 1:8.0.27-1.el7                     mysql-connector-odbc.x86_64 0:8.0.27-1.el7
  mysql-connector-odbc-debuginfo.x86_64 0:8.0.27-1.el7             mysql-connector-odbc-setup.x86_64 0:8.0.27-1.el7               mysql-connector-python.x86_64 0:8.0.23-1.el7
  mysql-connector-python-debuginfo.x86_64 0:2.1.7-1.el7            mysql-connector-python3.x86_64 0:8.0.27-1.el7

Dependency Installed:
  unixODBC.x86_64 0:2.3.1-14.0.1.el7

Complete!
[root@test-machine02 tmp]#
[root@test-machine02 tmp]# which mysql_config
/u01/mysql-8/bin/mysql_config
[root@test-machine02 tmp]#
[root@test-machine02 tmp]# whereis mysql_config
mysql_config: /u01/mysql-8/bin/mysql_config
[root@test-machine02 tmp]#


Step 4. Install mysql-devel packag: Export both utility location pg_config & mysql_config in PATH variable. And try to compile code using make command. Now we have a new error fatal error : mysql.h: No such file or directory” to fix this issue need to install mysql-devel package. Use OS yum command to install it. After installation is done use command whereis mysql.h.

[root@test-machine02 ~]#
[root@test-machine02 ~]# cd /tmp
[root@test-machine02 tmp]#
[root@test-machine02 tmp]# cd mysql_fdw-REL-2_6_1/
[root@test-machine02 mysql_fdw-REL-2_6_1]#
[root@test-machine02 mysql_fdw-REL-2_6_1]# pwd
/tmp/mysql_fdw-REL-2_6_1
[root@test-machine02 mysql_fdw-REL-2_6_1]#
[root@test-machine02 mysql_fdw-REL-2_6_1]# export PATH=/usr/pgsql-13/bin/:$PATH
[root@test-machine02 mysql_fdw-REL-2_6_1]# export PATH=/u01/mysql-8/bin/:$PATH
[root@test-machine02 mysql_fdw-REL-2_6_1]#
[root@test-machine02 mysql_fdw-REL-2_6_1]# which pg_config
/usr/pgsql-13/bin/pg_config
[root@test-machine02 mysql_fdw-REL-2_6_1]# which mysql_config
/u01/mysql-8/bin/mysql_config
[root@test-machine02 mysql_fdw-REL-2_6_1]#
[root@test-machine02 mysql_fdw-REL-2_6_1]# make USE_PGXS=1
gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -g -pipe -Wall -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector-strong --param=ssp-buffer-size=4 -grecord-gcc-switches   -m64 -mtune=generic -fPIC -I/usr/local/mysql/include -D _MYSQL_LIBNAME=\"libmysqlclient.so\" -I. -I./ -I/usr/pgsql-13/include/server -I/usr/pgsql-13/include/internal  -D_GNU_SOURCE -I/usr/include/libxml2  -I/usr/include  -c -o connection.o connection.c
Assembler messages:
Fatal error: can't create connection.o: Permission denied
In file included from connection.c:21:0:
mysql_fdw.h:21:19: fatal error: mysql.h: No such file or directory
 #include <mysql.h>
                   ^
compilation terminated.
make: *** [connection.o] Error 2
[root@test-machine02 mysql_fdw-REL-2_6_1]#
[root@test-machine02 mysql_fdw-REL-2_6_1]#
[root@test-machine02 mysql_fdw-REL-2_6_1]# yum install mysql-devel -y
Loaded plugins: langpacks, ulninfo
Resolving Dependencies
--> Running transaction check
---> Package mysql-community-devel.x86_64 0:8.0.27-1.el7 will be installed
--> Processing Dependency: mysql-community-libs(x86-64) >= 8.0.11 for package: mysql-community-devel-8.0.27-1.el7.x86_64
--> Processing Dependency: pkgconfig(openssl) for package: mysql-community-devel-8.0.27-1.el7.x86_64
--> Processing Dependency: libmysqlclient.so.21()(64bit) for package: mysql-community-devel-8.0.27-1.el7.x86_64
--> Running transaction check
---> Package e2fsprogs.x86_64 0:1.42.9-19.el7 will be updated
---> Package e2fsprogs.x86_64 0:1.45.4-3.0.5.el7 will be an update
---> Package e2fsprogs-libs.x86_64 0:1.42.9-19.el7 will be updated
---> Package e2fsprogs-libs.x86_64 0:1.45.4-3.0.5.el7 will be an update
---> Package libss.x86_64 0:1.42.9-19.el7 will be updated
---> Package libss.x86_64 0:1.45.4-3.0.5.el7 will be an update
--> Finished Dependency Resolution

Dependencies Resolved

==============================================================================================================================================================================================
 Package                                                   Arch                              Version                                       Repository                                    Size
==============================================================================================================================================================================================
Installing:
 mysql-community-devel                                     x86_64                            8.0.27-1.el7                                  mysql80-community                            7.4 M
 mysql-community-libs                                      x86_64                            8.0.27-1.el7                                  mysql80-community                            4.7 M
     replacing  mariadb-libs.x86_64 1:5.5.68-1.el7
 mysql-community-libs-compat                               x86_64                            8.0.27-1.el7                                  mysql80-community                            1.2 M
     replacing  mariadb-libs.x86_64 1:5.5.68-1.el7
Installing for dependencies:
 keyutils-libs-devel                                       x86_64                            1.5.8-3.el7                                   ol7_latest                                    37 k
 krb5-devel                                                x86_64                            1.15.1-50.0.1.el7                             ol7_latest                                   272 k
 libcom_err-devel                                          x86_64                            1.45.4-3.0.5.el7                              ol7_UEKR6                                     34 k
 libkadm5                                                  x86_64                            1.15.1-50.0.1.el7                             ol7_latest                                   179 k
 libselinux-devel                                          x86_64                            2.5-15.el7                                    ol7_latest                                   186 k
 libsepol-devel                                            x86_64                            2.5-10.el7                                    ol7_latest                                    76 k
 libverto-devel                                            x86_64                            0.2.5-4.el7                                   ol7_latest                                    11 k
 mysql-community-client-plugins                            x86_64                            8.0.27-1.el7                                  mysql80-community                            5.7 M
 mysql-community-common                                    x86_64                            8.0.27-1.el7                                  mysql80-community                            627 k
 openssl-devel                                             x86_64                            1:1.0.2k-22.el7_9                             ol7_latest                                   1.5 M
 pcre-devel                                                x86_64                            8.32-17.el7                                   ol7_latest                                   479 k
 zlib-devel                                                x86_64                            1.2.7-19.el7_9                                ol7_latest                                    50 k
Updating for dependencies:
 e2fsprogs                                                 x86_64                            1.45.4-3.0.5.el7                              ol7_UEKR6                                    1.0 M
 e2fsprogs-libs                                            x86_64                            1.45.4-3.0.5.el7                              ol7_UEKR6                                    222 k
 krb5-libs                                                 x86_64                            1.15.1-50.0.1.el7                             ol7_latest                                   809 k
 libcom_err                                                x86_64                            1.45.4-3.0.5.el7                              ol7_UEKR6                                     44 k
 libss                                                     x86_64                            1.45.4-3.0.5.el7                              ol7_UEKR6                                     48 k
 openssl                                                   x86_64                            1:1.0.2k-22.el7_9                             ol7_latest                                   493 k
 openssl-libs                                              x86_64                            1:1.0.2k-22.el7_9                             ol7_latest                                   1.2 M
 zlib                                                      x86_64                            1.2.7-19.el7_9                                ol7_latest                                    89 k

Transaction Summary
==============================================================================================================================================================================================
Install  3 Packages (+12 Dependent packages)
Upgrade             (  8 Dependent packages)

Total download size: 26 M
Downloading packages:
Delta RPMs disabled because /usr/bin/applydeltarpm not installed.
Installed:
  mysql-community-devel.x86_64 0:8.0.27-1.el7                  mysql-community-libs.x86_64 0:8.0.27-1.el7                  mysql-community-libs-compat.x86_64 0:8.0.27-1.el7

Dependency Installed:
  keyutils-libs-devel.x86_64 0:1.5.8-3.el7       krb5-devel.x86_64 0:1.15.1-50.0.1.el7    libcom_err-devel.x86_64 0:1.45.4-3.0.5.el7   libkadm5.x86_64 0:1.15.1-50.0.1.el7
  libselinux-devel.x86_64 0:2.5-15.el7           libsepol-devel.x86_64 0:2.5-10.el7       libverto-devel.x86_64 0:0.2.5-4.el7          mysql-community-client-plugins.x86_64 0:8.0.27-1.el7
  mysql-community-common.x86_64 0:8.0.27-1.el7   openssl-devel.x86_64 1:1.0.2k-22.el7_9   pcre-devel.x86_64 0:8.32-17.el7              zlib-devel.x86_64 0:1.2.7-19.el7_9

Dependency Updated:
  e2fsprogs.x86_64 0:1.45.4-3.0.5.el7  e2fsprogs-libs.x86_64 0:1.45.4-3.0.5.el7  krb5-libs.x86_64 0:1.15.1-50.0.1.el7  libcom_err.x86_64 0:1.45.4-3.0.5.el7  libss.x86_64 0:1.45.4-3.0.5.el7
  openssl.x86_64 1:1.0.2k-22.el7_9     openssl-libs.x86_64 1:1.0.2k-22.el7_9     zlib.x86_64 0:1.2.7-19.el7_9

Replaced:
  mariadb-libs.x86_64 1:5.5.68-1.el7

Complete!
[root@test-machine02 mysql_fdw-REL-2_6_1]# 
[root@test-machine02 mysql_fdw-REL-2_6_1]#  whereis mysql.h
mysql: /usr/include/mysql
[root@test-machine02 mysql_fdw-REL-2_6_1]# 


Step 5. Create Softlink in /usr/include directory: After installation is done. We will try to compile code again using make command. But again command filed with error “fatal error: mysql.h: No such file or directory“. But we know file exist in folder /usr/include/mysql. So to fix the missing file issues will create a softlink for mysql.h using ln -s command. Now make command failed due to error “fatal error: field_types.h: No such file or directory“. But file field_types.h also exist in /usr/include/mysql. So to fix all missing file issues will create softlink for all files under location : /usr/include.

[root@test-machine02 mysql_fdw-REL-2_6_1]#
[root@test-machine02 mysql_fdw-REL-2_6_1]# make USE_PGXS=1
gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -g -pipe -Wall -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector-strong --param=ssp-buffer-size=4 -grecord-gcc-switches   -m64 -mtune=generic -fPIC -I/usr/local/mysql/include -D _MYSQL_LIBNAME=\"libmysqlclient.so\" -I. -I./ -I/usr/pgsql-13/include/server -I/usr/pgsql-13/include/internal  -D_GNU_SOURCE -I/usr/include/libxml2  -I/usr/include  -c -o connection.o connection.c
In file included from connection.c:21:0:
mysql_fdw.h:21:19: fatal error: mysql.h: No such file or directory
 #include <mysql.h>
                   ^
compilation terminated.
make: *** [connection.o] Error 1
[root@test-machine02 mysql_fdw-REL-2_6_1]#

[root@test-machine02 ~]#
[root@test-machine02 ~]# cd /usr/include/mysql/
[root@test-machine02 mysql]#
[root@test-machine02 mysql]# ls -l mysql.h
-rw-r--r--. 1 root root 32959 Sep 28 14:46 mysql.h
[root@test-machine02 mysql]#
[root@test-machine02 mysql]# ls -ltr
total 396
-rw-r--r--. 1 root root   3554 Sep 28 14:46 mysql_time.h
-rw-r--r--. 1 root root  32959 Sep 28 14:46 mysql.h
-rw-r--r--. 1 root root  37189 Sep 28 14:46 mysql_com.h
-rw-r--r--. 1 root root   2165 Sep 28 14:46 my_list.h
-rw-r--r--. 1 root root   3646 Sep 28 14:46 my_compress.h
-rw-r--r--. 1 root root   4407 Sep 28 14:46 my_command.h
-rw-r--r--. 1 root root   3169 Sep 28 14:46 field_types.h
-rw-r--r--. 1 root root   5647 Sep 28 14:46 errmsg.h
-rw-r--r--. 1 root root   1807 Sep 28 17:07 mysqlx_version.h
-rw-r--r--. 1 root root   4112 Sep 28 17:07 mysqlx_error.h
-rw-r--r--. 1 root root   7205 Sep 28 17:07 mysqlx_ername.h
-rw-r--r--. 1 root root   1099 Sep 28 17:07 mysql_version.h
-rw-r--r--. 1 root root 242754 Sep 28 17:08 mysqld_error.h
drwxr-xr-x. 2 root root     89 Nov 18 11:15 mysql
-rw-r--r--. 1 root root   3827 Nov 18 12:10 udf_registration_types.h
-rw-r--r--. 1 root root   6795 Nov 18 12:10 plugin_auth_common.h
-rw-r--r--. 1 root root   8284 Nov 18 12:10 client_plugin.h
[root@test-machine02 mysql]#

[root@test-machine02 mysql]# cd /usr/include
[root@test-machine02 include]#
[root@test-machine02 include]# ln -s /usr/include/mysql/mysql.h mysql.h
[root@test-machine02 include]#
[root@test-machine02 include]# ls -l mysql.h
lrwxrwxrwx. 1 root root 26 Nov 18 11:31 mysql.h -> /usr/include/mysql/mysql.h
[root@test-machine02 include]#
[root@test-machine02 include]#

[root@test-machine02 mysql_fdw-REL-2_6_1]#
[root@test-machine02 mysql_fdw-REL-2_6_1]# make USE_PGXS=1
gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -g -pipe -Wall -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector-strong --param=ssp-buffer-size=4 -grecord-gcc-switches   -m64 -mtune=generic -fPIC -I/usr/local/mysql/include -D _MYSQL_LIBNAME=\"libmysqlclient.so\" -I. -I./ -I/usr/pgsql-13/include/server -I/usr/pgsql-13/include/internal  -D_GNU_SOURCE -I/usr/include/libxml2  -I/usr/include  -c -o connection.o connection.c
In file included from mysql_fdw.h:21:0,
                 from connection.c:21:
/usr/include/mysql.h:78:25: fatal error: field_types.h: No such file or directory
 #include "field_types.h"
                         ^
compilation terminated.
make: *** [connection.o] Error 1
[root@test-machine02 mysql_fdw-REL-2_6_1]#

[root@test-machine02 include]# pwd
/usr/include
[root@test-machine02 include]#
[root@test-machine02 include]# ln -s /usr/include/mysql/mysql_time.h mysql_time.h
[root@test-machine02 include]# ln -s /usr/include/mysql/mysql.h mysql.h
[root@test-machine02 include]# ln -s /usr/include/mysql/mysql_com.h mysql_com.h
[root@test-machine02 include]# ln -s /usr/include/mysql/my_list.h my_list.h
[root@test-machine02 include]# ln -s /usr/include/mysql/my_compress.h my_compress.h
[root@test-machine02 include]# ln -s /usr/include/mysql/my_command.h my_command.h
[root@test-machine02 include]# ln -s /usr/include/mysql/field_types.h field_types.h
[root@test-machine02 include]# ln -s /usr/include/mysql/errmsg.h errmsg.h
[root@test-machine02 include]# ln -s /usr/include/mysql/mysqlx_version.h mysqlx_version.h
[root@test-machine02 include]# ln -s /usr/include/mysql/mysqlx_error.h mysqlx_error.h
[root@test-machine02 include]# ln -s /usr/include/mysql/mysqlx_ername.h mysqlx_ername.h
[root@test-machine02 include]# ln -s /usr/include/mysql/mysql_version.h mysql_version.h
[root@test-machine02 include]# ln -s /usr/include/mysql/mysqld_error.h mysqld_error.h
[root@test-machine02 include]#


Step 6. Copy files from /usr/include/mysql/mysql into /usr/include/mysql/: After softlinks are created we tried to compile code again using same make command. But again command failed with new error “fatal error: mysql/udf_registration_types.h“. But this file exist in folder: /usr/include/mysql/mysql. So like Step 5 tried to create softlink for udf_registration_types.h under location: /usr/include but doesn’t fixed issue. Actually its looking for file under location: /usr/include/mysql. So lets copy all files from /usr/include/mysql/mysql to /usr/include/mysql.

[root@test-machine02 mysql_fdw-REL-2_6_1]#
[root@test-machine02 mysql_fdw-REL-2_6_1]# make USE_PGXS=1
gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -g -pipe -Wall -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector-strong --param=ssp-buffer-size=4 -grecord-gcc-switches   -m64 -mtune=generic -fPIC -I/usr/local/mysql/include -D _MYSQL_LIBNAME=\"libmysqlclient.so\" -I. -I./ -I/usr/pgsql-13/include/server -I/usr/pgsql-13/include/internal  -D_GNU_SOURCE -I/usr/include/libxml2  -I/usr/include  -c -o connection.o connection.c
In file included from /usr/include/mysql.h:80:0,
                 from mysql_fdw.h:21,
                 from connection.c:21:
/usr/include/mysql_com.h:1111:42: fatal error: mysql/udf_registration_types.h: No such file or directory
 #include "mysql/udf_registration_types.h"
                                          ^
compilation terminated.
make: *** [connection.o] Error 1
[root@test-machine02 mysql_fdw-REL-2_6_1]#

[postgres@test-machine02 ~]$ cd /usr/include/mysql/mysql
[postgres@test-machine02 mysql]$ ls -ltr
total 24
-rw-r--r--. 1 root root 3827 Sep 28 14:46 udf_registration_types.h
-rw-r--r--. 1 root root 6795 Sep 28 14:46 plugin_auth_common.h
-rw-r--r--. 1 root root 8284 Sep 28 14:46 client_plugin.h
[postgres@test-machine02 mysql]$
[postgres@test-machine02 mysql]$

[root@test-machine02 ~]#
[root@test-machine02 ~]# cd /usr/include
[root@test-machine02 include]#
[root@test-machine02 include]#
[root@test-machine02 include]# ln -s /usr/include/mysql/mysql/udf_registration_types.h udf_registration_types.h
[root@test-machine02 include]#
[root@test-machine02 include]#

[root@test-machine02 mysql_fdw-REL-2_6_1]#
[root@test-machine02 mysql_fdw-REL-2_6_1]#  make USE_PGXS=1
gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -g -pipe -Wall -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector-strong --param=ssp-buffer-size=4 -grecord-gcc-switches   -m64 -mtune=generic -fPIC -I/usr/local/mysql/include -D _MYSQL_LIBNAME=\"libmysqlclient.so\" -I. -I./ -I/usr/pgsql-13/include/server -I/usr/pgsql-13/include/internal  -D_GNU_SOURCE -I/usr/include/libxml2  -I/usr/include  -c -o connection.o connection.c
In file included from /usr/include/mysql.h:80:0,
                 from mysql_fdw.h:21,
                 from connection.c:21:
/usr/include/mysql_com.h:1111:42: fatal error: mysql/udf_registration_types.h: No such file or directory
 #include "mysql/udf_registration_types.h"
                                          ^
compilation terminated.
make: *** [connection.o] Error 1
[root@test-machine02 mysql_fdw-REL-2_6_1]#

[root@test-machine02 mysql]# pwd
/usr/include/mysql/mysql
[root@test-machine02 mysql]# ls
client_plugin.h  plugin_auth_common.h  udf_registration_types.h
[root@test-machine02 mysql]# cp * /usr/include/mysql
[root@test-machine02 mysql]#
[root@test-machine02 mysql]#
[root@test-machine02 mysql]# cd /usr/include/mysql
[root@test-machine02 mysql]#
[root@test-machine02 mysql]# pwd
/usr/include/mysql
[root@test-machine02 mysql]# ls -ltr
total 396
-rw-r--r--. 1 root root   3554 Sep 28 14:46 mysql_time.h
-rw-r--r--. 1 root root  32959 Sep 28 14:46 mysql.h
-rw-r--r--. 1 root root  37189 Sep 28 14:46 mysql_com.h
-rw-r--r--. 1 root root   2165 Sep 28 14:46 my_list.h
-rw-r--r--. 1 root root   3646 Sep 28 14:46 my_compress.h
-rw-r--r--. 1 root root   4407 Sep 28 14:46 my_command.h
-rw-r--r--. 1 root root   3169 Sep 28 14:46 field_types.h
-rw-r--r--. 1 root root   5647 Sep 28 14:46 errmsg.h
-rw-r--r--. 1 root root   1807 Sep 28 17:07 mysqlx_version.h
-rw-r--r--. 1 root root   4112 Sep 28 17:07 mysqlx_error.h
-rw-r--r--. 1 root root   7205 Sep 28 17:07 mysqlx_ername.h
-rw-r--r--. 1 root root   1099 Sep 28 17:07 mysql_version.h
-rw-r--r--. 1 root root 242754 Sep 28 17:08 mysqld_error.h
drwxr-xr-x. 2 root root     89 Nov 18 11:15 mysql
-rw-r--r--. 1 root root   3827 Nov 18 12:10 udf_registration_types.h
-rw-r--r--. 1 root root   6795 Nov 18 12:10 plugin_auth_common.h
-rw-r--r--. 1 root root   8284 Nov 18 12:10 client_plugin.h
[root@test-machine02 mysql]#

Step 7. Compile mysql_fdw using make and make install command: After all the above steps are done. Let’s try to compile code again using make command and this time all compiled successfully. The next step is to install a foreign data wrapper using make install command.

[root@test-machine02 mysql_fdw-REL-2_6_1]#
[root@test-machine02 mysql_fdw-REL-2_6_1]#
[root@test-machine02 mysql_fdw-REL-2_6_1]# make USE_PGXS=1
gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -g -pipe -Wall -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector-strong --param=ssp-buffer-size=4 -grecord-gcc-switches   -m64 -mtune=generic -fPIC -I/usr/local/mysql/include -D _MYSQL_LIBNAME=\"libmysqlclient.so\" -I. -I./ -I/usr/pgsql-13/include/server -I/usr/pgsql-13/include/internal  -D_GNU_SOURCE -I/usr/include/libxml2  -I/usr/include  -c -o connection.o connection.c
gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -g -pipe -Wall -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector-strong --param=ssp-buffer-size=4 -grecord-gcc-switches   -m64 -mtune=generic -fPIC -I/usr/local/mysql/include -D _MYSQL_LIBNAME=\"libmysqlclient.so\" -I. -I./ -I/usr/pgsql-13/include/server -I/usr/pgsql-13/include/internal  -D_GNU_SOURCE -I/usr/include/libxml2  -I/usr/include  -c -o option.o option.c
gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -g -pipe -Wall -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector-strong --param=ssp-buffer-size=4 -grecord-gcc-switches   -m64 -mtune=generic -fPIC -I/usr/local/mysql/include -D _MYSQL_LIBNAME=\"libmysqlclient.so\" -I. -I./ -I/usr/pgsql-13/include/server -I/usr/pgsql-13/include/internal  -D_GNU_SOURCE -I/usr/include/libxml2  -I/usr/include  -c -o deparse.o deparse.c
gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -g -pipe -Wall -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector-strong --param=ssp-buffer-size=4 -grecord-gcc-switches   -m64 -mtune=generic -fPIC -I/usr/local/mysql/include -D _MYSQL_LIBNAME=\"libmysqlclient.so\" -I. -I./ -I/usr/pgsql-13/include/server -I/usr/pgsql-13/include/internal  -D_GNU_SOURCE -I/usr/include/libxml2  -I/usr/include  -c -o mysql_query.o mysql_query.c
gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -g -pipe -Wall -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector-strong --param=ssp-buffer-size=4 -grecord-gcc-switches   -m64 -mtune=generic -fPIC -I/usr/local/mysql/include -D _MYSQL_LIBNAME=\"libmysqlclient.so\" -I. -I./ -I/usr/pgsql-13/include/server -I/usr/pgsql-13/include/internal  -D_GNU_SOURCE -I/usr/include/libxml2  -I/usr/include  -c -o mysql_fdw.o mysql_fdw.c
gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -g -pipe -Wall -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector-strong --param=ssp-buffer-size=4 -grecord-gcc-switches   -m64 -mtune=generic -fPIC -shared -o mysql_fdw.so connection.o option.o deparse.o mysql_query.o mysql_fdw.o -L/usr/pgsql-13/lib   -L/usr/lib64/llvm5.0/lib  -L/usr/lib64 -Wl,--as-needed -Wl,-rpath,'/usr/pgsql-13/lib',--enable-new-dtags
/opt/rh/llvm-toolset-7/root/usr/bin/clang -Wno-ignored-attributes -fno-strict-aliasing -fwrapv -O2  -I/usr/local/mysql/include -D _MYSQL_LIBNAME=\"libmysqlclient.so\" -I. -I./ -I/usr/pgsql-13/include/server -I/usr/pgsql-13/include/internal  -D_GNU_SOURCE -I/usr/include/libxml2  -I/usr/include -flto=thin -emit-llvm -c -o connection.bc connection.c
/opt/rh/llvm-toolset-7/root/usr/bin/clang -Wno-ignored-attributes -fno-strict-aliasing -fwrapv -O2  -I/usr/local/mysql/include -D _MYSQL_LIBNAME=\"libmysqlclient.so\" -I. -I./ -I/usr/pgsql-13/include/server -I/usr/pgsql-13/include/internal  -D_GNU_SOURCE -I/usr/include/libxml2  -I/usr/include -flto=thin -emit-llvm -c -o option.bc option.c
/opt/rh/llvm-toolset-7/root/usr/bin/clang -Wno-ignored-attributes -fno-strict-aliasing -fwrapv -O2  -I/usr/local/mysql/include -D _MYSQL_LIBNAME=\"libmysqlclient.so\" -I. -I./ -I/usr/pgsql-13/include/server -I/usr/pgsql-13/include/internal  -D_GNU_SOURCE -I/usr/include/libxml2  -I/usr/include -flto=thin -emit-llvm -c -o deparse.bc deparse.c
/opt/rh/llvm-toolset-7/root/usr/bin/clang -Wno-ignored-attributes -fno-strict-aliasing -fwrapv -O2  -I/usr/local/mysql/include -D _MYSQL_LIBNAME=\"libmysqlclient.so\" -I. -I./ -I/usr/pgsql-13/include/server -I/usr/pgsql-13/include/internal  -D_GNU_SOURCE -I/usr/include/libxml2  -I/usr/include -flto=thin -emit-llvm -c -o mysql_query.bc mysql_query.c
/opt/rh/llvm-toolset-7/root/usr/bin/clang -Wno-ignored-attributes -fno-strict-aliasing -fwrapv -O2  -I/usr/local/mysql/include -D _MYSQL_LIBNAME=\"libmysqlclient.so\" -I. -I./ -I/usr/pgsql-13/include/server -I/usr/pgsql-13/include/internal  -D_GNU_SOURCE -I/usr/include/libxml2  -I/usr/include -flto=thin -emit-llvm -c -o mysql_fdw.bc mysql_fdw.c
[root@test-machine02 mysql_fdw-REL-2_6_1]#
[root@test-machine02 mysql_fdw-REL-2_6_1]#

[root@test-machine02 mysql_fdw-REL-2_6_1]#
[root@test-machine02 mysql_fdw-REL-2_6_1]# make USE_PGXS=1 install
/bin/mkdir -p '/usr/pgsql-13/lib'
/bin/mkdir -p '/usr/pgsql-13/share/extension'
/bin/mkdir -p '/usr/pgsql-13/share/extension'
/bin/install -c -m 755  mysql_fdw.so '/usr/pgsql-13/lib/mysql_fdw.so'
/bin/install -c -m 644 .//mysql_fdw.control '/usr/pgsql-13/share/extension/'
/bin/install -c -m 644 .//mysql_fdw--1.0.sql .//mysql_fdw--1.1.sql .//mysql_fdw--1.0--1.1.sql  '/usr/pgsql-13/share/extension/'
/bin/mkdir -p '/usr/pgsql-13/lib/bitcode/mysql_fdw'
/bin/mkdir -p '/usr/pgsql-13/lib/bitcode'/mysql_fdw/
/bin/install -c -m 644 connection.bc '/usr/pgsql-13/lib/bitcode'/mysql_fdw/./
/bin/install -c -m 644 option.bc '/usr/pgsql-13/lib/bitcode'/mysql_fdw/./
/bin/install -c -m 644 deparse.bc '/usr/pgsql-13/lib/bitcode'/mysql_fdw/./
/bin/install -c -m 644 mysql_query.bc '/usr/pgsql-13/lib/bitcode'/mysql_fdw/./
/bin/install -c -m 644 mysql_fdw.bc '/usr/pgsql-13/lib/bitcode'/mysql_fdw/./
cd '/usr/pgsql-13/lib/bitcode' && /usr/lib64/llvm5.0/bin/llvm-lto -thinlto -thinlto-action=thinlink -o mysql_fdw.index.bc mysql_fdw/connection.bc mysql_fdw/option.bc mysql_fdw/deparse.bc mysql_fdw/mysql_query.bc mysql_fdw/mysql_fdw.bc
[root@test-machine02 mysql_fdw-REL-2_6_1]#


Step 8. Create the MYSQL_FDW extension: Once all the above steps are done. We are ready to create mysql_fdw extension using create extension command.  Please note all below commands we are executing in DB: local_db as we want to avoid using default postgres DB. Use psql command \dx or query view pg_extension to list all installed extensions.

[postgres@test-machine02 ~]$
[postgres@test-machine02 ~]$ psql local_db
psql (13.4)
Type "help" for help.

local_db=# CREATE EXTENSION mysql_fdw;
CREATE EXTENSION
local_db=#
local_db=# \dx
                             List of installed extensions
    Name    | Version |   Schema   |                   Description
------------+---------+------------+--------------------------------------------------
 mysql_fdw  | 1.1     | public     | Foreign data wrapper for querying a MySQL server
 oracle_fdw | 1.2     | public     | foreign data wrapper for Oracle access
 plpgsql    | 1.0     | pg_catalog | PL/pgSQL procedural language
(3 rows)

local_db=#
local_db=# select * from pg_extension;
  oid  |  extname   | extowner | extnamespace | extrelocatable | extversion | extconfig | extcondition
-------+------------+----------+--------------+----------------+------------+-----------+--------------
 14161 | plpgsql    |       10 |           11 | f              | 1.0        |           |
 34608 | oracle_fdw |       10 |         2200 | t              | 1.2        |           |
 44074 | mysql_fdw  |       10 |         2200 | t              | 1.1        |           |
(3 rows)

local_db=#


Step 9. Create the Foreign Server: Now we can create a server definition. This foreign server is created using the connection details of the remote mysql DB running on host “test-machine01“. Let’s name the foreign server as “mysql_server “. Use \des to list foreign servers or query view pg_foreign_server.

local_db=#
local_db=# create server mysql_server foreign data wrapper mysql_fdw options (host 'test-machine01', port '3306');
CREATE SERVER
local_db=#
local_db=#  \des+
                                                              List of foreign servers
         Name         |  Owner   | Foreign-data wrapper |  Access privileges  | Type | Version |             FDW options              | Description
----------------------+----------+----------------------+---------------------+------+---------+--------------------------------------+-------------
 mysql_server         | postgres | mysql_fdw            |                     |      |         | (host 'test-machine01', port '3306') |
 testmachine01_oracle | postgres | oracle_fdw           | postgres=U/postgres+|      |         | (dbserver '//test-machine01/test01') |
                      |          |                      | app_user=U/postgres |      |         |                                      |
(2 rows)

local_db=#
local_db=#  select * from pg_foreign_server;
  oid  |       srvname        | srvowner | srvfdw | srvtype | srvversion |                  srvacl                   |             srvoptions
-------+----------------------+----------+--------+---------+------------+-------------------------------------------+------------------------------------
 34615 | testmachine01_oracle |       10 |  34614 |         |            | {postgres=U/postgres,app_user=U/postgres} | {dbserver=//test-machine01/test01}
 44079 | mysql_server         |       10 |  44077 |         |            |                                           | {host=test-machine01,port=3306}
(2 rows)

local_db=#


Step 10. Create User Mapping: Create a mapping on the PostgreSQL local databaselocal_db user app_user to remote mysql databaseremote_db user fdw_user.

local_db=#
local_db=#
local_db=# create user mapping for app_user server mysql_server options (username 'fdw_user', password 'Root@1234');
CREATE USER MAPPING
local_db=#
local_db=#  select * from pg_user_mappings;
 umid  | srvid |       srvname        | umuser | usename  |               umoptions
-------+-------+----------------------+--------+----------+----------------------------------------
 34616 | 34615 | testmachine01_oracle |  34597 | app_user | {user=fdw_user,password=secret}
 44080 | 44079 | mysql_server         |  34597 | app_user | {username=fdw_user,password=Root@1234}
(2 rows)

local_db=#


Step 11. Grant the Local User Access to the Foreign Server: Grant our local user app_user access to the foreign server  mysql_server. Without the below permission, you will receive the error “ERROR: permission denied for foreign server mysql_server if you try to create Foreign Table as we are performing in Step 12.

local_db=#
local_db=# grant usage on foreign server mysql_server to app_user;
GRANT
local_db=#


Step 12. Create foreign Table: Create a foreign table in the local_db using our app user app_user with the same structure as the remote mysql table, but with OPTIONS specifying target schema_name and table_name. Now we can use the foreign table in local_db to access remote_db table employee.

[postgres@test-machine02 ~]$
[postgres@test-machine02 ~]$ psql -h test-machine02 -U app_user local_db
Password for user app_user:
psql (13.4)
Type "help" for help.

local_db=>
local_db=> create foreign table employee_mysql
local_db-> (id int, first_name varchar(20), last_name varchar(20))
local_db-> server mysql_server
local_db-> options (dbname 'remote_db', table_name 'employee');
CREATE FOREIGN TABLE
local_db=>
local_db=>
local_db=>
local_db=>
local_db=> select * from employee_mysql;
 id | first_name | last_name
----+------------+-----------
  1 | jobin      | augustine
  2 | avinash    | vallarapu
  3 | fernando   | camargos
(3 rows)

local_db=>


local_db=>  \dE+
                                     List of relations
 Schema |      Name       |     Type      |  Owner   | Persistence |  Size   | Description
--------+-----------------+---------------+----------+-------------+---------+-------------
 public | employee_mysql  | foreign table | app_user | permanent   | 0 bytes |
 public | employee_oracle | foreign table | app_user | permanent   | 0 bytes |
(2 rows)

local_db=>
local_db=> select * from information_schema.foreign_tables;
 foreign_table_catalog | foreign_table_schema | foreign_table_name | foreign_server_catalog | foreign_server_name
-----------------------+----------------------+--------------------+------------------------+----------------------
 local_db              | public               | employee_oracle    | local_db               | testmachine01_oracle
 local_db              | public               | employee_mysql     | local_db               | mysql_server
(2 rows)

local_db=>


Reference :
https://github.com/EnterpriseDB/mysql_fdw

This document is just for learning purpose and always validate in the LAB environment first before applying in the LIVE environment.


Hope so you like this article
!
Please share your valuable feedback/comments/subscribe and follow us below and don’t forget to click on the bell icon to get the most recent update. 
Click here to understand more about our pursuit.


Related Articles

Loading

How useful was this post?

Click on a star to rate it!

Average rating / 5. Vote count:

No votes so far! Be the first to rate this post.

As you found this post useful...

Follow us on social media!

We are sorry that this post was not useful for you!

Let us improve this post!

Tell us how we can improve this post?

Jamsher Khan

Hello and welcome to DBsGuru,I’m Jamsher Khan working as Senior Oracle DBA based in KSA-Jeddah, I have working experience in Oracle DBA, SQL Server, MySql, PostgreSQL, Linux, Golden Gate, ODA.Thanks for the visits!Share Learn Grow!