萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MySQL 教學

mysqldump 參數深度解析與生產級備份還原最佳實踐

核心參數詳細說明

參數功能適用場景/建議
--opt啟用預設優化組合 (--add-drop-table --add-locks --create-options --disable-keys --extended-insert --lock-tables --quick --set-charset)**預設開啟**。一般備份直接用 `--opt` 即可。大表建議搭配 `--quick`。
--single-transaction啟動一致性快照 (REPEATABLE READ),不鎖表**InnoDB 熱備份必加**。與 `--lock-tables` 互斥。
--lock-all-tables (-x)全域讀鎖 (FLUSH TABLES WITH READ LOCK)MyISAM/混合引擎備份一致性。會阻塞寫入,**生產慎用**。
--quick (-q)逐行串流輸出,不緩存結果集到記憶體**大表必加**,防止 OOM。
--extended-insert多行合併 INSERT (INSERT INTO t VALUES (...),(...),...)預設開啟。還原極快。若目標 `max_allowed_packet` 小需關閉 (`--skip-extended-insert`)。
--hex-blob二進位欄位以 0xHEX 輸出**含 BLOB/BINARY/GEOMETRY 必加**,避免編碼損壞。
--default-character-set指定連線字符集**必須與資料庫實際編碼一致** (如 `utf8mb4`),否則還原亂碼。
--routines (-R)包含預存程序/函數生產備份**建議加上**。
--triggers包含觸發器預設開啟 (`--skip-triggers` 關閉)。
--master-data=1/2輸出 CHANGE MASTER TO (binlog 位置)建立 Slave 同步基準。**1=註解,2=可執行**。需 `RELOAD` 權限。
--flush-logs備份前切換新 binlog配合 `--master-data` 使用,確保位置精確。
--no-create-info (-t)僅匯出資料 (無 CREATE TABLE)資料遷移/同步專用。
--no-data (-d)僅匯出結構Schema 比對/遷移專用。
--compatible=name相容模式 (ansi, mysql323, mysql40, postgresql, oracle, mssql, db2, maxdb...)跨版本/異構遷移時嘗試。不保證 100% 相容。

生產環境標準備份命令範本

MyISAM 表為主

/usr/local/mysql/bin/mysqldump \
  -u backup_user -p'StrongPass' \
  --default-character-set=utf8mb4 \
  --opt --extended-insert=FALSE \
  --triggers -R --hex-blob \
  --lock-all-tables \
  DB_Name > /backup/DB_Name_$(date +%F_%H%M).sql

InnoDB 表為主 (熱備份、不鎖表)

/usr/local/mysql/bin/mysqldump \
  -u backup_user -p'StrongPass' \
  --default-character-set=utf8mb4 \
  --opt --extended-insert=FALSE \
  --triggers -R --hex-blob \
  --single-transaction \
  --master-data=2 --flush-logs \
  DB_Name > /backup/DB_Name_$(date +%F_%H%M).sql

還原操作

方法一:mysql 客戶端管道導入 (標準、高效)

mysql -u root -p DB_Name < /backup/DB_Name_20240101.sql
# 或指定編碼防亂碼
mysql -u root -p --default-character-set=utf8mb4 DB_Name < /backup/DB_Name_20240101.sql

方法二:SOURCE 指令 (需進入 mysql 互動模式)

mysql> USE DB_Name;
mysql> SOURCE /backup/DB_Name_20240101.sql;
-- 注意:需 mysql 進程使用者有檔案讀取權限,路徑為伺服器端絕對路徑。

線上備份進階:GTID 複寫基準 (MySQL 5.6+)

mysqldump ... --single-transaction --master-data=2 --flush-logs --set-gtid-purged=ON DB_Name > backup.sql
-- 還原到 Slave 後自動包含 GTID_SET,CHANGE MASTER TO MASTER_AUTO_POSITION=1 即可同步。

常見坑點避雷

  • ❌ **忘記 `--single-transaction` 導致 InnoDB 鎖表阻塞業務**。
  • ❌ **編碼不匹配** (`latin1` 連線匯出 `utf8` 資料) → 還原後中文全是 `???`。永遠指定 `--default-character-set`。
  • ❌ **大表無 `--quick`** → 客戶端記憶體暴漲 OOM Kill。
  • ❌ **還原前未建立資料庫/編碼不一致** → 物件建立失敗或資料截斷。
  • ❌ **未驗證備份可用性** → 定期 `RESTORE VERIFYONLY` 或實測還原到測試環境。
copyright © 萬盛學電腦網 all rights reserved