Google Cloud SQL for MySQL to BigQuery, in real time
Replicate Google Cloud SQL for MySQL into BigQuery in real time. Turn on point-in-time recovery to enable the binary log, authorize our network, and changes stream within seconds.
- Setup time
- About 15 minutes
- Restart needed
- Possibly: enabling point-in-time recovery may restart the instance.
- Binary log retention
- Binary logs are kept for the point-in-time recovery window (default 7 days).
Set up Google Cloud SQL for change data capture
The in-app guide shows each of these with exact clicks and copy-paste commands, then tests the connection. Full reference: MySQL setup for change data capture.
- 1
Turn on point-in-time recovery
In Cloud SQL, the binary log is enabled by turning on point-in-time recovery in the instance's backup settings. Enabling it may restart the instance.
- 2
Check the row image
Cloud SQL logs in ROW format. Make sure the binlog_row_image flag is not set to minimal (the default, full, is correct).
- 3
Make sure the instance has a public IP
Under Connections → Networking, enable a public IP if the instance only has a private one.
- 4
Authorize our network
Add our IP addresses under Authorized networks.
- 5
Connect BigQuery and pick tables
Upload a service-account key, choose the tables, and start. Existing rows are copied first; after that every insert, update and delete arrives within seconds. See BigQuery setup.
What you get in BigQuery
- An up-to-date copy of each table with the same primary key, optionally partitioned by date.
- A change history of every insert, update and delete in a separate dataset, kept as long as you choose.
- New columns added automatically, and per-table freshness you can monitor.
Related guides
- How to connect MySQL to BigQuery: 5 ways
- Check your MySQL binlog settings (free tool)
- MySQL binlog_format and binlog_row_image explained
- The best MySQL to BigQuery replication tools
- Quayen vs Google Cloud Datastream
- Syncing MySQL deletes to BigQuery
Google Cloud SQL questions
Isn't Google Cloud Datastream simpler on Cloud SQL?
Datastream is a good fit if you replicate several database engines inside Google Cloud. See our comparison for pricing and feature differences.
Can I use a private IP?
Yes, through a small SSH bastion VM in the same VPC: the instance keeps only its private IP. See the guide to replicating a private-IP Cloud SQL instance (quayen.com/docs/cloud-sql-private-ip-to-bigquery).
What are Google's own ways to get Cloud SQL data into BigQuery?
Datastream replicates changes continuously. BigQuery federated queries (EXTERNAL_QUERY) read Cloud SQL live without copying, but every query runs on the production instance. You can also export tables to Cloud Storage and load them into BigQuery on a schedule.
How do I connect Google Cloud SQL to BigQuery?
Follow the steps above to turn on the binary log and allow access, create a read-only replication user, then upload a BigQuery service-account key and choose the tables. Existing rows are copied once and every later insert, update and delete is replicated. Most teams finish in about 15 minutes.
How quickly do Google Cloud SQL changes reach BigQuery?
Each change reaches a change-history table in BigQuery within seconds. The up-to-date copy of each table refreshes on a schedule you choose, from every minute to every hour (every 5 minutes by default).
Can I migrate Google Cloud SQL to BigQuery once instead of syncing it?
Yes: export the tables with mysqldump or as CSV and load them with bq load, after converting the schema with the free MySQL to BigQuery schema converter. A one-time copy goes stale as soon as MySQL changes, so use replication if reports need current data.
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.