Integrations

Amazon RDS for MySQL to BigQuery, in real time

Replicate Amazon RDS for MySQL into BigQuery in real time. Turn on the binary log with a parameter group, create a read-only user, and every change streams into BigQuery within seconds.

Setup time
About 15 minutes
Restart needed
Yes: attaching a new parameter group requires a reboot.
Binary log retention
CALL mysql.rds_set_configuration('binlog retention hours', 72);

Set up Amazon RDS 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. Database in a private subnet? See replicating a private RDS database.

  1. 1

    Turn on automated backups

    RDS writes the binary log only while automated backups are on. Modify the instance and set a backup retention period of at least 1 day.

  2. 2

    Create a DB parameter group

    Create a parameter group for your MySQL version and set binlog_format = ROW and binlog_row_image = full.

  3. 3

    Attach it and reboot

    Attach the parameter group to the instance and reboot when the status shows pending-reboot. This causes a few minutes of downtime, so pick a quiet time.

  4. 4

    Keep binary logs for 3 days

    RDS deletes binary logs quickly by default. Run CALL mysql.rds_set_configuration('binlog retention hours', 72); as the admin user.

  5. 5

    Allow network access

    Make the instance reachable and add an inbound rule for port 3306 from our IP addresses to its security group.

  6. 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

Amazon RDS questions

Do I need a read replica?

No. Changes are read from the primary's binary log with a read-only user; a replica isn't required.

Why are automated backups required?

RDS only writes the binary log while automated backups are enabled, and change data capture reads that log.

How do I connect Amazon RDS 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 RDS 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 RDS 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.