Compression & Encryption Workflows for MySQL Binary Log Archiving and PITR Automation #

A binary log archive is only useful if every byte that entered it can be replayed, verified, and decrypted years later without ambiguity. Uncompressed, plaintext binlogs inflate object-storage egress and retention costs, expose the full plaintext of every row change to anyone who can read the bucket, and stretch Point-in-Time Recovery (PITR) windows because large payloads take longer to download and stream through mysqlbinlog. This page defines the transform stage that sits between the MySQL datadir and the archival sink within the broader Automated Binlog Archiving to Object Storage pipeline: a single, ordered compress-then-encrypt workflow that emits authenticated, checksummed, GTID-tagged artifacts. Naive approaches fail in two predictable ways — they encrypt before compressing (ciphertext is high-entropy and will not compress, so the storage saving evaporates), or they reach for a non-authenticated cipher like AES-CBC that cannot detect silent bit-rot across a multi-year retention cycle. Getting the order and the cipher mode right is the difference between an archive you can trust and one you merely hope is intact.

Visual Overview #

Core Concept & Prerequisites #

The pipeline transforms a completed binary log segment into an archival artifact through two ordered stages that must never be reordered. Compression first, because ROW-format binlogs contain highly repetitive row images and compress by 60–80%; encryption second, because the output of zstd is still structured enough to compress but the output of AES is not. Reversing the order costs you the entire storage saving. Only closed segments are eligible — the active tail is still being written and its terminating Rotate event, final size, and checksum are not yet stable. Segment discovery, advisory locking (flock), and the guarantee that a file is fully flushed before it enters the transform queue are owned upstream by Rotation Scheduling & Cron Automation; this stage assumes it is handed a stable, read-only path.

Two distinct encryption planes exist and should not be confused. MySQL’s server-side binlog_encryption (available from 8.0.14) encrypts binlog files on the local datadir using a keyring component, which protects the tail before it is archived but does not travel with the file to object storage. The workflow on this page adds client-side, at-rest encryption so that plaintext row images never exist in the bucket even momentarily — the two planes are complementary, not redundant. The deep key-management patterns (envelope encryption, KMS data keys, FIPS 140-3 modules, rotation cadence) live in the companion page Implementing AES-256 Encryption for Archived Binlogs; here we cover the transform mechanics.

Prerequisites:

• MySQL 8.0.22+ on the source primary, with binlog_format = ROW, gtid_mode = ON, and enforce_gtid_consistency = ON — the archive is only deterministic if the underlying stream is. The trade-offs behind that format choice are covered in ROW vs STATEMENT vs MIXED Formats, and gap-free coordinates depend on GTID Tracking & Enforcement.
• Python 3.10+ for the transform service (match statements, slots=True dataclasses, the walrus operator in streaming loops).
• cryptography for AES-256-GCM (the correct AEAD primitive), tenacity for declarative retry policy, and the system zstd binary invoked via subprocess.

Production-Grade Python Implementation #

The transform service is a stateful, idempotent module. It refuses to re-process an artifact that already exists, binds each ciphertext to its segment name via GCM associated data (AAD) so a copied-or-renamed object fails authentication, streams the SHA-256 so it never loads a 256 MiB segment fully into memory twice, and writes its metadata sidecar atomically. Transient failures (an I/O hiccup, a zstd signal) are retried with exponential backoff through tenacity; deterministic failures (a bad key length, a corrupt payload) are raised immediately rather than retried into a livelock.

#!/usr/bin/env python3
"""Deterministic compress-then-encrypt transform for MySQL binlog segments.
Targets: MySQL 8.0.22+, Python 3.10+, POSIX. Requires: cryptography, tenacity, zstd."""
from __future__ import annotations

import hashlib
import json
import logging
import os
import subprocess
from dataclasses import asdict, dataclass
from pathlib import Path
from time import gmtime, strftime

from cryptography.exceptions import InvalidTag
from cryptography.hazmat.primitives.ciphers.aead import AESGCM
from tenacity import (
    retry,
    retry_if_exception_type,
    stop_after_attempt,
    wait_exponential,
)

logger = logging.getLogger("binlog.transform")
_CHUNK = 1 << 20  # 1 MiB streaming window


class TransientTransformError(RuntimeError):
    """Retryable: I/O hiccup, subprocess signal, partial write."""


@dataclass(slots=True, frozen=True)
class TransformConfig:
    zstd_level: int = 19          # level 1-3 is CPU-cheap; 19 favours ratio on NVMe hosts
    nonce_bytes: int = 12         # 96-bit nonce is the GCM standard (NIST SP 800-38D)
    pipeline_version: str = "2.1.0"


@dataclass(slots=True)
class ArtifactManifest:
    original_file: str
    archived_file: str
    gtid_range: str
    compression: str
    encryption: str
    nonce_hex: str
    sha256: str
    pipeline_version: str
    processed_at: str


def sha256_of(path: Path) -> str:
    """Stream the digest so large segments never sit fully in memory."""
    h = hashlib.sha256()
    with path.open("rb") as fh:
        while chunk := fh.read(_CHUNK):
            h.update(chunk)
    return h.hexdigest()


@retry(
    retry=retry_if_exception_type(TransientTransformError),
    stop=stop_after_attempt(3),
    wait=wait_exponential(multiplier=1, min=2, max=30),
    reraise=True,
)
def compress_zstd(src: Path, dst: Path, level: int) -> None:
    """Compress FIRST — high-entropy ciphertext would not compress."""
    proc = subprocess.run(
        ["zstd", f"-{level}", "-q", "-f", "-o", str(dst), str(src)],
        capture_output=True,
    )
    if proc.returncode != 0:
        raise TransientTransformError(
            f"zstd exit {proc.returncode}: {proc.stderr.decode().strip()}"
        )


def encrypt_gcm(src: Path, dst: Path, key: bytes, nonce_len: int) -> str:
    """AES-256-GCM. AAD binds the ciphertext to the segment name so a
    renamed or swapped object fails authentication on decrypt."""
    if len(key) != 32:
        raise ValueError("AES-256 requires a 32-byte key")
    nonce = os.urandom(nonce_len)
    aad = src.stem.encode()  # e.g. b"mysql-bin.000041"
    ciphertext = AESGCM(key).encrypt(nonce, src.read_bytes(), aad)
    dst.write_bytes(nonce + ciphertext)
    return nonce.hex()


def transform_segment(
    src: Path,
    dest: Path,
    key: bytes,
    gtid_range: str,
    cfg: TransformConfig = TransformConfig(),
    *,
    dry_run: bool = False,
) -> ArtifactManifest | None:
    """Idempotent compress -> encrypt -> checksum -> sidecar."""
    sidecar = dest.with_suffix(".json")
    if dest.exists() and sidecar.exists():
        logger.info("idempotent skip: %s already archived", dest.name)
        return ArtifactManifest(**json.loads(sidecar.read_text()))

    if dry_run:
        logger.info("[dry-run] would transform %s -> %s", src.name, dest.name)
        return None

    tmp_zst = dest.with_suffix(".zst.tmp")
    try:
        compress_zstd(src, tmp_zst, cfg.zstd_level)
        nonce_hex = encrypt_gcm(tmp_zst, dest, key, cfg.nonce_bytes)
    finally:
        tmp_zst.unlink(missing_ok=True)

    manifest = ArtifactManifest(
        original_file=src.name,
        archived_file=dest.name,
        gtid_range=gtid_range,
        compression=f"zstd-{cfg.zstd_level}",
        encryption="AES-256-GCM",
        nonce_hex=nonce_hex,
        sha256=sha256_of(dest),
        pipeline_version=cfg.pipeline_version,
        processed_at=strftime("%Y-%m-%dT%H:%M:%SZ", gmtime()),
    )

    tmp_sidecar = sidecar.with_suffix(".json.tmp")
    tmp_sidecar.write_text(json.dumps(asdict(manifest), indent=2))
    tmp_sidecar.rename(sidecar)  # atomic publish of the sidecar
    logger.info("archived %s sha256=%s gtid=%s",
                dest.name, manifest.sha256, gtid_range)
    return manifest

The gtid_range is passed in by the collector (from SHOW BINARY LOGS and the segment’s own GTID interval) rather than derived here, keeping this module a pure transform. The 32-byte key is injected at runtime from a secrets manager — never a hardcoded string, and never a static file committed to a repo — following the credential-scoping rules in Security & Access Frameworks. Once an artifact and its sidecar exist, they are handed to the transport layer for durable, cross-region upload via AWS S3 & GCS Sync Pipelines.

Configuration Reference #

Two configuration surfaces govern this stage: the MySQL server variables that shape what the transform receives, and the workflow parameters that shape what it produces. The server-side variables below assume MySQL 8.0.22+.

Workflow parameters live in the typed TransformConfig: zstd_level (1–3 is CPU-cheap and usually the right choice under write-heavy load; 19+ buys marginal ratio at steep CPU cost), nonce_bytes fixed at 12 for the standard 96-bit GCM nonce, and pipeline_version stamped into every sidecar so schema evolution is traceable. Retention of the resulting artifacts is governed independently by object-storage lifecycle rules; align those with the horizons described in Binlog Retention Boundaries.

Validation & Verification Gates #

An artifact is not “archived” until it passes every gate below. A transform that writes an object but skips verification records a recovery you do not actually have.

• Checksum reconciliation. The SHA-256 recorded in the sidecar must equal a freshly computed digest of the uploaded object. A mismatch is a non-retryable corruption event, not a transient one.
• Decrypt-to-null round trip. Before confirming an artifact, decrypt it back through AES-256-GCM to /dev/null. GCM’s authentication tag makes this a cryptographic integrity check: if a single bit flipped in storage, or the AAD does not match the segment name, InvalidTag is raised and the artifact is rejected.
• GTID sidecar continuity. The gtid_range in each sidecar must join contiguously to the previous segment’s interval. A gap here is the same recovery-blocking hole that Base Backup Integration for PITR exists to prevent, surfaced early.
• Sidecar/object pairing. Every .zst.enc object must have exactly one .json sidecar and vice versa. Orphans on either side indicate an interrupted transform.

def verify_artifact(dest: Path, key: bytes) -> bool:
    """Gate: checksum + authenticated decrypt round-trip before confirm."""
    manifest = ArtifactManifest(**json.loads(dest.with_suffix(".json").read_text()))
    if sha256_of(dest) != manifest.sha256:
        logger.error("checksum drift on %s", dest.name)
        return False
    blob = dest.read_bytes()
    nonce, ciphertext = blob[:12], blob[12:]
    try:
        AESGCM(key).decrypt(nonce, ciphertext, manifest.original_file.split(".")[0].encode())
    except InvalidTag:
        logger.error("GCM auth failure on %s (tamper or bit-rot)", dest.name)
        return False
    return True

The verification loop feeds directly into the manifest that a recovery orchestrator later maps a target GTID or timestamp against — the precise coordinate selection is covered in Timestamp Targeting Strategies.

Error Handling & Failure Modes #

Failures split cleanly into retryable (retry with backoff) and deterministic (page a human; retrying will never succeed). Mapping each symptom to the correct class is what keeps the pipeline from either livelocking on a corrupt payload or silently dropping a recoverable one.

The full backoff taxonomy, dead-letter routing, and jitter policy for the transport half of these failures is the subject of Error Handling & Retry Logic; this page’s TransientTransformError boundary is deliberately narrow so that only genuinely transient transform faults are retried.

Observability & Alerting #

The transform stage must expose enough telemetry that archiving lag is caught long before it threatens the local retention window. Emit one structured log line per artifact with stable fields — segment, gtid_range, bytes_in, bytes_out, compression_ratio, transform_ms, sha256, pipeline_version — so a log pipeline can compute ratio drift and throughput without parsing prose.

Server-side, watch the source’s own view of binlog activity and encryption state:

-- MySQL 8.0.22+: current binlog write and encryption posture
SELECT VARIABLE_NAME, VARIABLE_VALUE
FROM performance_schema.global_status
WHERE VARIABLE_NAME IN ('Binlog_cache_use', 'Binlog_cache_disk_use');

-- MySQL 8.0.22+: confirm rotated segments and whether each is encrypted at source
SHOW BINARY LOGS;   -- Log_name, File_size, Encrypted

Alert thresholds worth wiring:

• Archiving lag — age of the oldest closed-but-unconfirmed segment. Page well before it approaches binlog_expire_logs_seconds; this single SLI captures every transform and transport stall.
• Compression-ratio collapse — if bytes_out/bytes_in suddenly approaches 1.0, something is encrypting before compressing, or the source switched away from ROW. Alert on ratio, not just failures.
• Verification failure rate — any non-zero InvalidTag/checksum-mismatch count is a paging event, not a dashboard trend.
• Transform latency — transform_ms p99 climbing signals CPU saturation from too high a zstd level under load. Queue-depth and worker-sizing signals that pair with this are covered in Async Processing & Queue Management.

Frequently Asked Questions #

Related #

• Implementing AES-256 Encryption for Archived Binlogs — envelope encryption, KMS data keys, and rotation cadence for the key this workflow consumes.
• Rotation Scheduling & Cron Automation — how closed segments are detected, locked, and handed to this transform stage.
• AWS S3 & GCS Sync Pipelines — durable multipart upload and cross-region replication of the encrypted artifacts.
• Error Handling & Retry Logic — backoff, jitter, and dead-letter routing for the transport-side failures referenced above.

Back to Automated Binlog Archiving to Object Storage.