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 Name | Username | Hostname | IP | Port |
| 1 | MySql-remote_db | fdw_user | test-machine01 | 192.168.114.177 | 3306 |
| 2 | PostgreSQL-local_db | app_user | test-machine02 | 192.168.114.176 | 5432 |
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 database: local_db user app_user to remote mysql database: remote_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
- Oracle Critical Database Patch ID for October 2024 along with enabled Download Link
- Oracle Critical Database Patch ID for July 2024 along with enabled Download Link
- Oracle Critical Database Patch ID for April 2024 along with enabled Download Link
- Oracle Critical Database Patch ID for January 2024 along with enabled Download Link
- Oracle Critical Database Patch ID for October 2023 along with enabled Download Link
