Ways to connect MySQL to BigQuery

There are four common approaches. They differ in how fresh the data is, whether deletes come through, and how much load they put on MySQL. The full comparison is in how to replicate MySQL to BigQuery in real time, and tools are compared in the best MySQL to BigQuery replication tools.

  • Change data capture (real time)

    Reads the MySQL binary log and applies every insert, update and delete within seconds. Quayen, Google Cloud Datastream and Debezium work this way.

  • Scheduled exports

    Dumps or queries the tables every few hours and reloads them. Simple, but data is hours old and deleted rows are missed.

  • Federated queries (Cloud SQL only)

    BigQuery queries Cloud SQL directly. Nothing is copied, but every report puts load on the production database.

  • One-time migration

    mysqldump or CSV export loaded with bq load, after converting the schema. Fine for a one-off copy.

Questions

How do I connect MySQL to BigQuery?
Turn on row-based binary logging in MySQL and create a read-only replication user. In Google Cloud, create a service account with the BigQuery Data Editor and BigQuery Job User roles. A change data capture tool then copies each table once from a consistent snapshot and streams every later insert, update and delete from the binary log. With Quayen this takes about 15 minutes, and the in-app guide shows the exact steps for your MySQL host.
What is Google's own way to sync MySQL to BigQuery?
Google Cloud's managed change data capture service is Datastream, which reads the MySQL binary log and writes changes into BigQuery. For Cloud SQL, BigQuery federated queries can also read live MySQL data without copying it, but every query then runs against the production database. Quayen is a simpler alternative to Datastream focused only on MySQL to BigQuery, with a separate change-history table per table.
Can I migrate MySQL to BigQuery once instead of syncing it continuously?
Yes. Export the tables with mysqldump or as CSV files and load them with bq load. Convert the table definitions first with the free schema converter on quayen.com or the open-source CLI (npx mysql-to-bigquery-schema). A one-time copy goes stale as soon as MySQL changes, and later updates and deletes are only picked up by change data capture.
Are updates, deletes and schema changes replicated?
Yes. Quayen applies inserts, updates and deletes in commit order, so each BigQuery table matches MySQL row for row, and keeps every change in a separate change-history table. New MySQL columns appear in BigQuery automatically.
Which MySQL hosts and versions are supported?
Amazon RDS for MySQL, Amazon Aurora MySQL, Google Cloud SQL for MySQL, Azure Database for MySQL Flexible Server and self-hosted MySQL 8. Databases without a public endpoint can connect through an SSH bastion.

Your MySQL data in BigQuery today

Free during early access. Set it up yourself in about 15 minutes, or we'll do it with you on a call.