MySQL

This article describes how to enable CDC on your MySQL database.

This article guides you through the process of enabling CDC on your MySQL database.

Prerequisites for MySQL

Upsolver supports MySQL 5.6+ and 8.0.x. You can connect to:

• Generic MySQL (self-hosted)

• Amazon RDS MySQL

• Amazon Aurora MySQL

Permissions

In order for Upsolver to read the binlog and initial state of the database, the CDC data source requires the following permissions:

• SELECT (on the tables that are to be loaded, usually all tables)

• REPLICATION CLIENT

• REPLICATION SLAVE

• RELOAD

• SHOW DATABASES (this is only necessary if the server was started with the --skip-show-database option)

For more information about creating a user with the correct permissions, see the Debezium instructions for configuring MySQL.

Enabled binlog

The binlog must be enabled in ROW mode. You can follow the Debezium guide for MySQL to check if it is enabled and configure it if you are using your own MySQL server: ​

If you are using AWS RDS you will need to:

1. Ensure that Automated Backups are enabled. If not, the binlog will be disabled regardless of the parameter group settings.

2. Change the binlog mode to ROW in the parameter group settings for the cluster:

   1. Create a new parameter group based on the base group that matches your database version.

   2. Change the BINGLOG_FORMAT to ROW

   3. Change the database cluster to use the new parameter group and apply the changes.

Ensure binlog duration is long enough

Upsolver constantly reads the binlog's latest events, so generally the binlog retention only needs to be set high enough to handle server interruptions or situations where the data source or compute cluster is paused in Upsolver. It is therefore recommended to set the binlog retention to 1 day, and generally not recommended to set it below 3 hours.

Using AWS RDS, this can be achieved using the following command:

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

Please consult the documentation for your versions of MySQL for custom deployments.