How to connect MySQL to BigQuery: 5 ways, including real time▾

Learn

How to connect MySQL to BigQuery: 5 ways, including real time

Five ways to connect, sync or migrate MySQL to BigQuery, compared on freshness, deletes and load, with real-time replication set up step by step.

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.

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, Hevo, Estuary, Quayen): reads MySQL's binary log, so deletes are included and the load on MySQL is close to a replica's. How quickly changes reach BigQuery depends on the tool and plan.

For a side-by-side of the managed and open-source tools, see the best tools to replicate MySQL to BigQuery.

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.