BTS — Binlog Transfer System Quality Improvement (CDC)

# BTS — Binlog Transfer System Quality Improvement (CDC)

AI Summary

Purpose:

• Capture durable knowledge about improving BTS (Binlog Transfer System), the

mechanism that ships database changes from the company's master DB to customer on-premise DBs, and the move from file-based Binlog Shipping toward event-level Change Data Capture (CDC).

Key points:

• Old BTS = Binlog Shipping: master cuts binlog files (every ~1 hour, split when

data exceeds ~5MB), serves them over HTTP/FTP, and each customer Updater downloads and imports whole binlog files.

• Core problem: failures are All-or-Nothing at the file level. A single bad

event forces re-download/re-import of the entire file; partial-failure position is hard to track; per-customer batch times differ; selective re-processing is impossible.

• CDC moves to event-level (row-level) control with per-event offset/checkpoint,

enabling selective retry and structured (JSON) output.

• Tool survey concluded Maxwell's Daemon was the best fit (lightweight, single

process, direct JSON file output, no Kafka needed) over Debezium+Kafka (more scalable but heavier), Canal (weak file output), and a raw mysqlbinlog parser.

• DECISIVE PIVOT: MySQL 8.4 binlog_transaction_compression (ZSTD-compressed

Transaction_payload events) is NOT supported by Maxwell. This blocked Maxwell, so the team built a custom Python CDC Collector instead.

• Custom Collector uses pymysqlreplication + zstandard for client-side ZSTD

decompression, handles MySQL 8.4's removal of SHOW MASTER STATUS (falls back to SHOW BINARY LOG STATUS), supports resumable state, and emits JSONL with a per-binlog-file counter reset for full sequence integrity.

Relevant when:

• Working on data sync to customer DBs, CDC, binlog parsing, Maxwell/Debezium

selection, MySQL 8.4 compatibility, or the customer Updater.

• Cross-referencing Distribution DB instance separation (BTS runs against it).

Do not read full document unless:

• You need exact JSONL field layout, fallback logic, or the customer Updater

retry/checkpoint design.

Linked documents:

• [[distribution-db.md]]
• [[infra-db-monitoring.md]] (monitors binlog shipping)
• [[index.md]]

Open Questions

• Current rollout phase of the custom Collector (PoC vs pilot vs production) is

not stated as fully shipped. Needs confirmation.

• Whether the customer Updater v2 (event-level import + retry queue + DLQ) is

implemented or still proposed. Needs confirmation.

• HA design for the Collector (Active-Standby) appears proposed, not confirmed

deployed.

Details

Problem

Binlog Shipping treats a binlog file as the unit of work. One file holds many events, so a single failing event blocks the whole file and forces a full re-download/re-import. There is no event-level checkpoint, so recovery position is unclear, and per-customer batch schedules add operational complexity.

CDC evaluation

CDC parses the binlog into structured per-row change events (insert/update/ delete) with metadata (schema, binlog position, timestamp), allowing event-level retry and offset-based checkpoints.

Tool comparison outcome:

• Maxwell's Daemon — initially recommended: single process, no Kafka, direct

JSON file output, low memory, simple ops. Best match for a file-based, pull-style delivery model rather than real-time streaming.

• Debezium + Kafka — most scalable / buffered / multi-target, but requires Kafka

infra and higher operational complexity.

• Canal (Alibaba) — weak file output (MQ/DB targets), docs mostly Chinese →

rejected.

• Custom mysqlbinlog parser — full control but high build/maintain cost →

rejected initially.

Decisive constraint (MySQL 8.4)

binlog_transaction_compression wraps events in ZSTD-compressed Transaction_payload events. Maxwell does not support this → Maxwell rejected. The team pivoted to a custom Python CDC Collector.

Custom Python CDC Collector

• Library: pymysqlreplication (binlog stream) + zstandard (ZSTD decompress).
• Client-side decompression: decompress on the Collector to reduce DB load,

then recursively parse the internal WRITE/UPDATE/DELETE row events.

• MySQL 8.4 compatibility: SHOW MASTER STATUS was removed; fall back to

SHOW BINARY LOG STATUS. DB connection + position lookup is separated from stream init to bypass library internals.

• Resumable state (state.json or Redis): stores binlog_file,

binlog_position, in-transaction offset, and total_row_count. On restart, open the stream at saved (file, pos) and skip offset events to resume exactly.

• Counter / rotation logic (reset per binlog file): count = cumulative row

number within the current binlog file (reset to 1 on file rotation); offset = event order within a transaction block (reset to 0 per transaction); file_sequence = output filename sequence (reset to 0 on file rotation). On MySQL rotation, flush+close the current output file and reset.

• Output: JSONL (line-delimited JSON), count first. Example fields:

count, offset, type (upsert/delete), table, schema, ts, rows.

• Performance: batch buffering (e.g. 1000 rows) before disk write.
• Packaging: Docker (docker compose up -d --build).

Customer-side design (proposed)

• Pull model: client requests file list / metadata, downloads files, applies

with Upsert (INSERT → REPLACE/ON DUPLICATE to fix INSERT-conflict issues).

• Event-level import with a retry queue; persistent failures go to a Dead Letter

Queue. A cdc_checkpoint table tracks source_file/source_offset with a UNIQUE key to prevent duplicate import and to resume from last offset.

Why this matters (durable lesson)

CDC tool selection must verify source-DB feature compatibility first: MySQL 8.4 ZSTD binlog compression silently breaks Maxwell. A thin custom collector on pymysqlreplication + zstandard is a viable fallback when off-the-shelf CDC tools lag the DB version.