Binlog Archiving

Pinpointing the Binlog Position Before an Accidental DELETE

Someone ran DELETE FROM orders without a WHERE, and now recovery hinges on one number: the binlog coordinate immediately before that statement, so a replay can restore every transaction up to it and stop one event short of the damage. Get the coordinate wrong by one transaction and you either re-apply the destructive DELETE or drop a legitimate transaction that committed just before it. This page is the forensic procedure for finding that exact stop coordinate: locate the offending statement in the binary log, read the Gtid and end_log_pos of the transaction before it, and set a --stop-position (or GTID stop) that lands the recovery precisely on the safe side of the boundary.

Context & Prerequisites

This is the surgical-targeting case of Timestamp Targeting Strategies for MySQL Binary Log Archiving and PITR Automation; read that for the general --stop-datetime versus --stop-position model. You need MySQL 8.0.22+ client tools, Python 3.10+, binlog_format=ROW so the DELETE is visible as row events (per ROW vs STATEMENT vs MIXED Formats), and access to the archived segment covering the incident from Automated Binlog Archiving to Object Storage. The resolved coordinate feeds the replay in mysqlbinlog Replay Scripting.

Step-by-Step Implementation

1. Narrow to the segment and rough time window

Use the incident’s approximate time to pick the segment, then decode it to human-readable form for inspection.

# MySQL 8.0.22+  — decode the suspect segment with row events readable
mysqlbinlog --verify-binlog-checksum --base64-output=DECODE-ROWS --verbose \
  mysql-bin.000318 > /tmp/incident.sql

PITR relevance: --base64-output=DECODE-ROWS --verbose renders row events as readable pseudo-SQL so you can visually confirm the destructive statement, without producing an applicable file.

2. Locate the offending statement and its Gtid

Search the decoded output for the DELETE (or the table) and note the Gtid line and the end_log_pos that immediately precede it.

# find the destructive event and the boundary just before it
grep -nE "GTID_NEXT|end_log_pos|DELETE FROM .orders." /tmp/incident.sql | less

PITR relevance: the coordinate you want is the end_log_pos at the end of the transaction before the DELETE — replaying to there includes everything good and excludes the damage.

3. Read the exact stop coordinate programmatically

Parse the decoded stream to extract the boundary robustly rather than by eye.

# Python 3.10+
import re
from pathlib import Path

def stop_before(delete_marker: str, decoded: Path) -> tuple[int, str | None]:
    last_pos, last_gtid = 4, None
    for line in decoded.read_text().splitlines():
        if delete_marker in line:
            return last_pos, last_gtid          # boundary just before the DELETE
        if (m := re.search(r"end_log_pos (\d+)", line)):
            last_pos = int(m.group(1))
        if (g := re.search(r"SET @@SESSION.GTID_NEXT= '([^']+)'", line)):
            last_gtid = g.group(1)
    raise ValueError("destructive statement not found in segment")

PITR relevance: capturing both the position and the preceding GTID gives you two equivalent stop conditions — prefer the GTID for exactness, fall back to the position within the segment.

4. Recover up to the boundary on an isolated target

Replay to the resolved stop coordinate on an isolated target, verify the deleted rows are present, then promote.

# MySQL 8.0.22+  — replay everything up to (not including) the DELETE
mysqlbinlog --verify-binlog-checksum --stop-position=25120 \
  mysql-bin.000318 | mysql --host=recovery-target

PITR relevance: stopping one event before the DELETE reconstructs the table exactly as it was the instant before the mistake, which is the entire objective of surgical PITR.

Configuration Snippet & Reference Table

CoordinateHow to read itUse as
offending statementgrep the decoded streamthe boundary marker
preceding end_log_poslast end_log_pos before it--stop-position
preceding GTIDlast GTID_NEXT before itGTID stop (exact)
decode flags--base64-output=DECODE-ROWS --verboseinspection only
apply flags--stop-position=<n>the actual recovery

Verification Checklist

Gotchas & Version-Specific Caveats

--base64-output=DECODE-ROWS is for reading only. It renders row events as comments for inspection; a file produced with it is not applicable. Apply with the normal decode (no DECODE-ROWS) and the resolved --stop-position.

Off-by-one lands on the wrong side. Using the DELETE’s own end_log_pos instead of the preceding transaction’s includes the damage. Always take the boundary before the offending GTID.

Concurrent transactions share seconds. A --stop-datetime cannot distinguish the DELETE from a legitimate transaction that committed in the same second; use the position or GTID for surgical precision.

The event may span segments. If the incident sits near a rotation boundary, the preceding transaction may be in the prior segment; decode both and resolve across the boundary.

Frequently Asked Questions

Should I stop by position or by GTID?

Prefer the GTID of the last good transaction when you can name it — it is exact and immune to clock skew or events sharing a commit second. Use --stop-position as the concrete within-segment equivalent, which is what mysqlbinlog accepts directly. Capturing both during the forensic step gives you the exact stop and a fallback, and they should identify the same boundary.

How do I confirm I stopped in the right place?

Replay to the resolved coordinate on an isolated target and check two things: the rows the DELETE removed are present (you stopped before it), and the DELETE’s effect is absent (you did not replay past it). If both hold, the boundary is correct and the target can be promoted. Never validate on the production primary — always recover to an isolated instance first, per mysqlbinlog Replay Scripting.

Back to Timestamp Targeting Strategies.