Setting up CDC for MySQL on AWS Aurora

To configure Change Data Capture (CDC) for MySQL on AWS, follow these steps:

Note

Please consult this article for a list of supported DDL changes.

Note

You must have an existing Aurora cluster before proceeding.

Note

You must connect to Master.

Step 1- Create a Parameter Group

Step 2 - Assign the Parameter Group to the Aurora instance

Step 3 - Verify that the Parameter Group is assigned to the Aurora instance

Step 4 - Reboot the instance

Step 5 - Verify that binlog_format is set to ROW

Step 6 - Create a User

Step 7 - Binary log retention

Step 1- Create a Parameter Group

Go to your Amazon RDS Dashboard.
In the sidebar, click Parameter Groups, then click the blue Create DB Cluster Parameter Group button. You will see a screen with 4 boxes. Enter the following settings:
```
Parameter Group Family: aurora5.6
Type: DB Cluster Parameter Group
Cluster Parameter Group Name: mysql56sync
Parameter Group Description: MySQL5.6 sync settings
```
Once completed, click Create.
Click on the group you created and click Edit Parameters. Change binlog_format to ROW.
Click Save Changes to save your new settings.

Step 2 - Assign the Parameter Group to the Aurora instance

From the main RDS page:

In the navigation pane, choose Instances, and then select the your Instance.
Select Modify from the Instance Actions drop-down.
Under Database Options, select the Database Cluster Parameter Group you created.
Recommended: Select Instance Actions and then Modify. Set the following parameters:
```
Backup Retention Period: 7days 
Apply Immediately: Check 
```
Scroll to the bottom and click Continue to apply your changes.
In the Scheduling of Modifications screen that appears, select Apply immediately.
Click Modify DB Instance.

Note

This will require you to reboot the instance. See the next step.

Step 3 - Verify that the Parameter Group is assigned to the Aurora instance

From the main RDS page:

In the navigation pane, choose Instances, and then select the your Instance.
Verify your Instance is present in the Details section, Configurations column, looking for the parameter groups. The status should say "pending-reboot".
Note that the status will change to "in-sync" after the reboot.

Step 4 - Reboot the instance

From the main RDS page:

In the navigation pane, choose Instances, and then select the your Instance.
From the Instance Actions menu, choose Reboot.

Step 5 - Verify that binlog_format is set to ROW

Connect to your MySQL command line and run:

mysql> show global variables like '%binlog_format%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW   |
+---------------+-------+
1 row in set (0.04 sec)

Verify the value for binlog_format is ROW.

Step 6 - Create a User

Almost done! It's time to create a user for Alooma.

Log into the master server from a MySQL client (e.g., MySQL Workbench) with the master account.

Create a user to be used only by our sync process. You can do this in MySQL like so:

mysql> CREATE USER 'alooma'@'%' IDENTIFIED BY 'YOUR_PASSWORD'; 
mysql> GRANT REPLICATION SLAVE, SELECT, RELOAD, REPLICATION CLIENT, 
       LOCK TABLES, EXECUTE ON *.* TO 'alooma'@'%'; 
mysql> FLUSH PRIVILEGES;

For YOUR_PASSWORD, enter the password you want to use for this new user.

Step 7 - Binary log retention

To ensure proper replication, configure your system to retain binary logs for a minimum of 7 days (168 hours). To do this, enter the following MySQL command:

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

That's it, you're ready to continue to Connecting to MySQL.

Articles in this section

Note

Note