MySQL RDS Setup Guide

Follow these instructions to replicate your MySQL RDS 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.

Replica ID

The replica ID is a unique ID within the MySQL replica set. It must be an integer different from all other primary 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, decide whether to connect Fivetran to your MySQL database directly or through an SSH tunnel. How you configure security groups will differ based on your connection method.

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, follow these instructions before you proceed to the next step.

Create a read replica

If you already have a read replica, skip ahead to the Enable Access section.

If you do not have a read replica, follow the instructions below to create one. You must connect Fivetran to a read replica because RDS does not allow binary logging to be activated on the primary database alone; Fivetran requires binary logging to perform incremental updates. Connecting Fivetran to a read replica also allows us to integrate your data without putting unnecessary load on or interrupting the queries running on your primary server.

Navigate to your RDS Dashboard and select the MySQL primary instance.
Click Instance Actions, then select Create Read Replica.
Make sure the replica is accessible from outside your VPC.
Specify the instance type for the replica. It does not need to be as large your primary instance.
Set an instance ID.
Click Create Read Replica.
Your RDS dashboard should now show the status of the replica as "creating". It will take a few minutes for the read replica to finish being created.
The status will change to "available" when the read replica is created.

Enable access to read replica

Fivetran's data processing servers need access to your read replica. If your instance is in a VPC, two mechanisms control access - VPC Security Groups and Network Access Control Lists (ACLs). If your instance is not in a VPC, you only need to configure Security Groups.

Configure security group

These instructions assume that your read replica is in a VPC. If your read replica is not in a VPC, you can still use these instructions as a guide because configuring a non-VPC security group is almost identical.

Click on your read replica to view details.
A panel of details for your read replica appears. Scroll to the Connect panel and note the read replica's port number (you will need this later). Then click the link to its security group, which makes a new tab open.
In the security group panel, select the Inbound tab.
Click Edit.
Click Add Rule. This creates a new Custom TCP Rule at the bottom of the list with a blank space for a Port Range and a Source IP address.
Enter your Port Range and Source IP address values.
- In the Port Range field, enter your read replica's port number that you wrote down in Step 2 of this section. The port number will be 3306 for direct connections, unless you changed the default.
- In the Custom IP field, enter Fivetran's IP if you are connecting directly or {your-ssh-tunnel-server-ip-address}/32 if you are connecting through an SSH tunnel.
Click Save.

Configure Network ACLs

Return to the RDS Dashboard and click on the read replica.
Scroll to the Details section. Click the link to the read replica's VPC.
Select the VPC.
In the Summary tab, click the Network ACL link.
You will see tabs for Inbound Rules and Outbound Rules. You must edit both.

Edit inbound rules

Select Inbound Rules.
If you have a default VPC that was automatically created by AWS, the settings already allow all incoming traffic. To verify that the settings allow incoming traffic, confirm that the Source value is 0.0.0.0/0 and 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 Source field to allow 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 read replica. The port number will be 3306 for direct connections, unless you changed the default. For help on ACL configuration, see AWS' Network ACL documentation.

Edit outbound rules

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 (for direct connection) or {your-ssh-tunnel-server-ip-address}/32 for connection via SSH.

Create Fivetran user in primary database

Next, you must create a Fivetran user in your MySQL primary database. This will not work on the read replica you created in the previous step because the replica is read-only. When you create the user in the primary database, it will automatically be replicated to the replica.

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 WITH mysql_native_password BY 'password';
   GRANT SELECT, REPLICATION CLIENT, REPLICATION SLAVE ON *.* TO fivetran@'%';

Configure replica

You must make two changes to the default RDS replica configuration - you must change the binary logging format to ROW and turn on automated backups to enable binary logging.

Start by creating a new parameter group in your RDS Dashboard. Go to your RDS dashboard and click Parameter groups.
Click Create new parameter group.
Choose a Parameter Group Family that reflects the correct major version for your database. For example, if your database version is 5.6.21, then the Parameter Group Family is mysql5.6.
Enter a Group Name and a Description, then click Create.
Select the new parameter group.
Click Edit Parameters.
Find the binlog_format parameter. Change the binlog format value to ROW, then click Save Changes.
Now that you have created a custom parameter group, you must apply it to the replica and enable automated backups. Return to Instances in your RDS Dashboard.
Select your MySQL replica. Click Instance Actions, then click Modify.
In the Modify DB Instance screen, scroll down to find the Database Options section. Change the DB Parameter Group value to the new parameter group you created in Step 4 of this section.
Scroll down to Backup. Find the Backup Retention Period field.
Change the Backup Retention Period to 1 day, then click Continue.
Select Apply Immediately. Read the warning, then click Modify DB Instance.
The Parameter Group setting in the instance details should now show the name of your new parameter group. The Parameter Group status will say "applying" at first. Wait until the status changes to "pending-reboot".
Reboot your instance by selecting Instance Actions -> Reboot" to make the changes take effect.
Click Reboot to confirm that you want to reboot the instance.
Rebooting will take a few minutes. The configuration change is complete when the Parameter Group status changes to "in-sync" and the DB Instance Status changes to "available".

Set your binlog retention period

To set a longer binlog retention period, run the following command on the connected database:

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

Grant Fivetran permission to check binlog retention period

During the connector setup process, Fivetran can check your binlog retention period and alert you if you need to set a longer retention period. If you want Fivetran to check your binlog retention period, you must grant Fivetran permission to access the mysql.rds_configuration table.

To grant access, run the following command on your RDS primary:

GRANT SELECT ON mysql.rds_configuration to fivetran@'%'

Documentation