MySQL 每日自動備份腳本：週輪轉壓縮存檔與雙機備份擴展

前言

資料備份是資料庫運維生命線。本文提供一個生產就緒的 Shell 腳本，實現「每日全備、GZIP 壓縮、7 天週輪轉、cron 定時執行」，並說明雙機異地備份擴展方向。

備份腳本：/root/mysql-daily-backup.sh

#!/bin/sh
# 設定區
BACKUP_DIR="/home/mysql-backup"
DB_USER="backup_user"
DB_PASS="StrongPassword"
DB_HOST="localhost"
RETENTION_DAYS=7

# 進入備份目錄
cd "$BACKUP_DIR" || exit 1

# 週輪轉：刪除最舊、依序遞增編號
rm -f alldb.$RETENTION_DAYS.tar.gz
for i in $(seq $((RETENTION_DAYS-1)) -1 1); do
    mv -f "alldb.$i.tar.gz" "alldb.$((i+1)).tar.gz" 2>/dev/null
done
mv -f alldb.0.tar.gz alldb.1.tar.gz 2>/dev/null

# 匯出全庫（含常規、觸發器、事件、Binlog座標）
mysqldump --single-transaction --master-data=2 --routines --triggers --events \
    --quick --extended-insert --set-charset \
    -h "$DB_HOST" -u "$DB_USER" -p"$DB_PASS" --all-databases > alldb.sql

# 壓縮存檔
tar -czf alldb.0.tar.gz alldb.sql
rm -f alldb.sql

# 可選：同步至遠端備份機（需 SSH 金鑰免密登入）
# rsync -avz --delete "$BACKUP_DIR/" backup_user@backup-server:/remote/mysql-backup/

部署步驟

1. 建立備份目錄與腳本權限：

mkdir -p /home/mysql-backup
chmod 755 /root/mysql-daily-backup.sh
chown root:root /root/mysql-daily-backup.sh

2. 建立專用備份帳號（最小權限）：

CREATE USER 'backup_user'@'localhost' IDENTIFIED BY 'StrongPassword';
GRANT SELECT, RELOAD, LOCK TABLES, REPLICATION CLIENT, SHOW VIEW, EVENT, TRIGGER
ON *.* TO 'backup_user'@'localhost';
FLUSH PRIVILEGES;

3. 加入 cron 每日執行（凌晨 3:00）：

crontab -e
# 加入以下行
0 3 * * * /root/mysql-daily-backup.sh >> /var/log/mysql-backup.log 2>&1

腳本設計亮點

進階擴展：雙機異地備份

1. 備份機同樣部署 MySQL（僅作為備份存儲，不對外服務）。
2. 主庫配置 SSH 金鑰免密登入備份機。
3. 啟用腳本中的 rsync 行，每日備份後自動推送。
4. 備份機同樣配置輪轉腳本，形成兩地三中心保護。

結語

「每日全備 + 週輪轉 + 壓縮 + 異地同步 + 季度恢複演練」是中小型 MySQL 環境的標準備份基線。本腳本開箱即用，請根據資料量調整保留天數、壓縮等級、同步頻率，並納入監控告警體系（備份失敗、磁碟空間不足、檔案大小異常）。