MySQL 自動備份腳本與實作指南

自動化備份腳本設計

以下 Shell 腳本示範如何利用 mysqldump 實現 MySQL 資料庫的定時自動備份，支援多資料庫、壓縮儲存與時間戳記命名。

核心備份腳本

#!/bin/bash
# 設定備份時間格式：年月日時分
BACKUP_TIME=$(date +%Y%m%d%H%M)
BACKUP_DIR="/sqlbak"
MYSQL_USER="root"
MYSQL_PASS="1234567890"

# 建立備份目錄
mkdir -p "${BACKUP_DIR}"

# 備份 products 資料庫
db="products"
echo "# 產品庫" > "${BACKUP_DIR}/${db}_${BACKUP_TIME}.sql"
mysqldump --add-drop-table -c --allow-keywords --force \
  --user="${MYSQL_USER}" --password="${MYSQL_PASS}" "${db}" \
  >> "${BACKUP_DIR}/${db}_${BACKUP_TIME}.sql"

# 備份 news 資料庫並壓縮
db="news"
echo "# 新聞庫" > "${BACKUP_DIR}/${db}_${BACKUP_TIME}.sql"
mysqldump --add-drop-table -c --allow-keywords --force \
  --user="${MYSQL_USER}" --password="${MYSQL_PASS}" "${db}" \
  | bzip2 -9 > "${BACKUP_DIR}/${db}_${BACKUP_TIME}.sql.bz2"

關鍵參數解析

• --add-drop-table：在 CREATE TABLE 前加入 DROP TABLE，確保還原時不會因表已存在報錯。
• -c (--complete-insert)：使用完整 INSERT 語法（含欄位名），提高相容性。
• --force：發生錯誤時繼續執行，避免單一表損壞中斷整個備份。
• --allow-keywords：允許使用保留字作為欄位名，自動加上反引號。
• bzip2 -9：最高壓縮比，大幅減少備份檔案體積。

生產環境建議

1. 將密碼存放於 ~/.my.cnf 而非腳本明文。
2. 搭配 cron 設定每日自動執行（如 0 2 * * * /path/backup.sh）。
3. 定期驗證備份檔案完整性，並異地儲存。