在多數(shù)據(jù)源并存的企業(yè)環(huán)境中，常常需要在不同數(shù)據(jù)庫之間進行聯(lián)合分析或數(shù)據(jù)遷移。PostgreSQL 作為功能強大的開源關系型數(shù)據(jù)庫，提供了 Foreign Data Wrapper（FDW，外部數(shù)據(jù)包裝器）機制，允許它像訪問本地表一樣查詢遠程數(shù)據(jù)庫。

本文將手把手帶你配置 mysql_fdw，實現(xiàn) PostgreSQL 對 MySQL 表的透明讀寫訪問，真正做到“一處查詢，跨庫聯(lián)動”。

一、什么是 mysql_fdw？

mysql_fdw 是一個 PostgreSQL 的 FDW 擴展，由 EnterpriseDB 開發(fā)并開源。它通過 MySQL 客戶端庫（libmysqlclient）連接遠程 MySQL 實例，并將遠程表映射為 PostgreSQL 中的“外部表”（Foreign Table）。你可以在 PostgreSQL 中直接對這些外部表執(zhí)行 SELECT、INSERT、UPDATE、DELETE 等操作（取決于權限和配置）。

? 適用場景：

• 實時報表聚合（PG + MySQL 聯(lián)合查詢）
• 數(shù)據(jù)遷移過渡期
• 微服務間臨時數(shù)據(jù)打通
• 避免 ETL 中間層，簡化架構

二、環(huán)境準備

前提條件

• PostgreSQL 10+（推薦 12+）
• MySQL 5.7 或 8.0
• 操作系統(tǒng)：Linux（本文以 Ubuntu 22.04 為例）
• 具備 sudo 權限

安裝依賴

# 安裝編譯工具和 PostgreSQL 開發(fā)包
sudo apt update
sudo apt install build-essential postgresql-server-dev-all libmysqlclient-dev git
# 克隆 mysql_fdw 源碼（官方 GitHub）
git clone https://github.com/EnterpriseDB/mysql_fdw.git
cd mysql_fdw

?? 注意：確保 libmysqlclient-dev 版本與目標 MySQL 兼容。若使用 MySQL 8.0，可能需額外處理認證插件（如 caching_sha2_password）。

三、編譯并安裝 mysql_fdw

# 編譯（自動檢測 pg_config）
make
# 安裝到 PostgreSQL 擴展目錄
sudo make install

驗證是否安裝成功：

# 查看 PostgreSQL 的 extension 目錄
pg_config --sharedir
# 應能在 $SHAREDIR/extension/ 下看到 mysql_fdw.control 和 .so 文件

四、在 PostgreSQL 中啟用 mysql_fdw

以 postgres 用戶登錄 psql：

-- 創(chuàng)建擴展（每個需使用的數(shù)據(jù)庫都要執(zhí)行）
CREATE EXTENSION mysql_fdw;

五、配置外部服務器與用戶映射

1. 創(chuàng)建外部服務器（Foreign Server）

CREATE SERVER mysql_server
FOREIGN DATA WRAPPER mysql_fdw
OPTIONS (
    host '192.168.1.100',   -- MySQL 主機 IP
    port '3306'             -- MySQL 端口
);

2. 創(chuàng)建用戶映射（User Mapping）

將 PostgreSQL 用戶映射到 MySQL 的認證憑據(jù)：

CREATE USER MAPPING FOR postgres  -- PostgreSQL 本地用戶
SERVER mysql_server
OPTIONS (
    username 'remote_user',
    password 'secure_password'
);

?? 安全建議：避免在 SQL 中明文寫密碼，可結合 .pgpass 或 Vault 等密鑰管理工具。

六、創(chuàng)建外部表（Foreign Table）

假設 MySQL 中有數(shù)據(jù)庫 sales_db，表 orders 結構如下：

-- MySQL 表結構示例
CREATE TABLE orders (
    id INT PRIMARY KEY,
    customer_name VARCHAR(100),
    amount DECIMAL(10,2),
    created_at DATETIME
);

在 PostgreSQL 中創(chuàng)建對應的外部表：

CREATE FOREIGN TABLE foreign_orders (
    id INTEGER,
    customer_name TEXT,
    amount NUMERIC(10,2),
    created_at TIMESTAMP
)
SERVER mysql_server
OPTIONS (
    dbname 'sales_db',
    table_name 'orders'
);

?? 注意：

• 字段名必須一致（大小寫敏感）
• 類型需兼容（MySQL 的 VARCHAR → PG 的 TEXT，DATETIME → TIMESTAMP）
• 不支持所有 MySQL 特有類型（如 JSON 需測試）

七、實戰(zhàn)查詢與寫入

查詢數(shù)據(jù)

SELECT * FROM foreign_orders WHERE amount > 1000;

聯(lián)合本地表查詢

SELECT u.name, o.amount
FROM local_users u
JOIN foreign_orders o ON u.mysql_order_id = o.id;

寫入操作（需 MySQL 用戶有寫權限）

INSERT INTO foreign_orders (id, customer_name, amount, created_at)
VALUES (1001, 'Alice', 1500.00, NOW());
UPDATE foreign_orders SET amount = 1600 WHERE id = 1001;
DELETE FROM foreign_orders WHERE id = 1001;

?? 警告：寫操作會直接修改 MySQL 數(shù)據(jù)，請謹慎使用！

八、常見問題與排查

1. 連接失敗：could not connect to MySQL

• 檢查 MySQL 是否允許遠程連接（bind-address）
• 確認防火墻開放 3306 端口
• 驗證 MySQL 用戶權限：GRANT SELECT, INSERT... ON sales_db.* TO 'remote_user'@'%'

2. 認證失?。∕ySQL 8.0）

MySQL 8 默認使用 caching_sha2_password，而舊版 libmysqlclient 可能不支持。
解決方案：

• 升級 libmysqlclient-dev 到 8.0+
• 或在 MySQL 中創(chuàng)建兼容用戶：

  CREATE USER 'remote_user'@'%' IDENTIFIED WITH mysql_native_password BY 'password';

3. 性能問題

• 外部表查詢無法使用 PostgreSQL 的索引優(yōu)化
• 復雜 JOIN 可能導致大量數(shù)據(jù)拉取
• 建議：對高頻查詢結果物化（Materialized View）或定期同步

九、替代方案對比

十、總結

通過 mysql_fdw，PostgreSQL 成功打破了與 MySQL 的數(shù)據(jù)孤島。雖然它不適合高并發(fā)寫入或超大規(guī)模分析場景，但在開發(fā)調試、輕量級集成、臨時數(shù)據(jù)橋接等場景中極具價值。

到此這篇關于打通異構數(shù)據(jù)庫：PostgreSQL 通過 mysql_fdw 實現(xiàn) MySQL 透明查詢實戰(zhàn)的文章就介紹到這了,更多相關postgresql mysql表透明查詢內容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持腳本之家！

最新評論