Ubuntu Server MySQL 定時備份腳本實戰（含壓縮、保留策略、Cron 部署）

生產環境 MySQL 備份必須：自動化、壓縮、保留策略、異地存儲、可恢復驗證。本文提供生產級 Shell 腳本，部署於 Ubuntu Server，每日定時執行。

一、備份腳本 /home/mysql_data/mysql_databak.sh

#!/bin/bash
# ==========================================
# MySQL 定時備份腳本
# 功能：單庫備份 → 壓縮 → 保留最近 N 天 → 異地上傳(可選)
# ==========================================

set -euo pipefail

# ----- 配置區 -----
DUMP="/usr/bin/mysqldump"           # mysqldump 路徑
OUT_DIR="/home/mysql_data"          # 備份存放目錄
LINUX_USER="root"                   # 文件所有者
DB_NAME="pw85"                      # 目標數據庫
DB_USER="root"                      # 備份賬號（建議專用備份用戶）
DB_PASS="123456"                    # 密碼（建議用 ~/.my.cnf）
DAYS=7                              # 保留天數
DATE=$(date '+%Y_%m_%d')            # 日期後綴
OUT_SQL="${DATE}.sql"               # 臨時 SQL 文件
TAR_SQL="mysqldata_bak_${DATE}.tar.gz"  # 最終壓縮包
# ------------------

# 進入備份目錄
cd "$OUT_DIR"

# 1. 執行備份（關鍵參數解釋見下表）
"$DUMP" -u"$DB_USER" -p"$DB_PASS" "$DB_NAME" \
    --default-character-set=utf8mb4 \
    --opt \
    -Q \
    -R \
    --skip-lock-tables \
    --single-transaction \
    --master-data=2 \
    > "$OUT_SQL"

# 2. 壓縮
tar -czf "$TAR_SQL" "$OUT_SQL"

# 3. 清理臨時 SQL
rm -f "$OUT_SQL"

# 4. 修改所有者
chown "$LINUX_USER:$LINUX_USER" "$OUT_DIR/$TAR_SQL"

# 5. 清理過期備份（保留最近 DAYS 天）
find "$OUT_DIR" -name "mysqldata_bak_*" -type f -mtime +$DAYS -delete

# 6. 可選：上傳至異地存儲（OSS/S3/FTP/SCP）
# aws s3 cp "$OUT_DIR/$TAR_SQL" s3://my-backup-bucket/mysql/
# scp "$OUT_DIR/$TAR_SQL" user@backup-server:/backups/mysql/

echo "[$(date)] Backup completed: $TAR_SQL"

二、關鍵 mysqldump 參數解析

三、部署與權限

# 1. 創建目錄與腳本
mkdir -p /home/mysql_data
cat > /home/mysql_data/mysql_databak.sh <<'EOF'
# 上述腳本內容
EOF

# 2. 賦予執行權限
chmod +x /home/mysql_data/mysql_databak.sh

# 3. 安全：將密碼移至 ~/.my.cnf（僅 root 可讀）
cat > /root/.my.cnf <

# 編輯 root crontab
sudo crontab -e

# 每天 22:45 執行備份
45 22 * * * /home/mysql_data/mysql_databak.sh >> /var/log/mysql_backup.log 2>&1

# 或使用 systemd timer（更現代、支持隨機延遲、日志管理）
# /etc/systemd/system/mysql-backup.service
# /etc/systemd/system/mysql-backup.timer

# 1. 解壓
tar -zxvf mysqldata_bak_2024_01_15.tar.gz

# 2. 創建目標庫
mysql -e "CREATE DATABASE IF NOT EXISTS pw85_restore CHARACTER SET utf8mb4;"

# 3. 導入
mysql pw85_restore < 2024_01_15.sql

# 4. 校驗
mysql -e "SELECT COUNT(*) FROM pw85_restore.core_table;"
mysql -e "CHECKSUM TABLE pw85_restore.core_table;"