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:

1. The Polytomic MySQL user needs to be configured with replication privileges. This can be done with the following query (replace <username> with your MySQL Polytomic user):

   MySQL

   GRANT SELECT, REPLICATION CLIENT, REPLICATION SLAVE ON *.* TO <username>@'%';
   

   For example, if your Polytomic MySQL user is polytomic then your query would be:

   MySQL

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

2. Set the following on your database:

   log_bin: ON
   binlog_format: ROW
   binlog_row_image: full
   binlog_row_metadata: FULL
   slave_parallel_type: LOGICAL_CLOCK
   

   The exact way to set these will depend on your MySQL hosting platform. If you're hosting MySQL yourself then you'll need to edit your my.cnf file, whereas if you're on AWS RDS then you'll have to edit your parameter group as shown in this screenshot:

   

3. Set a log retention period of at least 24 hours; see the section below for instructions.

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

Log retention

The log retention period determines how log replication data (the "binlog") is retained before it's deleted. This determines how far behind a reader can be when replicating data, as well as how far back in time the reader can recover in the event of a failure. The trade-off for increasing the binlog retention period is additional required storage.

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

MySQL

Set binlog_expire_logs_secondsto the configure the binlog retention period for MySQL. See the MySQL documentation for a full description of the setting.

-- For MySQL 8.0.1 and later
SET GLOBAL binlog_expire_logs_seconds = 86400;

-- To make it persistent 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';

MySQL on RDS

MySQL on RDS uses the same configuration setting as MySQL, however the value is set on the parameter group. See the AWS documentation for information on editing parameter groups.

Aurora MySQL

Use the mysql.rds_set_configuration stored procedure to set log retention for Aurora MySQL. See the AWS documentation for details on calling the stored procedure.

Note that SHOW VARIABLES query will return a value when run on Aurora MySQL; this value is not informative.