MySQL mysqldump 邏輯備份完全指南：從基礎用法到增量恢復策略

核心用法分類

A. 基礎全庫備份

mysqldump -u root -p --single-transaction --routines --triggers --events --all-databases > full_backup_$(date +%F).sql

關鍵參數：

• --single-transaction：InnoDB 一致性快照，無阻塞
• --routines --triggers --events：包含存儲過程/觸發器/事件

B. 指定庫/表備份

# 單庫
mysqldump -u root -p --single-transaction mydb > mydb.sql

# 多庫
mysqldump -u root -p --single-transaction --databases db1 db2 db3 > multi_db.sql

# 單表
mysqldump -u root -p --single-transaction mydb users > users.sql

C. 僅結構/僅數據

# 僅結構（含 DROP IF EXISTS）
mysqldump -u root -p --no-data --single-transaction mydb > schema.sql

# 僅數據
mysqldump -u root -p --no-create-info --single-transaction mydb > data_only.sql

D. 跨服務器直接同步

# 從 host1 同步到 host2（需 host2 已建庫）
mysqldump -h host1 -u root -p --single-transaction --compress source_db | mysql -h host2 -u root -p target_db

進階參數 --opt 解析

--opt 為預設開啟，等效於：

--add-drop-table --add-locks --create-options --disable-keys --extended-insert --lock-tables --quick --set-charset

效果：

• 建表前加 DROP TABLE IF EXISTS
• INSERT 前後加 LOCK/UNLOCK TABLES
• 多行合併 INSERT（--extended-insert）
• 快速導出不緩存全表（--quick）

定時自動備份策略

Crontab 系統級定時

# 編輯 /etc/crontab
30 2 * * * root mysqldump -u backup_user -p'Pass#123' --single-transaction --all-databases | gzip > /backup/mysql/full_$(date +\%F).sql.gz

# 保留 30 天
0 3 * * * root find /backup/mysql -name "full_*.sql.gz" -mtime +30 -delete

Shell 腳本封裝（含日誌/通知）

#!/bin/bash
# /usr/local/bin/mysql_backup.sh
BACKUP_DIR="/backup/mysql"
LOG_FILE="/var/log/mysql_backup.log"
RETENTION_DAYS=30
DB_USER="backup_user"
DB_PASS="Pass#123"

mkdir -p "$BACKUP_DIR"
exec >> "$LOG_FILE" 2>&1

echo "[$(date)] 開始備份..."
FILENAME="full_$(date +%F_%H-%M).sql.gz"

mysqldump -u "$DB_USER" -p"$DB_PASS" --single-transaction --routines --triggers --events --all-databases | gzip > "$BACKUP_DIR/$FILENAME"

if [ $? -eq 0 ]; then
    echo "[$(date)] 備份成功: $FILENAME ($(du -h "$BACKUP_DIR/$FILENAME" | cut -f1))"
    # 清理舊備份
    find "$BACKUP_DIR" -name "full_*.sql.gz" -mtime +$RETENTION_DAYS -delete
else
    echo "[$(date)] 備份失敗！"
    # 這裡可接入告警：發送郵件/鉚釘/企業微信
fi

全量 + 增量恢復架構 (PITR)

啟用二進制日誌

# /etc/my.cnf
[mysqld]
log-bin = /var/log/mysql/mysql-bin
binlog_format = ROW
expire_logs_days = 7
server-id = 1

備份時刷新 Binlog（關鍵）

mysqldump -u root -p --single-transaction --flush-logs --master-data=2 --all-databases > backup_$(date +%F).sql

--master-data=2：輸出 CHANGE MASTER TO 註釋（含 Binlog 位置）

--flush-logs：切換新 Binlog 文件，增量起點清晰

恢復流程

# 1. 恢復全量備份
mysql -u root -p < backup_2024-01-15.sql

# 2. 恢復增量 Binlog（假設從 mysql-bin.000003 開始）
mysqlbinlog /var/log/mysql/mysql-bin.000003 | mysql -u root -p

# 3. 指定時間點恢復（精確到秒）
mysqlbinlog --stop-datetime="2024-01-16 10:30:00" /var/log/mysql/mysql-bin.000003 | mysql -u root -p

# 4. 跳過故障時段恢復
mysqlbinlog --start-datetime="2024-01-16 10:35:00" /var/log/mysql/mysql-bin.000003 | mysql -u root -p

mysqlbinlog 精確恢復參數

大數據量優化建議

• 使用 mydumper/myloader 並行備份恢復（速度提升 5-10 倍）
• 備份存儲分離：掛載獨立磁碟/NFS/對象存儲
• 驗證恢復：定期在測試環境演練全量+增量恢復
• 監控告警：備份文件大小異常、耗時異常、Binlog 積壓