MySQL FEDERATED 引擎實戰：跨實例、跨伺服器透明訪問遠程表

FEDERATED 引擎簡介

類似 Oracle DB Link，FEDERATED 讓本地建立「映射表」，透明訪問遠程 MySQL 實例的表。本地僅存 .frm 定義檔，無資料檔，所有操作轉發至遠程執行。

適用場景

• 跨資料庫實例查詢/寫入（不想搭建複寫）
• 微服務間共享參考資料（字典表、配置表）
• 異構查詢聯邦（MySQL 間）

限制與注意

• 遠程表必須存在，結構需一致（或相容）
• 不支援事務（本地不參與遠程事務）
• 效能依賴網路延遲，大量資料不適用
• 遠程 DDL 不同步，需手動同步結構變更
• 5.1+ 預設編譯但預設禁用，需啟用

啟用 FEDERATED

# my.cnf [mysqld] 區段
federated

# 重啟驗證
SHOW ENGINES;
-- FEDERATED | YES | Federated MySQL storage engine

實戰演示：本地 3306 映射遠程 3406

遠程端（3406）準備

# 建庫建表
CREATE DATABASE tempdb;
USE tempdb;
CREATE TABLE tb_engine AS
SELECT engine, support, comment FROM information_schema.engines;

# 建立遠程訪問帳號
GRANT ALL PRIVILEGES ON tempdb.* TO 'remote_user'@'192.168.1.131' IDENTIFIED BY 'xxx';
FLUSH PRIVILEGES;

# 取得建表語句供本地複製結構
SHOW CREATE TABLE tb_engine\G

本地端（3306）建立映射表

USE test;
CREATE TABLE `federated_engine` (
    `engine` VARCHAR(64) NOT NULL DEFAULT '',
    `support` VARCHAR(8) NOT NULL DEFAULT '',
    `comment` VARCHAR(80) NOT NULL DEFAULT ''
) ENGINE=FEDERATED DEFAULT CHARSET=utf8
CONNECTION='mysql://remote_user:xxx@192.168.1.131:3406/tempdb/tb_engine';

驗證操作

-- 查詢（實際在遠程執行）
SELECT * FROM federated_engine LIMIT 2;

-- 更新（遠程執行 UPDATE）
UPDATE federated_engine SET support='NO' WHERE engine='CSV';
SELECT * FROM federated_engine WHERE engine='CSV';
-- support 已變更為 NO

-- 本地僅有 .frm
ls -l /var/lib/mysql/test/federated_engine.frm

CONNECTION 字串完整語法

scheme://user_name[:password]@host_name[:port_num]/db_name/tbl_name

scheme       : 僅支援 mysql
user_name    : 遠程已建立的用戶
password     : 可選，明文儲存於 .frm（安全風險）
host_name    : 遠程 IP 或主機名
port_num     : 可選，預設 3306
db_name      : 遠程資料庫
tbl_name     : 遠程表名（可與本地表名不同）

範例

CONNECTION='mysql://user:pass@10.0.0.1:3306/db/tbl'
CONNECTION='mysql://user@10.0.0.1/db/tbl'
CONNECTION='mysql://user:pass@10.0.0.1/db/tbl'

進階用法與替代方案

• FEDERATEDX：MariaDB/Percona 改良版，支援事務、更好效能、連線池
• MySQL Router / ProxySQL：中間層路由，應用無感知
• 資料同步：CDC (Debezium/Maxwell) + Kafka → 遠程庫，查詢本地副本
• 分庫分表中間件：ShardingSphere、MyCat 透明路由

安全建議

• 專用只讀/最小權限帳號
• 網路層限制來源 IP（安全組/防火牆）
• 啟用 SSL：CONNECTION='mysql://user:pass@host:port/db/tbl?ssl_ca=...&ssl_cert=...&ssl_key=...' (需驗證版本支援)
• 避免在 .frm 明文存密碼，考慮 mysql_config_editor 或應用層組裝連線字串

小結

FEDERATED 為跨實例透明訪問提供輕量方案：ENGINE=FEDERATED CONNECTION='mysql://user:pass@host:port/db/tbl'。適合小表、低頻、參考資料場景。大數據、高並發、事務一致性需求請用複寫、CDC 同步或中間件路由。