Binlog Archiving

Configuring Logrotate for MySQL Binary Logs Safely

The failure signature is unmistakable and it always arrives after logrotate has been “working fine” for weeks: a replica IO thread stops dead with Got fatal error 1236 from master when reading data from binary log: 'Could not find first log file name in binary log index file', and a point-in-time recovery (PITR) drill discovers that mysqlbinlog cannot replay past a segment that logrotate compressed or truncated out from under the running server. The root cause is almost universal: an administrator dropped a copytruncate or compress stanza into /etc/logrotate.d/ pointed at mysql-bin.*, and logrotate took ownership of a rotation boundary that belongs exclusively to mysqld. This page fixes that. The only safe model is that MySQL owns rotation via FLUSH BINARY LOGS and max_binlog_size, while logrotate is demoted to a deterministic post-rotation trigger that archives, verifies, and cleans up segments the server has already closed — never a byte the server is still writing.

Visual Overview

Safe logrotate flow: MySQL owns rotation, logrotate only triggers archive after a segment is confirmed closedA logrotate tick (daily, rotate 14) runs a nocompress plus sharedscripts stanza. Its postrotate script issues FLUSH BINARY LOGS, then confirms with lsof that mysqld released the previous descriptor. Gate one: is the segment closed? No leads to skip and alert, never touching the active tail; Yes hands the closed segment to the archive queue. Gate two: is a durable, checksum-verified copy stored? No keeps the file local and retries through a dead-letter queue; Yes makes the segment eligible for PURGE BINARY LOGS.YesYesNoNologrotate tickdaily · rotate 14nocompress + sharedscriptspostrotateFLUSH BINARY LOGSconfirm mysqld released fdlsof -p $(pgrep -x mysqld)hand closed segmentto archive queueeligible forPURGE BINARY LOGSsegment closed?durable copy +checksum OK?skip + alertnever touch the active tailkeep local · retrydead-letter queue on repeat failMySQL owns the rotation boundary · logrotate only archives segments the server has already closed

Context & Prerequisites

This page is the filesystem-tooling companion to Rotation Scheduling & Cron Automation; read that parent first, because the cadence, single-flight locking, and handoff-before-purge invariant it defines are what this logrotate stanza plugs into — logrotate here is only the trigger, not the scheduler of record. The overriding constraint is that a binary log segment is safe to archive only after MySQL has closed it, so a gap-free GTID Tracking & Enforcement pipeline must be upstream and binlog_format=ROW already enforced. You need MySQL 8.0.22+ (for FLUSH BINARY LOGS, SHOW BINARY LOG STATUS, and the Encrypted column in SHOW BINARY LOGS), a Linux host running logrotate 3.18+, and the mysql/mysqladmin client plus lsof. The archive step assumes a durable, checksum-verified upload path such as the one in AWS S3 & GCS Sync Pipelines, and the local-retention math that keeps a closed segment on disk until that upload confirms is governed by Binlog Retention Boundaries.

Step-by-Step Implementation

1. Confirm MySQL owns the rotation boundary

Before writing any logrotate stanza, verify the server — not a filesystem tool — decides when a segment closes. Rotation is driven by max_binlog_size and explicit FLUSH BINARY LOGS; logrotate must never move, rename, or truncate an active inode.

-- MySQL 8.0.22+  (run as an admin holding BINLOG_ADMIN + REPLICATION CLIENT)
SELECT @@GLOBAL.log_bin, @@GLOBAL.binlog_format;   -- must return 1, ROW
SELECT @@GLOBAL.max_binlog_size;                    -- server-driven size cap
SHOW BINARY LOG STATUS;                             -- File = the ACTIVE tail, do not touch it

PITR relevance: the file reported by SHOW BINARY LOG STATUS is still receiving events. Every rule that follows exists to keep logrotate away from that one filename, because truncating or compressing it captures a torn transaction and dead-ends the recovery chain.

2. Write a postrotate-only logrotate stanza

Create /etc/logrotate.d/mysql-binlogs with nocompress, missingok, and notifempty, and delegate all real work to a postrotate script. There is deliberately no compress, no copytruncate, and no delaycompress — logrotate touches nothing itself.

# /etc/logrotate.d/mysql-binlogs
/var/lib/mysql/mysql-bin.[0-9]* {
    daily
    rotate 14
    missingok
    notifempty
    nocompress
    nocreate
    sharedscripts
    postrotate
        /usr/local/bin/mysql-binlog-archive.sh >> /var/log/mysql-binlog-archive.log 2>&1
    endscript
}

PITR relevance: nocompress guarantees logrotate never rewrites a segment’s bytes; compression happens later in the pipeline, only after the file is confirmed closed. sharedscripts makes the archive script run exactly once per tick even though the glob matches many files, so ordered handoff is never duplicated. The [0-9]* glob deliberately excludes mysql-bin.index, which must never be rotated.

3. Drive rotation from the postrotate script, then wait for the fd to close

The script issues a clean server-side rotation, then confirms mysqld has released the previous file descriptor before touching anything. This ordering is the whole safety guarantee.

#!/usr/bin/env bash
# /usr/local/bin/mysql-binlog-archive.sh — MySQL 8.0.22+
set -euo pipefail

DATADIR="/var/lib/mysql"
LOCK="/run/mysql-binlog-archive.lock"

# single-flight: never let two rotations race the archive queue
exec 9>"$LOCK"; flock -n 9 || { echo "archive already running; skip"; exit 0; }

# ask MySQL to close the current segment and open a fresh one
mysqladmin --defaults-file=/etc/mysql/archive.cnf flush-binary-logs

# the just-closed segment is the highest-numbered file that mysqld no longer holds open
mapfile -t open_fds < <(lsof -p "$(pgrep -x mysqld)" 2>/dev/null | awk '/mysql-bin\.[0-9]+/ {print $NF}')
for seg in "$DATADIR"/mysql-bin.[0-9]*; do
    [[ "$seg" == *.index ]] && continue
    if ! printf '%s\n' "${open_fds[@]}" | grep -qxF "$seg"; then
        /usr/local/bin/queue-binlog-segment.sh "$seg"   # hand off; never delete here
    fi
done

PITR relevance: flush-binary-logs forces the active tail to become a closed, immutable segment; the lsof filter proves the server no longer holds it open, so the archiver can read a stable, complete file rather than a moving target.

4. Hand off — but never purge — from the trigger

The postrotate path only enqueues closed segments; it must not delete or purge them. A segment becomes eligible for PURGE BINARY LOGS only after the archiver confirms a durable, checksum-verified copy, which is why deletion lives in the archive worker, not here.

# /usr/local/bin/queue-binlog-segment.sh (excerpt)
# publishes to the same ordered queue the async workers consume
redis-cli -s /run/redis/redis.sock RPUSH binlog:archive:queue "$1" >/dev/null

PITR relevance: keeping purge out of the rotation trigger enforces handoff-before-purge — the server’s local copy survives until object storage is proven durable, so a failed upload can never leave a hole in the recovery timeline.

Configuration Snippet & Reference Table

Persist the server side in my.cnf and give the archive account a least-privilege credential file. Every logrotate directive below is chosen specifically for binary logs; the defaults you would use for text logs are actively dangerous here.

# /etc/mysql/my.cnf — MySQL 8.0.22+
[mysqld]
log_bin                     = /var/lib/mysql/mysql-bin
binlog_format               = ROW
max_binlog_size             = 1073741824     # server owns the rotation boundary (1 GiB)
sync_binlog                 = 1
binlog_expire_logs_seconds  = 259200         # 3-day local safety window > max archive lag
binlog_checksum             = CRC32
DirectiveSet toWhy it matters for binary logs
copytruncateneverTruncates the active inode while mysqld writes it, destroying the event stream and halting replicas with ERROR 1236. Categorically unsafe.
compress / delaycompressomitRewrites segment bytes from the rotation tool; compression must happen in the pipeline after the file is confirmed closed.
nocompresspresentGuarantees logrotate leaves each segment byte-for-byte intact.
nocreatepresentMySQL, not logrotate, must recreate the next segment with correct ownership and headers.
sharedscriptspresentRuns postrotate once per tick, so ordered handoff is never duplicated across matched files.
rotate 14≥ retention SLABounds how many stale references logrotate tracks; the real retention gate is binlog_expire_logs_seconds.
glob mysql-bin.[0-9]*presentMatches numbered segments only and excludes mysql-bin.index, which must never rotate.

Verification Checklist

Gotchas & Version-Specific Caveats

copytruncate is never safe for binary logs. It is fine for many text logs but fatal here: MySQL keeps the inode open and writes at the previous offset, so truncation silently zeroes the event stream. There is no recovery for the truncated segment — the fix is prevention, never copytruncate tuning.

SHOW MASTER STATUS is deprecated. On MySQL 8.0.22+ use SHOW BINARY LOG STATUS to identify the active tail; the old spelling still resolves on 8.0 but is removed as the canonical form in 8.4, so pin the new statement in your scripts to stay forward-compatible.

expire_logs_days is gone. It was deprecated in 8.0 and removed; only binlog_expire_logs_seconds exists, and a leftover expire_logs_days line prevents startup on 8.4. Never pair the two — set exactly one purge horizon and keep it larger than your maximum archive lag.

Prefer a systemd timer where you can. logrotate’s own cron.daily invocation is coarse and unlocked; the state-aware, flock-guarded scheduling model in Rotation Scheduling & Cron Automation is strictly safer, and this stanza is best treated as the fallback for hosts without systemd. If both paths run during a migration, the shared advisory flock keeps them from colliding.

Compression choice is a pipeline decision, not a logrotate one. Because the stanza is nocompress, the compress/encrypt trade-off (zstd vs gzip, and streaming AES-256) lives downstream in Compression & Encryption Workflows, where the file is already closed and verified.

Frequently Asked Questions

Why can't I just let logrotate compress and rotate the binlogs like any other log?

Because a binary log is not a text log — MySQL holds the active segment open and depends on mysql-bin.index to enumerate it. If logrotate renames, moves, truncates, or compresses that active file, the server keeps writing to the old inode while the index no longer matches on disk, and replicas fail with ERROR 1236. Text logs tolerate copytruncate because the writer reopens on SIGHUP; MySQL does not work that way. The safe pattern is nocompress plus a postrotate script that only acts on segments MySQL has already closed via FLUSH BINARY LOGS.

What exactly does sharedscripts change for binary log rotation?

The mysql-bin.[0-9]* glob matches many files, and without sharedscripts logrotate runs the postrotate block once per matched file. For a binary-log archive trigger that means the flush-and-handoff would fire repeatedly in one tick, duplicating queue entries and racing the ordered upload. sharedscripts collapses that to a single execution per rotation cycle, which is the only correct behavior when the script hands segments to an ordered archive queue.

How do I confirm a segment is closed before the script touches it?

Issue FLUSH BINARY LOGS (or mysqladmin flush-binary-logs) to force a clean switch, then check mysqld’s open descriptors with lsof -p $(pgrep -x mysqld). Any mysql-bin.NNNNNN still listed is either the new active tail or not yet released — skip it. Only files that are absent from that fd list and older than the current SHOW BINARY LOG STATUS file are safe to read, checksum, and enqueue. This fd check is the guardrail that keeps the archiver off the active tail.

Back to Rotation Scheduling & Cron Automation.