MySQL 資料庫備份與遷移：mysqldump 與直接複製檔案的深度比較

兩大備份流派

mysqldump 核心用法

# 全庫備份（推薦生產參數）
mysqldump --opt --single-transaction --master-data=2 -u root -p db_name > backup.sql

# 壓縮輸出
mysqldump --opt db_name | gzip > backup.sql.gz

# 指定表備份
mysqldump db_name tbl1 tbl2 > partial.sql

# 遷移至遠端 Server（管道流，無中間檔）
mysqldump --opt db_name | mysql -h remote_host -u user -p db_name

關鍵參數解析

• --opt = --quick --add-drop-table --add-locks --extended-insert --lock-tables（綜合最佳化）。
• --single-transaction：InnoDB 熱備份關鍵，配合 MVCC 取得一致性視圖，不阻塞寫入。
• --master-data=2：在輸出中以註釋記錄 Binlog 座標，利於建立 Slave。
• --quick：逐行串流輸出，避免大結果集佔滿記憶體。
• --compress：跨網路傳輸時壓縮協議資料。

直接複製檔案適用場景

• MyISAM 表、同架構、同版本、可接受停機視窗。
• 極大資料庫（TB 級）邏輯備份耗時過長時的替代方案。

操作範例：

# 停服務
systemctl stop mysql
# 複製整個資料庫目錄
cp -r /var/lib/mysql/db_name /backup/mysql/
# 啟動服務
systemctl start mysql

備份策略最佳實踐

1. 定期排程：每日全量 + 每小時增量（Binlog/Relay Log）。
2. 啟用 Binlog：log-bin + expire_logs_days，支援時間點恢復 (PITR)。
3. 命名規範：db_name.YYYY-MM-DD.sql.gz，一眼可識別。
4. 異地冗餘：備份檔推送至異地存儲（S3/NAS/異地機房）。
5. 定期演練還原：未經驗證的備份等於沒有備份。

資料庫遷移技巧

同版本遷移：mysqldump | mysql -h new_host 管道直連。
跨版本遷移：導出 SQL → 目標版本 mysql_upgrade → 匯入 → 驗證。