Binlog Retention Boundaries for MySQL Binary Log Archiving and PITR Automation #

A retention boundary is the exact point at which a binary log stops being a live Point-in-Time Recovery (PITR) asset and becomes safe to discard from local disk. Get it wrong in one direction and you exhaust the datadir volume, stall commits, and take an outage; get it wrong in the other and you delete the one segment a replica still needed or a recovery would have replayed — a silent data-loss event you only discover during an incident. Naive retention fails because it reasons about files and wall-clock age: a find -mtime +3 -delete cron or a bare binlog_expire_logs_seconds treats a 4-day-old log as expendable even when a lagging replica has not yet consumed its transactions, or when the archival upload of that segment never completed. This page defines a retention boundary as a function of transaction state instead: the newest log file whose GTIDs are simultaneously (a) already applied by every downstream consumer, (b) durably archived and verified, and © outside every active compliance hold. Only a boundary that satisfies all three predicates is safe to feed to PURGE BINARY LOGS.

Visual Overview #

Core Concept & Prerequisites #

A retention boundary is the minimum of several independent safety floors, never the output of a single timer. The pipeline computes each floor as a binlog file name (or its terminating GTID coordinate) and purges only up to the oldest of them:

• The replication floor — the oldest log any replica or downstream consumer still needs. Derived by comparing each replica’s gtid_executed against the primary’s, so a replica that is Seconds_Behind_Source = 900 pins retention regardless of file age.
• The archival floor — the newest segment confirmed present, checksum-verified, and manifest-indexed in durable object storage. A segment that has not cleared verification is never inside the boundary.
• The compliance floor — the oldest segment covered by an active legal hold or a regulatory minimum-retention window. This is a hard floor that can only extend retention, never shorten it.
• The backup floor — the binlog coordinate of the most recent base backup. Logs older than the backup that anchors your PITR chain remain recoverable only if that backup is still valid, so the boundary is cross-referenced against the backup schedule.

Because GTIDs, not file positions, are the unit of continuity, the whole calculation is anchored to the executed-transaction set the server exposes. Understanding how the primary structures gtid_executed, gtid_purged, and the Previous_gtids_log_event that indexes each file is the foundation for everything below — that model is established in MySQL Binary Log Architecture & GTID Fundamentals, and a gap-free extraction of those sets is the job of the GTID Tracking & Enforcement pipeline this page consumes as an input.

The logging format shifts where the boundary can safely land. Under ROW vs STATEMENT vs MIXED Formats, binlog_format = ROW produces larger but deterministic segments whose replay is byte-for-byte reproducible, so the archival floor can be trusted the moment a checksum matches. STATEMENT and MIXED streams can contain non-deterministic constructs whose replay outcome depends on server state, which means the boundary must be validated more conservatively and never advanced past a segment whose replay has not been dry-run confirmed.

Prerequisites for the pipeline:

• MySQL 8.0.22+ on the primary and every replica, with gtid_mode = ON, enforce_gtid_consistency = ON, and binlog_format = ROW. GTID-based floor calculation is only sound when all nodes share the same identifier space.
• Python 3.10+ for the automation service (match statements, slots=True dataclasses, the walrus operator in polling loops).
• mysql-connector-python for pooled server connections and tenacity for declarative retry policy. Segment discovery and locking of closed files are assumed to be handled upstream so this stage only ever reasons about rotated, stable logs.

Production-Grade Python Implementation #

The boundary engine queries the primary and every registered replica, reduces the four floors to a single safe target log, gates the purge behind archival verification, and persists its last action so an interrupted run is idempotent. Transient connection faults are retried with exponential backoff through tenacity; a failed archival or a lagging replica halts the purge rather than forcing it. The module targets MySQL 8.0.22+ and Python 3.10+.

#!/usr/bin/env python3
"""Deterministic binlog retention-boundary engine.
Targets MySQL 8.0.22+ / Python 3.10+. Computes the safe PURGE target as the
minimum of the replication, archival, compliance and backup floors, then gates
the purge behind archival verification. Idempotent and dry-run capable."""
from __future__ import annotations

import argparse
import json
import logging
import sys
from dataclasses import dataclass, field
from pathlib import Path

import mysql.connector
from mysql.connector import Error as MySQLError
from mysql.connector import pooling
from tenacity import (
    retry,
    retry_if_exception_type,
    stop_after_attempt,
    wait_exponential,
)

logging.basicConfig(
    level=logging.INFO,
    format='{"ts":"%(asctime)s","level":"%(levelname)s","event":"%(message)s"}',
    handlers=[logging.StreamHandler(sys.stdout)],
)
logger = logging.getLogger("binlog_retention")


@dataclass(slots=True)
class BinlogSegment:
    name: str
    size: int
    encrypted: bool


@dataclass(slots=True)
class RetentionBoundary:
    replication_floor: str | None = None
    archival_floor: str | None = None
    compliance_floor: str | None = None
    backup_floor: str | None = None

    def safe_target(self, ordered_logs: list[str]) -> str | None:
        """Return the oldest (lowest-ordered) non-null floor: the log to purge
        UP TO but not including. None means nothing is safe to purge yet."""
        floors = [f for f in (
            self.replication_floor,
            self.archival_floor,
            self.compliance_floor,
            self.backup_floor,
        ) if f]
        if not floors:
            return None
        index = {name: i for i, name in enumerate(ordered_logs)}
        return min(floors, key=lambda name: index.get(name, len(ordered_logs)))


@dataclass(slots=True)
class RetentionState:
    pool: pooling.MySQLConnectionPool
    replica_hosts: list[dict]
    lag_ceiling_seconds: int
    dry_run: bool
    state_file: Path = field(default_factory=lambda: Path("/var/lib/mysql-archive/.retention_state.json"))
    last_purged_to: str | None = None

    def load(self) -> None:
        if self.state_file.exists():
            self.last_purged_to = json.loads(self.state_file.read_text()).get("last_purged_to")

    def save(self, target: str) -> None:
        self.last_purged_to = target
        self.state_file.write_text(json.dumps({"last_purged_to": target}, indent=2))


@retry(
    stop=stop_after_attempt(3),
    wait=wait_exponential(multiplier=1, min=2, max=10),
    retry=retry_if_exception_type((MySQLError, ConnectionError)),
)
def fetch_binlog_inventory(pool: pooling.MySQLConnectionPool) -> list[BinlogSegment]:
    """SHOW BINARY LOGS on the primary — ordered oldest to newest."""
    conn = pool.get_connection()
    try:
        with conn.cursor(dictionary=True) as cur:
            cur.execute("SHOW BINARY LOGS")  # MySQL 8.0.22+: Log_name, File_size, Encrypted
            return [
                BinlogSegment(r["Log_name"], int(r["File_size"]), r.get("Encrypted", "No") == "Yes")
                for r in cur.fetchall()
            ]
    finally:
        conn.close()


def replication_floor(state: RetentionState, ordered_logs: list[str]) -> str | None:
    """Oldest log any replica still needs. A replica lagging beyond the ceiling,
    or unreachable, pins retention at the oldest segment (never purge)."""
    for replica in state.replica_hosts:
        try:
            rconn = mysql.connector.connect(**replica)
        except MySQLError as exc:
            logger.warning("replica_unreachable host=%s err=%s hold=all", replica["host"], exc)
            return ordered_logs[0]  # conservative: hold everything
        try:
            with rconn.cursor(dictionary=True) as cur:
                cur.execute("SHOW REPLICA STATUS")  # MySQL 8.0.22+ (was SHOW SLAVE STATUS)
                row = cur.fetchone()
                lag = row and row.get("Seconds_Behind_Source")
                if lag is None or lag > state.lag_ceiling_seconds:
                    logger.warning("replica_lag host=%s lag=%s hold=all", replica["host"], lag)
                    return ordered_logs[0]
        finally:
            rconn.close()
    # All replicas healthy and within the ceiling: they no longer pin any file.
    return None


def archival_floor(archive_dir: Path, ordered_logs: list[str]) -> str | None:
    """Newest segment with a verified '.ok' manifest marker. Anything newer than
    it is not yet safe to purge, so the floor is the first UNverified log."""
    for name in ordered_logs[:-1]:  # never consider the active tail
        if not (archive_dir / f"{name}.ok").exists():
            return name
    return None


def purge_binlogs(pool: pooling.MySQLConnectionPool, target: str, dry_run: bool) -> None:
    if dry_run:
        logger.info("dry_run_purge target=%s", target)
        return
    conn = pool.get_connection()
    try:
        with conn.cursor() as cur:
            # MySQL 8.0.22+: exclusive — removes logs strictly older than target.
            cur.execute("PURGE BINARY LOGS TO %s", (target,))
        logger.info("purge_ok target=%s", target)
    finally:
        conn.close()


def run(state: RetentionState, archive_dir: Path, compliance_floor: str | None,
        backup_floor: str | None) -> None:
    state.load()
    segments = fetch_binlog_inventory(state.pool)
    if len(segments) <= 1:
        logger.info("nothing_to_purge only_active_log=true")
        return

    ordered = [s.name for s in segments]
    boundary = RetentionBoundary(
        replication_floor=replication_floor(state, ordered),
        archival_floor=archival_floor(archive_dir, ordered),
        compliance_floor=compliance_floor,
        backup_floor=backup_floor,
    )
    target = boundary.safe_target(ordered)

    match target:
        case None:
            logger.info("boundary_holds reason=no_safe_floor")
        case t if t == ordered[0]:
            logger.info("boundary_holds reason=oldest_pinned target=%s", t)
        case t if t == state.last_purged_to:
            logger.info("boundary_idempotent already_purged_to=%s", t)
        case t:
            purge_binlogs(state.pool, t, state.dry_run)
            if not state.dry_run:
                state.save(t)


if __name__ == "__main__":
    parser = argparse.ArgumentParser(description="Binlog retention-boundary engine")
    parser.add_argument("--host", required=True)
    parser.add_argument("--user", required=True)
    parser.add_argument("--password", required=True)
    parser.add_argument("--lag-ceiling", type=int, default=300)
    parser.add_argument("--dry-run", action="store_true")
    args = parser.parse_args()

    primary_pool = pooling.MySQLConnectionPool(
        pool_name="retention", pool_size=4, pool_reset_session=True,
        host=args.host, user=args.user, password=args.password, database="mysql",
    )
    engine = RetentionState(
        pool=primary_pool,
        replica_hosts=[{"host": "replica-a", "user": args.user, "password": args.password}],
        lag_ceiling_seconds=args.lag_ceiling,
        dry_run=args.dry_run,
    )
    run(engine, Path("/var/lib/mysql-archive/binlogs"),
        compliance_floor=None, backup_floor=None)

The engine never advances past the oldest segment while any replica is lagging or unreachable, treats an unverified archival marker as a hard stop, and short-circuits when the computed target equals the last purge it recorded, so a re-run after a crash is a no-op rather than a double purge. The compliance floor is passed in from the Setting Safe binlog_expire_logs_seconds for Compliance policy layer, which owns the mapping from SOX/HIPAA/GDPR windows to a concrete log coordinate.

Configuration Reference #

These server variables govern where the boundary can land and how the server itself expires logs underneath your automation. Set MySQL-native expiry to a value longer than your automation’s worst-case archival latency so the server never purges a segment the pipeline has not yet confirmed.

Validation & Verification Gates #

A boundary is only allowed to advance after every gate below passes. Each gate maps to a floor in the engine and is designed to fail closed — an inconclusive check holds retention rather than releasing it.

• Archival checksum reconciliation. Recompute the SHA-256 of each candidate segment and compare it to the manifest recorded at upload. A mismatch, or a missing .ok marker, pins the archival floor at that segment. This gate is what makes deletion safe rather than hopeful.
• GTID set diffing. Subtract the archived segments’ aggregated GTID set from the primary’s gtid_executed using GTID_SUBTRACT(). Any GTID that exists on the primary but not in the archive means the boundary would strand an unarchived transaction — halt.
• Replica acknowledgement. Confirm every replica’s Received_transaction_set (from performance_schema.replication_connection_status) is a superset of the transactions in every segment inside the boundary, so no downstream consumer still needs a file you are about to purge.
• Dry-run replay for non-ROW streams. Where the format is MIXED or STATEMENT, stream the oldest in-boundary segment through mysqlbinlog into a throwaway instance and confirm it applies without error before trusting the archival floor.
• Idempotent target check. Verify the computed PURGE BINARY LOGS TO target has not already been executed by comparing against persisted state, so a retried run cannot double-purge.

The GTID-diffing and replica-acknowledgement gates reuse the extraction and normalization logic documented under GTID Tracking & Enforcement; this page consumes that verified set as the authoritative input to the replication floor.

Error Handling & Failure Modes #

PURGE BINARY LOGS and the surrounding GTID accounting surface a small, well-defined set of errors. Map each to a specific recovery rather than a blanket retry — some are transient, others indicate the boundary was computed against stale state.

The most dangerous failure is not an error at all: an over-eager boundary that succeeds. If gtid_purged advances past the oldest archived segment, the locally-replayable window and the archive no longer overlap and PITR to any point in the gap becomes impossible. Guard against it by alerting whenever the primary’s gtid_purged grows beyond the archive’s oldest indexed GTID, and by degrading gracefully — when the boundary cannot be safely computed, the correct behaviour is to stop purging and let disk fill toward the alert threshold, then hand off to the Fallback Routing Strategies that offload archival I/O and defer expiry rather than force it.

Observability & Alerting #

The boundary is only as trustworthy as your ability to see it drifting. Emit these metrics from the engine and alert on the leading indicators, not just on hard failures.

• binlog_retention_boundary_age_seconds — age of the newest in-boundary segment. Rising steadily means archival or replication is falling behind the write rate; page before it approaches binlog_expire_logs_seconds.
• binlog_unarchived_gtid_count — cardinality of GTID_SUBTRACT(@@GLOBAL.gtid_executed, archived_set). Any sustained non-zero value is the archival floor failing to keep up.
• binlog_purge_target_lag_seconds — delta between archival verification and the subsequent PURGE execution.
• replication_lag_at_boundary_seconds — max Seconds_Behind_Source observed while computing the replication floor.

The performance_schema gives you the authoritative replica-side view without polling SHOW REPLICA STATUS in a tight loop:

-- MySQL 8.0.22+: transactions each replica has received vs. applied,
-- the inputs to the replication floor.
SELECT
  c.CHANNEL_NAME,
  c.RECEIVED_TRANSACTION_SET,
  a.APPLYING_TRANSACTION,
  a.LAST_APPLIED_TRANSACTION
FROM performance_schema.replication_connection_status AS c
JOIN performance_schema.replication_applier_status_by_worker AS a
  ON a.CHANNEL_NAME = c.CHANNEL_NAME;

Structure every log line as JSON with a stable event field (as the engine above does) so boundary_holds, purge_ok, and replica_lag events are trivially aggregated. Wire the alert threshold on binlog_retention_boundary_age_seconds to fire well before storage exhaustion, and treat any binlog_unarchived_gtid_count > 0 that persists past one archival cycle as a paging event — it is the precise signature of a boundary that is about to strand recoverable transactions.

Frequently Asked Questions #

Related #

• Setting Safe binlog_expire_logs_seconds for Compliance — mapping SOX/HIPAA/GDPR windows to the compliance floor this engine consumes.
• GTID Tracking & Enforcement — the verified gtid_executed/gtid_purged extraction that feeds the replication and archival floors.
• ROW vs STATEMENT vs MIXED Formats — why format choice changes how conservatively the boundary must be validated.
• Fallback Routing Strategies — graceful degradation when the boundary cannot be safely advanced.
• Compression & Encryption Workflows — the transform stage that produces the verified artifacts the archival floor depends on.

Back to MySQL Binary Log Architecture & GTID Fundamentals.