MySQL 備份與恢復實戰：mysqldump 邏輯備份全模式與 source 恢復指南

核心工具

• 備份：mysqldump (邏輯備份，產生 SQL 檔)
• 恢復：source (在 MySQL Client 內執行 SQL 檔)

mysqldump 常用參數解析

四大備份模式範例

1. 單庫備份

mysqldump -uroot -p'pass' --single-transaction --routines --triggers --events \
  --master-data=2 --hex-blob --quick \
  my_database > /backup/my_database_$(date +%F).sql

2. 多庫備份

mysqldump -uroot -p'pass' --single-transaction --routines --triggers --events \
  --databases db1 db2 db3 > /backup/multi_db_$(date +%F).sql

3. 指定表備份

mysqldump -uroot -p'pass' --single-transaction \
  my_database table1 table2 > /backup/partial_$(date +%F).sql

4. 全例備份 (所有庫)

mysqldump -uroot -p'pass' --single-transaction --routines --triggers --events \
  --all-databases --master-data=2 > /backup/full_$(date +%F).sql

恢復操作

方法一：MySQL Client 內 source (適合中小檔案)

mysql -uroot -p'pass'  # 進入客戶端
-- 若備份含 CREATE DATABASE，無需指定庫
source /backup/full_2024-01-15.sql;
-- 或指定目標庫
USE target_db;
source /backup/my_database_2024-01-15.sql;

方法二：Shell 管道直餵 (適合大檔案、自動化)

mysql -uroot -p'pass' < /backup/full_2024-01-15.sql
# 或指定庫
mysql -uroot -p'pass' target_db < /backup/my_database_2024-01-15.sql

方法三：並行恢復加速 (大數據量)

# 1. 拆分單表檔案 (需修改 mysqldump 腳本或用工具)
# 2. 使用 mysqlpump (MySQL 5.7.8+) 或 myloader (mydumper 套件)
mysqlpump -uroot -p'pass' --parallel-schemas=4 --databases db1 db2 > dump.sql
# 恢復
mysqlpump ... | mysql -uroot -p'pass'

時間點恢復 (PITR) - 結合 Binlog

1. 全量備份點：mysqldump --master-data=2 記錄 Binlog 位置 (File, Pos)。
2. 增量 Binlog：mysqlbinlog --start-position=XXX --stop-datetime='2024-01-15 14:30:00' mysql-bin.000005 | mysql -uroot -p
3. 先恢復全量，再回放增量 Binlog 至目標時間點。

生產環境最佳實踐

1. 每日全量 + 實時 Binlog：RPO < 1 分鐘，RTO 視資料量而定。
2. 備份驗證：定期 (如每週) 在測試環境 source 恢復，校驗資料完整性 (checksum、行數、關鍵業務查詢)。
3. 異地存儲：備份檔同步至異地存儲 (S3/OSS/NAS)，防單點災難。
4. 權限最小化：備份帳號僅需 SELECT, SHOW VIEW, TRIGGER, LOCK TABLES, RELOAD, REPLICATION CLIENT。
5. 加密傳輸/存儲：mysqldump | gpg --encrypt 或儲存於加密磁碟。

常見坑

• 忘記 --single-transaction → MyISAM 表鎖表、InnoDB 一致性無保障。
• 大表無 --quick → 客戶端 OOM。
• 恢復時字符集不匹配 → 導入前 SET NAMES utf8mb4; 或檔案頭指定。
• GTID 環境直接 source 含 GTID 的備份 → 主從同步報錯，需 --set-gtid-purged=OFF。

掌握 mysqldump 參數組合與多種恢復路徑，配合 Binlog PITR，構建可靠的 MySQL 資料保護體系。