title_wip

[Edit this page](https://github.com/ClickHouse/clickhouse-docs/blob/main/docs/integrations/data-ingestion/clickpipes/mysql/source/rds.md)

This step-by-step guide shows you how to configure Amazon RDS MySQL to replicate data into ClickHouse Cloud using the [MySQL ClickPipe](/docs/integrations/clickpipes/mysql). For common questions around MySQL CDC, see the [MySQL FAQs page](/docs/integrations/clickpipes/mysql/faq).

## Enable binary log retention[​](#enable-binlog-retention-rds "Direct link to 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. To configure binary log retention in RDS MySQL, you must [enable binary logging](#enable-binlog-logging) and [increase the binlog retention interval](#binlog-retention-interval).

### 1. Enable binary logging via automated backup[​](#enable-binlog-logging "Direct link to 1. Enable binary logging via automated backup")

The automated backups feature determines whether binary logging is turned on or off for MySQL. Automated backups can be configured for your instance in the RDS Console by navigating to **Modify** > **Additional configuration** > **Backup** and selecting the **Enable automated backups** checkbox (if not selected already).

We recommend setting the **Backup retention period** to a reasonably long value, depending on the replication use case.

### 2. Increase the binlog retention interval[​](#binlog-retention-interval "Direct link to 2. Increase the binlog retention interval")

Note

If ClickPipes tries to resume replication and the required binlog files have been purged due to the configured binlog retention value, the ClickPipe will enter an errored state and a resync is required.

By default, Amazon RDS purges the binary log as soon as possible (i.e., *lazy purging*). We recommend increasing the binlog retention interval to at least **72 hours** to ensure availability of binary log files for replication under failure scenarios. To set an interval for binary log retention ([`binlog retention hours`](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/mysql-stored-proc-configuring.html#mysql_rds_set_configuration-usage-notes.binlog-retention-hours)), use the [`mysql.rds_set_configuration`](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/mysql-stored-proc-configuring.html#mysql_rds_set_configuration) procedure:

```
mysql=> call mysql.rds_set_configuration('binlog retention hours', 72);
```

If this configuration isn't set or is set to a low interval, it can lead to gaps in the binary logs, compromising ClickPipes' ability to resume replication.

## Configure binlog settings[​](#binlog-settings "Direct link to Configure binlog settings")

The parameter group can be found when you click on your MySQL instance in the RDS Console, and then navigate to the **Configuration** tab.

Tip

If you have a MySQL cluster, the parameters below can be found in the [DB cluster](https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/USER_WorkingWithParamGroups.CreatingCluster.html) parameter group instead of the DB instance group.

  

Click the parameter group link, which will take you to its dedicated page. You should see an **Edit** button in the top right.

The following parameters 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 corner. 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 **Configuration** tab of the RDS instance.

## Enable GTID Mode[​](#gtid-mode "Direct link to Enable GTID Mode")

Tip

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

[Global Transaction Identifiers (GTIDs)](https://dev.mysql.com/doc/refman/8.0/en/replication-gtids.html) are unique IDs assigned to each committed transaction in MySQL. They simplify binlog replication and make troubleshooting more straightforward. We **recommend** enabling GTID mode, so that the MySQL ClickPipe can use GTID-based replication.

GTID-based replication is supported for Amazon RDS for MySQL versions 5.7, 8.0 and 8.4. To enable GTID mode for your Aurora MySQL instance, follow these steps:

1. In the RDS Console, click on your MySQL instance.
2. Click on the **Configuration** 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[​](#configure-database-user "Direct link to 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:

   ```
   CREATE USER 'clickpipes_user'@'host' IDENTIFIED BY 'some-password';
   ```
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:

   ```
   GRANT SELECT ON `mysql`.* TO 'clickpipes_user'@'host';
   ```
3. Grant replication permissions to the user:

   ```
   GRANT REPLICATION CLIENT ON *.* TO 'clickpipes_user'@'%';
   GRANT REPLICATION SLAVE ON *.* TO 'clickpipes_user'@'%';
   ```

## Configure network access[​](#configure-network-access "Direct link to Configure network access")

### IP-based access control[​](#ip-based-access-control "Direct link to IP-based access control")

To restrict traffic to your Aurora MySQL instance, add the [documented static NAT IPs](/docs/integrations/clickpipes#list-of-static-ips) to the **Inbound rules** of your RDS security group.

### Private access via AWS PrivateLink[​](#private-access-via-aws-privatelink "Direct link to Private access via AWS PrivateLink")

To connect to your RDS instance through a private network, you can use AWS PrivateLink. Follow the [AWS PrivateLink setup guide for ClickPipes](/docs/knowledgebase/aws-privatelink-setup-for-clickpipes) to set up the connection.

## Next steps[​](#next-steps "Direct link to Next steps")

Now that your Amazon RDS MySQL instance is configured for binlog replication and securely connecting to ClickHouse Cloud, you can [create your first MySQL ClickPipe](/docs/integrations/clickpipes/mysql#create-your-clickpipe). For common questions around MySQL CDC, see the [MySQL FAQs page](/docs/integrations/clickpipes/mysql/faq).

- [Enable binary log retention](#enable-binlog-retention-rds)
  - [1. Enable binary logging via automated backup](#enable-binlog-logging)- [2. Increase the binlog retention interval](#binlog-retention-interval)- [Configure binlog settings](#binlog-settings)- [Enable GTID Mode](#gtid-mode)- [Configure a database user](#configure-database-user)- [Configure network access](#configure-network-access)
          - [IP-based access control](#ip-based-access-control)- [Private access via AWS PrivateLink](#private-access-via-aws-privatelink)- [Next steps](#next-steps)

Was this page helpful?