BTS — Binlog Transfer System Quality Improvement (CDC)

# BTS — Binlog Transfer System Quality Improvement (CDC)

AI Summary

Purpose:

• Capture durable knowledge about the batch-oriented binlog CDC system that

delivers database changes from distribution DBs to customer on-premise DBs, plus later event-level CDC design and PoC work.

Key points:

• Production delivery is intentionally batch-oriented: distribution DBs emit

ROW-format binlog files, a Python/FastAPI download server serves raw files whose header is XOR-scrambled, and the customer-side Go Updater parses and applies their change events.

• The production Updater uses a local fork of go-mysql-org/go-mysql for offline

ParseFile, klauspost/compress/zstd for MySQL 8.4 compressed Transaction_payload, and go-sql-driver for parameterized SQL apply.

• File delivery remains the batch/recovery boundary in the verified production

path. Event-level offset/checkpoint, retry queue, and DLQ belong to later design or PoC work and must not be presented as confirmed production behavior.

• 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.

• Confluence confirms a hands-on Maxwell FileSink PoC that emitted INSERT, UPDATE,

DELETE, and DDL JSON events. It also confirms that the generic real-time CDC proposal was later deprecated; Maxwell and Kafka must not be described as the production path.

• 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.

• A separate custom Python Collector design/PoC uses pymysqlreplication and

zstandard, but it is not present in the verified production repository and must not be used as production resume evidence until its repository and rollout are confirmed.

• User-confirmed on 2026-07-16: Hyunwook directly designed and built the CDC

system and completed deployment-scenario review. The technical-support team, not Hyunwook, deploys and operates it in approximately ten customer environments. Hyunwook receives operating issues, reproduces them, improves the implementation, and provides technical support.

• Batch delivery is an intentional product and operations decision. Customer

databases receive binlog changes in batches, so real-time streaming was not the required delivery model.

• The verified operating description records approximately hourly binlog-file

batches, with files split when they exceed roughly 5 MB.

• Retry safety claims must be scoped to DML events successfully converted to

idempotent UPSERTs. The public v4 description records truncation error 110 as skip-and-advance, so end-to-end “no omissions” must not be claimed without a reconciliation or reprocessing path.

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]]
• ../../sources/career/2026-07-16-confluence-cdc-tool-research.md

Open Questions

• The repository/location and rollout state of the separate Python Collector

design/PoC remain unknown. Production evidence should use the verified Go Updater path instead.

• 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.

• How truncation error 110 is reconciled or reprocessed after skip-and-advance.

Until confirmed, describe it as a known consistency boundary rather than as lossless processing.

Details

Confirmed production state and ownership boundary (2026-07-16)

• Hyunwook directly designed and implemented the CDC system.
• Hyunwook completed the deployment-scenario review and handed deployment and

day-to-day operation to the technical-support team.

• The technical-support team deploys and operates it in approximately ten customer

environments.

• Hyunwook receives issues found in operation, reproduces them, improves the code,

and supports the technical-support team.

• Do not describe Hyunwook as personally deploying to or operating the customer

environments.

• Batch delivery is deliberate because the customer delivery path distributes

binlog changes in batches. Do not describe this as a real-time Kafka/Flink streaming platform.

• Verified production boundary from the repository review recorded in W28:

raw binlog files with an XOR-scrambled header are served by the Python/FastAPI download server; the customer-side Go Updater performs offline row-event parsing, ZSTD Transaction_payload decompression, and parameterized SQL apply.

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.

Confluence evidence adds two important boundaries:

• The comparison used concrete criteria: multi-instance operation, transport

flexibility, HA/scale, supported databases, and architectural complexity.

• The Maxwell FileSink PoC verified JSON output for INSERT, UPDATE, DELETE, and

DDL, but identified no official MySQL sink. The later real-time proposal was marked deprecated, so these PoC benefits are not production claims.

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 (design/PoC, not verified production)

• 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 event-level redesign (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