Posted inMySQL

MySQLデータベースのバックアップとリストア完全ガイド：本番環境での実践的な経験

MySQL tutorial - IT technology blog
MySQL tutorial - IT technology blog

本番データベースが深夜2時にダウン — 思い出したくない教訓

去年、かなり深刻な障害に対処しなければならなかった：power outageによってサーバーへの書き込み処理が中断され、テーブルが破損してしまったのだ。辛かったのはデータベースが壊れたことではなく、チームの最後のバックアップにはスキーマしか保存されておらず、データが含まれていなかったことだ。その夜3時間、自分とリードデブがログファイルから手作業で各テーブルを復旧した。

それ以来学んだのは、MySQLのバックアップはコマンドを実行するだけでは不十分だということだ。何をバックアップしているのか、どうリストアするのか、そして最も重要なのは、定期的にリストアのテストをすることだ。

MySQLバックアップが思った以上に失敗しやすい理由

これまで対処してきたインシデントを振り返ると、MySQLバックアップの失敗は決まって同じパターンに当てはまる：

  • スキーマのみのバックアップ：mysqldumpを実行するもフラグが誤っており、データなしでstructureのみをdumpしてしまう
  • 一貫性がない：write transactionが進行中にバックアップすると、テーブル間でデータの整合性がなくなる
  • バックアップファイルの破損：途中でディスクが満杯になったりプロセスがkillされたりすると、dumpファイルが不完全になるが誰も気づかない
  • リストアのテストをしない：バックアップは正常に実行されても、リストア時にcharacter set、foreign key、triggerのエラーが発生する

当時の本番データベースはMySQL 8.0で、約50GBのデータがあった。安定したワークフローを見つけるまでいくつかの方法を試した。最初は純粋なmysqldumpを使っていたが、毎回バックアップに45分近くかかり、その間サーバーが明らかに遅くなっていた。

MySQLのバックアップ方法

1. mysqldump — シンプルで小規模データベースには十分

追加インストール不要で、MySQLのインストール時から利用できる。10GB以下のデータベース、またはゼロダウンタイムバックアップが不要な開発/ステージング環境に適している。

# 単一データベースのバックアップ
mysqldump -u root -p mydb > mydb_backup_$(date +%Y%m%d_%H%M%S).sql

# 複数データベースの同時バックアップ
mysqldump -u root -p --databases mydb otherdb > multi_backup.sql

# MySQLサーバー全体のバックアップ
mysqldump -u root -p --all-databases > full_backup.sql

本番環境のバックアップ時に欠かせないフラグ：

mysqldump -u root -p \
  --single-transaction \     # 一貫性を確保し、InnoDBのテーブルをロックしない
  --routines \               # ストアドプロシージャ、ファンクションを含める
  --triggers \               # トリガーを含める
  --events \                 # スケジュールされたイベントを含める
  --hex-blob \               # バイナリデータをより安全にエンコード
  mydb > mydb_backup.sql

--single-transactionは本番環境で常に有効にしているフラグだ。テーブルをロックする代わりに、一貫性のある読み取りトランザクションを開始する——バックアップ中も他のwriteトランザクションは通常通り動作する。writeトラフィックがあるデータベースでこのフラグを省略すると、テーブル間でバックアップデータの整合性が取れなくなる。

dumpファイルからのリストア：

# 既存データベースへのリストア
mysql -u root -p mydb < mydb_backup.sql

# 新規データベースを作成してリストア
mysql -u root -p -e "CREATE DATABASE mydb_restored CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;"
mysql -u root -p mydb_restored < mydb_backup.sql

2. mysqlpump — 並列バックアップで大幅に高速化

MySQL 5.7.8で追加されたmysqlpumpは並列スレッド機能を備えており、テーブルを順次ではなく同時にexportする。同じ50GBのデータベースで、バックアップ時間が45分から18分に短縮された。

mysqlpump -u root -p \
  --default-parallelism=4 \     # 4スレッドで並列実行
  --single-transaction \
  mydb > mydb_pump_backup.sql

出力に進捗インジケーターがあるため、画面が固まったまま待つのではなく、どこまで進んでいるかわかる。実際の注意点として、8スレッドでテストするとディスクI/Oがsaturationを起こし、4スレッドより遅くなった。サーバーのディスクスループットが不明な場合は、ステージング環境で事前にベンチマークを取ることをお勧めする。

3. Percona XtraBackup — 本格的な本番環境向けホットバックアップ

バックアップ中にサーバーが遅くなることを許容できない大規模データベースには？XtraBackupが答えだ。mysqldumpのようにSQLをexportする代わりに、InnoDBファイルを物理レベルで直接コピーする——テーブルロックなし、クエリへの影響なし、そしてストレージ容量を節約するための増分バックアップもサポートしている。

# Ubuntu/Debianへのインストール
apt install percona-xtrabackup-80

# フルバックアップ
xtrabackup --backup \
  --user=root \
  --password=yourpassword \
  --target-dir=/backup/mysql/full/$(date +%Y%m%d)

# 増分バックアップ（前回からの変更分のみバックアップ）
xtrabackup --backup \
  --user=root \
  --password=yourpassword \
  --target-dir=/backup/mysql/inc/$(date +%Y%m%d) \
  --incremental-basedir=/backup/mysql/full/20250301

XtraBackupでのリストアは2ステップが必要——prepareステップは必須で省略できない：

# ステップ1：Prepare — 一貫性を確保するためにログを適用
xtrabackup --prepare --target-dir=/backup/mysql/full/20250301

# ステップ2：MySQLのdatadirにデータをコピー
systemctl stop mysql
xtrabackup --copy-back --target-dir=/backup/mysql/full/20250301
chown -R mysql:mysql /var/lib/mysql
systemctl start mysql

自動バックアップスクリプト — 実際に使用しているもの

以下のスクリプトは毎晩2時にcronで実行される。リストアが必要な場合に個別に復元しやすいよう、--all-databasesを使わず各データベースを個別にバックアップしている。ファイルはすぐに圧縮され、7日以上古いバックアップは自動的に削除される：

#!/bin/bash
# mysql_backup.sh

DB_USER="backup_user"
DB_PASS="$(cat /etc/mysql_backup_pass)"  # パスワードをハードコードしない
BACKUP_DIR="/backup/mysql"
DATE=$(date +%Y%m%d_%H%M%S)
RETENTION_DAYS=7
LOG_FILE="/var/log/mysql_backup.log"

echo "[$DATE] Starting MySQL backup..." >> $LOG_FILE

# 各データベースを個別にバックアップ（all-databasesより復元しやすい）
for DB in $(mysql -u$DB_USER -p$DB_PASS -e "SHOW DATABASES;" \
  | grep -Ev "(Database|information_schema|performance_schema|sys)"); do

    OUTPUT_FILE="$BACKUP_DIR/${DB}_${DATE}.sql.gz"

    mysqldump -u$DB_USER -p$DB_PASS \
        --single-transaction \
        --routines \
        --triggers \
        "$DB" | gzip -9 > "$OUTPUT_FILE"

    if [ $? -eq 0 ]; then
        echo "[$DATE] OK: $DB → $OUTPUT_FILE" >> $LOG_FILE
    else
        echo "[$DATE] ERROR: Failed to backup $DB" >> $LOG_FILE
        # 必要に応じてSlack/emailアラートをここに追加
    fi
done

# RETENTION_DAYS日より古いバックアップを削除
find $BACKUP_DIR -name "*.sql.gz" -mtime +$RETENTION_DAYS -delete
echo "[$DATE] Cleaned backups older than $RETENTION_DAYS days" >> $LOG_FILE

毎日午前2時に実行するためにcrontabに追加：

0 2 * * * /usr/local/bin/mysql_backup.sh

リストアのテスト — ほとんどの人が省略する必須ステップ

リストアテストをしていないバックアップ＝本当のバックアップではない。極端に聞こえるかもしれないが、毎月正常に実行されていたバックアップが、実際にリストアしようとしたらエンコーディングエラーになるケースや、dumpファイルが途中で切れていても誰も気づかないケースを実際に目の当たりにしてきた。毎月一度、ステージングサーバーにリストアして重要なテーブルの行数を確認している：

# 解凍してテストデータベースへリストア
gunzip -c /backup/mysql/mydb_20250301_020000.sql.gz | \
  mysql -u root -p mydb_test

# 重要なテーブルの行数を確認
mysql -u root -p mydb_test -e "
  SELECT 'users' AS tbl, COUNT(*) AS cnt FROM users
  UNION ALL
  SELECT 'orders', COUNT(*) FROM orders
  UNION ALL
  SELECT 'products', COUNT(*) FROM products;
"

binlogと組み合わせた3-2-1バックアップ戦略

こうした障害を何度か経験した後、より徹底的な対策としてMySQLに3-2-1戦略を適用することにした：

  • 3つのバックアップ：週次フルバックアップ + 日次増分バックアップ + バイナリログによる継続的バックアップ
  • 2種類のストレージ：ローカルディスクとクラウド（S3、Backblaze B2）
  • 1つのオフサイト：最低1つは本番サーバーとは別の場所に保管

スクリプト実行後にバックアップをS3にアップロード：

# AWS CLIのインストールとcredentials設定が必要
aws s3 cp /backup/mysql/mydb_${DATE}.sql.gz \
  s3://your-bucket/mysql-backups/ \
  --storage-class STANDARD_IA   # Infrequent Access — Standardより低コスト

バイナリログ（binlog）は、あの障害で自分を救ってくれたものだ。binlogが有効になっていれば、ポイントインタイムリカバリが可能——障害が発生する直前の時点まで正確にリストアできる：

# /etc/mysql/mysql.conf.d/mysqld.cnfでbinlogを有効化
[mysqld]
log_bin = /var/log/mysql/mysql-bin.log
binlog_format = ROW
binlog_expire_logs_seconds = 1209600  # 14日間

# ポイントインタイムリカバリ：バックアップ後から必要な時点までbinlogを適用
mysqlbinlog \
  --start-datetime="2025-03-01 02:00:00" \
  --stop-datetime="2025-03-01 14:30:00" \
  /var/log/mysql/mysql-bin.000123 | mysql -u root -p mydb

50GBの本番データベースでの現在の構成：週末ごとにXtraBackupでフルバックアップ、日中の継続的なリカバリのためにbinlog、重要な各データベースの日次バックアップにmysqldumpを使用している。すべて実行後にS3に同期される。3層の保護——そして重要なのは、3つとも実際にリストアテスト済みだということだ。誰も確認しないcronジョブが静かに動いているだけだった頃より、ずっと安心して眠れるようになった。

Share:
Tags: