To configure Change Data Capture (CDC) for MySQL on AWS, follow these steps:
Note
You must have an existing Aurora cluster before proceeding.
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 5 - Verify that binlog_format is set to ROW
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.
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.
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.
From the main RDS page:
In the navigation pane, choose Instances, and then select the your Instance.
From the Instance Actions menu, choose Reboot.
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
.
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.
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.
Comments
0 comments
Article is closed for comments.