Binlog Archiving

Configuring Logrotate for MySQL Binary Logs Safely

MySQL binary logs maintain strict sequential integrity for replication streams and point-in-time recovery. Introducing external rotation mechanisms like logrotate into this lifecycle requires surgical precision. Naive rotation configurations routinely trigger ERROR 1236 (HY000): Could not find first log file name in binary log index file, break replica IO threads, and silently corrupt PITR timelines. The operational mandate is absolute: MySQL must own the rotation boundary, while logrotate functions strictly as a post-rotation archival, compression, and cleanup orchestrator. This guide details the exact configuration, forensic validation, and safe recovery paths required to integrate logrotate into a production-grade Automated Binlog Archiving to Object Storage pipeline without compromising data durability.

Visual Overview 

flowchart TD
  A["logrotate: daily, rotate 14"] --> B["sharedscripts postrotate"]
  B --> C["mysql-binlog-archive.sh"]
  C --> D{"Upload + ETag OK?"}
  D -->|"No"| E["Keep local + retry"]
  D -->|"Yes"| F["Safe to purge local"]

The Logrotate/MySQL Conflict and Safe Configuration Boundaries

MySQL tracks binary logs through the binlog.index file and manages active file switching via max_binlog_size or explicit FLUSH BINARY LOGS commands. When logrotate operates independently on active log files, it creates a severe file descriptor race condition where the server continues writing to an inode that has been moved, renamed, or truncated. The copytruncate directive is categorically unsafe for MySQL binary logs. Truncating an active binlog file destroys the event stream, immediately halting replicas with Got fatal error 1236 from master when reading data from binary log and rendering the truncated segment unrecoverable for PITR.

The only safe pattern delegates rotation to MySQL and uses logrotate as a deterministic cleanup trigger. Your /etc/logrotate.d/mysql-binlogs configuration must enforce nocompress, missingok, and notifempty, while relying exclusively on a postrotate script to verify file closure before initiating archival workflows.

/var/lib/mysql/mysql-bin.* {
    daily
    rotate 14
    missingok
    notifempty
    nocompress
    create 0640 mysql mysql
    sharedscripts
    postrotate
        /usr/local/bin/mysql-binlog-archive.sh
    endscript
}

The sharedscripts directive ensures the archival script runs exactly once per rotation cycle, preventing duplicate processing when multiple log files match the glob pattern. The create 0640 mysql mysql directive is a prevention hook that guarantees correct ownership if MySQL recreates a log file during the rotation window. Never use delaycompress or compress in the logrotate stanza itself; compression must occur in the post-rotation pipeline after the file is fully closed and verified.

Postrotate Orchestration and Async Queue Integration

The postrotate script is the critical control plane between the filesystem and your storage layer. It must execute a non-blocking FLUSH BINARY LOGS to force MySQL to close the current active log, verify the file is no longer open by the mysqld process, and then hand off the closed file to an asynchronous processing queue. Modern implementations leverage Python 3.10+ with asyncio and aiofiles to handle I/O without blocking the logrotate daemon.

A production-ready postrotate handler follows this sequence:

  1. Issue FLUSH BINARY LOGS via the MySQL C-API or mysqladmin to trigger a clean file switch.
  2. Poll /proc/<pid>/fd or lsof to confirm mysqld has released the file descriptor.
  3. Calculate a SHA-256 checksum before moving the file to a local staging directory.
  4. Dispatch the staging path to a message broker (Redis Streams, RabbitMQ, or AWS SQS) for background processing.

Scheduling this orchestration requires precise alignment with database workload peaks. Implementing Rotation Scheduling & Cron Automation ensures that logrotate triggers during low-I/O windows, preventing lock contention during high-throughput transactional bursts.

Compression, Encryption & Cloud Sync Pipelines

Once the async worker consumes the queued event, it must handle compression and encryption before cloud synchronization. MySQL 8.0+ generates uncompressed binary logs by default. The worker should apply zstd or gzip with multi-threading (-T0) to maximize throughput. Immediately after compression, apply AES-256-GCM encryption using a KMS-managed key ring. This guarantees compliance with data-at-rest standards while preparing the payload for distributed storage.

The sync pipeline must implement multipart uploads with automatic retry logic. Network partitions or transient cloud API throttling should trigger exponential backoff. Never delete the local binary log until the cloud upload returns a successful ETag validation and the checksum matches the pre-upload hash. Refer to official AWS S3 & GCS Sync Pipelines documentation for best practices on resumable transfers and cross-region replication.

Error Handling, Retry Logic & PITR Validation

Archival pipelines fail. Network timeouts, disk exhaustion, or KMS key rotation errors will occur. Your Python automation must implement idempotent retry logic with dead-letter queue (DLQ) routing. Each retry attempt should log the exact binlog filename, retry count, and failure reason to a centralized observability stack.

For PITR readiness, integrate archival metadata with your Base Backup Integration for PITR strategy. Every archived binlog must be tagged with its corresponding GTID range and base backup snapshot ID. This enables precise timestamp targeting during disaster recovery. If a binlog fails validation or upload, the pipeline must halt local cleanup and alert the DBA team immediately. Silent data loss during archival is unacceptable.

Forensic Validation & Safe Recovery Paths

After deploying the configuration, validate the entire chain before trusting it with production data. Run mysqlbinlog --verify-binlog-checksum against archived files to confirm stream integrity. Monitor replica IO threads with SHOW REPLICA STATUS\G and verify Last_IO_Error remains empty. Use lsof -p $(pgrep -x mysqld) | grep mysql-bin to confirm logrotate never touches active file descriptors.

If a misconfiguration occurs, immediately revert to MySQL-native rotation. Disable the logrotate cron entry, purge the corrupted staging directory, and run PURGE BINARY LOGS TO 'mysql-bin.XXXXXX' only after verifying replica catch-up. Always test recovery procedures in a staging environment that mirrors production I/O characteristics. For teams migrating legacy setups, a Zero-Downtime Archiving Pipeline Migration strategy using dual-write staging and shadow replication guarantees continuous availability during the transition.