RDS MariaDB source setup guide

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

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

  

References

We also recommend going through the MySQL FAQs [here](/docs/integrations/clickpipes/mysql/faq). The FAQs page is being actively updated.

## 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 a MySQL server instance. Binary log files are required for replication. Both of the steps below must be followed:

### 1. Enable binary logging via automated backup[​](#enable-binlog-logging-rds "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. 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[​](#binlog-retention-hours-rds "Direct link to 2. Binlog retention hours")

Amazon RDS for MariaDB 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 aren't 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.

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

## Configure binlog settings in the parameter group[​](#binlog-parameter-group-rds "Direct link to Configure binlog settings in the parameter group")

The parameter group can be found when you click on your MariaDB instance in the RDS Console, and then navigate to the `Configurations` tab.

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

Settings `binlog_format`, `binlog_row_metadata` and `binlog_row_image` need to be set as follows:

1. `binlog_format` to `ROW`.

2. `binlog_row_metadata` to `FULL`

3. `binlog_row_image` to `FULL`

Next, click on `Save Changes` in the top-right. You may need to reboot your instance for the changes to take effect. If you see `Pending reboot` next to the parameter group link in the Configurations tab of the RDS instance, this is a good indication that a reboot of your instance is needed.

  

Tip

If you have a MariaDB cluster, the above parameters would be found in a [DB Cluster](https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/USER_WorkingWithParamGroups.CreatingCluster.html) parameter group and not the DB instance group.

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

Global Transaction Identifiers (GTIDs) are unique IDs assigned to each committed transaction in MySQL/MariaDB. They simplify binlog replication and make troubleshooting more straightforward. MariaDB enables GTID mode by default, so no user action is needed to use it.

## Configure a database user[​](#configure-database-user-rds "Direct link to Configure a database user")

Connect to your RDS MariaDB 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 that 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")

If you want to restrict traffic to your RDS instance, please 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 our [AWS PrivateLink setup guide for ClickPipes](/docs/knowledgebase/aws-privatelink-setup-for-clickpipes) to set up the connection.

- [Enable binary log retention](#enable-binlog-retention-rds)
  - [1. Enable binary logging via automated backup](#enable-binlog-logging-rds)- [2. Binlog retention hours](#binlog-retention-hours-rds)- [Configure binlog settings in the parameter group](#binlog-parameter-group-rds)- [Enabling GTID Mode](#gtid-mode-rds)- [Configure a database user](#configure-database-user-rds)- [Configure network access](#configure-network-access)
          - [IP-based access control](#ip-based-access-control)- [Private access via AWS PrivateLink](#private-access-via-aws-privatelink)

Was this page helpful?