大家好， 这次和大家分享的是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
^
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 是不支持的