MySQL Binary Logs: Size Retention for Offline Consumers

Context

MySQL binary logs serve several consumers. Replicas use them to receive changes, backup workflows may need them for recovery, and change-data-capture tools may read them independently. A retention period should account for all of these consumers. Removing a file because the nearest replica has caught up can strand a different consumer that is currently offline.

Gather prerequisites

Inventory every replication channel, backup process, and external log reader. Record its owner, last successful position, maximum planned disconnection, and recovery requirements. Confirm the source version and free disk capacity. This procedure discusses self-managed MySQL 8.4; a managed service may impose different controls. Use an account with inspection permissions for the initial review, keeping deletion privileges separate from routine monitoring where practical.

Establish the retention boundary

1. Read SHOW BINARY LOGS and the current binary-log expiration variables. In particular, inspect binlog_expire_logs_seconds and binlog_expire_logs_auto_purge. Do not assume an edited configuration file matches the running server. Record both the configured policy and the actual oldest retained file.
2. Collect the required source position from every consumer, including disconnected ones. For replicas, use their last trustworthy status record and reconnect evidence when possible. For backup recovery, identify the oldest retained base backup that still requires subsequent logs. A backup archive and the source filesystem may have different retention responsibilities.
3. Measure log generation across representative workload periods. Include imports, batch updates, schema operations, and maintenance jobs. Estimate storage needs from observed generation and the required retention window, then allow room for bursts and delayed intervention. These are environment-specific estimates; no universal number of retention days fits every system.
4. Review a proposed purge boundary against the consumer inventory. MySQL protects certain logs being read by connected replicas, but disconnected consumers remain an operational responsibility. Prefer supported server log-management controls over filesystem deletion. Rehearse the review process in a test topology before granting anyone authority to perform a production purge.

Retention diagnosis

Illustrative scenario

A reporting replica is shut down for a maintenance weekend. Another replica remains current, so its status suggests that old logs are unused. The reporting replica still needs earlier history when it returns. The team postpones deletion, confirms archive availability, and updates the maintenance SOP to declare the reader's required position before shutdown. This avoids treating connected consumers as the entire topology.

Verify a policy change

After an approved change, inspect retained files at normal rotation points and verify that all readers continue progressing. Test recovery from a retained backup using its associated archived logs in an isolated environment. Compare actual disk growth with the forecast. If a required file has already disappeared, stop experimenting with position changes and choose a documented archive-recovery or replica rebuild path.

Monitoring and ownership

Track free space, generation rate, oldest available log, oldest required consumer position, and archive failures. Send alerts with the affected consumer owner and estimated intervention deadline. Review retention after topology changes, extended maintenance, or backup-policy changes. Keep deletion decisions auditable by recording which consumers and recovery sets were checked, rather than recording only the final filename.

Explore Clouvenix backup disaster recovery for implementation and operational handover.

Technical reference

• dev.mysql.com documentation
• dev.mysql.com documentation