RDS MySQL source setup guide

This is a step-by-step guide on how to configure your RDS MySQL instance for replicating its data via the MySQL ClickPipe.

info

We also recommend going through the MySQL FAQs here. The FAQs page is being actively updated.

Enable binary log retention

The binary log is a set of log files that contain information about data modifications made to an MySQL server instance, and binary log files are required for replication. Both of the below steps must be followed:

1. Enable binary logging via automated backup

The automated backups feature determines whether binary logging is turned on or off for MySQL. It can be set in the AWS console:

Setting backup retention to a reasonably long value depending on the replication use-case is advisable.

2. Binlog retention hours

Amazon RDS for MySQL has a different method of setting binlog retention duration, which is the amount of time a binlog file containing changes is kept. If some changes are not read before the binlog file is removed, replication will be unable to continue. The default value of binlog retention hours is NULL, which means binary logs aren't retained.

To specify the number of hours to retain binary logs on a DB instance, use the mysql.rds_set_configuration function with a binlog retention period long enough for replication to occur. 24 hours is the recommended minimum.

Configure binlog settings in the parameter group

The parameter group can be found when you click on your MySQL instance in the RDS Console, and then heading over to the Configurations tab.

Upon clicking on the parameter group link, you will be taken to the page for it. You will see an Edit button in the top-right.

The following settings need to be set as follows:

1. binlog_format to ROW.

2. binlog_row_metadata to FULL

3. binlog_row_image to FULL

Then click on Save Changes in the top-right. You may need to reboot your instance for the changes to take effect - a way of knowing this is if you see Pending reboot next to the parameter group link in the Configurations tab of the RDS instance.

tip

If you have a MySQL cluster, the above parameters would be found in a DB Cluster parameter group and not the DB instance group.

Enabling GTID Mode

Global Transaction Identifiers (GTIDs) are unique IDs assigned to each committed transaction in MySQL. They simplify binlog replication and make troubleshooting more straightforward.

If your MySQL instance is MySQL 5.7, 8.0 or 8.4, we recommend enabling GTID mode so that the MySQL ClickPipe can use GTID replication.

To enable GTID mode for your MySQL instance, follow the steps as follows:

1. In the RDS Console, click on your MySQL instance.
2. Click on the Configurations tab.
3. Click on the parameter group link.
4. Click on the Edit button in the top-right corner.
5. Set enforce_gtid_consistency to ON.
6. Set gtid-mode to ON.
7. Click on Save Changes in the top-right corner.
8. Reboot your instance for the changes to take effect.

tip

The MySQL ClickPipe also supports replication without GTID mode. However, enabling GTID mode is recommended for better performance and easier troubleshooting.

Configure a database user

Connect to your RDS MySQL instance as an admin user and execute the following commands:

1. Create a dedicated user for ClickPipes:

2. Grant schema permissions. The following example shows permissions for the mysql database. Repeat these commands for each database and host you want to replicate:

3. Grant replication permissions to the user:

Configure network access

IP-based access control

If you want to restrict traffic to your RDS instance, please add the documented static NAT IPs to the Inbound rules of your RDS security group.

Private access via AWS PrivateLink

To connect to your RDS instance through a private network, you can use AWS PrivateLink. Follow our AWS PrivateLink setup guide for ClickPipes to set up the connection.