MySQL 備份策略實戰：mysqldump 全量 + Binlog 增量雙軌制自動化腳本

備份架構設計

全量備份腳本（DBFullyBak.sh）

#!/bin/bash
# 每週日 03:00 執行
# crontab: 0 3 * * 0 /root/DBFullyBak.sh >/dev/null 2>&1

set -euo pipefail

BakDir=/home/mysql/backup
LogFile=${BakDir}/bak.log
Date=$(date +%Y%m%d)
Begin=$(date +"%Y年%m月%d日 %H:%M:%S")

mkdir -p "${BakDir}"
cd "${BakDir}"

DumpFile="${Date}.sql"
GZDumpFile="${Date}.sql.tgz"

# 核心參數：--single-transaction(一致性) --flush-logs(切binlog) --delete-master-logs(清舊binlog)
/usr/local/mysql/bin/mysqldump \
  -uroot -p123456 \
  --quick \
  --all-databases \
  --single-transaction \
  --flush-logs \
  --delete-master-logs \
  > "${DumpFile}"

if [[ $? -eq 0 ]]; then
    tar czf "${GZDumpFile}" "${DumpFile}" >> "${LogFile}" 2>&1
    echo "[${GZDumpFile}] Backup Successful!" >> "${LogFile}"
    rm -f "${DumpFile}"
else
    echo "Database Backup Fail!" >> "${LogFile}"
    # mail -s "DB Full Backup Failed" admin@example.com
fi

# 清理增量備份目錄舊 binlog（全量已切換新 binlog）
rm -f "${BakDir}/daily"/*

Last=$(date +"%Y年%m月%d日 %H:%M:%S")
echo "開始:${Begin} 結束:${Last} ${GZDumpFile} succ" >> "${LogFile}"

增量備份腳本（DBDailyBak.sh）

#!/bin/bash
# 週一~六 03:00 執行
# crontab: 0 3 * * 1-6 /root/DBDailyBak.sh >/dev/null 2>&1

set -euo pipefail

BakDir=/home/mysql/backup/daily
BinDir=/home/mysql/data
LogFile=/home/mysql/backup/bak.log
BinIndex=${BinDir}/mysql-bin.index

mkdir -p "${BakDir}"

# 1. 刷新日誌，產生新的 mysql-bin.NNNNNN
/usr/local/mysql/bin/mysqladmin -uroot -p123456 flush-logs

# 2. 讀取 binlog 索引，複製除最新外的所有檔案
Counter=$(wc -l < "${BinIndex}")
NextNum=0

while IFS= read -r file; do
    base=$(basename "${file}")
    NextNum=$((NextNum + 1))

    if [[ ${NextNum} -eq ${Counter} ]]; then
        echo "${base} skip!" >> "${LogFile}"  # 最新 binlog 正在寫入，跳過
        continue
    fi

    dest="${BakDir}/${base}"
    if [[ -e "${dest}" ]]; then
        echo "${base} exist!" >> "${LogFile}"
    else
        cp "${BinDir}/${base}" "${BakDir}/"
        echo "${base} copying" >> "${LogFile}"
    fi
done < "${BinIndex}"

echo "$(date +"%Y年%m月%d日 %H:%M:%S") Bakup succ!" >> "${LogFile}"

Crontab 部署

# /etc/crontab 或 crontab -e
# 全量備份：每週日 03:00
0 3 * * 0 root /root/DBFullyBak.sh >/dev/null 2>&1

# 增量備份：週一至六 03:00
0 3 * * 1-6 root /root/DBDailyBak.sh >/dev/null 2>&1

關鍵參數深度解析

還原演練流程（Point-in-Time Recovery）

# 1. 還原最近全量備份
tar xzf 20240107.sql.tgz
mysql -uroot -p < 20240107.sql

# 2. 依序應用增量 binlog
mysqlbinlog /home/mysql/backup/daily/mysql-bin.000001 | mysql -uroot -p
mysqlbinlog /home/mysql/backup/daily/mysql-bin.000002 | mysql -uroot -p
# ... 直到目標時間點

# 3. 或指定位置還原
mysqlbinlog --start-position=12345 --stop-position=67890 \
  /home/mysql/backup/daily/mysql-bin.000003 | mysql -uroot -p

驗證與監控清單

• ✅ 每日檢查 bak.log 確認 "Backup Successful!" / "Bakup succ!"。
• ✅ 定期（月度）演練還原至測試環境，驗證資料完整性。
• ✅ 監控備份磁碟空間，設定告警（>80% 觸發）。
• ✅ sh -n /root/DBFullyBak.sh 語法檢查納入 CI/CD。
• ✅ 密碼移至 ~/.my.cnf（chmod 600），腳本不含明文。