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

MySQL 數據庫備份與恢復實戰：mysqldump 參數詳解與自動化腳本

編輯：MySQL 綜合教學

MySQL 數據庫備份與恢復實戰：mysqldump 參數詳解與自動化腳本

mysqldump 是 MySQL 官方邏輯備份工具，支援全量、增量 (配合 Binlog)、單表、結構/數據分離等多種模式。本文系統整理核心參數、常見場景命令、恢復驗證、自動化腳本模板。

一、核心參數速查

參數	說明	典型值
-u, --user	用戶名	root
-p, --password	密碼 (緊跟 -p 或交互式)	-p'pass'
-h, --host	主機	127.0.0.1
-P, --port	端口	3306
-A, --all-databases	備份所有庫
-B, --databases	指定多個庫 (含 CREATE DATABASE)	db1 db2
--single-transaction	InnoDB 熱備核心：啟動一致性快照，無鎖	必加
--master-data=2	輸出 CHANGE MASTER TO 註釋 (含 Binlog 位點)，2 為註釋形式	主從搭建必加
-R, --routines	備份存儲過程/函數	建議加
--triggers	備份觸發器 (默認開啟)
--events	備份事件調度器	建議加
-d, --no-data	僅結構	Schema 遷移
-t, --no-create-info	僅數據	數據遷移
--where	篩選條件	"id > 1000"
--hex-blob	二進制列十六進制導出	含 BLOB 必加
--quick / -q	流式讀取，不緩存結果集 (大表必加)
--compress	網絡傳輸壓縮	遠程備份

二、常見場景命令模板

1. 全量熱備 (生產標準)

mysqldump -uroot -p'pass' -h127.0.0.1 \
  --single-transaction --master-data=2 -R --events --hex-blob -q \
  --all-databases | gzip > /backup/full_$(date +%F_%H%M).sql.gz

2. 指定庫備份 (含建庫語句)

mysqldump -uroot -p -B db1 db2 --single-transaction -R --events | gzip > /backup/dbs_$(date +%F).sql.gz

3. 單表結構 + 數據

mysqldump -uroot -p db1 tbl1 tbl2 --single-transaction > /backup/tables_$(date +%F).sql

4. 僅結構 (Schema 遷移/審計)

mysqldump -uroot -p -d db1 --single-transaction > /backup/schema_db1_$(date +%F).sql

5. 僅數據 (數據遷移/初始化)

mysqldump -uroot -p -t db1 --single-transaction > /backup/data_db1_$(date +%F).sql

6. 遠程直接導入另一實例

mysqldump -h src_host -u u -p db1 --single-transaction | mysql -h dst_host -u u -p db1

三、恢復驗證

四、自動化備份腳本模板 (cron 部署)

#!/bin/bash
# /usr/local/bin/mysql_backup.sh
set -euo pipefail

BACKUP_DIR="/backup/mysql"
RETENTION_DAYS=7
MYSQL_USER="backup_user"
MYSQL_PASS="strong_pass"
MYSQL_HOST="127.0.0.1"

mkdir -p "$BACKUP_DIR"
FILENAME="full_$(date +%F_%H%M).sql.gz"

mysqldump -u"$MYSQL_USER" -p"$MYSQL_PASS" -h"$MYSQL_HOST" \
  --single-transaction --master-data=2 -R --events --hex-blob -q \
  --all-databases | gzip > "$BACKUP_DIR/$FILENAME"

# 驗證文件非空
if [[ ! -s "$BACKUP_DIR/$FILENAME" ]]; then
    echo "ERROR: Backup file empty!" >&2
    exit 1
fi

# 刪除舊備份
find "$BACKUP_DIR" -name 'full_*.sql.gz' -mtime +$RETENTION_DAYS -delete

echo "SUCCESS: $FILENAME"
# 可對接企業微信/釘釘/郵件告警

# crontab -e 每天凌晨 2 點備份
0 2 * * * /usr/local/bin/mysql_backup.sh >> /var/log/mysql_backup.log 2>&1

五、進階：增量備份策略 (配合 Binlog)

啟用 Binlog：log_bin=mysql-bin、binlog_format=ROW、expire_logs_days=7。
全量備份時記錄 Binlog 位點 (--master-data=2)。
增量備份：mysqlbinlog --start-position=XXX --stop-position=YYY mysql-bin.000001 > inc.sql 或使用 mysqlbinlog --read-from-remote-server 實時拉取。
恢復：先導入全量 → 依序重放增量 Binlog。

六、避坑清單

大表必加 --quick：否則客戶端緩存全量結果集導致 OOM。
含 BLOB 必加 --hex-blob：否則二進制數據導出損壞。
備份用戶最小權限：GRANT SELECT, RELOAD, LOCK TABLES, SHOW VIEW, EVENT, TRIGGER, PROCESS ON *.* TO 'backup_user'@'%';
定期演練恢復：備份未驗證等於無備份，每季度至少一次全量恢復演練。

邏輯備份雖慢於物理備份 (XtraBackup)，但跨版本、跨架構、可編輯、可遷移至異構資料庫，是中小規模、Schema 變更頻繁環境的首選。建議「每日全量 + Binlog 增量」雙軌制。