MySQL mysqldump 數據備份與恢復全攻略

mysqldump 核心定位

官方邏輯備份工具，導出 SQL 文本文件，支持跨版本遷移、異構引擎、部分恢復。不適用於超大庫 (TB 級) 物理備份場景。

一、常用備份模式

1.1 基礎備份

# 單庫備份 (含結構+數據)
mysqldump -uroot -p db_name > db_backup.sql

# 指定表備份
mysqldump -uroot -p db_name table1 table2 > tables_backup.sql

# 全庫備份
mysqldump -uroot -p --all-databases > full_backup.sql

1.2 生產級參數 (--opt 組合)

mysqldump -uroot -p \
  --single-transaction \      # 一致性快照 (InnoDB 必須)
  --routines --triggers --events \  # 存儲過程/觸發器/事件
  --master-data=2 \           # 記錄 binlog 位置 (CHANGE MASTER TO 註釋)
  --hex-blob \                # 二進制字段十六進制導出
  --quick \                   # 流式導出，不緩存全表
  --set-gtid-purged=OFF \     # GTID 場景避免污染
  db_name > prod_backup.sql

--opt 默認開啟：--add-drop-table --add-locks --create-options --disable-keys --extended-insert --lock-tables --quick --set-charset。

1.3 只備份結構 / 只備份數據

# 只結構
mysqldump -uroot -p --no-data db_name > schema.sql

# 只數據 (不含建表語句)
mysqldump -uroot -p --no-create-info db_name > data_only.sql

1.4 跨主機直接遷移

# 源庫 -> 目標庫 (目標庫需預創建)
mysqldump -h source_host -uroot -p --single-transaction db_name | \
  mysql -h target_host -uroot -p -C target_db
# -C 啟用壓縮傳輸

二、恢復策略

2.1 全量恢復

mysql -uroot -p db_name < backup.sql
# 或
mysql -uroot -p -e "SOURCE /path/backup.sql;" db_name

2.2 從全量備份恢復單庫 (--one-database)

mysql -uroot -p target_db --one-database < full_backup.sql

2.3 定時自動備份腳本

#!/bin/bash
# /backup/auto_backup.sh
BACKUP_DIR="/backup/mysql"
DATE=$(date +%F_%H%M)
USER="backup_user"
PASS="BackupPass123!"

mkdir -p $BACKUP_DIR
mysqldump -u$USER -p$PASS \
  --single-transaction --routines --triggers --events \
  --master-data=2 --all-databases | gzip > $BACKUP_DIR/full_$DATE.sql.gz

# 保留 7 天
find $BACKUP_DIR -name "*.gz" -mtime +7 -delete

# crontab -e
30 2 * * * /backup/auto_backup.sh > /dev/null 2>&1

三、進階：全量 + 增量 (binlog) 恢復體系

3.1 啟用 binlog (my.cnf)

[mysqld]
log-bin=mysql-bin
binlog-format=ROW
server-id=1
expire_logs_days=7
max_binlog_size=1G

3.2 備份時刷新 binlog (--flush-logs)

mysqldump --single-transaction --flush-logs --master-data=2 --all-databases > full_backup.sql
# 生成新 binlog 文件 (如 mysql-bin.000003)，增量從此開始

3.3 恢復流程

# 1. 恢復全量備份 (到備份時間點)
mysql -uroot -p < full_backup.sql

# 2. 恢復增量 binlog (從備份點到故障點)
mysqlbinlog mysql-bin.000003 | mysql -uroot -p

# 3. 指定時間點恢復 (跳過誤操作)
mysqlbinlog --stop-datetime="2023-10-01 10:00:00" mysql-bin.000003 | mysql -uroot -p

# 4. 指定位置恢復 (更精準)
mysqlbinlog --start-position=368315 mysql-bin.000003 | mysql -uroot -p

3.4 mysqlbinlog 關鍵參數

四、最佳實踐清單

• ✅ 生產環境必須 --single-transaction (InnoDB 一致性)
• ✅ 定期驗證備份可恢復性 (測試環境導入校驗)
• ✅ 備份文件異地存儲 (對象存儲/異地機房)
• ✅ 密碼不寫腳本明文 (使用 ~/.my.cnf [client] section)
• ✅ 監控備份耗時、文件大小、成功率 (告警異常)
• ✅ 核心庫全量+增量雙轨，RPO < 5 分鐘

總結

mysqldump 是 MySQL 邏輯備份基石。掌握 生產級參數組合、定時腳本、全量增量聯動恢復、mysqlbinlog 精準時間/位置恢復 四大核心能力，構建企業級備份體系。記住：備份未驗證等於沒備份。