How To: Set up a MySQL CDC connection on AWS RDS
Check Binlog Format
Connect to your RDS MySQL instance. Check the binlog format. It is 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)
Specify ROW Binlog Format
- Open the Amazon Relational Database Service (Amazon RDS) console.
- 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. - For Type, choose DB Cluster Parameter Group.
- Select the DB custom cluster parameter group, choose Parameter group actions, and then choose Edit.
- Change the value for the
binlog_format
parameter toROW
- Choose Save changes.
- If you created a new parameter group, be sure to modify your database to use the new DB cluster parameter group.
- Since
binlog_format
is a static parameter, you must reboot 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 24h. 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 properly configured for change data capture. You can now set up your Decodable connection using the MySQL CDC connector documentation.
Updated 10 months ago