Replicate a private RDS MySQL database to BigQuery

Many production RDS and Aurora databases are deliberately not publicly accessible. You can still replicate them to BigQuery without opening MySQL to the internet: connect through a small SSH bastion inside the same VPC.

1. Turn on binary logging

Create a custom DB parameter group (cluster parameter group for Aurora) with binlog_format = ROW and binlog_row_image = FULL, attach it, and reboot. Then keep binary logs long enough to survive an outage:

CALL mysql.rds_set_configuration('binlog retention hours', 168);

RDS needs automated backups enabled for binary logging to be on. Details: RDS guide, Aurora guide.

2. Launch a bastion

A very small EC2 instance in a public subnet of the same VPC is enough; it only forwards traffic. Give it a security group that allows inbound SSH (port 22) only from Quayen's IP addresses, shown in the setup wizard.

3. Let the bastion reach MySQL

In the RDS instance's security group, add an inbound rule for port 3306 with the bastion's security group as the source. MySQL stays private.

4. Add Quayen's key and connect

In the Quayen setup wizard, tick Connect through an SSH bastion. It shows a public key unique to your organization and the commands to create a quayen user on the bastion with that key. Then enter the bastion's public address as the SSH host and the RDS endpoint as the MySQL host, and test the connection.

The connection check tells you exactly which hop fails: the bastion not reachable, the key not accepted, or the bastion unable to reach MySQL. The bastion's host key is pinned on first connection, so a different server at the same address is refused.