CDC replication

📘

CDC replication only for Bulk Syncs

CDC replication from MySQL is only available for Bulk Syncs.

When creating a Polytomic Bulk Sync from Google Cloud SQL for MySQL into your data warehouse, it's preferred (though not required) for Polytomic to use CDC (change data capture) replication. This avoids running full table scans to find changes since the last sync. Instead, Polytomic captures changes in real time by reading your instance's binary log.

This page covers the Cloud SQL–specific setup. For the underlying MySQL concepts, see the general MySQL CDC replication guide.

Requirements

Grant replication privileges

Grant your Polytomic MySQL user replication privileges (replace polytomic_user with your user):

GRANT SELECT, REPLICATION CLIENT, REPLICATION SLAVE ON *.* TO 'polytomic_user'@'%';

Enable binary logging

Cloud SQL for MySQL enables binary logging (log_bin) when you turn on point-in-time recovery (PITR). Enabling PITR is what makes CDC replication possible.

1. In the Cloud SQL console, open your instance and click Edit.

2. Expand the Data protection section.

3. Select Enable point-in-time recovery.

4. Click Save.

Set the binary log format flags

Cloud SQL controls MySQL server settings through database flags. Polytomic needs complete row images and row metadata in the binary log:

binlog_format: ROW          (managed by Cloud SQL; always ROW)
binlog_row_image: FULL      (Cloud SQL default)
binlog_row_metadata: FULL   (Cloud SQL default is MINIMAL; you must change it)

Set binlog_row_metadata to FULL, and make sure binlog_row_image hasn't been changed from FULL. To set a flag:

1. On the instance Edit page, expand the Flags section and click Add a database flag.

2. Choose the flag and set its value.

3. Click Save. Neither flag requires a restart.

📘

GTID is enabled by default

Cloud SQL for MySQL runs with gtid_mode and enforce_gtid_consistency set to ON and does not allow disabling them. Polytomic automatically detects GTID support and uses GTID-based replication, which provides more reliable resumption than position-based tracking.

Set the transaction log retention period

Cloud SQL retains binary logs for the shorter of the transaction log retention period (set in days) and the binlog_expire_logs_seconds flag (default: one day). This determines how far behind Polytomic can be when replicating, and how far back it can recover after a failure. Longer retention requires more storage.

🚧

Purged binary logs require a reset

If Cloud SQL purges the binary logs Polytomic needs before Polytomic reads them, you must reset the sync.

We recommend retaining at least one day of logs; a longer window gives more room to recover.

1. On the instance Edit page, expand the Data protection section.

2. Set Days of transaction logs to retain for point-in-time recovery (1–7 days for Cloud SQL Enterprise edition, 1–35 days for Cloud SQL Enterprise Plus edition).

3. Click Save.

4. If you retain more than one day of transaction logs, raise the binlog_expire_logs_seconds flag to match (for example, 259200 for three days).

Enable replication in Polytomic

Check the Use replication for bulk syncs box in your Google Cloud MySQL connection configuration and click Save.

Verifying your configuration

To confirm your instance is configured correctly for replication, run the following queries:

-- Check binlog configuration
SHOW VARIABLES LIKE 'log_bin';                     -- Should be 'ON'
SHOW VARIABLES LIKE 'binlog_format';               -- Should be 'ROW'
SHOW VARIABLES LIKE 'binlog_row_image';            -- Should be 'FULL'
SHOW VARIABLES LIKE 'binlog_row_metadata';         -- Should be 'FULL'
SHOW VARIABLES LIKE 'binlog_expire_logs_seconds';  -- At least 86400

-- Check GTID configuration
SHOW VARIABLES WHERE variable_name IN ('gtid_mode', 'enforce_gtid_consistency');
-- Both should be 'ON'