PG 如何通过 FDW 访问 mysql 数据库
大家好， 这次和大家分享的是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
我们下载软件：
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 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.需要配置环境变量
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.编译软件
如果遇到错误：
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 的开发包：
我们继续安装：
/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 创建一个账户测试一下
Query OK, 0 rows affected (0.01 sec)
mysql> database testdb;
ERROR 1007 (HY000): Can’t ‘testdb’; database exists
mysql> database test_db;
Query OK, 1 row affected (0.01 sec)
mysql> use test_db;
Database changed
mysql> timestamp) ;
Query OK, 0 rows affected (0.04 sec)
mysql> ’10.%’;
Query OK, 0 rows affected (0.00 sec)
4.我们登录一下 PG, 创建 mysql_fdw的 extension
2022-06-17 15:52:29.468 CST [67953] ERROR: failed to query:
libmysqlclient.so: cannot directory
locate the library.
create extension mysql_fdw;
ERROR: failed to query:
libmysqlclient.so: cannot directory
HINT: locate the library.
根据提示： 我们需要 export 一下 LD_LIBRARY_PATH
libmysqlclient.so 这个的路径在 /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
include ld.so.conf.d/*.conf
/usr/lib64/mysql/libmysqlclient.so
执行命令： ldconfig 使其生效
我们再次创建：
CREATE EXTENSION
5.我们创建一下 server
CREATE SERVER
6.创建用户映射
CREATE USER MAPPING
7.创建外部映射表
(
warehouse_id int,
warehouse_name text,
warehouse_created timestamp
)
SERVER mysql_server
‘warehouse’);
CREATE FOREIGN TABLE
8.我们去mysql 端插入几条数据，尝试在 postgres 端读取
mysql
Query OK, 1 row affected (0.00 sec)
mysql>
‘TV’, current_date);Query OK, 1 row affected (0.00 sec)
mysql>
‘Table’, current_date);Query OK, 1 row affected (0.00 sec)
PG：
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 的外部表：
1
db3@#71079 UPDATE warehouse set warehouse_name = ‘UPS_NEW’ where warehouse_id = 1;
1
db3@#71079 EXPLAIN VERBOSE SELECT warehouse_id, warehouse_name FROM warehouse WHERE warehouse_name LIKE ‘TV’ limit 1;
QUERY PLAN
—————————————————————————————————————————————-
36)
Output: warehouse_id, warehouse_name
Remote 25
Remote 1
-8683994370260527774
(rows)
mysql 端查询数据：数据已被更新和删除
+————–+—————-+———————+
| 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 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 是不支持的
