MySQL 數據拆分三大流派實戰對比：mysqldump、xtrabackup、Transportable Tablespace

前言

業務增長驅動數據拆分（分庫/分表/遷移），工具選型直接影響停機窗口、數據一致性、運維成本。本文對比三大主流方案——邏輯導出、物理備份+恢復、表空間傳輸的原理、適用場景、操作步驟、優劣勢，助你按需選型。

一、方案對比總覽

二、方案一：mysqldump 邏輯導出（通用、慢、兼容性強）

核心參數

mysqldump -h$host -u$user -p$pass \
  --single-transaction \      -- InnoDB 一致性視圖，無鎖
  --master-data=2 \           -- 記錄 Binlog 位點（從庫同步用）
  --routines --triggers \     -- 存儲過程/觸發器
  --set-gtid-purged=OFF \     -- GTID 模式避免污染
  --quick \                   -- 流式讀取，不緩存全表
  --compress \                -- 網絡壓縮
  db_name tbl1 tbl2 > dump.sql

導入

mysql -h$target -u$user -p$pass db_name < dump.sql
# 或並行導入工具：mydumper/myloader

避坑

• 大表導入極慢：關閉索引 ALTER TABLE t DISABLE KEYS（MyISAM）、分批 INSERT、調大 innodb_buffer_pool_size、innodb_log_file_size
• 字符集不一致導致亂碼：導出導入全鏈路指定 --default-character-set=utf8mb4

三、方案二：xtrabackup 物理備份 + 部分恢復（大數據、同版本）

備份指定表

# 1. 準備表清單文件
echo "db.tbl1" > /tmp/tables.txt
echo "db.tbl2" >> /tmp/tables.txt

# 2. 並行備份（需 xtrabackup 2.4+ / 8.0）
xtrabackup --backup \
  --target-dir=/backup/partial \
  --tables-file=/tmp/tables.txt \
  --parallel=4 \
  --compress \
  --compress-threads=2

恢復到新實例並建立複製

# 1. 恢復備份
xtrabackup --prepare --target-dir=/backup/partial --export

# 2. 啟動新實例（datadir 指向恢復目錄），修改 server_id、port

# 3. 配置複製過濾，僅同步目標表
# my.cnf
replicate-wild-do-table=db.tbl%
# 或
replicate-do-table=db.tbl1
replicate-do-table=db.tbl2

# 4. CHANGE MASTER TO ... 從原主庫同步增量

四、方案三：Transportable Tablespace（TTS）單表秒級遷移（InnoDB、5.6.6+）

標準流程（參考文章 171585/171587）

# Master
USE db;
SHOW CREATE TABLE tbl;                    -- 1. 獲取建表語句
FLUSH TABLES tbl FOR EXPORT;              -- 2. 產生 .cfg、刷髒頁

# Slave
USE db;
CREATE TABLE tbl (...);                   -- 3. 建空表
ALTER TABLE tbl DISCARD TABLESPACE;       -- 4. 刪 .ibd

# 文件傳輸
scp /data/mysql/db/tbl.{ibd,cfg} slave:/data/mysql/db/

# Slave 繼續
ALTER TABLE tbl IMPORT TABLESPACE;        -- 5. 導入
UNLOCK TABLES;                            -- 6. Master 解鎖

關鍵約束

• 源/目標 MySQL 大版本一致、InnoDB 頁大小一致（通常 16KB）
• 表不能在 foreign_key_checks=1 時有外鍵依賴（需先刪外鍵）
• FLUSH TABLES ... FOR EXPORT 期間表只讀，業務寫入阻塞
• .cfg 文件 5.6 必須，8.0 可選（SDI 存儲在 .ibd 內）

五、選型決策樹

IF 數據量 < 50GB AND 需跨版本/異構引擎:
    USE mysqldump / mydumper
ELSE IF 數據量 50GB - 2TB AND 同版本 InnoDB:
    USE xtrabackup + 複製過濾
ELSE IF 單表 > 10GB AND 需極短停機 AND 同版本:
    USE TTS
ELSE:
    考慮異構同步 + 雙寫遷移

六、生產級遷移清單

1. 制定遷移方案文檔：工具、窗口、回滾預案、驗收標準
2. 預演：測試環境全流程跑通，記錄耗時、報錯
3. 監控：備份/恢復進度、磁盤 IO、網絡帶寬、主從延遲
4. 驗收：pt-table-checksum 一致性校驗、業務驗證、壓測
5. 回滾：保留舊實例 48h，確認無問題再下線