生產環境 mysqldump 大數據庫備份與跨機器遷移實戰記錄

場景與耗時統計

線上庫 datatuan，A 機器 (2.26GHz) 導出，B 機器 (2.8GHz) 導入。

A 端導出

time mysqldump -u datatuan -ppasswd --databases datatuan > datatuan.sql
# real 5m39.516s, user 0m40.898s, sys 0m5.762s
du -sh datatuan.sql  # 1.4G

關鍵參數：-x / --lock-all-tables 全局讀鎖，自動關閉 --single-transaction 和 --lock-tables，確保一致性視圖。

跨機器傳輸（nc 管道）

# B 端監聽
nc -l 4444 > datatuan.sql
# A 端發送
time nc -w 1 B_IP 4444 < datatuan.sql
# real 3m2.263s, user 0m0.146s, sys 0m4.224s

B 端導入

mysql -uroot -p
CREATE DATABASE datatuan;
GRANT USAGE ON datatuan.* TO 'datatuan'@'%' IDENTIFIED BY 'passwd';
GRANT ALL ON datatuan.* TO 'datatuan'@'%' IDENTIFIED BY 'passwd';
FLUSH PRIVILEGES;

time mysql -uroot -pabdata datatuan -f < datatuan.sql
# real 29m12.757s, user 0m33.248s, sys 0m1.358s
# 過程中報 ERROR 1146: Table 'datatuan.TmpPlan' doesn't exist (多次)
# -f 強制繼續執行

關鍵經驗總結

1. 大庫備份用 nc/管道直傳：省去落盤再讀，節省 I/O 與磁盤空間
2. mysqldump 加 --single-transaction (InnoDB) 或 --lock-all-tables (MyISAM)：確保一致性，避免業務阻塞
3. 導入加 -f 強制模式：忽略缺表等非致命錯誤，防止中斷
4. 預先創建庫、用戶、權限：避免導入 SQL 中 CREATE DATABASE 權限不足報錯
5. 臨時表 (TmpPlan) 缺失：應用層動態建表，備份時可能不存在，導入報錯可忽略或預先建表
6. 導入耗時遠大於導出：涉及索引重建、外鍵檢查、日志寫入，可臨時關閉 unique_checks/foreign_key_checks、調大 innodb_buffer_pool 加速

優化版導入命令建議

mysql -uroot -p --init-command="SET UNIQUE_CHECKS=0; SET FOREIGN_KEY_CHECKS=0; SET SQL_LOG_BIN=0;" datatuan < datatuan.sql
# 導入後記得重新啟用檢查