GoKawiilMost MySQL-to-warehouse pipelines run on the same pattern: a scheduled job selects rows, compares them to what was there before, and writes the difference. It works, until it doesn't.
What periodic syncs miss
A SELECT -based sync only sees what exists right now. It has no way to know a row existed and was deleted between two runs, no way to see intermediate states of a row that changed more than once, and it puts real load on your production database every time it scans a large table just to find a handful of changed rows.
What CDC does differently
Change Data Capture reads directly from MySQL's binary log (binlog), the same mechanism MySQL uses internally for replication. Every INSERT, UPDATE, and DELETE is captured as it's written to the log, in order, with the complete row state. Nothing is inferred by comparison. Nothing depends on when a batch job happens to run.
This isn't about speed. A CDC pipeline that runs once an hour is still fundamentally more reliable than a batch sync that runs once a minute, because it captures everything that happened, not just the latest snapshot.
What has to be true on the MySQL side
CDC via binlog has real prerequisites:
Binary logging in ROW format, with FULL row images. If binlog_row_image isn't set to FULL , DELETE and UPDATE events won't carry the complete before/after state, only what's strictly needed to apply the change. That's often not enough for a downstream consumer that needs the full row. binlog_row_value_options must not be PARTIAL_JSON . If it is, updates to JSON columns only log what changed inside the JSON value, not the full value. Silent, and easy to miss until you compare against the source. The replication user needs REPLICATION SLAVE and REPLICATION CLIENT privileges to read and monitor the binlog, plus SELECT , RELOAD , and SHOW DATABASES for the initial snapshot. A unique server-id for every replication client attached to the database, including your CDC connection. Collisions with existing replicas cause silent failures that are painful to debug. Binlog retention long enough to cover downtime. MySQL purges binlog files after a configurable window (30 days by default). If your CDC connection is offline longer than that, it won't be able to resume from where it left off. It'll need a fresh initial snapshot.
Setting it up
... continue reading