Rotation Scheduling & Cron Automation for MySQL Binary Log Archiving and PITR #

Binary log rotation is routinely mischaracterised as disk hygiene, but it is the trigger that starts every downstream guarantee: it closes the segment a Point-in-Time Recovery (PITR) chain depends on, releases that segment for durable upload, and marks the boundary after which local disk may be reclaimed. When the rotation cadence drifts out of step with the archiving pipeline, three failure modes appear — a recovery window fractured by a segment that was purged before it was uploaded, a primary that fills its datadir because rotation stalled, or duplicate object-storage uploads from two rotations racing on the same host. This page defines the scheduling layer that sits at the head of the Automated Binlog Archiving to Object Storage pipeline. Naive approaches fail because a fixed-interval cron line that blindly calls mysqladmin flush-logs is blind to server state: it rotates through a long-running transaction, during a replica catch-up, or while a backup holds the log — producing exactly the fractured chains it was meant to prevent. The fix is to move from time-based triggers to state-aware, idempotent, single-flight scheduling.

Visual Overview #

Core Concept & Prerequisites #

Deterministic scheduling replaces the wall clock with server state as the rotation decision input. The scheduler queries the running instance, confirms replication is healthy and transactions are quiescent, and only then issues FLUSH BINARY LOGS. This guarantees that every segment it closes is fully committed, indexed in the binary log index, and immediately eligible for the transform stage. The two invariants that make this safe are single-flight execution — never two rotations at once on one host — and handoff-before-purge — a closed segment is never deleted until the archiver confirms a durable, verified copy exists. The transform stage that consumes each closed segment is Compression & Encryption Workflows; this page owns everything up to the moment a stable, read-only segment is handed off.

Rotation is only meaningful if the underlying stream is deterministic, so the format and identity choices upstream are prerequisites, not details. The recovery guarantees here assume binlog_format = ROW (the reasoning is in ROW vs STATEMENT vs MIXED Formats) and a gap-free stream of global transaction identifiers from a correctly configured GTID Tracking & Enforcement pipeline. The scheduler must also never let purge outrun archiving — its timing has to be reconciled against the horizons described in Binlog Retention Boundaries.

Prerequisites:

• MySQL 8.0.22+ on the source, with log_bin = ON, gtid_mode = ON, and enforce_gtid_consistency = ON. From 8.0.22 the status command is SHOW BINARY LOG STATUS (the former SHOW MASTER STATUS) and replication uses SHOW REPLICA STATUS with Seconds_Behind_Source.
• Python 3.10+ for the orchestration layer (match statements, slots=True typed dataclasses, the walrus operator in streaming loops).
• mysql-connector-python for the pre-flight queries, tenacity for declarative retry policy, and either systemd (preferred) or a POSIX flock-wrapped cron line for the trigger and mutual-exclusion guard.
• A dedicated archiver MySQL account holding only RELOAD (for FLUSH BINARY LOGS), REPLICATION CLIENT, and PROCESS — scoped per Security & Access Frameworks.

The Case Against Fixed-Interval Cron #

A crontab line such as */15 * * * * mysqladmin flush-logs has no knowledge of what the server is doing at minute 15. Three concrete collisions follow. It can fire mid-way through a large ALTER or a bulk load, closing a segment whose transaction spans the rotation boundary and complicating replay ordering. It can fire while a replica is minutes behind, so rotation and the replica’s relay pull contend for the same index. And with no mutual-exclusion guard, a slow rotation that overruns its 15-minute window is joined by the next tick, so two processes call FLUSH BINARY LOGS and race the archive queue, producing duplicate uploads and, worse, a purge that deletes a segment the other run had not yet shipped.

Systemd timers supersede raw crontab entries for this workload because they provide native OnCalendar precision, Persistent=true catch-up after downtime, integrated journaling, and — combined with flock or a oneshot service — first-class single-flight semantics. The cron path remains valid where systemd is unavailable, provided every invocation is wrapped in flock -n so a second tick exits immediately rather than running concurrently.

A production timer unit enforces cadence and catch-up:

# /etc/systemd/system/mysql-binlog-rotation.timer
[Unit]
Description=MySQL Binary Log Rotation Scheduler
Requires=mysql-binlog-rotation.service

[Timer]
OnCalendar=*:0/15
Persistent=true
AccuracySec=10
Unit=mysql-binlog-rotation.service

[Install]
WantedBy=timers.target

The paired service wraps execution as a oneshot, which systemd already runs single-flight per unit:

# /etc/systemd/system/mysql-binlog-rotation.service
[Unit]
Description=MySQL Binlog Rotation Execution
After=network.target mysqld.service

[Service]
Type=oneshot
ExecStart=/usr/local/bin/mysql-binlog-rotate --timeout=300
EnvironmentFile=/etc/mysql/archiver.env
StandardOutput=journal
StandardError=journal
RuntimeDirectory=mysql-rotation

On a cron-only host the equivalent guard is an advisory lock the orchestrator must acquire before it does anything else:

# /etc/cron.d/mysql-binlog-rotation  (cron fallback — flock enforces single-flight)
*/15 * * * * mysql  /usr/bin/flock -n /run/lock/binlog-rotate.lock \
  /usr/local/bin/mysql-binlog-rotate --timeout=300

Production-Grade Python Implementation #

The orchestrator is a stateful, idempotent module. It refuses to rotate unless a pre-flight gate passes, holds a flock for its whole run so it is single-flight even across the systemd and cron paths, retries only genuinely transient database faults through tenacity, and emits one structured JSON line per state transition. Deterministic faults — a bad grant, binary logging switched off — are raised immediately rather than retried into a livelock.

#!/usr/bin/env python3
"""State-aware rotation orchestrator for MySQL binary logs.
Targets: MySQL 8.0.22+, Python 3.10+, POSIX. Requires: mysql-connector-python, tenacity."""
from __future__ import annotations

import fcntl
import json
import logging
import sys
from contextlib import contextmanager
from dataclasses import dataclass
from pathlib import Path
from time import gmtime, strftime

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

logger = logging.getLogger("binlog.rotation")


class TransientRotationError(RuntimeError):
    """Retryable: connection reset, lock-wait timeout, transient I/O."""


class PreflightRejected(RuntimeError):
    """Deterministic for this tick: state gate said 'do not rotate now'."""


@dataclass(slots=True, frozen=True)
class RotationConfig:
    host: str = "127.0.0.1"
    port: int = 3306
    user: str = "archiver"
    password: str = ""
    connect_timeout: int = 10
    max_replica_lag_sec: int = 120
    max_open_txn_age_sec: int = 60
    lock_path: str = "/run/lock/binlog-rotate.lock"
    dry_run: bool = False


@contextmanager
def single_flight(lock_path: str):
    """Advisory flock so a second tick (systemd OR cron) exits, never races."""
    fh = open(lock_path, "w")
    try:
        try:
            fcntl.flock(fh, fcntl.LOCK_EX | fcntl.LOCK_NB)
        except BlockingIOError as exc:
            raise PreflightRejected("another rotation holds the lock") from exc
        yield
    finally:
        fcntl.flock(fh, fcntl.LOCK_UN)
        fh.close()


@contextmanager
def mysql_connection(cfg: RotationConfig):
    conn = connect(
        host=cfg.host, port=cfg.port, user=cfg.user, password=cfg.password,
        connection_timeout=cfg.connect_timeout, autocommit=True,
    )
    try:
        yield conn
    finally:
        conn.close()


def validate_preconditions(conn, cfg: RotationConfig) -> None:
    """Raise PreflightRejected unless the server is safe to rotate right now."""
    with conn.cursor(dictionary=True) as cur:
        # MySQL 8.0.22+: replica health via SHOW REPLICA STATUS
        cur.execute("SHOW REPLICA STATUS")
        if (replica := cur.fetchone()) and replica.get("Seconds_Behind_Source") is not None:
            if (lag := int(replica["Seconds_Behind_Source"])) > cfg.max_replica_lag_sec:
                raise PreflightRejected(f"replica lag {lag}s > {cfg.max_replica_lag_sec}s")

        # MySQL 8.0+: no long-running open transaction spanning the boundary
        cur.execute(
            """
            SELECT COUNT(*) AS open_txns
            FROM information_schema.innodb_trx
            WHERE TIME_TO_SEC(TIMEDIFF(NOW(), trx_started)) > %s
            """,
            (cfg.max_open_txn_age_sec,),
        )
        if (open_txns := cur.fetchone()["open_txns"]) > 0:
            raise PreflightRejected(f"{open_txns} long-running transaction(s) open")

        # MySQL 8.0+: binary logging actually on (else FLUSH is meaningless)
        cur.execute("SHOW VARIABLES LIKE 'log_bin'")
        if (row := cur.fetchone()) is None or row["Value"] != "ON":
            raise PreflightRejected("log_bin is OFF; nothing to rotate")


@retry(
    retry=retry_if_exception_type(TransientRotationError),
    stop=stop_after_attempt(3),
    wait=wait_exponential(multiplier=1, min=2, max=20),
    reraise=True,
)
def execute_rotation(conn) -> str:
    """Close the active segment and return the new active binlog filename."""
    try:
        with conn.cursor(dictionary=True) as cur:
            cur.execute("FLUSH BINARY LOGS")
            # MySQL 8.0.22+: SHOW BINARY LOG STATUS replaces SHOW MASTER STATUS
            cur.execute("SHOW BINARY LOG STATUS")
            if (status := cur.fetchone()) is None:
                raise PreflightRejected("SHOW BINARY LOG STATUS empty (log_bin off?)")
            return status["File"]
    except MySQLError as exc:
        # 1598 = ER_BINLOG_LOGGING_IMPOSSIBLE (disk/write error) is transient-ish;
        # surface it for retry, everything else deterministic re-raises.
        if exc.errno == 1598:
            raise TransientRotationError(f"binlog write error: {exc.msg}") from exc
        raise


def emit(event: str, **fields) -> None:
    logger.info(json.dumps(
        {"event": event, "ts": strftime("%Y-%m-%dT%H:%M:%SZ", gmtime()), **fields}
    ))


def main() -> int:
    logging.basicConfig(level=logging.INFO, format="%(message)s")
    cfg = RotationConfig(dry_run="--dry-run" in sys.argv)
    try:
        with single_flight(cfg.lock_path), mysql_connection(cfg) as conn:
            validate_preconditions(conn, cfg)
            if cfg.dry_run:
                emit("dry_run", action="rotation_skipped")
                return 0
            new_binlog = execute_rotation(conn)
            emit("rotation_executed", new_binlog=new_binlog)
            return 0
    except PreflightRejected as exc:
        emit("rotation_skipped", reason=str(exc))
        return 0  # a skipped tick is a success, not an error to page on
    except MySQLError as exc:
        emit("mysql_error", code=exc.errno, message=exc.msg)
        return 2
    except Exception as exc:  # noqa: BLE001 — last-resort structured capture
        emit("unhandled_failure", error=repr(exc))
        return 3


if __name__ == "__main__":
    raise SystemExit(main())

Two design choices are load-bearing. A rejected pre-flight exits 0, not non-zero — a tick that correctly declines to rotate is a healthy outcome, and paging on it would train operators to ignore the alert. And the flock is held for the whole connection lifetime, so the systemd and cron paths can coexist during a migration without ever racing. Once execute_rotation returns, the closed segment is published to the archive queue; the durable upload and cross-region replication of that segment are owned by AWS S3 & GCS Sync Pipelines, and the queue depth and worker sizing behind it by Async Processing & Queue Management.

Configuration Reference #

Two surfaces govern rotation: MySQL server variables that shape when and how segments close, and the orchestrator parameters in RotationConfig. The server variables below assume MySQL 8.0.22+.

Orchestrator parameters live in the typed RotationConfig: max_replica_lag_sec and max_open_txn_age_sec are the two pre-flight gates, connect_timeout bounds a hung primary, and lock_path names the single-flight mutex. Set binlog_expire_logs_seconds from the archiving-lag SLO, not the other way round — the purge horizon is a consequence of how fast the pipeline confirms durability, and the Base Backup Integration for PITR snapshot cadence sets the floor below which no segment may be expired.

Validation & Verification Gates #

A rotation is not “done” when FLUSH BINARY LOGS returns. It is done when the closed segment is confirmed durable and the local copy is cleared for reclamation. Each gate below must pass before the next runs.

• Single-flight acquired. The flock must be held before any query executes. A failed acquisition is a skip, not a failure — another run owns this tick.
• Pre-flight clean. Replica lag under threshold, no long-running open transaction, and log_bin = ON. Any failed gate skips this tick and records the reason.
• New segment observed. After FLUSH BINARY LOGS, SHOW BINARY LOG STATUS must report a different File than before the flush, and the previous file must appear in SHOW BINARY LOGS as a closed segment. If the filename did not advance, the rotation did not happen.
• Handoff-before-purge. The closed segment must be marked confirmed by the archiver — checksum reconciled against the uploaded object — before it is eligible for PURGE BINARY LOGS or before binlog_expire_logs_seconds is allowed to reach it. This is the single gate that prevents a fractured recovery chain.
• GTID contiguity. The GTID interval of the newly closed segment must join contiguously to the previous one; a gap is a recovery-blocking hole surfaced early.

-- MySQL 8.0.22+: confirm the active segment advanced after a flush
SHOW BINARY LOG STATUS;   -- File must differ from the pre-flush File

-- MySQL 8.0.22+: list closed segments and confirm the just-rotated one is present
SHOW BINARY LOGS;         -- Log_name, File_size, Encrypted

-- MySQL 8.0+: only purge a segment the archiver has already confirmed durable
PURGE BINARY LOGS TO 'mysql-bin.000042';

The confirmed segment feeds the manifest a recovery orchestrator later maps a target timestamp or GTID against; that coordinate selection is covered in Timestamp Targeting Strategies.

Error Handling & Failure Modes #

Rotation failures split into retryable (back off and retry the same tick) and deterministic (skip or page; retrying cannot help). Mapping each signal to the right class is what keeps the scheduler from either livelocking or silently dropping a segment.

The full backoff taxonomy, jitter, and dead-letter routing for the transport half of these faults — an upload that throttles or times out after a clean rotation — is the subject of Error Handling & Retry Logic; the TransientRotationError boundary here is deliberately narrow so only genuinely transient database faults are retried.

Observability & Alerting #

Every state transition is an observable event. Emit one structured JSON line per tick with stable fields — event, ts, new_binlog, reason, preflight_lag, preflight_open_txns, rotation_ms — so a log pipeline computes cadence and skip-rate without parsing prose. Server-side, watch the source’s own view of binlog activity:

-- MySQL 8.0.22+: binlog cache pressure hints at rotation cadence mismatch
SELECT VARIABLE_NAME, VARIABLE_VALUE
FROM performance_schema.global_status
WHERE VARIABLE_NAME IN ('Binlog_cache_use', 'Binlog_cache_disk_use');

-- MySQL 8.0+: watch for a transaction old enough to straddle a rotation boundary
SELECT trx_id, TIME_TO_SEC(TIMEDIFF(NOW(), trx_started)) AS age_sec
FROM information_schema.innodb_trx
ORDER BY age_sec DESC
LIMIT 5;

Alert thresholds worth wiring:

• Rotation staleness — time since the last rotation_executed event. If it exceeds two timer intervals, the timer, the lock, or the primary is stuck; this single SLI catches a wedged scheduler before disk fills.
• Skip-rate spike — a sustained rise in rotation_skipped means the pre-flight gate is chronically tripping (a replica stuck behind, or a perpetual long transaction), which is a workload problem masquerading as a scheduling one.
• Archiving lag — age of the oldest closed-but-unconfirmed segment. Page well before it approaches binlog_expire_logs_seconds; this is the metric that guards the handoff-before-purge invariant.
• Local datadir headroom — free space on the binlog volume. A rotation that succeeds but whose segments are not being purged (because archiving stalled) will still exhaust disk; alert on the trend, not just the failure.

Expose Prometheus counters — mysql_binlog_rotation_success_total, mysql_binlog_rotation_skipped_total{reason}, mysql_binlog_rotation_duration_seconds — so cadence, skip reasons, and latency are dashboard-able platform-wide.

Frequently Asked Questions #

Related #

• Compression & Encryption Workflows — the compress-then-encrypt transform that consumes each closed segment this scheduler hands off.
• AWS S3 & GCS Sync Pipelines — durable multipart upload and cross-region replication of the rotated segments.
• Configuring Logrotate for MySQL Binary Logs Safely — safe postrotate hooks and why copytruncate must be avoided when systemd is not an option.
• Error Handling & Retry Logic — backoff, jitter, and dead-letter routing for the transport-side failures referenced above.
• Binlog Retention Boundaries — reconciling the purge horizon with replication lag so rotation never outpaces durability.

Back to Automated Binlog Archiving to Object Storage.