Настройка синхронизации MySQL CDC в BigQuery: как обеспечить полную целостность данных

MySQL CDC to BigQuery: A Step-by-Step Setup Guide MySQL CDC syncs miss deletes and intermediate updates. Learn how binlog-based Change Data Capture works, what MySQL settings it requires, and how to land it reliably in BigQuery. Most 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 toFULL , 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 bePARTIAL_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 andREPLICATION CLIENT privileges to read and monitor the binlog, plusSELECT ,RELOAD , andSHOW 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 GRANT SELECT, RELOAD, SHOW DATABASES, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'your_user';FLUSH PRIVILEGES; Then confirm your binlog configuration: SHOW VARIABLES LIKE 'log_bin';SHOW VARIABLES LIKE 'binlog_format';SHOW VARIABLES LIKE 'binlog_row_image'; If any of those aren't set correctly, they go in my.cnf, and MySQL needs a restart to apply them. The full connector documentation, including every prerequisite and troubleshooting step, is at docs.erathos.com/connectors/databases/mysql#cdc-setup. This is where a managed platform earns its keep. Erathos and similar tools handle the snapshot mode selection (full initial snapshot vs. binlog-only), the server-id assignment and collision avoidance, and the recovery logic when a binlog gets purged before the connection catches up, so the person running the pipeline doesn't have to rebuild that logic by hand every time a new source gets connected. Landing it in BigQuery Once CDC is capturing changes correctly, the destination side is comparatively simple: each change event maps to a row operation in your BigQuery tables. The part worth getting right isn't the load into BigQuery, it's making sure what arrives there is complete. A pipeline that lands incomplete data on time is worse than one that's occasionally a few minutes behind but never wrong. If your team is still running full-table batch syncs against production MySQL, the question worth asking isn't "how do we make this faster." It's "what are we currently unable to see." If you want to try this in practice, you can create an Erathos account and connect your MySQL source with CDC enabled in a few minutes.