Binlog Archiving

Recovering the Binlog Position After an Unclean Shutdown

The pager fires: a primary hard-crashed and came back on its own. The database is up, replication is reconnecting, and the archiver is about to resume — but resuming blindly is how a crash turns into a corrupt recovery chain. If the archiver had captured the active tail just before the crash and the server then truncated a partial trailing transaction, the already-uploaded object is now longer than the recovered file, and a future replay will dead-end on the torn event. This page is the operator procedure for the minutes after an unclean shutdown: confirm crash recovery actually completed, read the authoritative recovered position, re-hash the last archived segment against the recovered file, and resume archiving only once the tail is proven consistent.

Context & Prerequisites

This is the runbook counterpart to the mechanism explained in Crash-Safe Binlog Recovery; read that for why the recovered tail — not the pre-crash bytes — is authoritative. You need MySQL 8.0.22+ with durable settings (sync_binlog=1, innodb_flush_log_at_trx_commit=1), Python 3.10+, shell access to the primary, and the archive manifest from Automated Binlog Archiving to Object Storage.

Step-by-Step Implementation

1. Confirm crash recovery completed before touching anything

Check the error log for the InnoDB crash-recovery banner and that the server is accepting connections; do not let the archiver resume while recovery is in progress.

# after an unclean shutdown
grep -E "Crash recovery|Recovering after a crash|ready for connections" \
  /var/log/mysql/error.log | tail -5

PITR relevance: reading a position mid-recovery captures a tail the server may still truncate; waiting for “ready for connections” guarantees the position is final.

2. Read the authoritative recovered position

Once up, the recovered tail is whatever the server now reports — this is the new source of truth.

-- MySQL 8.0.22+
SHOW BINARY LOG STATUS;          -- File + Position of the recovered active tail
SELECT @@GLOBAL.gtid_executed;   -- the recovered executed set

PITR relevance: the recovered gtid_executed is what the archived chain must be contiguous with; any transaction the archive claims beyond this set is phantom and must be reconciled away.

3. Re-hash the last archived segment against the recovered file

If the newest closed segment on disk differs from what the manifest recorded, recovery truncated it — supersede the uploaded object.

# Python 3.10+
import hashlib
from pathlib import Path

def rehash(path: Path) -> str:
    h = hashlib.sha256()
    with path.open("rb") as fh:
        while chunk := fh.read(1 << 20):
            h.update(chunk)
    return h.hexdigest()

def needs_resupersede(seg_name: str, binlog_dir: Path, manifest) -> bool:
    local = binlog_dir / seg_name
    return local.exists() and rehash(local) != manifest.sha256_of(seg_name)

PITR relevance: a segment whose bytes changed after recovery was truncated; the pre-crash upload is invalid and must be replaced with the recovered version before it can serve a recovery.

4. Resume archiving from the reconciled tail

Only after the position is confirmed and the last segment re-verified, let the archiver re-enter its detect loop from the recovered state.

PITR relevance: resuming from the reconciled tail guarantees the next archived segment continues the chain contiguously, with no torn trailing transaction carried into object storage.

Configuration Snippet & Reference Table

StepCommand / checkWhy it matters for PITR
recovery doneerror-log “ready for connections”Position is final only after recovery completes.
recovered tailSHOW BINARY LOG STATUSThe authoritative post-crash position.
executed setSELECT @@GLOBAL.gtid_executedWhat the archive must stay contiguous with.
re-hashSHA-256 of newest closed segmentDetects a truncated last segment.
resumearchiver detect loopContinues the chain from the reconciled tail.

Verification Checklist

Gotchas & Version-Specific Caveats

Never RESET to tidy up. Running RESET BINARY LOGS AND GTID_SET after a crash discards the recovered tail and orphans the entire archived chain; let recovery restore the position.

SHOW MASTER STATUS is deprecated. Use SHOW BINARY LOG STATUS on 8.0.22+; the old form is removed as canonical in 8.4.

Relaxed durability widens the damage. With sync_binlog=0 a crash can lose durably-unlogged events and mark an Incident, which is a real chain hole — detect it with the gap check in Detecting GTID Gaps Before PITR Replay.

The active tail should never have been uploaded. If re-hashing finds a changed segment that was already confirmed, your archiver captured an active tail — fix the deferral rule per Building a Python Script to Sync Binlogs to S3 with Boto3.

Frequently Asked Questions

How do I know if recovery truncated my last segment?

Re-hash the newest closed segment on disk and compare it to the SHA-256 the manifest recorded at archive time. If they differ, recovery truncated a partial trailing transaction, so the file is now shorter than the uploaded object. Supersede the archived object with the recovered file. If the hashes match, the segment was already durable and nothing needs to change.

Do I need to intervene at all, or does MySQL handle it?

MySQL handles the database recovery automatically — it reconciles the binlog with InnoDB and restores a clean position. Your intervention is on the archiving side: making sure the archiver waits for recovery, trusts the recovered tail, and re-verifies the last segment. The server will not fix an already-uploaded truncated object for you; that reconciliation is yours.

Back to Crash-Safe Binlog Recovery.