There are five common ways to connect MySQL to BigQuery, whether you want a one-time migration or a copy that stays in sync. They differ mostly in how fresh the data is, whether deletes come through, and how much you have to run yourself.
1. Scheduled exports
A nightly dump or SELECT ... INTO OUTFILE, then a BigQuery load job. Simple and cheap, but data is up to a day old, every run re-reads whole tables, and deletes only show up if you reload everything.
2. Incremental queries on updated_at
Fresher, but they miss deletes and any update that doesn't touch updated_at, and they put query load on MySQL.
3. Federated queries (Cloud SQL only)
BigQuery's EXTERNAL_QUERY reads Cloud SQL live with no copy at all, but every query hits your production database and nothing is stored in BigQuery.
4. Self-managed CDC
Debezium, Kafka and a BigQuery sink. Real time and flexible, but you operate several services and the merge logic for updates and deletes yourself.
5. Managed log-based CDC
Google Cloud Datastream, Fivetran, Airbyte, Hevo, Estuary, Quayen and others read MySQL's binary log, so deletes are included and the load on MySQL is close to a replica's. How quickly changes become queryable depends on the tool and plan.
Why log-based CDC is the usual answer
MySQL already records every insert, update and delete in its binary log for replicas. Reading that log captures changes exactly once and in order, including deletes, without querying your tables. You need:
SHOW VARIABLES WHERE Variable_name IN ('log_bin','binlog_format','binlog_row_image');
-- want: log_bin=ON, binlog_format=ROW, binlog_row_image=FULL
and a user with SELECT, REPLICATION SLAVE and REPLICATION CLIENT.
Which to pick
- One-off migration: export +
bq load. - Dashboards that can be a day old: scheduled exports.
- Anything that must match the app (status, deletes, cancellations): log-based CDC.
Disclosure: I work on Quayen, a managed MySQL-to-BigQuery CDC service. The full guide, with per-host setup steps, is on our site.
Top comments (0)