Aurora RDS MySQL Setup Guide

Follow these instructions to replicate your Aurora RDS MySQL database to your destination via Fivetran.

Prerequisites

To connect your MySQL database to Fivetran, you need:

MySQL version 5.1.5 or above for non-RDS databases (5.5.40 is the earliest version tested). MySQL version 5.6.13 or above for RDS databases.
IP (for example, 1.2.3.4) or host (your.server.com)
Port (usually 3306)

For the prerequisites for connecting via an SSH tunnel, see Fivetran's Connection Options page.

Note: We do not support serverless Aurora.

Replica ID

The replica ID is a unique ID within the MySQL replica set. It must be an integer different from all other master and replica server IDs within the same group. By default, the replica ID is a random integer greater than 1000.

Choose your connection option

First, you'll need to decide whether to connect your MySQL database directly or via an SSH tunnel. Security group configuration in subsequent steps will differ depending on this decision.

Connect directly

If you connect directly, you must create a rule in a security group that allows Fivetran access to your database instance.

Connect via SSH

If you connect via SSH, Fivetran connects to a separate server in your network which provides an SSH tunnel to your database. You must then configure your tunnel server's security group to allow Fivetran access and configure the database instance's security to allow access from the tunnel. You must connect through SSH if your database is contained within an inaccessible subnet.

If you have an SSH connection, please follow these instructions before proceeding to the next step.

Allow access to master

You must allow Fivetran's data processing server access to your master/writer server. You cannot connect Fivetran to an Aurora MySQL reader because AWS does not make binary logs available on reader nodes. We need binary logs to perform incremental updates.

In your RDS Dashboard, select the Aurora instance you would like to use. Make sure its replication role is "writer", then click on the instance to view its details.
Scroll down to the Connect panel and make sure that the Publicly Accessible setting is "yes". If the setting is not "yes", please contact us to discuss options.
Enter the Endpoint host address and port number into the form fields on the left.

Configure security group

You must configure your Aurora cluster's VPC security group to allow Fivetran to access the cluster.

Click the link to the master node's Security Group, which will open a new tab.
In the security group panel, select the Inbound tab.
Click Edit.
Click Add Rule.
A new Custom TCP rule is created at the bottom of the list with a blank space for a Port Range and a Source IP address.
- In the Port Range field, enter your master node's port number that you copied into the form on the left.
- What you enter in the Source Custom IP field depends on whether you're connecting directly or via an SSH tunnel. For a direct connection, enter Fivetran's IP. For a connection via an SSH tunnel, enter {your-ssh-tunnel-server-ip-address}/32.
Click Save.

Configure Network ACLs

Return to the master node and scroll to the Details panel. Click the link to the VPC, which will open a new tab.
Select the VPC.
In the Summary tab, click the Network ACL link. This will open a new tab.
Select the Network ACL.
You will see tabs for Inbound Rules and Outbound Rules. You need to edit both. First, select Inbound Rules.
If you have a default VPC that was automatically created by AWS, the settings already allow all incoming traffic as indicated by the Source value 0.0.0.0/0 and the fact that the ALLOW entry is listed above the DENY entry.
If your inbound rules don't include an ALL - 0.0.0.0/0 - ALLOW entry, edit the rules to allow either Fivetran's IP (for direct connection) or {your-ssh-tunnel-server-ip-address}/32 (for connection via SSH) to access the port number of your master node. For help on ACL configuration, see AWS' Network ACL documentation.
Select Outbound Rules.
If your outbound rules don't include an ALL - 0.0.0.0/0 - ALLOW entry, edit the rules to allow outbound traffic to all ports 1024-65535 for destination Fivetran's IP or your SSH server.

Create user

Next, you must create a Fivetran user in your Aurora database. Make sure to do this on the master node, because replicas are read-only.

WARNING: This user must be reserved for Fivetran use only and must be unique to your connector. For more information, see our MySQL documentation.

Using your favorite SQL tool (for example, MySQL Workbench or the "mysql" command in your operating system's terminal window), create the fivetran user and grant replication permissions by running the following SQL commands. Replace password with a password of your choice.

CREATE USER fivetran@'%' IDENTIFIED BY 'password';
GRANT SELECT, REPLICATION CLIENT, REPLICATION SLAVE ON *.* TO fivetran@'%';

Configure replication

To enable incremental updates, you must configure the master node in your Aurora cluster to output binary logs. First, note the name of your existing DB Cluster Parameter Group. If its name begins with default., it is a default parameter group. Otherwise, it is a custom group.

Navigate to Parameter Groups.
If you have a default parameter group, you must create a new parameter group. Alternately, you can copy your existing cluster parameter group.
If you are creating a new group, make sure the Parameter Group Family value matches the one in your existing default parameter group name (from above). Make sure the Type is "DB Cluster Parameter Group". Give the new group a name and description, then click Create.
Click on the link to the new group (whether you created a new group or copied an existing group). Make sure the Type is "DB Cluster Parameter Group". If not, revisit previous steps.
Find the binlog_format parameter. Click Edit parameters.
Change the binlog_format value to "ROW". Click Save.
Navigate back to Instances.
Select your Aurora master node (the one with the "writer" replication role).
Click Instance Actions -> Modify.
In the Modify DB Instance screen, scroll down to find the Database Option section. Change the DB cluster parameter group to the new group you created.
Select Continue at the bottom of the page.
Select Apply Immediately. Read the warning and confirm the changes by clicking Modify DB Instance.
To make the changes to take effect, you need to reboot the DB instance. With the master node selected, click Instance Actions -> Reboot.
Click Reboot to confirm that you want to reboot the instance.
Wait for the master node to reboot. The instance's Status will change from "rebooting" to "available" when it is done.
The configuration change will be complete when the DB Cluster Parameter Group status is "in-sync".

Set Binary Log Retention Period

We require a minimum of 24 hours of binary logs to be retained on the source MySQL Database to allow for replication. By default, Aurora is set to cull binary log files as quickly as possible. View your current settings with the following function:

CALL mysql.rds_show_configuration;

If the result of this query is NULL, your database has the default behavior. When your database culls log files as quickly as possible, it can disrupt our incremental update process. Fivetran may have to re-sync your database in full, which can cause significant downtime. Update your configuration to the Fivetran minimum required value of 24 hours:

CALL mysql.rds_set_configuration('binlog retention hours', 24);

We recommend that you set a binary log retention period of 7 days, but know that increasing the binary log retention period requires additional disk space on your source MySQL database to store the log files:

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

Choose schema prefix

Each schema from your source database will be mapped to a schema in the destination by adding a prefix to the original schema name. For example, if your original database contains schemas "foo" and "bar" and if you choose the prefix "pre", then you will get schemas "pre_foo" and "pre_bar" in the output.

Documentation