PostgreSQL借助mysql_fdw连通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-devel2) 解压编译安装unzip /opt/mysql_fdw-REL-2_9_3.zip -d /home/postgrescd myql_fdw-REL-2_9_3make USE_PGXS=1make 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 postgresSERVER mysql_serverOPTIONS (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_serverOPTIONS (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 someschemaFROM SERVER mysql_serverINTO 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;

总结

mysql_fdw 是一个极其便捷的“数据桥梁”工具。 它让跨数据库的数据访问变得像访问本地表一样自然，特别适合混合架构下的数据查询、迁移过渡期以及临时的数据整合需求。