Setting a Safe binlog_expire_logs_seconds for Compliance Retention #

A single under-scoped timer value is the most common way an operator turns a compliance requirement into an outage. Set binlog_expire_logs_seconds too low and a lagging or rebuilding replica halts with error 1236 — “Cannot replicate because the master purged required binary logs” the instant the primary expires a segment the replica still needed; the GTID chain fractures and your only fix is a full replica rebuild. Set it with no capacity math and the opposite failure arrives: the datadir volume fills, commits stall, and the instance crashes with Disk is full. This page resolves the narrow but high-stakes decision of choosing one value that simultaneously satisfies a regulatory retention window (SOC 2, HIPAA, PCI-DSS, GDPR) and never purges a log a downstream consumer or Point-in-Time Recovery (PITR) replay still requires — using MySQL 8.0’s second-precision binlog_expire_logs_seconds rather than the removed expire_logs_days.

Visual Overview #

Context & Prerequisites #

The server timer is a backstop, not your primary retention control. A precise, recovery-safe purge boundary is computed from transaction state — the minimum of the replication, archival, compliance, and backup floors — by the engine described in Binlog Retention Boundaries; binlog_expire_logs_seconds exists only to catch segments that pipeline already confirmed are safe. Its job is therefore to be set longer than your worst-case archival latency and replica lag, and to encode the compliance floor — the regulatory minimum you may never purge below. Because continuity is measured in Global Transaction Identifiers rather than file positions, this only holds when every node shares one identifier space: require MySQL 8.0.22+ with gtid_mode = ON and enforce_gtid_consistency = ON, extracted gap-free by the GTID Tracking & Enforcement pipeline, and understand the file/GTID model from MySQL Binary Log Architecture & GTID Fundamentals. The value also interacts with storage volume: under ROW vs STATEMENT vs MIXED Formats, the ROW format required for deterministic PITR replay generates 3–10× more log volume than STATEMENT, so a long compliance window is also a large storage commitment.

Step-by-Step Implementation #

Each step below is annotated with its PITR relevance — why the number you pick either preserves or destroys the ability to replay to an arbitrary point.

1. Convert the compliance window to exact seconds #

Regulatory frameworks specify days; the variable takes seconds. The conversion is deterministic:

seconds = retention_days × 86,400

A 30-day audit window is 30 × 86,400 = 2,592,000 seconds. PITR relevance: this is the floor below which the value must never drop — it is the minimum span over which an auditor (or a recovery) can reconstruct state, so it is a hard lower bound on the timer, never the final answer.

2. Measure worst-case replica lag and archival latency #

The safe value is the compliance floor or the slowest consumer’s catch-up window plus your archival latency — whichever is larger. Measure the current worst case before committing a number:

-- MySQL 8.0.22+ : worst replica lag, run on each replica (or via a monitoring roll-up)
SELECT
    CHANNEL_NAME,
    SERVICE_STATE,
    LAST_QUEUED_TRANSACTION,
    LAST_APPLIED_TRANSACTION
FROM performance_schema.replication_applier_status_by_worker;

-- Coarse lag signal from the replica's status
SHOW REPLICA STATUS\G   -- read Seconds_Behind_Source

PITR relevance: a replica lagging Seconds_Behind_Source = 900 still needs every segment back to its applied GTID. If the timer expires one of those, the replica cannot resume from SOURCE_AUTO_POSITION = 1 and the recovery chain that depends on that replica is gone. Add a safety buffer of at least 24 hours (86,400 seconds) over the measured worst case to absorb spikes and archival upload delays.

3. Apply dynamically, then persist #

Set the value on the running instance first to avoid a restart, then persist it so it survives service lifecycle events:

-- MySQL 8.0.22+
-- 30-day compliance floor (2,592,000) + 24h buffer (86,400) = 2,678,400
SET GLOBAL  binlog_expire_logs_seconds = 2678400;   -- takes effect now
SET PERSIST binlog_expire_logs_seconds = 2678400;   -- writes mysqld-auto.cnf, survives restart

PITR relevance: SET GLOBAL alone is lost on restart — a node that reboots reverts to the compiled/my.cnf default (2592000, 30 days) and may silently start purging on a tighter schedule than compliance requires. SET PERSIST closes that drift window. Note that changing the value does not retroactively rewrite the age of existing logs; expiry is evaluated against each file’s last-modified time at the next opportunity.

4. Forecast disk and set a guardrail #

A long window is a storage commitment. Measure daily generation, then reserve headroom:

-- MySQL 8.0.22+ : cumulative binlog I/O for growth-rate estimation
SELECT
    EVENT_NAME,
    SUM_NUMBER_OF_BYTES_WRITE
FROM performance_schema.file_summary_by_instance
WHERE EVENT_NAME LIKE 'wait/io/file/sql/binlog%';

-- Current position / active file (8.4 name; use SHOW MASTER STATUS on 8.0)
SHOW BINARY LOG STATUS\G

Reserve at least 30% free space after projecting the full retention window: an instance writing 50 GB/day held for 30 days needs 1,500 GB × 1.3 ≈ 1,950 GB of dedicated volume. PITR relevance: if the volume fills before the timer expires anything, MySQL stalls and can crash — a longer window that you cannot store is not safer, it is an outage waiting for its first busy day. Mount binary logs on a volume separate from the datadir so log growth cannot starve the tablespace.

5. Archive every segment before it can expire #

The timer must never be the reason a recoverable transaction disappears. Stream each completed segment to durable storage — verified — before expiry can reach it. This is the transform stage detailed in Compression & Encryption Workflows; a minimal streaming extractor:

# Python 3.10+
import asyncio
from dataclasses import dataclass
from pathlib import Path


@dataclass(slots=True, frozen=True)
class ArchiveResult:
    log_name: str
    dest: Path
    ok: bool


async def archive_binlog(log_name: str, dest_dir: Path) -> ArchiveResult:
    """Stream one binary log off the primary in raw form, ready for verify + upload."""
    dest = dest_dir / log_name
    proc = await asyncio.create_subprocess_exec(
        "mysqlbinlog",
        "--read-from-remote-server",
        "--host=primary.internal",
        "--user=binlog_reader",
        "--raw",
        "--verify-binlog-checksum",     # fail fast on silent corruption
        "--result-file", f"{dest_dir}/",
        log_name,
        stdout=asyncio.subprocess.PIPE,
        stderr=asyncio.subprocess.PIPE,
    )
    _, stderr = await proc.communicate()
    match proc.returncode:
        case 0:
            return ArchiveResult(log_name, dest, ok=True)
        case code:
            raise RuntimeError(f"extract of {log_name} failed ({code}): {stderr.decode()}")

PITR relevance: once a segment is checksum-verified in object storage, expiring its local copy costs nothing recoverable — replay can pull it back. Pass the credential via a secrets manager rather than the command line; see Security & Access Frameworks. Run extraction against a replica, not the primary, to keep archival I/O off the write path.

Configuration Reference #

A minimal, copy-pasteable my.cnf block for a 30-day compliance window with a 24-hour buffer:

[mysqld]
log_bin                        = mysql-bin
binlog_format                  = ROW
binlog_expire_logs_seconds     = 2678400   # 31 days (30d compliance + 24h buffer)
sync_binlog                    = 1         # crash-safe durability of retained logs
innodb_flush_log_at_trx_commit = 1
# expire_logs_days is intentionally left unset (removed in MySQL 8.4)

Verification Checklist #

• Compliance window converted with days × 86400 and treated as a hard floor
• Worst-case Seconds_Behind_Source measured across every replica and a ≥ 24 h buffer added
• SELECT @@GLOBAL.binlog_expire_logs_seconds; returns the intended value on the running instance
• SET PERSIST applied — value present in mysqld-auto.cnf and survives a test restart
• expire_logs_days is unset (or 0) so the two timers cannot conflict
• Projected daily_GB × retention_days × 1.3 fits the dedicated binlog volume with ≥ 30% free
• Archival extractor verified with --verify-binlog-checksum before any local expiry
• Alert wired for when gtid_purged advances past the oldest archived GTID
• SYSTEM_VARIABLES_ADMIN restricted to a compliance role, never an application account

Gotchas & Version-Specific Caveats #

Purging is event-driven, not a 300-second poll. A widespread myth is that a background thread scans every five minutes. MySQL evaluates expiry at server startup and on each binary log rotation (a size-triggered switch, an explicit FLUSH BINARY LOGS, or FLUSH LOGS). On a quiet instance that rarely rotates, expired logs can linger well past their timestamp; on a busy one they clear promptly. Do not expect immediate disk reclamation the moment you shorten the window — force a rotation if you need space back sooner.

expire_logs_days is gone in MySQL 8.4. It was deprecated in 8.0 and removed in 8.4.0. Configs carried forward from 5.7/8.0 that still set it will fail to start on 8.4. On 8.0, if both expire_logs_days and binlog_expire_logs_seconds are non-zero, the seconds value wins and the days value is ignored with a warning — set only the seconds variable.

SHOW MASTER STATUS was renamed. It is deprecated as of MySQL 8.2.0 and replaced by SHOW BINARY LOG STATUS in 8.4; RESET MASTER likewise became RESET BINARY LOGS AND GTIDS. Automation that parses the old command breaks silently after an 8.4 upgrade — pin the statement to the server version.

Never delete binary log files from the filesystem. Removing mysql-bin.00xxxx by hand corrupts the binlog.index file and desynchronizes gtid_purged, forcing a full recovery. Reclaim space only through PURGE BINARY LOGS TO 'log_name' or the expiry timer. If a purge targets a name a concurrent rotation already dropped, you get ERROR 1373 (ER_UNKNOWN_TARGET_BINLOG) — re-read SHOW BINARY LOGS and recompute rather than hard-coding a target.

A tight timer during a replica rebuild is a trap. If you are reprovisioning a replica from backup, its needed GTID range extends far back in time. Temporarily raise binlog_expire_logs_seconds (or gate purging through the Binlog Retention Boundaries engine) until the rebuild catches up, or route around the gap using Fallback Routing Strategies. Otherwise the timer can expire the very logs the rebuild is streaming.

Frequently Asked Questions #

Related #

• Binlog Retention Boundaries — the GTID-aware engine that computes the real purge boundary this timer only backstops.
• Securing Binlog Access with MySQL 8.0 Dynamic Privileges — locking SYSTEM_VARIABLES_ADMIN and the binlog reader account down to compliance roles.
• Configuring logrotate for MySQL Binary Logs Safely — why OS-level rotation must never race the server’s own expiry.
• Base Backup Integration for PITR — anchoring the retention window to the backup that defines your recoverable start point.

Back to Binlog Retention Boundaries.