MySQLのバイナリログでポイントインタイムリカバリ（PITR）する手順

MySQL のポイントインタイムリカバリ（PITR、任意の時点への復旧）は、フルバックアップを戻したあと、その後の変更をバイナリログ（binlog）から再実行して、問題の操作の直前まで進める方法です。誤って DELETE や DROP TABLE を実行した、WordPress の投稿を一括削除してしまった、といった場面で、前日のバックアップより新しい状態まで戻せます。

この記事のコマンドは、2026年10月時点の MySQL 8.4 LTS（Docker の公式イメージ）と Ubuntu 24.04 の mysqlbinlog で実際に動かして確認しています。MySQL 8.0 と MariaDB の違いは、その都度書きます。

仕組みと前提

PITR には次の2つが両方とも必要です。どちらかが欠けると、その時点には戻せません。

• MySQL 8.0 以降は、バイナリログが初期設定で有効です（データディレクトリに binlog.000001 のような名前で作られます）。
• MariaDB は初期設定では無効です。log_bin を設定するまで PITR はできません。
• バイナリログはデータベースと同じディスクにあるので、ディスク故障やサーバーの喪失に備えるなら、フルバックアップと一緒に別の場所へ定期的にコピーしておく必要があります。

ステップ1: バイナリログの設定を確認する

まず、バイナリログが有効か、何日分残る設定かを確認します。

SELECT @@log_bin, @@binlog_format, @@binlog_expire_logs_seconds, @@log_bin_basename;
SHOW BINARY LOGS;

MySQL 8.4 の初期状態では次のようになります。

log_bin が 0 の場合や、保存期間を変えたい場合は、設定ファイルの [mysqld] セクションに書いて MySQL を再起動します。

[mysqld]
server_id = 1
log_bin = binlog
# フルバックアップの間隔より長くする（例: 14日）
binlog_expire_logs_seconds = 1209600

保存期間は、フルバックアップの間隔より長くしないと意味がありません。週1回のフルバックアップなのに binlog が3日で消えると、バックアップから4日目以降の変更は戻せなくなります。

expire_logs_days は MySQL 8.0 で非推奨になり、MySQL 8.4 には存在しません（8.4 で SHOW VARIABLES LIKE 'expire_logs_days' は何も返しません）。MySQL では binlog_expire_logs_seconds を使ってください。

ステップ2: binlogの位置を記録したフルバックアップを取る

mysqldump に --source-data=2 を付けると、バックアップを取った時点のバイナリログのファイル名と位置が、ダンプの先頭付近にコメントとして書き込まれます。これが復旧の出発点になります。

mysqldump -u root -p \
  --single-transaction \
  --source-data=2 \
  --routines --triggers --events \
  --all-databases | gzip > /backup/full-$(date +%Y%m%d-%H%M).sql.gz

• --single-transaction は InnoDB のテーブルを、ロックを長く持たずに一貫した状態で取り出します（WordPress のテーブルは通常 InnoDB です）。
• --source-data には RELOAD 権限と、バイナリログの状態を参照できる権限が必要です。
• --source-data は MySQL 8.0.26 で追加された名前です。それより古い MySQL と MariaDB では --master-data=2 を使います。

ダンプに記録された位置は次のように確認できます。

zcat /backup/full-20261110-0300.sql.gz | grep -m1 "CHANGE REPLICATION SOURCE TO"
# -- CHANGE REPLICATION SOURCE TO SOURCE_LOG_FILE='binlog.000002', SOURCE_LOG_POS=13222;

MariaDB や古い MySQL では CHANGE MASTER TO MASTER_LOG_FILE=... の形で書かれるので、grep -m1 "CHANGE MASTER TO" で探します。

バイナリログも別の場所にコピーする

使用中のファイルを閉じて新しいファイルに切り替えてから、閉じたファイルをコピーします。

mysql -u root -p -e "FLUSH BINARY LOGS;"
rsync -a /var/lib/mysql/binlog.0* backup-host:/backup/binlog/

cron で定期的に実行するなら、パスワードはコマンドに書かず、実行ユーザーの ~/.my.cnf（パーミッション 600）に置きます。

ステップ3: 事故が起きたら、まず書き込みを止めてbinlogを確保する

誤操作に気づいたら、次の順で動きます。

1. サイトをメンテナンス状態にする、またはアプリケーションを止めて、新しい書き込みを止める。
2. SHOW BINARY LOGS; でファイルの一覧を確認し、バイナリログ一式を別の場所へコピーする（自動削除や上書きで失わないため）。
3. 復旧作業は、可能なら別のサーバー（または別インスタンス）で行い、確認してから切り替える。本番のデータベースを直接上書きすると、手順を誤ったときに戻せなくなります。

ステップ4: 誤操作のイベント位置を特定する

mysqlbinlog でバイナリログを読み、誤操作の位置（バイト位置）を探します。行ベース（ROW）の記録は、そのままでは読めないので --base64-output=DECODE-ROWS --verbose を付けて SQL 風のコメントに変換します。

mysqlbinlog --base64-output=DECODE-ROWS --verbose \
  --start-datetime="2026-11-10 15:30:00" --stop-datetime="2026-11-10 15:40:00" \
  /var/lib/mysql/binlog.000002 | grep -n -B 20 "### DELETE FROM"

出力の例（必要な行だけ抜粋）です。

# at 13512
#261110 15:36:08 server id 1  end_log_pos 13591 CRC32 0xfe9728f1  Anonymous_GTID ...
# at 13591
#261110 15:36:08 server id 1  end_log_pos 13662 CRC32 0x842007b5  Query  thread_id=14 ...
# at 13662
#261110 15:36:08 server id 1  end_log_pos 13722 CRC32 0x5be60a3a  Table_map: `wordpress`.`wp_posts` mapped to number 120
# at 13722
#261110 15:36:08 server id 1  end_log_pos 13785 CRC32 0x52e6e411  Delete_rows: table id 120 flags: STMT_END_F
### DELETE FROM `wordpress`.`wp_posts`
...
# at 13785
#261110 15:36:08 server id 1  end_log_pos 13816 CRC32 0x0846cbe9  Xid = 105
# at 13816

読み方のポイントです。

• # at N は、その下のイベントが始まる位置です。
• 1つのトランザクションは GTID（GTID を使っていない場合は Anonymous_GTID）のイベントから始まり、Xid のイベントで終わります。上の例では、誤った削除のトランザクションは 13512 から始まり、13816 の手前で終わります。
• 止める位置には、Delete_rows の位置（13722）ではなく、トランザクションの先頭（13512）を使います。途中で止めると、トランザクションが中途半端に適用されます。

--start-datetime と --stop-datetime の時刻は、mysqlbinlog を実行するマシンのタイムゾーンで解釈されます。サーバーが UTC で動いていると、日本時間で指定した範囲とずれるので注意してください。また、MySQL の公式マニュアルは、時刻の指定は位置を探す目的だけに使い、適用には位置（--start-position / --stop-position）を使うよう勧めています。時刻で区切ると、イベントを取りこぼす危険が高くなるためです。

ステップ5: フルバックアップを戻し、直前まで再実行する

5-1. フルバックアップを戻す

zcat /backup/full-20261110-0300.sql.gz | mysql -u root -p

5-2. 誤操作の直前までbinlogを適用する

ダンプに記録された位置（13222）から、誤操作の直前（13512）までを適用します。

mysqlbinlog --start-position=13222 --stop-position=13512 \
  /backup/binlog/binlog.000002 | mysql -u root -p

フルバックアップのあとにバイナリログのファイルが複数ある場合は、1回の mysqlbinlog にまとめて渡します。--start-position は最初のファイル、--stop-position は最後のファイルに対して働きます。ファイルごとに別々の接続で流すと、一時テーブルなどを使うトランザクションが正しく再現されないことがあるため、公式マニュアルも1つの接続で適用するよう案内しています。

mysqlbinlog --start-position=13222 --stop-position=2048 \
  binlog.000002 binlog.000003 binlog.000004 | mysql -u root -p

5-3. 誤操作だけを飛ばして後の変更も戻す（必要な場合）

誤操作の後の正しい変更も戻したい場合は、誤操作のトランザクションの次（13816）から最後までを適用します。こうすると、誤操作だけを飛ばしてそれ以外の変更を残せます。

mysqlbinlog --start-position=13816 \
  /backup/binlog/binlog.000002 /backup/binlog/binlog.000003 | mysql -u root -p

検証用の環境では、削除前にあった4行が 5-2 で戻り、削除の後に追加した1行が 5-3 で戻ることを確認しています。

5-4. データを確認して切り替える

WordPress なら、投稿数や最新の投稿、管理画面の表示を確かめてから、本番に切り替えます。

SELECT post_status, COUNT(*) FROM wordpress.wp_posts GROUP BY post_status;

GTIDを使っている場合の注意

gtid_mode=ON のサーバーでは、すでに実行済みの GTID を持つトランザクションは、もう一度流しても自動的に飛ばされます。

• 誤操作をしたサーバー自身に binlog を流し直しても、実行済みのトランザクションは適用されません。新しいインスタンスにダンプを戻してから適用します。
• GTID が有効なサーバーで取った mysqldump のダンプには、既定で SET @@GLOBAL.gtid_purged=... が含まれます。新しいインスタンスに戻すと、ダンプ時点までの GTID が実行済みとして登録され、それ以降の binlog が適用できる状態になります。

MySQL 8.0・MariaDBとの違い

Ubuntu の MySQL パッケージでは、mysqlbinlog は mysql-server-core-8.0 パッケージに含まれています。データベースサーバーの入っていない作業用マシンで読む場合は、このパッケージを入れるか、サーバー上で実行します。

注意すること

• バイナリログには、変更されたデータそのものが入っています。 ファイルのアクセス権を mysql ユーザーに限り、コピー先も同じように保護してください。
• バイナリログはディスクを使います。 更新の多いサイトでは1日で数GBになることもあるので、SHOW BINARY LOGS; の File_size を見て、保存期間とディスクの空きを決めてください。
• 定期的に復旧の練習をします。 検証用の環境でフルバックアップと binlog から戻せることを確かめておかないと、いざというときに位置の記録がない、ファイルが欠けているといった問題に気づけません。

まとめ

PITR は「フルバックアップ＋その時点の binlog の位置＋それ以降の binlog 一式」がそろって初めてできます。普段は --source-data=2 付きでバックアップを取り、binlog を別の場所へコピーし、保存期間をバックアップの間隔より長くしておきます。事故が起きたら書き込みを止め、mysqlbinlog で誤操作のトランザクションの先頭位置を探し、その直前まで（必要ならその後も）適用します。

関連記事:

• MySQLのbinlog（バイナリログ）の確認と活用方法
• MySQLのフルバックアップを自動化する方法（mysqldump＋cron）
• バックアップの世代管理と3-2-1ルールの考え方