腳本功能特色

• 自動偵測所有資料庫，逐庫 mysqldump 產出 .sql。
• 每庫檔案含 DROP/CREATE DATABASE 與 USE，支援直接 SOURCE 還原。
• 每日打包為 YYYYMMDD.tar，自動清理超期備份 (可配置留存份數)。
• 完整日誌記錄 (耗時、清理紀錄)。

核心腳本 (mysql_data_backup.sh)

#!/bin/bash
# ===== 使用者配置區 =====
DB_HOST="192.168.0.222"
DB_USER="admin"
DB_PASS="123456"
BACKUP_DIR="/sqlbackup"
KEEP_DAYS=4          # 保留份數
LOG_FILE="/sqlbackup/backup.log"
# =======================

# 檢查相依指令
command -v mysqlshow >/dev/null || { echo "mysqlshow not found"; exit 1; }
command -v mysqldump >/dev/null || { echo "mysqldump not found"; exit 1; }

mkdir -p "$BACKUP_DIR"
touch "$LOG_FILE"

log() { echo "[$(date '+%F %T')] $*" | tee -a "$LOG_FILE"; }

log "=== 備份開始 ==="
START_SEC=$(date +%s)

# 取得資料庫列表 (排除系統庫)
DBS=$(mysqlshow -u"$DB_USER" -p"$DB_PASS" -h"$DB_HOST" \
      | awk 'NR>4 && NF>1 {print $2}' \
      | grep -Ev '^(Database|information_schema|performance_schema|mysql|sys|test)$')

if [ -z "$DBS" ]; then
    log "無使用者資料庫，結束。"
    exit 0
fi

DATE_STAMP=$(date +%Y%m%d)
DAY_DIR="$BACKUP_DIR/$DATE_STAMP"
mkdir -p "$DAY_DIR"

for DB in $DBS; do
    log "備份資料庫: $DB"
    SQL_FILE="$DAY_DIR/$DB.sql"
    {
        echo "DROP DATABASE IF EXISTS \`$DB\`;"
        echo "CREATE DATABASE \`$DB\` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;"
        echo "USE \`$DB\`;"
        mysqldump -h"$DB_HOST" -u"$DB_USER" -p"$DB_PASS" \
                  --single-transaction --routines --triggers --events "$DB"
    } > "$SQL_FILE"
done

# 打包
log "打包壓縮..."
tar -czf "$BACKUP_DIR/$DATE_STAMP.tar.gz" -C "$BACKUP_DIR" "$DATE_STAMP"
rm -rf "$DAY_DIR"

# 清理舊備份
log "清理超期備份 (保留 $KEEP_DAYS 份)..."
cd "$BACKUP_DIR"
ls -1t *.tar.gz 2>/dev/null | tail -n +$((KEEP_DAYS+1)) | while read OLD; do
    log "刪除: $OLD"
    rm -f "$OLD"
done

END_SEC=$(date +%s)
log "=== 備份完成，總耗時 $((END_SEC-START_SEC)) 秒 ==="

部署步驟

1. 存為 /usr/local/bin/mysql_data_backup.sh，chmod +x。
2. 建立專用備份帳號：

GRANT SELECT, LOCK TABLES, SHOW VIEW, EVENT, TRIGGER, PROCESS
ON *.* TO 'backup_user'@'localhost' IDENTIFIED BY 'StrongPass!';
FLUSH PRIVILEGES;

3. 加入 crontab (每天凌晨 2:30)：

30 2 * * * /usr/local/bin/mysql_data_backup.sh

還原操作

# 1. 解壓
tar -xzf 20231015.tar.gz

# 2. 進入 MySQL
mysql -u root -p

# 3. 逐庫還原 (含建庫)
SOURCE /path/20231015/db1.sql;
SOURCE /path/20231015/db2.sql;

進階強化建議

• 上傳至異地存儲 (S3、NFS、rsync 遠端)。
• 加入加密：gpg --symmetric --cipher-algo AES256 backup.tar.gz。
• 整合監控：備份失敗發送 Email/Slack/鉚釘告警。
• 定期演練還原，驗證備份可用性。