How to replicate MySQL to BigQuery in real time

There are five common ways to get MySQL tables into BigQuery. They differ mostly in how fresh the data is, whether deletes come through, and how much you have to run yourself.

The options

• 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.
• Incremental queries on an updated_at column: fresher, but they miss deletes and any update that doesn't touch updated_at, and they put query load on MySQL.
• Federated queries (BigQuery EXTERNAL_QUERY, Cloud SQL only): no copy at all, but every query hits your production database and nothing is stored in BigQuery.
• Self-managed CDC (Debezium, Kafka and a sink): real time and flexible, but you operate several services and the BigQuery merge logic yourself.
• Managed log-based CDC (Google Cloud Datastream, Fivetran, Airbyte, Quayen): reads MySQL's binary log, so changes arrive within seconds, deletes are included and the load on MySQL is close to a replica's.

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. See what change data capture is for the details.

Set it up with Quayen

1. Turn on row-based binary logging and create a read-only replication user (MySQL setup). Each host has a guide: integrations.
2. Create a BigQuery service account (BigQuery setup).
3. Pick the tables. Existing rows are copied once, then changes stream in within seconds and the up-to-date tables refresh on the schedule you choose.

Most teams are done in about 15 minutes. If your database is on a private network, connect through an SSH bastion.