Amazon Aurora MySQL to BigQuery, in real time
Stream Amazon Aurora MySQL into BigQuery with change data capture. Enable the binary log in a cluster parameter group, reboot the writer, and replicate every change within seconds.
- Setup time
- About 15 minutes
- Restart needed
- Yes: the writer instance must be rebooted once.
- Binary log retention
- CALL mysql.rds_set_configuration('binlog retention hours', 72);
Set up Amazon Aurora 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
Create a DB cluster parameter group
Aurora's binary log is configured at the cluster level. Create a cluster parameter group for your Aurora MySQL version.
- 2
Set the binary log format
Set binlog_format = ROW and binlog_row_image = full in the cluster parameter group.
- 3
Attach it and reboot the writer
Attach the group to the cluster, then reboot the writer instance. The cluster is briefly unavailable during the reboot.
- 4
Keep binary logs for 3 days
Run CALL mysql.rds_set_configuration('binlog retention hours', 72); on the writer as the admin user.
- 5
Allow network access
Add an inbound rule for port 3306 from our IP addresses to the cluster's security group, and connect to the writer endpoint.
- 6
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 AWS DMS
- Syncing MySQL deletes to BigQuery
Amazon Aurora questions
Should I connect to the writer or a reader endpoint?
The writer (cluster) endpoint. The binary log is written by the writer instance.
Does enabling the binary log affect Aurora performance?
There is a small write overhead, as with any binlog-based replication.
How do I connect Amazon Aurora 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 Amazon Aurora 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 Amazon Aurora 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.