Amazon RDS for MySQL Setup Guide

Follow these instructions to replicate your Amazon RDS for MySQL database to your destination using Fivetran.

Prerequisites

To connect your Amazon RDS for MySQL database to Fivetran, you need:

MySQL version 5.6.13 or above
Your database host's IP (e.g., 1.2.3.4) or domain (your.server.com)
A unique replica ID for Fivetran. We need this ID because we connect to your database as a replica. We provide a random replica ID in your setup form, but you can provide your own if you'd prefer or if the form's replica ID conflicts with one of your existing replica IDs.

NOTE: The replica ID is a unique ID within the MySQL replica set. By default, the replica ID is a random integer greater than 1000.

Additionally, if you will be using binary logs as your incremental sync method:

If you upgrade your database version from 5.7 to 8.0, you do not need to re-sync your existing Amazon RDS for MySQL connectors to continue syncing successfully. However, if your connector doesn't sync before the database's binary logs expire, you will need to re-sync.

Setup instructions

IMPORTANT: Do not perform the Choose connection method step if you want to use Hybrid Deployment for your data pipeline.

Choose connection method

First, decide whether to connect Fivetran to your Amazon RDS for MySQL primary database or read replica directly, using an SSH tunnel, using AWS PrivateLink, or using Proxy Agent. How you configure your security groups in later steps will differ depending on this decision.

Connect directly (TLS required)

IMPORTANT: You must have TLS enabled on your database to connect directly to Fivetran. Follow Amazon's TLS setup instructions to enable TLS on your database.

Fivetran connects directly to your Amazon RDS for MySQL database. This is the simplest method.

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

Connect using SSH (TLS optional)

Fivetran connects to a separate server in your network that provides an SSH tunnel to your database. You must connect through SSH if your database is in an inaccessible subnet.

If you connect using SSH, you will configure your tunnel server's security group to allow Fivetran access and configure your database's security to allow access from the tunnel.

Before you proceed to the next step, you must follow our SSH connection instructions. If you want Fivetran to tunnel SSH over TLS, follow Amazon's TLS setup instructions to enable TLS on your database.

Connect using AWS PrivateLink

IMPORTANT: You must have a Business Critical plan to use AWS PrivateLink.

AWS PrivateLink allows VPCs and AWS-hosted or on-premises services to communicate with one another without exposing traffic to the public internet. PrivateLink is the most secure connection method. Learn more in AWS’ PrivateLink documentation.

Follow our AWS PrivateLink setup guide to configure PrivateLink for your database.

Connect using Proxy Agent

Fivetran connects to your database through the Proxy Agent, providing secure communication between Fivetran processes and your database host. The Proxy Agent is installed in your network and creates an outbound network connection to the Fivetran-managed SaaS.

To learn more about the Proxy Agent, how to install it, and how to configure it, see our Proxy Agent documentation.

Choose incremental sync method

To keep your data up to date after the initial sync, we use one of the following incremental sync methods:

Binary log
Fivetran Teleport Sync

Each of these methods keeps a record of recent data changes, which allows Fivetran to update only the data that has changed since our last sync.

To learn the differences between the two methods, see our incremental sync documentation.

Create read replica (optional)

If you'd like, create a read replica for Fivetran's exclusive use. Using a read replica also allows us to integrate your data without putting unnecessary load on or interrupting the queries running on your primary server. We recommend that you connect a read replica to Fivetran, but it's not required.

If you want to connect Fivetran to your primary database or already have a read replica, skip ahead to Step 4.

In your Amazon RDS dashboard, select the MySQL primary instance that you want to replicate.
Click Actions, then select Create Read Replica.
In the Settings section, enter your chosen instance ID.
In the DB instance size section, specify the instance class for the read replica. It does not need to be as large as your primary instance.
In the Connectivity section, set the Public access setting to Publicly accessible to ensure that the read replica is accessible from outside your VPC.
[Binary logging only] In the Additional configuration section, set Enable automated backups to true and set Backup retention period to a value greater than 0 days. You must configure backups to ensure that binary logging is fully enabled.
Click Create read replica.
The replica's status should now be creating.

It will take a few minutes for the read replica to finish being created. The status will change to available when it is done.

Enable database access

Grant Fivetran's data processing servers access to your database server. How you grant access depends on whether or not your database instance is in a VPC.

If your instance is in a VPC, you must configure the two methods that 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

NOTE: These instructions assume that your database instance is in a VPC. If your database instance is not in a VPC, you can still use these instructions because configuring a non-VPC security group is an almost identical process.

In your Amazon RDS dashboard, click on the database that you want to connect to Fivetran.
In the Endpoint & port column, find the database's port number and make a note of it. You will need the port number to configure Fivetran.
In the Security column, click the link to the database instance's security group.
In the Security Group panel, click Actions, then select Edit inbound rules from the drop-down menu.
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.
Fill in the new Custom TCP Rule.
- In the Port Range field, enter your database'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.
- What you enter in the Custom IP field depends on whether you're connecting directly or using an SSH tunnel.
  - If you're connecting directly, enter Fivetran's IPs for your database's region.
  - If you're connecting using an SSH tunnel, enter {your-ssh-tunnel-server-ip-address}/32.
- (Optional) Enter a brief description in the Description field.
Click Save rules.

Configure Network ACLs (VPC only)

If your database instance is not in a VPC, skip ahead to Step 5.

Return to the RDS Dashboard.
Click on your MySQL database.
In the Networking column, click the link to the instance's VPC.
Select the VPC.
In the Summary tab, click the Network ACL link.
On the Network ACLs page, click the Network ACL ID.

You will see tabs for Inbound Rules and Outbound Rules. You must edit both.

Edit inbound rules

Go to the Inbound Rules tab.
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 ALL - 0.0.0.0/0 - ALLOW entry, edit the rules to allow the Source to access the port number of your database instance. (The port will be 3306 for direct connections, unless you changed the default.) What you enter in the Source field depends on whether you're connecting directly or using an SSH tunnel.
- If you're connecting directly, enter Fivetran's IPs for your database's region.
- If you're connecting using an SSH tunnel, enter {your-ssh-tunnel-server-ip-address}/32.

For additional help, see Amazon's Network ACLs documentation.

Edit outbound rules

Go to the Outbound Rules tab.
If you have a default VPC that was automatically created by AWS, the settings already allow all outbound traffic. To verify that the settings allow outbound traffic, confirm that the Destination value is 0.0.0.0/0 and that the ALLOW entry is listed above the DENY entry.
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 the following Destination(s):
- If you're connecting directly, enter Fivetran's IPs for your database's region.
- If you're connecting using an SSH tunnel, enter {your-ssh-tunnel-server-ip-address}/32.

Create user and configure incremental updates

In your MySQL primary database, create a database user for Fivetran's exclusive use. You cannot create a user in a read replica because it is read-only. Once 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.

How you create a user depends on which incremental sync method you are using. Follow the instructions below for your incremental sync method Step 2:

Binary log

Open a connection to your primary database in your favorite SQL tool (for example, MySQL Workbench or the "mysql" command in your operating system's terminal window).
Create a user for Fivetran and grant replication permissions with commands that depend on your MySQL version.

NOTE: You must grant access to mysql.rds_heartbeat2 to help verify that you are using the correct connector for your database. You must grant access to mysql.rds_configuration to help configure the sync interval.

On MySQL version 5.6 to 8.0, execute the following SQL command. Replace <USERNAME> and <PASSWORD> with a username and password of your choice:

    CREATE USER '<USERNAME>'@'%' IDENTIFIED BY '<PASSWORD>';
    GRANT SELECT, REPLICATION CLIENT, REPLICATION SLAVE ON *.* TO <username>@'%';
    GRANT SELECT ON mysql.rds_heartbeat2 TO <username>@'%';
    GRANT SELECT ON mysql.rds_configuration TO <username>@'%';
    GRANT EXECUTE ON PROCEDURE mysql.rds_kill to '<username>'@'%';

On MySQL version 8.0 and later, execute the following SQL command. Replace <username> and password with a username and password of your choice:

    CREATE USER <username>@'%' IDENTIFIED WITH mysql_native_password BY 'password';
    GRANT SELECT, REPLICATION CLIENT, REPLICATION SLAVE ON *.* TO <username>@'%';
    GRANT SELECT ON mysql.rds_heartbeat2 TO <username>@'%';
    GRANT SELECT ON mysql.rds_configuration TO <username>@'%';
    GRANT EXECUTE ON PROCEDURE mysql.rds_kill to '<username>'@'%';

NOTE: You must grant the Fivetran user SELECT permissions for all of the columns in the tables that you want to sync. When we do not have SELECT access to all columns in a table, we trigger a re-sync for that table, which slows down your syncs. If you don't want to sync certain columns, you can exclude them from your syncs in the Fivetran dashboard.

NOTE: The GRANT EXECUTE ON PROCEDURE mysql.rds_kill permission is an optional GRANT. We use this function to terminate any connections Fivetran creates that may remain open for multiple reasons. If you do not provide this permission, the connector can still sync, but open connections may need to be manually cleaned up on your database.

Configure binary logging

Update your database's default RDS configuration to enable binary logging. We need binary logs to perform incremental updates.

NOTE: You only need to apply the following configurations to the databases that you want to connect to Fivetran.

Change binary logging format

Return to the RDS Dashboard.
In the left menu, click Parameter groups.
Click Create parameter group.
On the Create parameter group page, enter the following information:
- In the Parameter group family drop-down menu, choose the most current major version for your database. For example, if your database version is 5.6.21, your parameter group family value would be mysql5.6.
- In the Group name field, enter a name for the parameter group.
- In the Description field, enter a brief description of the parameter group.
Click Create. You will be redirected to the Parameter groups page.
Select the new parameter group.
In the Parameter group actions menu, click Edit.
Select the binlog_format parameter, then click Edit parameters.
In the Values field, select ROW from the drop-down menu.
Click Save Changes.
If you have connected Fivetran to a read replica and plan to run your connector on history mode, check your read replica's slave_parallel_workers value.
- If the slave_parallel_workers value is 0, you do not need to do any additional configuration. Proceed to the Turn on automated backups section.
- If the slave_parallel_workers value is not 0, set the slave_preserve_commit_order value to 1.
  NOTE: Setting the slave_preserve_commit_order value to 1 does not preserve the order of non-transactional DML updates in your binlog. During incremental updates, Fivetran assumes that your binlog events are in sequential order; when they are not, we may encounter replication gaps that can cause data integrity problems in your destination. Learn more about gaps in MySQL’s Replication Inconsistencies documentation.
  Next, set the slave_parallel_type value to LOGICAL_CLOCK.
  Connect to your primary database, then set the binlog_order_commits value to 1.
  Learn more about these variables in MySQL's replica server variables documentation.

Turn on automated backups

In the RDS dashboard, click Databases in the left menu.
Select your MySQL database, then click Modify.
In the Modify DB Instance screen, scroll down to find the Additional configurations section. Change the DB Parameter Group value to the new parameter group you created in the preceding section.
Scroll down to Backup.
Change the Backup Retention Period to 1 day.
Click Continue.
Select Apply Immediately.
Click Modify DB Instance.
The Parameter Group setting in the database 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."
To make your changes take effect, reboot your instance by clicking Actions > Reboot.
Click Confirm to reboot the instance.
Rebooting will take a few minutes. The configuration change is complete when the database's status changes to "available" and the parameter group status changes to "in-sync."

Set your binlog retention period

Fivetran recommends that you set your binlog retention period to seven days (168 hours). To update your binlog retention period, run the following command on the Amazon RDS for MySQL database that you want to connect to Fivetran:

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.

Open a connection to your primary database in your favorite SQL tool (for example, MySQL Workbench or the "mysql" command in your operating system's terminal window).
Run the following command to grant Fivetran permission to access the mysql.rds_configuration table:
GRANT SELECT ON mysql.rds_configuration to <username>@'%'

Fivetran Teleport Sync

Open a connection to your MySQL primary database using your favorite SQL tool (for example, MySQL Workbench or the mysql command in your operating system's terminal window).

Create a Fivetran user and grant SELECT permissions by running the following SQL commands. Replace <username> and password with a username and password of your choice.

CREATE USER <username>@'%' IDENTIFIED WITH mysql_native_password BY 'password';
GRANT EXECUTE ON PROCEDURE mysql.rds_kill to '<username>'@'%';

-- Option 1: Grant user SELECT permission on all tables and columns
GRANT SELECT ON *.* TO <username>@'%';

-- Option 2: Grant user SELECT permission on only specified table and columns 
GRANT SELECT ON <tables/columns> TO <username>@'%';

Finish Fivetran configuration

In your connector setup form, enter a destination schema prefix. This prefix applies to each replicated schema and cannot be changed once your connector is created.
In the Host field, enter your database host's IP (for example, 1.2.3.4) or domain (for example, your-database.cp0rdhwjbsae.us-east-1.rds.amazonaws.com)
Enter your database instance's port number (usually 3306).
Enter the Fivetran-specific user that you created in Step 5.
Enter the password for the Fivetran-specific user that you created in Step 5.
(Hybrid Deployment only) If your destination is configured for Hybrid Deployment, the Hybrid Deployment Agent associated with your destination is pre-selected in the Select an existing agent drop-down menu. To use a different agent, select the agent of your choice, and then select the same agent for your destination.
(Not applicable to Hybrid Deployment) Choose your connection method. If you selected Connect via an SSH tunnel, copy or make a note of the Public Key and add it to the authorized_keys file while configuring the SSH tunnel, and provide the following information:
- SSH hostname (do not use a load balancer's IP address/hostname)
- SSH port
- SSH user
- If you enabled TLS on your database in Step 1, set the Require TLS through tunnel toggle to ON.
(Binary log only) Enter a unique replica ID for Fivetran. We provide a random replica ID, but you can provide your own if you'd prefer or if the setup form's replica ID conflicts with one of your existing replica IDs.
(Optional for Hybrid Deployment) If you want to use a TLS connection between the connector and your source database in your own environment, set the Require TLS toggle to ON.
IMPORTANT: Before you set this toggle to ON, you must first enable TLS on your database. Learn how in the Security section of the MySQL reference manual for your database version.
Click Save & Test. Fivetran tests and validates our connection to your Amazon RDS for MySQL database. Upon successful completion of the setup tests, you can sync your data using Fivetran.

Setup tests

Fivetran performs the following tests to ensure that we can connect to your Amazon RDS for MySQL database and that it is properly configured:

The Connecting to SSH Tunnel Test validates the SSH tunnel details you provided in the setup form. It generates a pop-up window where you must verify the SSH fingerprint. It then checks that we can connect to your database using the SSH Tunnel. (We skip this test if you are connecting directly.)
The Connecting to Host Test verifies that the database host is not private and checks that we can connect to the host.
The Validating Certificate Test generates a pop-up window where you must choose which certificate you want Fivetran to use. It then validates that certificate and checks that we can connect to your database using TLS. (We skip this test if you are connecting using an SSH tunnel and did not choose to require TLS.)
The Validating Database User Test validates the database credentials you provided in the setup form.
(Binary log only) The Checking Database Configuration Test verifies that we can find your database's server ID. It then checks your binary log configuration and confirms that we can connect to the binary log.
The Validating Database Type Test checks that your database type matches the connector type. For example, this test will generate a warning if you try to set up a generic MySQL connector with a Amazon RDS for MySQL database. To perform this test, the Fivetran user needs permission to access the mysql system database, though we do not sync any data from that DB.
(Binary log only) The Checking Binlog Retention Period Test verifies that your binary log is set to retain at least 1 day's worth of changes.
The Validating Speed Setup test checks how quickly Fivetran can fetch data from your source database. During this test, we measure our ability to download sample data from your source database to Fivetran, but we do not perform a historical sync. We start a timer, then download the sample data in memory. We then calculate the connector speed based on how much data we downloaded and how long it took to download. The test shows a warning if the download speed is less than 5MB/sec.