MySQL setup for change data capture

Quayen reads MySQL's binary log, so the server must keep one in row format. The in-app guide shows exact clicks for RDS, Aurora, Cloud SQL and Azure; this page lists what is required and why.

Required server settings

• log_bin on. On managed services this is turned on by enabling automated backups (RDS, Aurora) or point-in-time recovery (Cloud SQL).
• binlog_format = ROW: each change records the actual row values.
• binlog_row_image = FULL: updates include every column, not only the changed ones.
• Binary logs kept for at least 24 hours, ideally 3 days (binlog_expire_logs_seconds = 259200), so a paused pipeline can resume without a full re-copy.

A read-only user

Create a dedicated user. It can read the tables and the binary log, and nothing else:

CREATE USER 'quayen'@'%' IDENTIFIED BY 'a-long-random-password';
GRANT SELECT ON `your_database`.* TO 'quayen'@'%';
GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'quayen'@'%';

• SELECT on your database: needed for the initial copy.
• REPLICATION SLAVE: lets the user read the binary log.
• REPLICATION CLIENT: lets the user read the current log position.

Tables need a primary key

Each replicated table needs a primary key, so updates and deletes can be matched to the right row in BigQuery. Tables without one are shown but can't be selected.

Network access

Allow Quayen's IP addresses to reach port 3306 (security group, authorized networks or firewall rule, depending on your host). Connections use TLS whenever the server supports it.

Private databases: SSH bastion

If MySQL has no public address, for example a private RDS or Aurora instance, connect through an SSH bastion: a small server in the same network. Quayen generates an SSH key for your organization; add its public half to the bastion, allow Quayen's IP addresses to reach the bastion on port 22, and allow the bastion to reach MySQL. Then enter MySQL's private address as the host.

Every connection, including the initial copy and the binary log stream, goes through the bastion. Its host key is recorded the first time Quayen connects, and a different server at the same address is refused.