MySQL CDC to BigQuery: what periodic syncs miss, and how binlog avoids it

Most MySQL-to-warehouse pipelines tally connected the aforesaid pattern: a scheduled occupation selects rows, compares them to what was location before, and writes the difference. It works, until it doesn't.

What periodic syncs miss

A SELECT-based sync only sees what exists correct now. It has nary measurement to cognize a statement existed and was deleted betwixt 2 runs, nary measurement to spot intermediate states of a statement that changed much than once, and it puts existent load connected your accumulation database each clip it scans a ample array conscionable to find a fistful of changed rows.

What CDC does differently

Change Data Capture sounds straight from MySQL's binary log (binlog), the aforesaid system MySQL uses internally for replication. Every INSERT, UPDATE, and DELETE is captured arsenic it's written to the log, successful order, pinch the complete statement state. Nothing is inferred by comparison. Nothing depends connected erstwhile a batch occupation happens to run.

This isn't astir speed. A CDC pipeline that runs erstwhile an hr is still fundamentally much reliable than a batch sync that runs erstwhile a minute, because it captures everything that happened, not conscionable the latest snapshot.

What has to beryllium existent connected the MySQL side

CDC via binlog has existent prerequisites:

1. Binary logging successful ROW format, pinch FULL statement images. If binlog_row_image isn't group to FULL, DELETE and UPDATE events won't transportation the complete before/after state, only what's strictly needed to use the change. That's often not capable for a downstream user that needs the afloat row.
2. binlog_row_value_options must not be PARTIAL_JSON. If it is, updates to JSON columns only log what changed wrong the JSON value, not the afloat value. Silent, and easy to miss until you comparison against the source.
3. The replication personification needs REPLICATION SLAVE and REPLICATION CLIENT privileges to publication and show the binlog, positive SELECT, RELOAD, and SHOW DATABASES for the first snapshot.
4. A unique server-id for each replication customer attached to the database, including your CDC connection. Collisions pinch existing replicas origin silent failures that are achy to debug.
5. Binlog retention agelong capable to screen downtime. MySQL purges binlog files aft a configurable model (30 days by default). If your CDC relationship is offline longer than that, it won't beryllium capable to resume from wherever it near off. It'll request a caller first snapshot.

Setting it up

GRANT SELECT, RELOAD, SHOW DATABASES, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'your_user';
FLUSH PRIVILEGES;

Then corroborate your binlog configuration:

SHOW VARIABLES LIKE 'log_bin';
SHOW VARIABLES LIKE 'binlog_format';
SHOW VARIABLES LIKE 'binlog_row_image';

If immoderate of those aren't group correctly, they spell successful my.cnf, and MySQL needs a restart to use them.

The afloat connector documentation, including each prerequisite and troubleshooting step, is astatine docs.erathos.com/connectors/databases/mysql#cdc-setup.

This is wherever a managed level earns its keep. Erathos and akin devices grip the snapshot mode action (full first snapshot vs. binlog-only), the server-id duty and collision avoidance, and the betterment logic erstwhile a binlog gets purged earlier the relationship catches up, truthful the personification moving the pipeline doesn't person to rebuild that logic by manus each clip a caller root gets connected.

Landing it successful BigQuery

Once CDC is capturing changes correctly, the destination broadside is comparatively simple: each alteration arena maps to a statement cognition successful your BigQuery tables. The portion worthy getting correct isn't the load into BigQuery, it's making judge what arrives location is complete. A pipeline that lands incomplete information connected clip is worse than 1 that's occasionally a fewer minutes down but ne'er wrong.

If your squad is still moving full-table batch syncs against accumulation MySQL, the mobility worthy asking isn't "how do we make this faster." It's "what are we presently incapable to see."

If you want to effort this successful practice, you can create an Erathos account and link your MySQL root pinch CDC enabled successful a fewer minutes.