Linux 定時備份 MySQL 資料庫完整實作指南

前言

資料庫備份是系統維護中最關鍵的環節之一。本文將詳細介紹如何在 Linux 環境下，透過 Shell 腳本結合 crontab 實現 MySQL 資料庫的自動化定時備份，並包含舊備份自動清理與特定日期備份保留的進階策略。

備份腳本設計

建立一個名為 mysql_bak.sh 的 Shell 腳本，內容如下：

#!/bin/bash
# MySQL 資料庫自動備份腳本
# 設定資料庫連線參數
DB_USER="root"
DB_PASS="your_password"
DB_NAME="your_database"
BACKUP_DIR="/home/bak/db"

# 產生帶日期的備份檔名
DATE_SUFFIX=$(date +%y%m%d)
BACKUP_FILE="${BACKUP_DIR}/sql_${DB_NAME}_${DATE_SUFFIX}.sql"

# 執行備份（使用 mysqldump）
/usr/local/mysql/bin/mysqldump -u${DB_USER} -p${DB_PASS} \
  -c --hex-blob --quick --default-character-set=utf8mb4 \
  -B ${DB_NAME} > ${BACKUP_FILE}

# 檢查備份是否成功
if [ $? -eq 0 ]; then
    echo "[$(date)] 備份成功: ${BACKUP_FILE}"
else
    echo "[$(date)] 備份失敗！" >&2
    exit 1
fi

# 保留策略：保留最近 7 天備份，且保留每月 1 號的備份
DAY_OF_MONTH=$(date -d '-7 day' +'%d')
if [ "${DAY_OF_MONTH}" != "01" ]; then
    OLD_FILE="${BACKUP_DIR}/sql_${DB_NAME}_$(date -d '-7 day' +'%y%m%d').sql"
    if [ -f "${OLD_FILE}" ]; then
        rm -f "${OLD_FILE}"
        echo "[$(date)] 已刪除 7 天前備份: ${OLD_FILE}"
    fi
fi

關鍵參數說明

• -c / --complete-insert：使用完整的 INSERT 語句（包含欄位名），利於資料遷移
• --hex-blob：以十六進位格式導出二進位欄位（BLOB、BINARY 等）
• --quick：逐行讀取大表，避免記憶體耗盡
• --default-character-set=utf8mb4：確保完整支援 Unicode（含 Emoji）
• -B / --databases：在輸出中包含 CREATE DATABASE 與 USE 語句

設定定時任務

編輯 crontab：

crontab -e

加入以下行，每天凌晨 2:30 執行備份：

30 2 * * * /path/to/mysql_bak.sh >> /var/log/mysql_backup.log 2>&1

賦予執行權限

chmod +x /path/to/mysql_bak.sh

進階建議

1. 遠端備份：搭配 scp 或 rsync 將備份檔同步至異地伺服器
2. 壓縮儲存：在腳本中加入 gzip ${BACKUP_FILE} 節省空間
3. 監控告警：整合監控系統（如 Zabbix、Prometheus）檢測備份狀態
4. 定期演練：每季進行一次資料還原測試，驗證備份可用性

常見問題排查