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

TL;DR — MySQL binlog retention without disk surprises:Usebinlog_expire_logs_seconds(MySQL 8.0+, replaces deprecatedexpire_logs_days) to set retention — typical safe value is604800 seconds (7 days). Don'tPURGE BINARY LOGS BEFOREmanually unless you've confirmed every replica'sRead_Source_Log_Posis past the boundary. On AWS RDS usemysql.rds_set_configuration('binlog retention hours', 168)instead of the in-instance variable. Monitor binlog disk usage (SHOW BINARY LOGS) — flow-control on a stalled replica can let binlogs grow to TB before alerting. Rotate manually withFLUSH BINARY LOGSafter maintenance, never to 'save space'.

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.

TL;DR

• Usebinlog_expire_logs_seconds(MySQL 8.0+) instead of the deprecatedexpire_logs_daysfor automatic expiry.
• Always checkSHOW REPLICA STATUSbefore manually purging to confirm replicas have consumed the logs you plan to drop.
• UsePURGE BINARY LOGS TOorPURGE BINARY LOGS BEFOREfor targeted manual cleanup.
• Setsync_binlog=1in production for durability — never trade crash safety for performance without understanding the risk.
• On RDS, usemysql.rds_set_configuration('binlog retention hours', N)instead of native MySQL variables.
• Monitor disk usage continuously; binlog volume spikes during heavy write workloads without warning.

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>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 reachesmax_binlog_size(default: 1 GB).
• The server is restarted orFLUSH LOGSis executed.
• ARESET BINARY LOGS AND GTIDS(formerlyRESET 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.

Warning

Never manually delete binlog files from the filesystem usingrm. Doing so orphans entries in the index file, which corrupts MySQL's view of its own log state. Always use SQL commands or let the automatic expiry system handle deletions.

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_secondssets 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.

iniCopy

# 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:

sqlCopy

SET GLOBAL binlog_expire_logs_seconds = 604800;

MySQL 5.7 and Earlier: expire_logs_days

On older versions, the equivalent variable isexpire_logs_days:

iniCopy

# my.cnf (MySQL 5.7) expire_logs_days = 7

Warning

In MySQL 8.0,expire_logs_daysis deprecated. If both variables are set simultaneously,binlog_expire_logs_secondstakes precedence. Do not rely onexpire_logs_daysin MySQL 8.0 deployments — it may be removed in a future release.

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:

iniCopy

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 withNOW(),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,ROWformat is the right choice. Expect binlog files to be larger than with STATEMENT format — factor this into your disk planning.

sync_binlog for Durability

Thesync_binlogvariable controls how often MySQL flushes the binlog to disk:

iniCopy

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

Withsync_binlog=1, MySQL callsfsync()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 to0delegates 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

sqlCopy

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:

sqlCopy

-- Run on each replica SHOW REPLICA STATUS\G

Look at theSource_Log_Filefield (formerlyMaster_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.

Warning

If you purge a binlog file that a replica still needs, the replica's IO thread will fail with "Got fatal error 1236 from source when reading data from binary log." Recovering from this requires either re-syncing the replica from a backup or relying on GTID-based auto-positioning, which is not always available in every configuration.

Step 3: Purge

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

sqlCopy

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

To purge all logs older than a specific timestamp:

sqlCopy

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

Tip

When usingPURGE BINARY LOGS BEFORE, set the timestamp at least 24 hours behind the current time to account for any replication lag on your replicas. A lagging replica may still need logs that appear old by wall clock time.

RDS and Cloud-Managed MySQL

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

sqlCopy

-- 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

sqlCopy

-- 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 ofSHOW BINARY LOGSdirectly or checking filesystem usage on the binlog directory:

bashCopy

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:

bashCopy

#!/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';"

Tip

Alert when the binlog directory exceeds 70% of the volume's total capacity. By the time you hit 90%, you may not have enough headroom to take an emergency backup before the disk fills completely.

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

Key Takeaways

• Binary logs are essential for replication and PITR — they must be managed, not disabled, to reclaim disk space.
• Usebinlog_expire_logs_secondson MySQL 8.0+;expire_logs_daysis deprecated and will be removed.
• Always runSHOW REPLICA STATUSon every replica before manual purging — never guess which logs are safe to drop.
• UsePURGE BINARY LOGS TOfor file-based targeting andPURGE BINARY LOGS BEFOREfor time-based targeting; never usermon binlog files directly.
• Setsync_binlog=1in production to prevent committed transaction loss on crash.
• UseROWbinlog format for deterministic replication; account for its larger file sizes in disk planning.
• On RDS, configure retention viamysql.rds_set_configuration('binlog retention hours', N)— it defaults to unlimited and will fill your storage.
• Build proactive disk monitoring around your binlog directory; do not wait for alerts after the volume is full.