mysqlbinlogでMySQLをポイントインタイムリカバリする方法｜誤操作直前まで戻す復旧手順

この記事のポイント

・mysqlbinlog ＋ フルバックアップで誤操作直前まで復旧できる
・--stop-position でDROP/DELETE文だけをスキップして再適用できる
・バイナリログが期限切れで削除されると復旧不可能になる
・ROW形式のバイナリログ確認には --base64-output=DECODE-ROWS が必要

mysql -u root -p -e "SHOW VARIABLES LIKE 'log_bin';"

+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | ON    |
+---------------+-------+

[mysqld]
log_bin = /var/lib/mysql/mysql-bin
server_id = 1
# MySQL 8.0以降。5.7は expire_logs_days で設定する
binlog_expire_logs_seconds = 604800

mysql -u root -p -e "SHOW VARIABLES LIKE 'binlog_format';"

+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW   |
+---------------+-------+

# バックアップファイルを確認
ls -lh /backup/mysql/
# -rw-r--r-- 1 root root 2.3G Aug 18 03:00 full_backup_20260818.sql.gz

# ダンプの先頭でバイナリログ情報を確認
zcat /backup/mysql/full_backup_20260818.sql.gz | head -50 | grep -E 'MASTER_LOG|binlog'
# -- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000042', MASTER_LOG_POS=157;

# リストア（時間がかかるため画面から切れないようにscreenかtmux内で実行）
zcat /backup/mysql/full_backup_20260818.sql.gz | mysql -u root -p

mysql -u root -p -e "SHOW BINARY LOGS;"

+------------------+-----------+-----------+
| Log_name         | File_size | Encrypted |
+------------------+-----------+-----------+
| mysql-bin.000042 |  54332172 | No        |
| mysql-bin.000043 |  98765432 | No        |
| mysql-bin.000044 |   3456789 | No        |
+------------------+-----------+-----------+

mysqlbinlog --base64-output=DECODE-ROWS -vv \
  /var/lib/mysql/mysql-bin.000044 | grep -B 5 -A 5 "DROP TABLE\|DELETE FROM"

# at 892471
#260818 11:57:42 server id 1  end_log_pos 892612 CRC32 0x4b2f1a3c      Query   thread_id=8     exec_time=0     error_code=0
SET TIMESTAMP=1755478662/*!*/;
DROP TABLE `orders`
/*!*/;

# バックアップ取得後の最初のファイルから中間ファイルまで全件適用
mysqlbinlog \
  --start-position=157 \
  /var/lib/mysql/mysql-bin.000042 \
  /var/lib/mysql/mysql-bin.000043 | mysql -u root -p

# 最後のファイルは誤操作ポジションで停止（そのポジション自体は含まれない）
mysqlbinlog \
  --stop-position=892471 \
  /var/lib/mysql/mysql-bin.000044 | mysql -u root -p

mysql -u root -p -e "SELECT COUNT(*) FROM mydb.orders;"
# +----------+
# | COUNT(*) |
# +----------+
# |    14823 |
# +----------+

# 誤操作イベントの end_log_pos（出力例では892612）の次のポジションを確認
mysqlbinlog --base64-output=DECODE-ROWS -vv \
  /var/lib/mysql/mysql-bin.000044 | grep "^# at" | grep -A 1 "892612"
# # at 892613

# 892613以降（誤操作の直後）から最新まで適用
mysqlbinlog \
  --start-position=892613 \
  /var/lib/mysql/mysql-bin.000044 | mysql -u root -p

# 日時指定の例（誤操作が 2026-08-18 11:57:42 の場合、その1秒前で停止）
mysqlbinlog \
  --start-datetime="2026-08-18 03:00:00" \
  --stop-datetime="2026-08-18 11:57:41" \
  /var/lib/mysql/mysql-bin.000042 \
  /var/lib/mysql/mysql-bin.000043 \
  /var/lib/mysql/mysql-bin.000044 | mysql -u root -p

# オプションなし → Base64のまま（内容を確認できない）
mysqlbinlog /var/lib/mysql/mysql-bin.000044 | grep -A 3 "BINLOG"
# BINLOG '
# 3xKuZBMBAAAALgAAAKcBAAAAACQAAAAAAAEABm15ZGJiBgBvcmRlcnMM...

# --base64-output=DECODE-ROWS -vv を付けると確認できる
mysqlbinlog --base64-output=DECODE-ROWS -vv \
  /var/lib/mysql/mysql-bin.000044 | grep -A 5 "DELETE"

# --no-defaults を付けてmy.cnfを読み込まないようにする
mysqlbinlog --no-defaults \
  --stop-position=892471 \
  /var/lib/mysql/mysql-bin.000044 | mysql --no-defaults -u root -p

# バックアップに記録されたファイル: mysql-bin.000042
# 現在残っているログを確認
mysql -u root -p -e "SHOW BINARY LOGS;"
# mysql-bin.000045 から始まる場合
# → 000042～000044 が削除済み → PITRは不可