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

mysqldump 邏輯備份工具全參數詳解與實戰腳本

編輯：MySQL 綜合教學

mysqldump 核心參數速查表

mysqldump 是 MySQL 官方邏輯備份工具，將資料庫對象導出為 SQL 腳本，支援跨版本遷移。掌握核心參數是高效備份的關鍵。

常用參數分類

類別	參數	說明	建議值
輸出控制	`--add-drop-table`	建表前加 DROP TABLE	預設開啟
	`--add-locks`	備份時鎖表	預設開啟
	`--complete-insert`	完整 INSERT 語句（含欄位名）	提高導入效率，注意 `max_allowed_packet`
	`--extended-insert`	多行合併 INSERT	預設開啟，大表建議關閉
範圍選擇	`--all-databases`	備份所有庫	全量備份
	`--databases db1 db2`	指定多個庫	部分庫備份
	`db_name table1 table2`	指定庫下的表	單表備份
	`--no-data`	僅導出結構	DDL 遷移
一致性與性能	`--single-transaction`	事務一致性視圖（InnoDB 必備）	生產環境強烈推薦
	`--lock-all-tables`	全域讀鎖	MyISAM/混合引擎環境
	`--quick`	流式讀取，不緩存結果集	大表必加
	`--master-data=2`	記錄 binlog 位置（註解形式）	搭建從庫必備
	`--flush-logs`	刷新 binlog	增量備份切分點
對象完整性	`--routines`	導出存儲過程/函數	預設關閉，建議開啟
	`--triggers`	導出觸發器	預設開啟
	`--events`	導出事件調度器	預設關閉，建議開啟
	`--hex-blob`	二進制欄位用十六進制	含 BLOB/BINARY 時必加
字符集與兼容	`--default-character-set`	指定導出字符集	必須與源庫一致，防亂碼
	`--compatible=mysql40`	兼容舊版本	跨版本遷移時使用
	`--set-charset`	輸出 SET NAMES 陳述式	預設開啟

實戰範例

1. 單庫備份（推薦生產配置）

mysqldump -u backup_user -p \
  --default-character-set=utf8mb4 \
  --single-transaction \
  --quick \
  --master-data=2 \
  --flush-logs \
  --routines \
  --triggers \
  --events \
  --hex-blob \
  --databases mydb > mydb_$(date +%F).sql

2. 全量備份（含所有系統庫）

mysqldump -u backup_user -p \
  --all-databases \
  --single-transaction \
  --master-data=2 \
  --flush-logs \
  --routines \
  --events \
  | gzip > full_$(date +%F).sql.gz

3. 結構遷移（不含數據）

mysqldump -u backup_user -p \
  --no-data \
  --routines \
  --triggers \
  --events \
  --databases mydb > mydb_schema_$(date +%F).sql

自動化備份腳本（生產就緒版）

#!/bin/bash
# mysql_dump_backup.sh - 每日全量備份 + 7天保留

DATE=$(date +%Y-%m-%d)
BASE_DIR="/u04/mysqlbackup"
BACKUP_DIR="$BASE_DIR/$DATE"
FILENAME="backup_all.sql.gz"

USER="backup_user"
PASS="******"  # 建議使用 --defaults-extra-file
RETENTION=7

mkdir -p "$BACKUP_DIR"

# 執行備份
mysqldump --user=$USER --password=$PASS \
  --all-databases \
  --events --routines \
  --master-data=2 \
  --flush-logs \
  --single-transaction \
  --quick \
  | gzip > "$BACKUP_DIR/$FILENAME"

# 記錄日誌
echo "$(date '+%F %T') : Full backup completed: $BACKUP_DIR/$FILENAME" >> "$BASE_DIR/history.log"

# 清理舊備份
find "$BASE_DIR" -type d -mtime +$RETENTION -exec rm -rf {} \;
echo "$(date '+%F %T') : Expired backups cleaned" >> "$BASE_DIR/history.log"

binlog 增量備份腳本

#!/bin/bash
# mysql_log_backup.sh - 每小時增量備份 binlog

USER="backup_user"
PASS="******"
BINLOG_DIR="/var/lib/mysql"
BACKUP_DIR="/u04/mysqlbackup/binlog"

mkdir -p "$BACKUP_DIR"

# 刷新日誌產生新 binlog
mysql --user=$USER --password=$PASS -e 'FLUSH LOGS'

# 複製最新 binlog（排除正在寫入的）
cp $BINLOG_DIR/mysql-bin.* $BACKUP_DIR/ 2>/dev/null

# 可選：使用 mysqlbinlog 解析為 SQL
# mysqlbinlog $BINLOG_DIR/mysql-bin.0000xx | gzip > $BACKUP_DIR/incremental_$(date +%F_%H).sql.gz

關鍵默認值提醒

--lock-tables 預設 TRUE（MyISAM 適用，InnoDB 用 --single-transaction）
--extended-insert 預設 TRUE（大表導入可能超過 max_allowed_packet）
--quick 預設 TRUE（大表必不可少）
--default-character-set 預設 utf8（建議顯式指定 utf8mb4）

避坑指南

權限最小化：備份帳號僅需 SELECT, LOCK TABLES, SHOW VIEW, EVENT, TRIGGER, RELOAD, PROCESS
避免密碼明文：使用 [client] 區塊在 ~/.my.cnf 存儲密碼
大表優化：加 --quick --single-transaction，關 --extended-insert
驗證備份：定期在測試環境執行 mysql < backup.sql 驗證可還原性