【MySQL】バイナリログの役割とポイントインタイムリカバリ

MySQL
2022.12.16

こんにちは、かつコーチです。

前回はmysqldumpによるバックアップとリストアを解説しましたが、mysqldumpだけでは「昨晩0時時点」までしか戻せません。

もし障害が発生したのが夜23時だった場合、直近23時間分のデータは失われてしまいます。

今回は、この課題を解決するポイントインタイムリカバリ（PITR）と、その基盤となるバイナリログについて、上級者向けに解説します。

バイナリログの役割を再確認する

レプリケーションとPITRを支える共通基盤

前回のレプリケーションの回でも触れましたが、バイナリログ（binlog）はマスター上でのデータ変更をすべて記録するログファイルです。

レプリケーションではレプリカへの複製に使われましたが、バイナリログはそれ自体が「フルバックアップ以降のすべての変更履歴」でもあるため、バックアップと組み合わせることでピンポイントな復旧に使えます。

-- 現在のバイナリログの状態と一覧を確認する
SHOW MASTER STATUS;
SHOW BINARY LOGS;

-- バイナリログが有効になっているか確認する
SHOW VARIABLES LIKE 'log_bin';

log_binがONになっていない場合、バイナリログ自体が記録されていないため、PITRの前提条件としてまず有効化しておく必要があります。

# my.cnfでバイナリログを有効化する設定例
[mysqld]

log_bin = /var/log/mysql/mysql-bin binlog_expire_logs_seconds = 604800 server_id = 1

binlog_expire_logs_secondsで保持期間を指定でき、この例では7日間（604800秒）を超えた古いバイナリログが自動的に削除されます。

ポイントインタイムリカバリ（PITR）の考え方

フルバックアップ＋差分ログという組み合わせ

ポイントインタイムリカバリ（Point-in-Time Recovery）とは、フルバックアップに、その後のバイナリログを適用することで、任意の時刻の状態まで復元する手法です。

流れは大きく次の3ステップです。

  1. mysqldumpなどで取得した直近のフルバックアップをリストアする
  2. フルバックアップ取得時点以降のバイナリログを特定する
  3. 復元したい時刻（あるいは誤操作の直前）までのバイナリログだけを適用する
# ステップ1：フルバックアップをリストアする
mysql -u root -p my_database < backup_20260902_0000.sql

# ステップ2：バイナリログの内容を確認し、誤操作のクエリを特定する
mysqlbinlog --no-defaults /var/log/mysql/mysql-bin.000045 | less

# ステップ3：フルバックアップ以降〜誤操作の直前までのバイナリログを適用する
mysqlbinlog --no-defaults \
    --start-datetime="2026-09-02 00:00:01" \
    --stop-datetime="2026-09-02 22:58:00" \
    /var/log/mysql/mysql-bin.000045 | mysql -u root -p my_database

--stop-datetimeを誤操作発生の直前に設定することで、誤って実行されたDELETEやDROP TABLEのクエリだけを除外して復元できます。

GTIDやポジションを使った、より正確な指定

時刻指定は便利ですが、同じ秒に複数のクエリが実行されている場合など、意図せず必要なクエリまで除外してしまうことがあります。

より正確に指定したい場合は、バイナリログのポジションやGTIDを使います。

# 特定のポジション範囲を指定して適用する
mysqlbinlog --no-defaults \
    --start-position=4 \
    --stop-position=891023 \
    /var/log/mysql/mysql-bin.000045 | mysql -u root -p my_database

# 特定のGTIDを除外して適用する（誤操作のGTIDだけをスキップ）
mysqlbinlog --no-defaults \
    --exclude-gtids="3E11FA47-71CA-11E1-9E33-C80AA9429562:23" \
    /var/log/mysql/mysql-bin.000045 | mysql -u root -p my_database

誤操作のクエリが実行された正確なポジションやGTIDを特定できれば、--stop-positionや--exclude-gtidsでピンポイントに除外でき、時刻指定よりも取りこぼしのリスクを減らせます。

PITR運用の注意点

バイナリログフォーマットの選択

バイナリログには、SQL文そのものを記録するSTATEMENT形式、変更後の行データを記録するROW形式、状況に応じて自動切り替えするMIXED形式があります。

MySQL 8.0のデフォルトはROW形式で、NOW()やUUID()のような結果が非決定的な関数を含むクエリでも、レプリカやリストア先で同じ結果を再現できるという利点があります。

-- 現在のバイナリログフォーマットを確認する
SHOW VARIABLES LIKE 'binlog_format';

PITRを前提にするなら、STATEMENT形式ではなくROW形式（デフォルト）のまま運用することを強く推奨します。

私がPITRに救われた本番障害

以前、深夜のバッチ処理を修正した際、対象を絞るWHERE句を書き忘れたままUPDATEを実行してしまい、本来1件だけ更新するはずが数万件のレコードを上書きしてしまったことがあります。

幸い、直近のフルバックアップとバイナリログが残っていたため、まずステージング環境でフルバックアップをリストアし、mysqlbinlogで該当時間帯のログを確認して、事故のUPDATE文が実行された正確なポジションを特定しました。

# 事故のUPDATE文の直前までを適用し、そこで止める
mysqlbinlog --no-defaults \
    --start-position=4 \
    --stop-position=2201456 \
    /var/log/mysql/mysql-bin.000078 | mysql -u root -p my_database

事故直前のポジションで復元を止めたことで、失われたのはその後の数分間の正常な書き込みだけに抑えられ、数万件の誤更新は防ぐことができました。

この経験から、mysqldumpだけの「1日単位のバックアップ」では守れない領域があり、バイナリログを併用したPITRの仕組みを事前に整えておくことの重要性を痛感しました。

まとめ

この記事のポイント

  • バイナリログはレプリケーションの複製元であると同時に、フルバックアップ以降の変更履歴としてPITRにも使える
  • PITRは「フルバックアップのリストア＋バイナリログの適用」という2段構えで、任意の時刻まで復元する手法
  • mysqlbinlogの--start-datetime/--stop-datetimeや、より正確な--start-position/--exclude-gtidsで適用範囲を絞れる
  • ROW形式のバイナリログは非決定的な関数を含むクエリでも結果を正確に再現でき、PITR運用に向いている
  • 誤操作の直前で復元を止められれば、被害を最小限に抑えられる

次に読むべき記事

「レプリケーション・バックアップ編」は今回で完結です。

次は「ユーザー権限管理：GRANT・REVOKEの基本」から始まる「ユーザー管理・セキュリティ編」で、データを守るもう1つの観点である権限管理を解説していきます。

タグ: MySQL, 上級者向け, バックアップ

タイトルとURLをコピーしました