您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 綜合教學

每天進步一點點：mysqldump 參數精講與備份策略最佳實踐

編輯：MySQL 綜合教學

每天進步一點點：mysqldump 參數精講與備份策略最佳實踐

mysqldump 是 MySQL 官方邏輯備份工具，將數據導出為 SQL 腳本，支援跨版本、跨引擎、異構遷移。掌握其核心參數，構建可靠備份體系。

核心參數分類速查

類別	參數	作用	推薦場景
連接	-h, --host	主機地址	遠程備份
	-u, --user	用戶名
	-p, --password	密碼（建議配置文件）
	-P, --port	端口	非 3306
輸出內容控制	-A, --all-databases	備份所有庫	全量備份
	-B, --databases	指定多個庫（含 CREATE DATABASE）	遷移/恢復到新實例
	--tables	指定表（覆蓋庫參數）	單表備份
	-d, --no-data	僅結構	Schema 遷移/審計
	-t, --no-create-info	僅數據	數據遷移/刷新測試庫
	--routines / --triggers / --events	包含存儲過程/觸發器/事件	完整邏輯備份必加
一致性與鎖	--single-transaction	InnoDB 熱備核心：啟動事務+一致性視圖，無阻塞	InnoDB 生產環境必加
	--lock-all-tables	全局讀鎖（MyISAM/混合引擎）	非 InnoDB 表
	--lock-tables	庫級鎖（默認行為）	影響寫入，慎用
	--master-data=2	輸出 CHANGE MASTER 註釋（位置點）	搭建從庫必加
	--flush-logs	備份前滾動 Binlog	配合 PITR 切分點
性能優化	--quick / -q	流式讀取，不緩存結果集到內存	大表必加，防 OOM
	--extended-insert	多行合并 INSERT（默認開啟）	加速恢復
	--max-allowed-packet	最大包大小	大字段表調大（如 1G）
	--net-buffer-length	網絡緩衝區	配合 max_allowed_packet
壓縮與輸出	\| gzip > file.sql.gz	管道壓縮（推薦）	節省 70%+ 空間
	--result-file / -r	輸出文件（Windows 換行符處理）	跨平台
	--tab / -T	導出純文本（.sql+.txt）	LOAD DATA 高速導入

三大經典備份命令模板

1. 生產環境 InnoDB 熱備（標準模板）

mysqldump -h 10.0.1.10 -u backup_user -p'Str0ngPwd' \
  --single-transaction --master-data=2 --flush-logs \
  --routines --triggers --events --quick \
  --set-gtid-purged=OFF \
  --databases db1 db2 \
  | gzip > /backup/mysql_$(date +%F_%H%M).sql.gz

--single-transaction：不阻塞寫入，基於 MVCC 一致性視圖。
--master-data=2：輸出 -- CHANGE MASTER TO MASTER_LOG_FILE='binlog.000012', MASTER_LOG_POS=12345; 註釋，搭建 Slave 免去手工查位置。
--set-gtid-purged=OFF：避免 GTID 信息干擾異構恢復。

2. 結構遷移/審計（無數據）

mysqldump -u root -p -d --routines --triggers --events db_name > schema_$(date +%F).sql

3. 大表分片導出（配合並行導入）

# 導出為多文件（需配合 where 切片或 pt-table-sync）
mysqldump -u root -p --quick --no-create-info --where="id BETWEEN 1 AND 1000000" db_name big_table > part1.sql

備份策略最佳實踐（3-2-1 原則）

3 副本：本地磁盤 + 異機同機房 + 異地（云存儲/OSS/S3）。
2 種介質：磁盤 + 磁帶/云存儲/異構存儲。
1 份離線：不可變備份（WORM），防勒索軟件。

分層備份節奏

全量備份：每日 02:00（低峰），保留 7 天。
增量備份：Binlog 實時同步到異地（Canal/Flink）或每小時 mysqlbinlog 切片。
邏輯備份：每週一次 mysqldump --single-transaction 校驗物理備份可用性。

恢復驗證閉環（備份未驗證 = 無備份）

# 1. 解壓
gunzip -c backup.sql.gz > /tmp/restore.sql

# 2. 建庫導入（測試環境）
mysql -u root -p -e "CREATE DATABASE db_restore;"
mysql -u root -p db_restore < /tmp/restore.sql

# 3. 核心表抽樣校驗
mysql -u root -p -e "SELECT COUNT(*) FROM db_restore.core_table;"
# 4. 應用層冒煙測試
# 5. 記錄恢復耗時 RTO，對比 SLA 目標

避坑清單

❌ 直接 mysqldump > file.sql 無 --single-transaction → 鎖表阻塞業務
❌ 僅備份 .sql 不壓縮 → 傳輸慢、佔空間
❌ 從未做過恢復演練 → 故障時手忙腳亂
❌ 備份賬號權限過大 → 安全風險
✅ 專用備份賬號：GRANT SELECT, RELOAD, LOCK TABLES, SHOW VIEW, EVENT, TRIGGER, PROCESS ON *.* TO 'backup'@'10.0.1.%' IDENTIFIED BY 'pwd';

參數精通是基礎，策略落地與演練閉環才是備份體系的靈魂。