Crash-Safe Binlog Recovery: Restoring Position After an Unclean Shutdown #

An unclean shutdown — a power loss, an OOM kill, a hypervisor stall mid-commit — leaves the binary log and the InnoDB redo log momentarily disagreeing about the last transaction, and how MySQL reconciles them on restart determines whether your archived chain stays sound. A naive archiver that keeps uploading whatever bytes are on disk after a crash can capture a partial trailing transaction that the server is about to truncate, poisoning the recovery chain with a segment mysqlbinlog cannot fully replay. This guide explains binlog crash recovery precisely: how the server scans the binary log against InnoDB’s committed state, truncates an unfinished trailing transaction, restores a clean position, and why archiving and recovery automation must treat the post-recovery tail — not the pre-crash bytes — as authoritative. It is the durability foundation beneath MySQL Binary Log Architecture & GTID Fundamentals.

Visual Overview #

Core Concept & Prerequisites #

MySQL commits to the binary log and InnoDB through a two-phase protocol coordinated by the binlog: a transaction is prepared in InnoDB, written to the binlog, then committed in InnoDB. A crash can land between any two of those steps, so on restart the server must decide, for the trailing transaction, whether it was durably logged. It scans the binary log from the last known-good position and, for each trailing transaction, checks InnoDB’s prepared/committed state: a transaction the binlog recorded and InnoDB prepared is committed forward; a transaction written only partially, or logged without InnoDB committing, is truncated so the binlog ends on a clean transaction boundary. The recovered position is authoritative — it, not the raw bytes that were on disk at crash time, defines the archivable tail.

You need MySQL 8.0.22+ with sync_binlog=1 and innodb_flush_log_at_trx_commit=1 for the crash-safety guarantees to hold (these are the durable settings; relaxing them trades durability for throughput and widens the truncation window). binlog_checksum=CRC32 lets recovery detect a torn event. The archiving pipeline that consumes the recovered tail is Automated Binlog Archiving to Object Storage, and the retention that keeps the pre-crash segments available is Binlog Retention Boundaries.

Production-Grade Python Implementation #

Automation’s job around a crash is to wait for and trust the recovered state rather than race it. This helper confirms the server has completed crash recovery and returns the authoritative tail position before the archiver is allowed to resume.

# recovery/crashsafe.py — Python 3.10+
from __future__ import annotations

import time
from dataclasses import dataclass

import mysql.connector


@dataclass(slots=True, frozen=True)
class RecoveredTail:
    active_file: str
    position: int
    gtid_executed: str


def wait_for_recovery(conn_factory, timeout_s: float = 120.0) -> RecoveredTail:
    """Block until the server is up post-recovery, then read the clean tail."""
    deadline = time.monotonic() + timeout_s
    while True:
        try:
            conn = conn_factory()
            cur = conn.cursor(dictionary=True)
            cur.execute("SHOW BINARY LOG STATUS")          # MySQL 8.0.22+ (was SHOW MASTER STATUS)
            row = cur.fetchone()
            cur.execute("SELECT @@GLOBAL.gtid_executed AS g")
            gtid = cur.fetchone()["g"]
            cur.close(); conn.close()
            return RecoveredTail(row["File"], int(row["Position"]), gtid)
        except mysql.connector.Error:
            if time.monotonic() > deadline:
                raise
            time.sleep(2)                                  # server still recovering

The critical rule the automation enforces: the archiver reconciles its manifest against this recovered tail, and any closed segment whose recorded size or checksum no longer matches the on-disk file after recovery is re-hashed, because recovery may have truncated the last segment. Never confirm a segment to the archive that was captured from the pre-recovery tail — the deferral discipline that prevents this is the same active-tail rule in Building a Python Script to Sync Binlogs to S3 with Boto3. The step-by-step operator procedure for restoring position after a crash is in Recovering the Binlog Position After an Unclean Shutdown.

Configuration Reference #

Validation & Verification Gates #

• sync_binlog=1 and innodb_flush_log_at_trx_commit=1 on any primary whose chain feeds PITR.
• Automation waits for the server to complete crash recovery before reading the tail position.
• The recovered SHOW BINARY LOG STATUS position is treated as authoritative, not pre-crash bytes.
• The last segment is re-hashed after recovery in case it was truncated.
• No segment captured from the pre-recovery active tail is confirmed to the archive.
• gtid_executed after recovery is contiguous with the archived chain (no phantom transaction).

Error Handling & Failure Modes #

A truncated last segment already uploaded. If the archiver captured the active tail before a crash and the server then truncated it, the uploaded object is longer than the recovered file and will fail replay with read_log_event(): 'read error'. Re-hash and re-archive from the recovered file; this is why only closed segments are ever archived.

ERROR 1590 LOST_EVENTS / an Incident event. With relaxed durability (sync_binlog=0), a crash can lose events the binlog never durably recorded, and recovery marks an Incident. This is a genuine chain hole; surface it via the gap detector in Detecting GTID Gaps Before PITR Replay.

Manual RESET after a crash. Never RESET BINARY LOGS AND GTID_SET to “clean up” after a crash on a primary feeding PITR — it discards the recovered tail and orphans the archived chain. Let recovery restore the position.

Observability & Alerting #

Alert on any unclean-shutdown event (server uptime reset without a graceful stop) and gate the archiver’s resume on confirmed recovery completion. Track whether the last archived segment’s checksum changed after a crash — a change means truncation occurred and the pre-crash upload must be superseded. Cross-reference the recovered gtid_executed against the archive’s newest confirmed GTID from Deriving RPO Targets from Binlog Archiving Lag: a crash that widened the gap is an RPO event worth paging on.

Frequently Asked Questions #

Related #

• Recovering the Binlog Position After an Unclean Shutdown — the step-by-step operator procedure.
• Binlog Retention Boundaries — keeping pre-crash segments available to re-verify.
• Detecting GTID Gaps Before PITR Replay — surfacing a real chain hole an Incident event signals.
• Automated Binlog Archiving to Object Storage — the pipeline that consumes the recovered tail.

Back to MySQL Binary Log Architecture & GTID Fundamentals.