CDC replication

📘
CDC replication only for Bulk Syncs
CDC replication from MySQL is only available for Bulk Syncs.
When bulk-syncing data from MySQL into your data warehouse, it's preferred (though not required) for Polytomic to utilize CDC (change data capture) replication. This will avoid Polytomic running full table scans to calculate changes since the last sync. Rather, Polytomic will be able to capture changes in real-time without scanning your tables.

Requirements

To enable this, the following settings need to be enabled for your MySQL database:

The Polytomic MySQL user must have replication privileges. Grant them with the following query (replace polytomic_user with your MySQL Polytomic user):
```
GRANT SELECT, REPLICATION CLIENT, REPLICATION SLAVE ON *.* TO 'polytomic_user'@'%';
```
REPLICATION CLIENT lets Polytomic read the current binary log position, and REPLICATION SLAVE lets it stream the binary log. These privilege names are unchanged in MySQL 8.4 and later.
Set the following on your database:

Required for all replication modes:
```
log_bin: ON
binlog_format: ROW
binlog_row_image: FULL
binlog_row_metadata: FULL
```
log_bin is on and binlog_format is ROW by default in MySQL 8.0 and later. MySQL 8.0.34 deprecates binlog_format, but MySQL still honors it, so make sure it hasn't been changed from ROW. binlog_row_metadata defaults to MINIMAL, and you must change it to FULL. (MySQL versions before 8.0.1 don't have binlog_row_metadata; Polytomic treats them as compatible.)

For GTID-based replication (recommended):
```
gtid_mode: ON
enforce_gtid_consistency: ON
```
Both settings must be exactly ON. Polytomic treats ON_PERMISSIVE as GTIDs being disabled. If GTIDs aren't enabled, Polytomic uses binary log position tracking instead.

How you set these depends on your MySQL hosting platform. If you host MySQL yourself, edit your my.cnf file (or use SET PERSIST). If you're on AWS RDS or Aurora, edit your DB parameter group (DB cluster parameter group for Aurora), as shown in this screenshot:

🚧
Binary logging on Amazon RDS and Aurora
On Amazon RDS for MySQL, binary logging is only on when automated backups are turned on (backup retention period of 1 day or more).
On Aurora MySQL, binary logging is off by default. Setting binlog_format to ROW in the DB cluster parameter group turns it on. You must reboot the writer instance for the change to take effect.
Set a log retention period of at least 24 hours; see the Log retention section below for instructions. On Amazon RDS for MySQL, binary logs are not retained by default, so this step is required.
Check the Use replication for bulk syncs box in your Polytomic MySQL connection configuration and click Save.

Verifying your configuration

To verify your MySQL server is configured correctly for replication, run the following queries:

-- Check binlog configuration (required for all modes)
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'

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

-- View the current binary log position
SHOW BINARY LOG STATUS;  -- MySQL 8.2 and later
SHOW MASTER STATUS;      -- MySQL 8.0 and 8.1 (removed in MySQL 8.4)

GTID and position-based replication

When a sync first starts replicating, Polytomic checks whether GTIDs are enabled. If they are, Polytomic uses GTID-based replication. If not, it uses binary log position tracking. That choice is fixed for the life of the sync.

If you enable GTIDs after a sync has started, the sync continues to use position tracking. Reset the sync to switch it to GTID-based replication.
If you disable GTIDs on a server while a sync is using GTID-based replication, the sync fails until you reset it.

Log retention

The log retention period determines how long MySQL keeps log replication data (the "binlog") before deleting it. This determines how far behind a reader can be when replicating data, as well as how far back in time the reader can recover after a failure. A longer retention period requires more storage.

🚧
Purged binary logs require a reset
If MySQL purges the binary logs Polytomic needs before Polytomic reads them, the sync can't resume and you must reset it.

We recommend setting the log retention to at least 24 hours.

MySQL

Set binlog_expire_logs_seconds to configure the binlog retention period for MySQL. (The older expire_logs_days setting is deprecated in MySQL 8.0 and removed in MySQL 8.4.) See the MySQL documentation for a full description of the setting.

-- Takes effect immediately and persists across restarts
SET PERSIST binlog_expire_logs_seconds = 86400;

You can confirm the settings by using a SHOW query.

SHOW VARIABLES LIKE 'binlog_expire_logs_seconds';

Amazon RDS for MySQL and Aurora MySQL

Amazon RDS and Aurora manage binary log retention with the mysql.rds_set_configuration stored procedure, not binlog_expire_logs_seconds. On RDS for MySQL, the default is NULL, which means RDS purges binary logs as soon as possible. Set retention to at least 24 hours:

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

The maximum is 168 hours (7 days) for RDS for MySQL and 2160 hours (90 days) for Aurora MySQL version 3. Confirm the setting with:

CALL mysql.rds_show_configuration;

See the AWS documentation for RDS for MySQL and Aurora MySQL for details.

SHOW VARIABLES LIKE 'binlog_expire_logs_seconds' returns a value on RDS and Aurora, but it doesn't reflect the retention RDS applies.