シェルスクリプトでmysqldumpの自動バックアップを設計する方法｜世代管理・エラートラップ・ローテーション実装パターン

この記事のポイント

・mysqldump失敗を検出するにはset -euo pipefailとtrap ERRの組み合わせが基本
・接続情報は.my.cnfに外部化するとスクリプト内にパスワードを書かずに済む
・findコマンドで指定日数より古いダンプファイルを自動削除して世代管理できる
・gzip -tで圧縮ファイルの整合性チェックをバックアップ直後に自動実行する

# NG例：エラー検知も世代管理もない単純なcron設定
0 2 * * * mysqldump -u root -pPASSWORD mydb | gzip > /backup/mydb_$(date +%Y%m%d).sql.gz

# /etc/mysql/backup.cnf（rootが読める600権限で保存する）
[client]
user=backup_user
password=s3cret_pass
host=127.0.0.1

# 権限設定（他ユーザーに読まれないよう必ず実施）
$ chmod 600 /etc/mysql/backup.cnf
$ ls -la /etc/mysql/backup.cnf
-rw------- 1 root root 61 Sep 21 09:00 /etc/mysql/backup.cnf

#!/bin/bash
set -euo pipefail

MYSQL_CNF="/etc/mysql/backup.cnf"
BACKUP_DIR="/backup/mysql"
DB_NAME="mydb"
DATE=$(date +%Y%m%d_%H%M%S)
DUMP_FILE="${BACKUP_DIR}/${DB_NAME}_${DATE}.sql.gz"
TMPFILE=""

# バックアップディレクトリがなければ作成
mkdir -p "$BACKUP_DIR"

#!/bin/bash
set -euo pipefail

MYSQL_CNF="/etc/mysql/backup.cnf"
BACKUP_DIR="/backup/mysql"
DB_NAME="mydb"
DATE=$(date +%Y%m%d_%H%M%S)
DUMP_FILE="${BACKUP_DIR}/${DB_NAME}_${DATE}.sql.gz"
TMPFILE=""

# 後片付け関数（EXITで必ず呼ばれる）
cleanup() {
  local exit_code=$?
  if [[ -n "$TMPFILE" && -f "$TMPFILE" ]]; then
    rm -f "$TMPFILE"
  fi
  if [[ $exit_code -ne 0 ]]; then
    echo "[ERROR] バックアップが失敗しました（exit code: ${exit_code}）" >&2
    # 不完全なダンプファイルを削除
    [[ -f "$DUMP_FILE" ]] && rm -f "$DUMP_FILE"
  fi
}
trap cleanup EXIT

mkdir -p "$BACKUP_DIR"

# 一時ファイルに書き出してから最終的にリネーム（原子的置換）
TMPFILE=$(mktemp "${BACKUP_DIR}/.${DB_NAME}_XXXX.sql.gz.tmp")
mysqldump --defaults-extra-file="$MYSQL_CNF" \
  --single-transaction \
  --routines \
  --events \
  "$DB_NAME" | gzip -c > "$TMPFILE"

# 問題がなければ最終ファイル名にリネーム
mv "$TMPFILE" "$DUMP_FILE"
TMPFILE=""  # cleanup関数がTMPFILEを削除しないようクリア
echo "[OK] バックアップ完了: ${DUMP_FILE}"

# バックアップ保持日数の設定
RETENTION_DAYS=14

# 削除前に残存ファイル数を確認
BEFORE_COUNT=$(find "$BACKUP_DIR" -name "${DB_NAME}_*.sql.gz" | wc -l)

# 14日より古いバックアップを削除
find "$BACKUP_DIR" \
  -name "${DB_NAME}_*.sql.gz" \
  -mtime +"$RETENTION_DAYS" \
  -delete

AFTER_COUNT=$(find "$BACKUP_DIR" -name "${DB_NAME}_*.sql.gz" | wc -l)
DELETED=$(( BEFORE_COUNT - AFTER_COUNT ))
echo "[INFO] ローテーション完了: ${DELETED}本削除（残存: ${AFTER_COUNT}本）"

# 曜日で処理を切り分ける（0=日曜, 1=月曜 ... 6=土曜）
DOW=$(date +%w)

if [[ "$DOW" -eq 0 ]]; then
  # 日曜: フルバックアップ
  TYPE="full"
  mysqldump --defaults-extra-file="$MYSQL_CNF" \
    --single-transaction --all-databases | gzip -c > \
    "${BACKUP_DIR}/all_${TYPE}_${DATE}.sql.gz"
else
  # 月～土: バイナリログのフラッシュ＆コピー
  TYPE="binlog"
  mysql --defaults-extra-file="$MYSQL_CNF" -e "FLUSH LOGS;"
  # バイナリログの保管は /var/lib/mysql/mysql-bin.* を対象にする
  rsync -a /var/lib/mysql/mysql-bin.* \
    "${BACKUP_DIR}/binlog_${DATE}/"
fi
echo "[OK] ${TYPE} バックアップ完了"

# バックアップ後すぐに整合性チェック
if gzip -t "$DUMP_FILE" 2>/dev/null; then
  echo "[OK] 整合性チェック: ${DUMP_FILE}"
else
  echo "[ERROR] ダンプファイルが破損しています: ${DUMP_FILE}" >&2
  rm -f "$DUMP_FILE"
  exit 1
fi

# ファイルサイズが極端に小さい場合もエラーとして扱う（100KB未満）
FILE_SIZE=$(stat -c%s "$DUMP_FILE")
if [[ "$FILE_SIZE" -lt 102400 ]]; then
  echo "[WARN] ダンプファイルが異常に小さいです: ${FILE_SIZE}バイト" >&2
fi

ALERT_MAIL="admin@example.com"
HOSTNAME_STR=$(hostname -s)

notify_error() {
  local message="$1"
  local subject="[BACKUP ERROR] ${HOSTNAME_STR}: ${DB_NAME}"
  # mailxがある環境向け（sendmailの場合はsendmailコマンドに変える）
  echo "$message" | mailx -s "$subject" "$ALERT_MAIL" 2>/dev/null || \
    echo "[WARN] メール送信に失敗しました" >&2
}

cleanup() {
  local exit_code=$?
  if [[ -n "$TMPFILE" && -f "$TMPFILE" ]]; then
    rm -f "$TMPFILE"
  fi
  if [[ $exit_code -ne 0 ]]; then
    local msg="バックアップ失敗（exit: ${exit_code}）\nDB: ${DB_NAME}\nサーバー: ${HOSTNAME_STR}\n時刻: $(date '+%Y-%m-%d %H:%M:%S')"
    echo -e "$msg" >&2
    notify_error "$msg"
    [[ -f "$DUMP_FILE" ]] && rm -f "$DUMP_FILE"
  fi
}
trap cleanup EXIT

# NG: mysqldump の exit codeだけを信じる
mysqldump -u root mydb > /backup/mydb.sql
echo $?  # 0が返っても不完全な可能性がある

# OK: gzip -t でダンプ後に整合性検証を必ず追加する
mysqldump ... | gzip -c > "$TMPFILE"
gzip -t "$TMPFILE" || { echo "破損検出" >&2; exit 1; }

# バックアップ専用ユーザーに必要な権限
mysql> GRANT SELECT, RELOAD, LOCK TABLES, REPLICATION CLIENT,
           SHOW VIEW, EVENT, TRIGGER
       ON *.* TO 'backup_user'@'127.0.0.1';
mysql> FLUSH PRIVILEGES;