大家好， 这次和大家分享的是PG 通过 FDW 访问 mysql 数据库。
熟悉ORACLE的朋友们，都知道可以通过创建DBLINK 来实现不同ORACLE数据库之间的访问。
如果ORACLE想访问sqlserver, mysql, postgres 的话， 可以通过oracle 官方的透明网关（Gateway）来实现。
对于PG来说，如果想访问其他的数据库比如 sqlserver, oracle, mongodb, mysql 可以通过 extension 的方式：
sqlserver_fdw
oracle_fdw
mongodb_fdw
mysql_fdw
具体的每一种数据库的FDW： Foreign Data Wrapper 都需要去下载和编译安装原代码。
我们看看目前由EDB公司维护的 mysql_fdw的使用方式：
我们需要先下载软件： https://github.com/EnterpriseDB/mysql_fdw
我们下载软件：
Jason.ChenTJ@CN-L201098 MINGW64 /d/vpn/tanpopo-win-v0.3.1
$ git clone https://github.com/EnterpriseDB/mysql_fdw.git
Cloning into 'mysql_fdw'...
remote: Enumerating objects: 1141, done.
remote: Counting objects: 100% (367/367), done.
remote: Compressing objects: 100% (73/73), done.
remote: Total 1141 (delta 319), reused 308 (delta 294), pack-reused 774
Receiving objects: 100% (1141/1141), 612.00 KiB | 1.01 MiB/s, done.
Resolving deltas: 100% (816/816), done.
安装：进入文件路径
INFRA [postgres@wqdcsrv3352 contrib]# cd mysql_fdw/ INFRA [postgres@wqdcsrv3352 mysql_fdw]# ls connection.c deparse.c LICENSE META.json mysql_fdw--1.0.sql mysql_fdw.c mysql_fdw.h mysql_query.c option.c sql CONTRIBUTING.md expected Makefile mysql_fdw--1.0--1.1.sql mysql_fdw--1.1.sql mysql_fdw.control mysql_init.sh mysql_query.h README.md
整个安装过程我们完全参考 readme 文件
1.需要配置环境变量
PG_HOME=/opt/postgreSQL/pg15 MYSQL_HOME=/data/postgreSQL/percona8.0/ PATH=$PATH:$HOME/.local/bin:$HOME/bin:$PG_HOME/bin:$MYSQL_HOME:bin export PATH INFRA [postgres@wqdcsrv3352 ~]# mysql --version mysql Ver 8.0.27-18 for Linux on x86_64 (Percona Server (GPL), Release 18, Revision 24801e21b45) INFRA [postgres@wqdcsrv3352 ~]# postgres --version postgres (PostgreSQL) 12.3
2.编译软件
make USE_PGXS=1
如果遇到错误：
In file included from connection.c:20: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
需要安装 mysql 的开发包：
sudo yum install mysql-devel
我们继续安装：
INFRA [postgres@wqdcsrv3352 mysql_fdw]# make USE_PGXS=1 install /bin/mkdir -p '/opt/postgreSQL/pg15/lib/postgresql' /bin/mkdir -p '/opt/postgreSQL/pg15/share/postgresql/extension' /bin/mkdir -p '/opt/postgreSQL/pg15/share/postgresql/extension' /bin/install -c -m 755 mysql_fdw.so '/opt/postgreSQL/pg15/lib/postgresql/mysql_fdw.so' /bin/install -c -m 644 .//mysql_fdw.control '/opt/postgreSQL/pg15/share/postgresql/extension/' /bin/install -c -m 644 .//mysql_fdw--1.0.sql .//mysql_fdw--1.1.sql .//mysql_fdw--1.0--1.1.sql '/opt/postgreSQL/pg15/share/postgresql/extension/'
3.我们来mysql 创建一个账户测试一下
mysql> create user jason@'10.%' identified with mysql_native_password by '123456';
Query OK, 0 rows affected (0.01 sec)
mysql> create database testdb;
ERROR 1007 (HY000): Can't create database 'testdb'; database exists
mysql> create database test_db;
Query OK, 1 row affected (0.01 sec)
mysql> use test_db;
Database changed
mysql> create table warehouse (warehouse_id int primary key,warehouse_name varchar(200),warehouse_created timestamp) ;
Query OK, 0 rows affected (0.04 sec)
mysql> grant all on test_db.warehouse to jason@'10.%';
Query OK, 0 rows affected (0.00 sec)
4.我们登录一下 PG, 创建 mysql_fdw的 extension
db3@127.0.0.1:1992=#67953 create extension mysql_fdw;
2022-06-17 15:52:29.468 CST [67953] ERROR: failed to load the mysql query:
libmysqlclient.so: cannot open shared object file: No such file or directory
2022-06-17 15:52:29.468 CST [67953] HINT: Export LD_LIBRARY_PATH to locate the library.
2022-06-17 15:52:29.468 CST [67953] STATEMENT: create extension mysql_fdw;
ERROR: failed to load the mysql query:
libmysqlclient.so: cannot open shared object file: No such file or directory
HINT: Export LD_LIBRARY_PATH to locate the library.
根据提示： 我们需要 export 一下 LD_LIBRARY_PATH
libmysqlclient.so 这个的路径在 /usr/lib64/mysql/
INFRA [postgres@wqdcsrv3352 lib]# cd /usr/lib64/mysql/ INFRA [postgres@wqdcsrv3352 mysql]# ls libmysqlclient_r.so libmysqlclient.so libmysqlclient.so.18 libmysqlclient.so.18.0.0 mysql_config plugin
解决方法：/etc/ld.so.conf 文件中添加 /usr/lib64/mysql/libmysqlclient.so
INFRA [postgres@wqdcsrv3352 ~]# sudo vi /etc/ld.so.conf include ld.so.conf.d/*.conf /usr/lib64/mysql/libmysqlclient.so
执行命令： ldconfig 使其生效
INFRA [postgres@wqdcsrv3352 ~]# sudo ldconfig
我们再次创建：
db3@127.0.0.1:1992=#71079 create extension mysql_fdw; CREATE EXTENSION
5.我们创建一下 server
db3@127.0.0.1:1992=#71079 CREATE SERVER mysql_server FOREIGN DATA WRAPPER mysql_fdw OPTIONS (host '10.67.38.50', port '3060'); CREATE SERVER
6.创建用户映射
db3@127.0.0.1:1992=#71079 CREATE USER MAPPING FOR postgres SERVER mysql_server OPTIONS (username 'jason', password '123456'); CREATE USER MAPPING
7.创建外部映射表
db3@127.0.0.1:1992=#71079 CREATE FOREIGN TABLE warehouse
db3-# (
db3(# warehouse_id int,
db3(# warehouse_name text,
db3(# warehouse_created timestamp
db3(# )
db3-# SERVER mysql_server
db3-# OPTIONS (dbname 'test_db', table_name 'warehouse');
CREATE FOREIGN TABLE
8.我们去mysql 端插入几条数据，尝试在 postgres 端读取
mysql
mysql> INSERT INTO warehouse values (1, 'UPS', current_date);
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO warehouse values (2, 'TV', current_date);
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO warehouse values (3, 'Table', current_date);
Query OK, 1 row affected (0.00 sec)
PG：
db3@127.0.0.1:1992=#71079 select * from warehouse; warehouse_id | warehouse_name | warehouse_created --------------+----------------+--------------------- 1 | UPS | 2022-06-17 00:00:00 2 | TV | 2022-06-17 00:00:00 3 | Table | 2022-06-17 00:00:00 (3 rows)
目前看是可以查询到数据的。 当然如果我们 user mapping的账号有在mysql 端数据库有权限的话，我们还可以进行更新和删除。
PG 的外部表：
db3@127.0.0.1:1992=#71079 DELETE FROM warehouse where warehouse_id = 3;
DELETE 1
db3@127.0.0.1:1992=#71079 UPDATE warehouse set warehouse_name = 'UPS_NEW' where warehouse_id = 1;
UPDATE 1
db3@127.0.0.1:1992=#71079 EXPLAIN VERBOSE SELECT warehouse_id, warehouse_name FROM warehouse WHERE warehouse_name LIKE 'TV' limit 1;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------
Foreign Scan on public.warehouse (cost=1.00..2.00 rows=1 width=36)
Output: warehouse_id, warehouse_name
Remote server startup cost: 25
Remote query: SELECT `warehouse_id`, `warehouse_name` FROM `test_db`.`warehouse` WHERE ((`warehouse_name` LIKE BINARY 'TV')) LIMIT 1
Query Identifier: -8683994370260527774
(5 rows)
mysql 端查询数据：数据已被更新和删除
mysql> select * from warehouse;
+--------------+----------------+---------------------+
| warehouse_id | warehouse_name | warehouse_created |
+--------------+----------------+---------------------+
| 1 | UPS_NEW | 2022-06-17 00:00:00 |
| 2 | TV | 2022-06-17 00:00:00 |
+--------------+----------------+---------------------+
2 rows in set (0.00 sec)
最后我们再来简单的看一下 mysql_fdw 在 github 上描述的功能加强：
Write-able FDW： 可以更新，删除源端的数据
Connection Pooling： 连接池设置
WHERE clause push-down: 谓词下推到源端
Column push-down： 列下推到源端
Prepared Statement ： Prepared statement的支持
JOIN push-down ： 连接支持下推到源端，不过对于 FULL OUTER, SEMI, and ANTI join 不支持。目前支持的是 INNER and LEFT/RIGHT OUTER joins
AGGREGATE push-down： 聚合操作支持下推： 目前仅仅支持： min, max, sum, avg, and count ，并且 aggregate filters and orders 不支持。
ORDER BY push-down ： 排除支持下推到mysql ,
LIMIT OFFSET push-down: llimit ，offset 分页支持下推到mysql ，但是 OFFSET without LIMIT 是不支持的
