大型資料庫遷移工具選型與效能優化

百萬級以上資料遷移，工具選擇與參數調優決定成敗。測試環境：101 萬筆、平均行大小 46KB。

方案對比實測

方法	指令	耗時	優缺點
mysqldump (預設)	mysqldump -u user -p db > dump.sql	2124 秒	通用、邏輯備份、跨版本相容
mysqldump + 單一交易	mysqldump --single-transaction --quick -u user -p db > dump.sql	~1500 秒	InnoDB 熱備份、不鎖表、需較大記憶體
SELECT INTO OUTFILE	SELECT * INTO OUTFILE '/path/file.txt' FROM tbl	3252 秒	純文字、需 FILE 權限、檔案在 Server 端
LOAD DATA INFILE	LOAD DATA INFILE '/path/file.txt' INTO TABLE tbl	**最快**	匯入極快、需同機器、格式固定
物理複製 (data 目錄)	停服務 → 複製 /var/lib/mysql/db → 啟動	秒級	需相同版本、相同架構、有 root 權限

最佳實務流程

1. 建表先行：先在目標建好表結構與索引（含二級索引）
2. 停用外鍵/唯一檢查：

SET FOREIGN_KEY_CHECKS=0;
SET UNIQUE_CHECKS=0;
SET AUTOCOMMIT=0;

3. 分批匯入：大表拆分多檔，避免單一交易過大
4. 匯入後重建索引統計：ANALYZE TABLE tbl;

跨字元集遷移避坑

匯入時指定字元集防止亂碼：

mysql --default-character-set=gb2312 -u user -p db < dump.sql

自動化腳本範例

#!/bin/bash
DB="blog"
DATE=$(date +%Y%m%d)
mysqldump --single-transaction --quick --routines --triggers -u root -p"$PWD" $DB | gzip > /backup/${DB}_${DATE}.sql.gz

# 匯入
gunzip -c /backup/${DB}_${DATE}.sql.gz | mysql -u root -p"$PWD" $DB

黃金法則：有 root 權限且版本一致 → 物理複製最快；異構/跨版本/無 root → mysqldump + LOAD DATA 組合；極大表必須分批、先建索引後匯入。