Binlog Archiving

Building a Python Script to Sync Binlogs to S3 with Boto3

Reliable point-in-time recovery (PITR) and cross-region disaster recovery depend entirely on the integrity of your binary log archive. When binlog synchronization pipelines are implemented as naive cron jobs or unverified file copies, they introduce silent data loss, break recovery chains, and create unpredictable replication lag. This guide provides a production-grade, single-intent operational blueprint for building a Python 3.10+ daemon that streams, compresses, encrypts, and verifies MySQL binary logs to Amazon S3 using boto3. The architecture enforces strict ordering, idempotent state tracking, and observable failure boundaries, aligning with established Automated Binlog Archiving to Object Storage patterns where deterministic replay is non-negotiable.

Visual Overview 

sequenceDiagram
  participant D as Daemon
  participant M as MySQL
  participant S as S3
  D->>M: SHOW BINARY LOGS
  D->>D: compare cursor
  D->>S: upload_fileobj (gzip stream, KMS)
  S-->>D: ETag
  D->>D: verify SHA-256 + update cursor

Prerequisites & MySQL 8.0 Configuration

Before deploying the synchronization agent, the MySQL instance must be hardened for continuous, crash-safe logging. Modern MySQL 8.0 deployments require explicit retention boundaries and deterministic formatting.

  1. Enable Continuous Logging: Ensure log_bin is active and points to a dedicated, high-IOPS volume.
  2. Enforce ROW Format: Set binlog_format=ROW. Statement-based logging introduces non-deterministic replay risks during PITR, while row-level binlogs guarantee exact data reconstruction.
  3. Crash-Safe Flushing: Configure sync_binlog=1. This forces the storage engine to flush binlog events to disk after every transaction commit, eliminating the risk of losing committed transactions during an unexpected host reboot.
  4. Modern Retention Policy: Disable the deprecated expire_logs_days parameter. Replace it with binlog_expire_logs_seconds set to a window that safely exceeds your maximum expected sync latency (typically 86400 to 172800 seconds). Premature local deletion before the Python agent confirms successful upload will permanently break your recovery chain.

The synchronization agent must execute under a dedicated, non-root OS user with read access to /var/lib/mysql and explicit network egress permissions to your S3 VPC endpoint or public gateway. Consult the official MySQL Binary Log documentation for version-specific parameter validation.

Stateful Daemon Architecture & Cursor Management

A stateless cron execution is fundamentally unsafe for binlog archival. Network partitions, transient S3 throttling, or partial uploads can leave your archive in an inconsistent state. Instead, implement a stateful daemon that tracks progress via a local cursor file, typically /var/lib/mysql/.binlog_sync_cursor.

The cursor maintains a JSON structure containing:

  • last_synced_file: The exact filename of the last successfully archived binlog.
  • sha256_checksum: The cryptographic hash of the uploaded artifact.
  • last_timestamp: UTC epoch of the last successful sync cycle.

On each execution cycle, the script queries SHOW BINARY LOGS via a read-only MySQL connection or parses the local mysql-bin.index file. It compares the discovered log sequence against the cursor to identify new candidates. If a candidate matches last_synced_file, the script skips it, guaranteeing idempotency. If a mismatch or corruption is detected during verification, the pipeline halts and emits a critical alert rather than proceeding with a broken chain. This deterministic gating is critical when designing AWS S3 & GCS Sync Pipelines that must survive regional failovers.

Streaming Compression & Zero-Touch I/O

Raw binary logs consume excessive network bandwidth and inflate S3 storage costs. The pipeline must compress data in-flight without materializing temporary files on disk, which introduces I/O contention and increases the attack surface for disk exhaustion.

Python 3.10+ provides robust streaming primitives. Use the built-in zlib incremental compressor with a gzip wrapper (16 + zlib.MAX_WBITS) at compresslevel=6 for a balanced CPU-to-compression ratio, or integrate the zstandard library for higher throughput on modern multi-core hosts. The compression stream should be wrapped in a generator that reads the raw binlog in 64KB chunks, compresses each chunk incrementally, and yields the compressed bytes directly to boto3. See the official Python gzip module reference for stream-based implementation patterns.

import zlib
from pathlib import Path

def stream_compressed_binlog(binlog_path: Path, chunk_size: int = 65536):
    # Incremental gzip-format compressor (16 + MAX_WBITS selects the gzip wrapper).
    compressor = zlib.compressobj(6, zlib.DEFLATED, 16 + zlib.MAX_WBITS)
    with open(binlog_path, "rb") as f_in:
        while chunk := f_in.read(chunk_size):
            compressed = compressor.compress(chunk)
            if compressed:
                yield compressed
    tail = compressor.flush()
    if tail:
        yield tail

This generator pattern ensures memory usage remains bounded and disk I/O stays near zero, preserving database performance during peak transactional loads.

Secure S3 Uploads with Boto3 & KMS

The upload layer must handle multipart transfers gracefully, enforce encryption at rest, and provide explicit retry boundaries. boto3’s upload_fileobj method is optimized for streaming payloads but requires careful TransferConfig tuning to avoid connection timeouts or excessive thread contention.

Configure the transfer manager with multipart_threshold=8*1024*1024 (8 MB) and max_concurrency=4. This aligns with AWS best practices for stable throughput on standard EC2 instance types. Reference the official Boto3 S3 Transfer documentation for advanced tuning parameters.

Encryption must be enforced at the object layer using AWS KMS. Pass the following extra_args to upload_fileobj:

extra_args={
    "ServerSideEncryption": "aws:kms",
    "SSEKMSKeyId": "arn:aws:kms:us-east-1:123456789012:key/your-kms-key-id"
}

Never rely on client-side encryption unless your compliance framework explicitly mandates it. KMS-managed SSE integrates natively with IAM policy boundaries, CloudTrail audit trails, and S3 lifecycle rules. After upload completion, compute the SHA-256 hash of the local compressed stream and verify it against the S3 ETag metadata or a custom object tag. Update the cursor file only after this cryptographic handshake succeeds.

Production Hardening & Operational Integration

A robust binlog sync script is only one component of a resilient recovery architecture. To ensure fast, safe resolution during incidents, integrate the following operational controls:

  • Retry Logic & Exponential Backoff: Wrap the upload call in a retry decorator that catches botocore.exceptions.ClientError for 5xx and Throttling responses. Implement jittered exponential backoff starting at 2 seconds, capping at 60 seconds, to prevent cascading API failures.
  • Scheduling & Rotation: Replace traditional cron with a systemd.timer or a lightweight process supervisor. This provides precise execution tracking, automatic restarts on OOM kills, and structured journal logging. Align the timer interval with your binlog_expire_logs_seconds window to guarantee overlap.
  • PITR & Base Backup Integration: Binlog archives are useless without a known-good starting point. Coordinate the sync agent with your base backup scheduler (e.g., Percona XtraBackup or mysqldump --single-transaction). Store the backup timestamp and corresponding binlog coordinates in a centralized metadata registry to enable precise Timestamp Targeting Strategies during recovery drills.
  • Zero-Downtime Pipeline Migration: When upgrading the sync agent or rotating KMS keys, deploy the new version alongside the existing daemon. Use a shared lock file or distributed coordination service (e.g., Redis, Consul) to ensure only one process writes to the cursor file at a time. Validate the new pipeline against a shadow S3 bucket before cutting over production traffic.
  • Observable Failure Boundaries: Emit structured JSON logs to stdout. Include binlog_filename, bytes_processed, upload_duration_ms, and checksum_status. Route these logs to your observability stack and configure alerts for cursor staleness exceeding 15 minutes.

By enforcing strict ordering, cryptographic verification, and stateful execution, this Python automation eliminates the silent data corruption and latency spikes that plague ad-hoc binlog sync implementations. When paired with disciplined retention policies and automated recovery testing, it forms the backbone of a reliable, auditable PITR workflow.