Posted inMySQL

MySQLバイナリログで秒単位のデータ復元を実現する：Point-in-Time Recovery（PITR）完全ガイド

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

午前2時、WHERE句のないDELETE文

電話が鳴り響く。Slackが爆発する。ユーザーから「今日の注文データが全部消えた」という報告が入る。開発者がクエリ履歴を見返すと、見慣れたはずのSQL文から、最も重要な末尾が抜け落ちていた：

DELETE FROM orders WHERE status = 'pending';
-- 実際に実行されたのは：
DELETE FROM orders;

以前、深夜3時にデータベース破損が発生してバックアップからリストアした経験があり、それ以来毎日バックアップを確認するようにしている。しかしバックアップだけでは不十分だ。直近のバックアップが午前1時で、障害が2時47分に発生したとすると、約2時間分のデータが失われる。1時間に数百件の注文を処理するECシステムでは、それは本当の意味での惨事だ。

まさにそのときに必要なのがPoint-in-Time Recovery（PITR）だ。フルバックアップとMySQLバイナリログを組み合わせることで、データベースを任意の時点、秒単位の精度で巻き戻せる機能だ。

バイナリログとは何か、そしてなぜ命を救うのか

バイナリログ（binlog）は、MySQLがすべてのデータ変更を時系列で記録するログファイルだ：INSERT、UPDATE、DELETE、CREATE TABLE、DROP TABLEなど。SELECTはデータを変更しないため記録されない。

PITRの仕組みは実は難しくない：

  1. 直近のフルバックアップからリストアする（開始地点）
  2. バックアップ時点から障害発生直前までのバイナリログを再生する（終了地点）

結果：問題のあるSQL文を含まない、クリーンな状態のデータベースが手に入る。

バイナリログが有効かどうかを確認する

SHOW VARIABLES LIKE 'log_bin';
-- Variable_name | Value
-- log_bin       | ON

OFFと表示された場合は先に有効化する必要がある——残念ながら、binlogが記録されていなかった過去は復元できない。本番環境では、これはオプションではなく最低条件だ。

MySQL 8.xでバイナリログを有効にする

# /etc/mysql/mysql.conf.d/mysqld.cnf に追記する
[mysqld]
server-id          = 1
log_bin            = /var/log/mysql/mysql-bin.log
binlog_format      = ROW
binlog_expire_logs_seconds = 604800  # 7日間保持する
max_binlog_size    = 100M

sudo systemctl restart mysql

PITRにはROWフォーマットが最善の選択だ——SQLではなく、変更された各行の実際の値を記録する。NOW()やRAND()のような関数を使ったUPDATE文で重要な違いが出る：STATEMENTフォーマットでは再生のたびに異なる結果になるが、ROWフォーマットは常に実際に書き込まれた値を保持する。MySQL 8のインストールと設定の完全ガイドも参照すると、サーバー構成の全体像を把握できる。

PITRを段階的に実践する

ステップ1：バイナリログ内の時刻と位置を特定する

障害発生時の最優先事項：リストアや他の作業を始める前に、どのSQL文が問題を引き起こしたか、そしてbinlog内のその位置を正確に特定することだ。

# 現在のbinlogファイル一覧を確認する
mysqlbinlog --no-defaults /var/log/mysql/mysql-bin.index

# またはMySQL内で確認する
SHOW BINARY LOGS;

# binlogの内容を時間でフィルタリングして読み取る
mysqlbinlog --no-defaults \
  --start-datetime="2026-04-01 02:00:00" \
  --stop-datetime="2026-04-01 03:00:00" \
  /var/log/mysql/mysql-bin.000023 | grep -A5 "DELETE FROM orders"

出力には次のような行が表示される：

# at 487219
#260401  2:47:33 server id 1  end_log_pos 487391
### DELETE FROM `mydb`.`orders`

2つの情報を記録しておく：タイムスタンプ（2:47:33）とポジション（487219）。タイムスタンプは重複する可能性があるため、ポジションのほうが正確だ。

ステップ2：フルバックアップをリストアする

障害前の午前1時のバックアップがあると仮定する：

# mysqldumpからリストアする
mysql -u root -p mydb < backup_20260401_0100.sql

# Percona XtraBackupを使っている場合
xtrabackup --copy-back --target-dir=/backup/2026-04-01_01-00/

mysqldumpを使う際の小技：ダンプ時に必ず--master-data=2（MySQL 5.x）または--source-data=2（MySQL 8.x）フラグを追加すること。このフラグはバックアップ時点のbinlogポジションをSQLファイルの先頭に記録してくれるため、後の検索時間を大幅に節約できる：

mysqldump --single-transaction --source-data=2 \
  -u root -p mydb > backup_20260401_0100.sql

# ファイルの先頭に次の行が記録される：
-- CHANGE REPLICATION SOURCE TO SOURCE_LOG_FILE='mysql-bin.000023', SOURCE_LOG_POS=321847;

ステップ3：障害直前までバイナリログを再生する

ここは正確に行う必要がある——ポジションを誤るとさらにデータを失う。ポジション321847（バックアップ時点）からポジション487219直前（危険なDELETE文）まで再生する：

mysqlbinlog --no-defaults \
  --start-position=321847 \
  --stop-position=487219 \
  /var/log/mysql/mysql-bin.000023 | mysql -u root -p mydb

障害が複数のbinlogファイルにまたがる場合は、すべてを1つのコマンドに列挙する：

mysqlbinlog --no-defaults \
  --start-position=321847 \
  /var/log/mysql/mysql-bin.000022 \
  /var/log/mysql/mysql-bin.000023 \
  --stop-position=487219 | mysql -u root -p mydb

ステップ4：結果を確認する

-- 注文件数が戻っているか確認する
SELECT COUNT(*), MIN(created_at), MAX(created_at) FROM orders;

-- 業務ログがあれば照合する
SELECT * FROM orders ORDER BY created_at DESC LIMIT 10;

途中の特定SQL文をスキップしたい場合の対処

障害前の部分を再生し、障害部分をスキップして、その後の部分を続けて再生する必要がある場合がある。例えば障害がポジション487219から487391の間にあり、その後にも正常なトランザクションが続いている場合：

# 障害前の部分を再生する
mysqlbinlog --no-defaults \
  --start-position=321847 \
  --stop-position=487219 \
  /var/log/mysql/mysql-bin.000023 | mysql -u root -p mydb

# 障害後の部分を再生する（487219→487391の区間をスキップ）
mysqlbinlog --no-defaults \
  --start-position=487391 \
  /var/log/mysql/mysql-bin.000023 | mysql -u root -p mydb

本番環境での実践的な注意点

  • binlogを別の場所にバックアップする：binlogはMySQLと同じサーバー上にある。ディスクが壊れれば両方失う。rsyncまたはmysqlbinlog --read-from-remote-serverを使って、リアルタイムで別サーバーにbinlogをストリーミングすること。
  • PITRを定期的にテストする：テストされていないバックアップは存在しないも同然だ。毎月1回、ステージング環境でPITRを試して、実際に必要になる前に手順が確実に機能することを確認すること。
  • 適切な保持期間を設定する：7日間が一般的に適切だ。短すぎると障害の発見が間に合わず、長すぎるとストレージを圧迫する（大規模データベースでは1日数GBのbinlogが発生することは珍しくない）。SHOW BINARY LOGSで監視すること。
  • GTIDモード：GTIDレプリケーションを使用している場合は、トランザクションIDの競合を避けるためにbinlogの再生時に--skip-gtidsを追加すること。MySQL Group Replication環境ではGTIDが必須となるため、この点は特に重要だ。
# GTIDモードの場合
mysqlbinlog --no-defaults --skip-gtids \
  --start-position=321847 \
  --stop-position=487219 \
  /var/log/mysql/mysql-bin.000023 | mysql -u root -p mydb

まとめ

PITRは高度な機能ではない。これは生存スキルだ——MySQLの本番環境を任されるすべての人が習得しなければならないスキルだ。フルバックアップが出発点であり、バイナリログはその後の一歩一歩だ。この2つを組み合わせることで、災害の直前のどの瞬間にも巻き戻すことができる。

次に新しいMySQL本番環境を構築するとき、セットアップ完了後に最初にやることは：binlogを有効にし、--source-data=2を付けてバックアップを設定し、PITRを一度テストすること。あわせてMySQLサーバーのセキュリティ設定を見直すことで、本番データを多層的に保護できる。深夜2時になってから学ぼうとしてはいけない。

Share:
Tags: