How To: Set up a MySQL CDC connection on Amazon RDS

Check binlog format

Connect to your RDS MySQL instance. Check the binlog format. It’s likely set to MIXED by default.

mysql> show variables like 'binlog_format';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | MIXED |
+---------------+-------+
1 row in set (0.03 sec)

Enable automated backups

Make sure that Automated Backups is enabled. See Enabling Automated Backups in the Amazon RDS documentation for more information.

Configure ROW binlog format

  1. Open the Amazon Relational Database Service (Amazon RDS) console.

  2. In the navigation pane, choose Parameter groups.
    Note: If you’re using a default cluster parameter group, then create a new DB cluster parameter group.

  3. For Type, choose DB Cluster Parameter Group.

  4. Select the DB custom cluster parameter group, choose Parameter group actions, and then choose Edit.

  5. Change the value for the binlog_format parameter to ROW

  6. Choose Save changes.

  7. If you created a new parameter group, make sure to modify your database to use the new DB cluster parameter group.

  8. Since binlog_format is a static parameter, you must restart the writer DB instance in your cluster for the change to take effect.

Verify CDC is enabled

mysql> show variables like 'binlog_format';
+----------------+------------+
| Variable_name  | Value      |
+----------------+------------+
| binlog_format  | ROW        |
+----------------+------------+

mysql> select @@binlog_row_image as binlog_row_image ;
+------------------+
| binlog_row_image |
+------------------+
| FULL         	   |
+------------------+

Configure binlog retention

Aurora MySQL defaults to lazy binlog retention, usually not longer than 24 hours. In order to prevent data loss, it’s recommended to set this higher in the event the Decodable connector is stopped or restarted. The maximum retention time is 7 days (168 hours).

mysql> call mysql.rds_set_configuration('binlog retention hours', 168);
Query OK, 0 rows affected (0.03 sec)

After you set the retention period, monitor storage usage for the DB instance to make sure that the retained binary logs don’t take up too much storage.

Add a CDC database user

The database user that connects to the source database must have the replication client, replication slave and select roles. You can run the following code on the database to create the relevant user and grant the roles:

mysql> create user <username>@'%' identified  by '<password>';
Query OK, 0 rows affected (0.06 sec)

mysql> grant replication slave, replication client, select on *.* to <username>@'%'
Query OK, 0 rows affected, 1 warning (0.03 sec)

Create a Decodable connection

Your database should now be configured for change data capture. You can now set up your Decodable connection using the MySQL CDC connector documentation.