Streaming MySQL to BigQuery with the binlog: how CDC works, and the parts that bite

Most teams start syncing MySQL to BigQuery with a nightly dump or a query like SELECT * FROM orders WHERE updated_at > :last_run. It works until someone asks why deleted orders still show up in the dashboard, or why yesterday's numbers changed. This post walks through how log-based change data capture (CDC) from MySQL to BigQuery works end to end, and the details that decide whether it's correct and affordable.

Disclosure: I build Quayen, a managed MySQL to BigQuery CDC service. Everything here applies whether you build this yourself with Debezium or use any managed tool.

Why polling isn't enough

Query-based syncs on an updated_at column have three structural problems:

• Deletes are invisible. A deleted row has no updated_at; it's simply gone, so the query never returns it and BigQuery keeps it forever.
• Not every write touches updated_at. Bulk updates, migrations and some ORMs skip it.
• They load production. Every run scans an index on your busiest tables.

MySQL already records every committed change, including deletes, in its binary log so replicas can follow along. Reading that log is what log-based CDC does.

Step 1: configure the binlog

CDC needs three settings, plus enough retention:

SHOW GLOBAL VARIABLES WHERE Variable_name IN
  ('log_bin', 'binlog_format', 'binlog_row_image', 'binlog_expire_logs_seconds');

• log_bin = ON. On RDS this means automated backups must be on; on Cloud SQL it's point-in-time recovery.
• binlog_format = ROW. STATEMENT logs the SQL text, not the resulting row values, so a CDC reader can't know what changed. MIXED falls back to statements most of the time.
• binlog_row_image = FULL. MINIMAL logs only changed columns, so your BigQuery rows end up with holes.
• Retention of at least 3 days. If the pipeline stops and MySQL purges logs it hasn't read, you get error 1236: Could not find first log file name in binary log index file and have to re-copy everything. On RDS and Aurora, retention is CALL mysql.rds_set_configuration('binlog retention hours', 72);, not binlog_expire_logs_seconds.

The replication user needs SELECT on your schema plus REPLICATION SLAVE and REPLICATION CLIENT on *.*. Nothing else. (If you want to check a server quickly, I made a free binlog checker that reads the output of the query above.)

Step 2: snapshot, then stream from the exact position

Existing rows aren't in the binlog (it's been purged long ago), so a pipeline starts with a snapshot:

1. Open a consistent snapshot (START TRANSACTION WITH CONSISTENT SNAPSHOT) and record the binlog position (file + offset, or GTID set) at that same moment.
2. Copy every row of the selected tables into BigQuery.
3. Start reading the binlog from the recorded position.

Changes that happen during a long copy aren't lost: they're in the binlog after the recorded position and are applied afterwards. The ordering rule below makes it safe if a change touches a row that the snapshot already copied.

Step 3: land changes in an append-only change log

Don't try to UPDATE BigQuery row by row as events arrive. BigQuery is built for appends and big set-based statements, and DML quotas will stop you. Instead, append every event to a change-log table:

column	meaning
_op	c insert, u update, d delete
_seq, _row	binlog position of the event, and the row's index within it
_ingested_at	when it reached BigQuery
the table's columns	the row values after the change (before, for deletes)

Appends via the Storage Write API land within seconds, and the change log is useful on its own: it's a full history of every row ("what was this order's status last Tuesday?").

Two details matter here:

• Only save your read position after BigQuery has accepted the batch. Then a crash replays a few events instead of losing them.
• Replays must be harmless. That's what _seq is for.

Step 4: merge into an up-to-date table

Analysts want a table that looks like MySQL, not an event log. On a schedule, apply the new changes with a MERGE, keeping only the latest change per primary key:

MERGE `analytics.orders` t
USING (
  SELECT * EXCEPT (rn) FROM (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY id ORDER BY _seq DESC, _row DESC) AS rn
    FROM `analytics_changelog.orders`
    WHERE _seq > @last_applied_seq
  ) WHERE rn = 1
) s
ON t.id = s.id
WHEN MATCHED AND s._op = 'd' AND s._seq > t._seq THEN DELETE
WHEN MATCHED AND s._seq > t._seq THEN
  UPDATE SET status = s.status, total = s.total, _seq = s._seq
WHEN NOT MATCHED AND s._op != 'd' THEN
  INSERT (id, status, total, _seq) VALUES (s.id, s.status, s.total, s._seq);

Ordering by binlog position (not by arrival time) means applying the same batch twice, or overlapping batches, can't produce a wrong row. The s._seq > t._seq guard protects rows the snapshot copied after a change was already applied.

Step 5: handle schema changes and the ugly types

• New columns: when MySQL gains a column, add it to both BigQuery tables before applying events that contain it.
• Type mapping: BIGINT UNSIGNED doesn't fit INT64 (use NUMERIC), wide DECIMALs need BIGNUMERIC, TIME can exceed 24 hours so store it as STRING, and TINYINT(1) is MySQL's boolean. (Free converter that applies these rules to a CREATE TABLE.)
• Primary keys are mandatory. Without one there's no way to match an update or delete to a row.

The part that surprises people: BigQuery cost

With on-demand pricing, a MERGE that updates or deletes rows is billed for the whole target table it scans (or the partitions it touches). Refresh a 200 GiB table every 5 minutes and that's hundreds of TiB a month. The fixes: refresh less often, partition by a column that's set once (like created_at), use insert-only merges when a batch has no updates or deletes, and skip tables with no new changes. I wrote up the numbers in a separate post.

Build or buy

• Debezium + Kafka + a BigQuery sink: flexible and open source, but you operate Kafka Connect, schema history, and write the merge logic above yourself.
• Google Cloud Datastream: serverless and inside your GCP project, priced per GiB processed.
• Fivetran, Airbyte, Hevo, Estuary: broad connector platforms; check how often they actually merge into BigQuery and how they meter volume.
• Quayen: does only MySQL to BigQuery, with the snapshot, change log, ordered merges and cost controls above built in.

Whichever you choose, check these four things: deletes are removed (not just flagged), changes are ordered by log position, the read position is saved only after BigQuery accepts a batch, and you know what each refresh costs.