PostgreSQL通过mysql_fdw连通MySQL实战_Mysql

mysql_fdw 就像是 postgresql 和 mysql 数据库之间的一座双向桥梁。它允许你在 postgresql 中创建一个“外部表”，这个表直接映射到远程 mysql 数据库中的真实表。之后，你就可以像操作本地 postgresql 表一样，对这个外部表进行查询（select）、插入（insert）、更新（update）和删除（delete）操作，而 mysql_fdw 会在背后自动完成所有与 mysql 数据库的通信和数据转换工作。

1. 安装插件

你需要在 postgresql 所在的服务器上安装 mysql_fdw，它依赖 mysql 的客户端开发库。从github或pgxn网站下载mysql_fdw源码包。

mysql_fdw github下载地址：

https://github.com/enterprisedb/mysql_fdw

1）使用yum安装依赖和开发包
yum install postgresql-server-devel mysql-devel
2) 解压编译安装
unzip /opt/mysql_fdw-rel-2_9_3.zip -d /home/postgres
cd myql_fdw-rel-2_9_3
make use_pgxs=1
make use_pgxs=1 install

安装成功后，你需要重启 postgresql服务，
或者至少让正在使用的数据库重新加载共享库。

2. 启用插件服务

以下是一个简单的使用示例，所有命令在 poatgresql 中执行：

1)加载扩展插件
create extension mysql_fdw;
2)创建服务器对象
create server mysql_server 
foreign data wrapper mysql_fdw 
options (host '192.168.3.11', port '3306');
3) 授权
grant usage on foreign 
server mysql_server to postgres;

3. 建立用户映射

create user mapping for postgres
server mysql_server
options (username 'root', password 'root@a123');

4. 处理数据

桥梁已经接通，最后一步就是在 postgresql 这端创建一个“入口”，也就是外部表（foreign table）。这张表的列名和数据类型应该与 mysql 中的源表相对应。

create foreign table warehouse (
warehouse_id int,
warehouse_name text,
warehouse_created timestamp
)server mysql_server
options (dbname 'aidb', table_name 'warehouse');

这里要注意，mysql_fdw 的一个核心限制是为了确保能准确追踪和操作远程表中的特定行，它要求外部表定义中的第一列，必须对应远程mysql表中的一个唯一列（如主键或具有唯一约束的列）。

接下里我们就可以在postgresql 中处理相关的数据了

1）插入数据 
insert into warehouse values (1, 'ups', current_date);
insert into warehouse values (2, 'tv', current_date);
insert into warehouse values (3, 'table', current_date);
2）删除更新数据
delete from warehouse where warehouse_id = 3;
update warehouse set warehouse_name = 'ups_new' where warehouse_id = 1;
3）查看 执行计划
explain verbose 
select warehouse_id, warehouse_name 
from warehouse 
where warehouse_name like 'tv' limit 1;

如果需要映射一个模式（schema）下的多张表，使用 import foreign schema命令会非常高效，它可以自动为你创建所有表的外部表定义。

import foreign schema someschema
from server mysql_server
into public;

5.运维管理

当通过 mysql_fdw 连接 mysql 进行运维时，可以查询以下 postgresql 系统表和目录视图来查看和管理外部对象

查看所有外部表的定义
select * from pg_foreign_table;
以标准 sql 视角查看外部表
select * from 
information_schema.foreign_tables;
查看 mysql_fdw 服务器的连接配置
select * from pg_foreign_server;
查看连接到 mysql 所用的用户名映射
select * from pg_user_mappings;