MySQL binlog Retention, Rotation & Purge: Production Guide (2026)

Binary logs are the heartbeat of MySQL replication and point-in-time recovery — but left unmanaged, they silently consume disk space until your database server grinds to a halt at 3 AM. Every production MySQL deployment eventually faces the same reckoning: gigabytes of binlog files piling up on a volume that was never sized to hold them indefinitely. Understanding how binary logs rotate, expire, and can be safely purged is not optional knowledge for a DBA — it is survival knowledge. This guide covers the full lifecycle of MySQL binary logs, from configuration through safe deletion, with the guardrails you need to avoid destroying a replication chain or losing your recovery window.

What Are Binary Logs?

MySQL binary logs (binlogs) are a sequential record of every change that modifies data in the database. Unlike the general query log, binlogs capture only data-modifying statements — INSERT, UPDATE, DELETE, CREATE TABLE, DROP, and so on. They do not record SELECT queries.

Binary logs serve two critical purposes in production systems:

• Replication: Replicas read the primary's binlog stream to stay synchronized. Without binlogs, MySQL replication does not function.
• Point-in-time recovery (PITR): After restoring a full backup, you replay binlogs forward to recover to a specific moment before a failure or accidental deletion.

Binary logging is enabled by default in MySQL 8.0. On older installations you may need to verify it explicitly:

SHOW VARIABLES LIKE 'log_bin';

If the result is ON, binary logging is active. If it is OFF, add log_bin to your my.cnf and restart — but be aware this requires a server restart and has implications for all downstream consumers.

How Binary Log Rotation Works

MySQL writes to a single active binlog file until one of several rotation triggers fires, at which point it closes that file and opens a new one with an incremented sequence number (e.g., mysql-bin.000001 → mysql-bin.000002).

Rotation occurs when:

• The current file reaches max_binlog_size (default: 1 GB).
• The server is restarted or FLUSH LOGS is executed.
• A RESET BINARY LOGS AND GTIDS (formerly RESET MASTER) statement is issued.

Each rotation produces a new file. The index file (mysql-bin.index) maintains the ordered list of all current binlog files on disk. This is the file MySQL consults to know which logs exist and which can be cleaned up.

Configuring Expiry and Retention

Automatic cleanup is the first line of defense against disk exhaustion. MySQL provides two variables for this, and which one you use depends on your server version.

MySQL 8.0+: binlog_expire_logs_seconds

binlog_expire_logs_seconds sets the retention window in seconds. A binary log file is eligible for automatic deletion once it is older than this threshold and a rotation event occurs.

# my.cnf
[mysqld]
log_bin                    = /var/lib/mysql/mysql-bin
max_binlog_size            = 512M
binlog_expire_logs_seconds = 604800   # 7 days in seconds

You can also change it at runtime without a restart:

SET GLOBAL binlog_expire_logs_seconds = 604800;

MySQL 5.7 and Earlier: expire_logs_days

On older versions, the equivalent variable is expire_logs_days:

# my.cnf (MySQL 5.7)
expire_logs_days = 7

Choosing a Retention Window

Your retention window should be long enough to cover your backup cycle plus a comfortable recovery buffer. A common baseline is 7 days, but production teams with longer RTO requirements often extend to 14 or 30 days. The tradeoff is direct: longer retention means more disk space consumed. Size your binlog volume accordingly before extending retention.

binlog_format: ROW vs STATEMENT

The format in which events are written to the binlog significantly affects both file size and behavior:

binlog_format = ROW

• ROW (recommended): Records the actual row changes. Larger files, but deterministic — the replica knows exactly which rows changed regardless of non-deterministic functions.
• STATEMENT: Records the SQL statement itself. More compact, but unsafe with NOW(), RAND(), and other non-deterministic functions — can cause replica drift.
• MIXED: Uses STATEMENT by default and falls back to ROW for unsafe statements. A historical compromise that most modern deployments avoid.

For production replication and PITR, ROW format is the right choice. Expect binlog files to be larger than with STATEMENT format — factor this into your disk planning.

sync_binlog for Durability

The sync_binlog variable controls how often MySQL flushes the binlog to disk:

sync_binlog = 1   # Flush to disk after every commit (safest)

With sync_binlog=1, MySQL calls fsync() after every transaction commit, ensuring no committed transactions are lost in a crash. The performance cost is real on high-throughput systems, but the data safety guarantee is essential for production primaries. Setting it to 0 delegates flushing to the OS — faster, but you risk losing the last few seconds of committed transactions on a crash.

Purging Binary Logs Safely

Automatic expiry handles routine cleanup, but there are scenarios where you need to manually reclaim disk space immediately. MySQL provides two targeted purge commands.

Step 1: Check What You Have

SHOW BINARY LOGS;

This returns a list of all binlog files with their sizes. Identify the oldest files you want to remove.

Step 2: Verify Replica Status

This is the step most people skip — and it is the one that breaks replication. Before purging any log, confirm your replicas have already consumed it:

-- Run on each replica
SHOW REPLICA STATUS\G

Look at the Source_Log_File field (formerly Master_Log_File). This tells you which binlog file the replica is currently reading from. Never purge any file with a sequence number equal to or greater than this value.

Step 3: Purge

To purge all logs up to (but not including) a specific file:

PURGE BINARY LOGS TO 'mysql-bin.000042';

To purge all logs older than a specific timestamp:

PURGE BINARY LOGS BEFORE '2026-02-15 00:00:00';

RDS and Cloud-Managed MySQL

On Amazon RDS for MySQL, you cannot set binlog_expire_logs_seconds directly via parameter groups. RDS manages binlog lifecycle through its own mechanism. Use the provided stored procedure to configure retention:

-- Set binlog retention to 72 hours on RDS
CALL mysql.rds_set_configuration('binlog retention hours', 72);

-- Verify
CALL mysql.rds_show_configuration();

RDS defaults to NULL (no retention limit) if this is never configured, which means binlogs accumulate indefinitely. Set this immediately after provisioning any RDS MySQL instance that uses replication or PITR.

Monitoring Disk Usage

Disk exhaustion from binary logs is a foreseeable and preventable failure. Build monitoring around it rather than reacting to it after the fact.

Checking Current Binlog Disk Usage

-- Total size of all binary logs on disk
SELECT
    COUNT(*) AS log_count,
    ROUND(SUM(File_size) / 1024 / 1024 / 1024, 2) AS total_size_gb
FROM information_schema.FILES
WHERE FILE_TYPE = 'UNDO LOG';

-- Alternative: sum from SHOW BINARY LOGS
SELECT
    COUNT(*) AS log_count,
    ROUND(SUM(File_size) / 1073741824, 2) AS total_gb
FROM (
    SELECT File_size FROM mysql.`general_log` LIMIT 0  -- placeholder
) t;

The most reliable approach is querying the output of SHOW BINARY LOGS directly or checking filesystem usage on the binlog directory:

du -sh /var/lib/mysql/mysql-bin.*
df -h /var/lib/mysql

Automated Cleanup Script

For self-managed MySQL servers without automated expiry events, a simple cron-based approach works well. This script checks replica consumption before purging:

#!/bin/bash
# safe-binlog-purge.sh
# Purge binlogs older than 7 days, only if replicas are current

MYSQL="mysql -u root -p${MYSQL_PWD}"
RETENTION_DAYS=7
CUTOFF=$(date -d "-${RETENTION_DAYS} days" '+%Y-%m-%d %H:%M:%S')

# Get the oldest log file still needed by any replica
REPLICA_LOG=$($MYSQL -e "SHOW REPLICA STATUS\G" 2>/dev/null \
  | grep "Source_Log_File" | awk '{print $2}')

if [ -z "$REPLICA_LOG" ]; then
  echo "No replica detected or SHOW REPLICA STATUS returned empty."
  echo "Proceeding with time-based purge with caution."
fi

echo "Purging binary logs before: $CUTOFF"
$MYSQL -e "PURGE BINARY LOGS BEFORE '$CUTOFF';"

Metrics to Track

• Binlog disk usage (GB) — alert at 70% volume capacity.
• Binlog write rate (MB/s) — spikes indicate heavy write workloads that will consume retention faster.
• Seconds behind source — replica lag means replicas need older logs; increasing lag widens the window of logs you cannot safely delete.
• Number of binlog files — a proxy for whether automatic expiry is functioning correctly.

Key Takeaways

Simplify MySQL Operations with JusDB

Binary log management is one of dozens of operational tasks that compound into significant overhead for database teams running MySQL at scale. Tracking retention windows, validating replica state before every purge, and monitoring disk across multiple servers adds up — especially when your team would rather be building product than firefighting storage alerts.

JusDB provides fully managed MySQL hosting with automated binary log lifecycle management built in. Retention policies are applied consistently, disk usage is monitored continuously, and replica health is validated before any cleanup operation runs. You get the reliability of a production-hardened MySQL environment without the operational burden of managing it yourself.

Explore JusDB managed MySQL and see how much overhead you can hand off.