Learn
How to enable binary logging on Cloud SQL for MySQL
Turn on the binary log in Cloud SQL for MySQL by enabling point-in-time recovery (console or gcloud), check it with SHOW VARIABLES, and set retention for CDC.
Cloud SQL for MySQL has no separate log_bin flag: the binary log is turned on together with point-in-time recovery (PITR), which needs automated backups. Replicas, PITR and every change data capture tool read this log.
In the console
- Open the instance and click Edit.
- Under Data protection, turn on automated daily backups and point-in-time recovery.
- Save. Enabling PITR on an existing instance restarts it, so pick a quiet time.
With gcloud
# automated backups are required first gcloud sql instances patch INSTANCE_NAME --backup-start-time=02:00 # turn on PITR (and the binary log), keeping 7 days of logs gcloud sql instances patch INSTANCE_NAME --enable-bin-log --retained-transaction-log-days=7 # confirm: look for binaryLogEnabled: true under backupConfiguration gcloud sql instances describe INSTANCE_NAME
Check it from MySQL
SHOW VARIABLES WHERE Variable_name IN ('log_bin', 'binlog_format', 'binlog_row_image');For CDC you want log_bin = ON, binlog_format = ROW (Cloud SQL's default) and binlog_row_image = FULL. If binlog_row_image was changed to minimal with a database flag, set it back. Paste the full output into the free binlog checker to check everything at once.
Retention
Binary logs are kept for the transaction log retention period (1 to 7 days on Cloud SQL Enterprise; 7 by default). A CDC tool that is stopped for longer than that has to re-copy its tables, so keep the maximum. See how long to keep binary logs.
Next: a replication user
CREATE USER 'quayen'@'%' IDENTIFIED BY 'a-long-random-password'; GRANT SELECT ON `your_database`.* TO 'quayen'@'%'; GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'quayen'@'%';
Then follow the Cloud SQL to BigQuery guide. If the instance only has a private IP, see replicating a private-IP Cloud SQL instance.