Implementing AES-256 Encryption for Archived MySQL Binary Logs #

Binary logs are sequential recovery artifacts, not disposable storage objects. A single unauthenticated cipher choice, a reused nonce, or a mis-ordered compress/encrypt step turns an archive that looks protected into one that either leaks every row change in the bucket or silently fails to decrypt during a Point-in-Time Recovery (PITR) at 03:00. This page resolves one narrow scenario: taking a closed binlog segment and producing an at-rest AES-256 artifact that is authenticated, deterministically decryptable, and bound to its recovery coordinates — without inflating storage cost or breaking PITR precision. It targets MySQL 8.0.22+ sources and a Python 3.10+ transform service.

Visual Overview #

Context & Prerequisites #

This procedure is the encryption stage of the broader Compression & Encryption Workflows transform, and it assumes the upstream contract that page defines: it is handed a closed, fully-flushed, read-only segment whose Rotate event and checksum are already stable, and compression runs before encryption. It does not own segment discovery or advisory locking — that belongs to Rotation Scheduling & Cron Automation. It also does not own the transfer to the bucket; multipart upload and retry are covered in Building a Python Script to Sync Binlogs to S3 with boto3. Every artifact must remain replay-safe, so the source stream must run binlog_format = ROW with a gap-free GTID Tracking & Enforcement configuration — the ciphertext is only useful if the GTID set it maps to is contiguous.

Requirements before you start:

• MySQL 8.0.22+ source, binlog_format = ROW, gtid_mode = ON, enforce_gtid_consistency = ON.
• Python 3.10+ with the cryptography package for AES-256-GCM (the correct AEAD primitive) and tenacity for retry policy.
• A managed key service (AWS KMS, GCP Cloud KMS, or HashiCorp Vault) that can issue and unwrap data encryption keys. Long-lived symmetric keys pasted into config are out of scope — they defeat rotation and forensics.

Step-by-Step Implementation #

Each step below is annotated with why it matters for recovery. The through-line: an encrypted archive is worthless unless it decrypts deterministically and lands on the exact GTID coordinate PITR asks for.

1. Decouple encryption from the MySQL host. Cryptographic work on the primary competes with the server’s I/O thread and inflates binlog_cache_size pressure when sync_binlog = 1 is enforced. Route each rotated segment onto an asynchronous queue with a correlation ID and process it off-host — the same decoupling pattern used in Using Celery for Async Binlog Upload Processing. PITR relevance: keeping the source unblocked prevents rotation stalls that would leave gaps in the recoverable range.

2. Compress before you encrypt. AES output is indistinguishable from random, so it will not compress — encrypting a plaintext binlog first flattens entropy and destroys the 60–80% saving that ROW-format row images normally yield, inflating storage by several hundred percent. Run zstd (or LZ4) to completion and only then hand the compressed bytes to the cipher. PITR relevance: smaller artifacts download and stream through mysqlbinlog faster, directly shrinking your recovery time objective (RTO).

3. Fetch a per-batch data encryption key via envelope encryption. Ask the key service for a fresh data encryption key (DEK) per batch, use its plaintext form in memory only, and persist the KMS-wrapped ciphertext of that DEK alongside the archive. This removes long-lived key rotation from the hot path and lets you version keys without breaking historical recovery chains. PITR relevance: a recovery months later unwraps the stored DEK with the master key, so no rotation event can orphan an old segment.

4. Encrypt each segment with AES-256-GCM using a fresh nonce. GCM is authenticated (AEAD): the tag it produces detects any bit-rot or tampering at decrypt time, which CBC cannot do on its own. Generate a fresh 96-bit random nonce for every artifact and never reuse a (key, nonce) pair.

import os
from cryptography.hazmat.primitives.ciphers.aead import AESGCM


def encrypt_binlog(plaintext: bytes, key: bytes, segment: str) -> tuple[bytes, bytes]:
    """Encrypt one compressed binlog segment with AES-256-GCM.

    Returns (nonce, ciphertext_with_tag). ``key`` must be 32 bytes for AES-256.
    The segment name is bound in as associated data (AAD) so a copied or
    renamed object fails authentication instead of decrypting silently.
    """
    if len(key) != 32:
        raise ValueError("AES-256 requires a 32-byte key")
    nonce = os.urandom(12)  # 96-bit nonce, the GCM-recommended width
    aesgcm = AESGCM(key)
    ciphertext = aesgcm.encrypt(nonce, plaintext, segment.encode())
    return nonce, ciphertext


def decrypt_binlog(nonce: bytes, ciphertext: bytes, key: bytes, segment: str) -> bytes:
    """Decrypt and authenticate an AES-256-GCM ciphertext for ``segment``."""
    return AESGCM(key).decrypt(nonce, ciphertext, segment.encode())

PITR relevance: binding the segment name as associated data guarantees the object you fetch during recovery is the one the manifest indexed — a shuffled or truncated upload is rejected before it can corrupt a replay.

5. Verify by trial-decrypt before upload. Promoting an unverified payload risks silent corruption you only discover during an emergency. Decrypt to a discard sink and confirm the GCM tag validates; only an authenticated artifact enters the upload queue.

from cryptography.exceptions import InvalidTag


def verify_encryption(nonce: bytes, ciphertext: bytes, key: bytes, segment: str) -> bool:
    """Return True if the ciphertext decrypts and authenticates for ``segment``."""
    try:
        decrypt_binlog(nonce, ciphertext, key, segment)
        return True
    except InvalidTag:
        return False

PITR relevance: a green tag here is your guarantee that the byte stream will replay months later — the check is non-negotiable for compliance and recovery reliability.

6. Write a recovery manifest, then upload. Record correlation ID, first/last GTID, sequence number, nonce, wrapped DEK reference, uncompressed checksum, and exact timestamps for each artifact. Hand the verified file to the object-storage layer, which chunks payloads over ~100 MB into 8–16 MB multipart segments and resumes from the last successful part on failure. Transient API throttling is expected, so back off with jitter — see Handling S3 Throttling During High-Throughput Binlog Archiving. PITR relevance: the manifest is what lets a restore align archived binlogs with the base backup’s GTID set and target an exact timestamp; without it, encrypted segments are unindexable and therefore unrecoverable.

Configuration Reference #

Minimal parameters that govern the encryption stage and its interaction with the source server:

A minimal source-side my.cnf fragment that the encryption schedule depends on:

# MySQL 8.0.22+  —  source primary feeding the archive
[mysqld]
binlog_format             = ROW
gtid_mode                 = ON
enforce_gtid_consistency  = ON
sync_binlog               = 1
binlog_encryption         = ON          # local-tail encryption (8.0.14+), complementary
binlog_expire_logs_seconds = 259200     # 3 days — must exceed the archive pipeline SLA

Verification Checklist #

• Compression (zstd/LZ4) completes and is confirmed BEFORE any AES call runs.
• Every artifact uses a fresh 12-byte os.urandom nonce; no (key, nonce) pair is ever reused.
• The segment filename is bound as AES-GCM associated data and the wrapped DEK is stored beside the object.
• verify_encryption() returns True (GCM tag validates) before the file enters the upload queue.
• The manifest row records correlation ID, first/last GTID, nonce, DEK reference, checksum, and timestamps.
• binlog_expire_logs_seconds on the source exceeds the worst-case encrypt-plus-upload latency.
• A dry-run restore unwraps the DEK, decrypts, decompresses, and replays a sample segment against a base backup with no GTID gap.

Gotchas & Version-Specific Caveats #

• CBC has no authentication. The OpenSSL enc command does not support GCM, so a shell one-liner like openssl enc -aes-256-cbc -pbkdf2 -iter 100000 ... cannot detect corruption on its own. If you are forced onto CBC as a fallback, you must add an HMAC-SHA256 tag over the ciphertext yourself — otherwise silent bit-rot across a multi-year retention window goes undetected until recovery fails. Prefer the cryptography library’s GCM in production.
• Nonce reuse is fatal, not cosmetic. Reusing a (key, nonce) pair under GCM exposes the XOR of the plaintexts and leaks the authentication subkey, letting an attacker forge tags. Always generate the nonce per artifact; never derive it from a counter you might reset.
• Transaction-level compression stacks with file-level. MySQL 8.0.20+ can set binlog_transaction_compression = ON (OFF by default), which compresses payloads inside the event stream. If it is on, your file-level zstd sees already-compressed bytes and yields little further gain — account for it rather than double-compressing blindly.
• 8.0 vs 8.4 keyring components. Server-side binlog_encryption relies on a keyring component (the older keyring plugins are removed in 8.4). Confirm your keyring is component-based before enabling it on 8.4, or the source will refuse to start with encryption on.
• FIPS deployments. Use the cryptography library over pycryptodome where FIPS 140-3 alignment and modern cipher-suite validation are required.
• Secrets never touch the repo. Do not place KMS credentials in plaintext environment variables or commit them; source them from Vault or a cloud secret manager, and scope binlog read access with least privilege as described in Securing Binlog Access with MySQL 8.0 Dynamic Privileges.

Related #

• Compression & Encryption Workflows — the compress-then-encrypt transform this page plugs into.
• Building a Python Script to Sync Binlogs to S3 with boto3 — the multipart upload stage that receives verified artifacts.
• Handling S3 Throttling During High-Throughput Binlog Archiving — retry and backoff for the transfer that follows encryption.

Back to Compression & Encryption Workflows · Part of Automated Binlog Archiving to Object Storage.