Amazon Aurora MySQL Setup Guide

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

Prerequisites

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

MySQL version 5.6.13 or above.
NOTE: We recommend using an upgraded version of Aurora MySQL. For versions older than 1.22.5, there is a known issue with intermittent connection loss that can affect your connector's performance. Learn how Aurora MySQL versions compare to MySQL versions in Aurora's major versions documentation.
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.

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 Aurora MySQL database 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 Aurora MySQL TLS setup instructions to enable TLS on your database.

Fivetran connects directly to your Amazon Aurora 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 Aurora MySQL 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.

NOTE: We only use PrivateLink or SSH tunnel to connect to your database with Aurora serverless v1.

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.

NOTE: Fivetran Teleport Sync is the only method available for Amazon Aurora MySQL serverless v1.

Enable database access

Find endpoint and port

Find the endpoint and port for the database that you want to connect to Fivetran.

In your Amazon RDS dashboard, click on the Amazon Aurora MySQL database that you want to connect to Fivetran.
NOTE: The database's role must be "writer."
(If you're connecting directly) In the Connectivity & security section, ensure that the Publicly Accessibility setting is Yes.
IMPORTANT: If you're connecting using SSH, skip this step. You do not have to make your database publicly accessible.
Find the endpoint and port and make a note of them. You will need them to configure Fivetran.

Configure security group

Configure your Amazon Aurora MySQL cluster's VPC security group to allow Fivetran to access the cluster.

In the Security column, click the link to your database's security group.
On the Security Groups page, click on the security group ID.
On the Inbound tab, click Edit inbound rules.
Click Add Rule. This creates a new Custom TCP Rule at the bottom of the list.
Fill in the new Custom TCP Rule.
- In the Port Range field, enter your database's port number that you copied in the previous section.
- What you enter in the Source Custom IP field depends on whether you're connecting directly or using SSH.
  - If you're connecting directly, enter Fivetran's IPs for your database's region.
  - If you're connecting using SSH, 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 is not in a VPC, skip ahead to Step 4.

Return to the instance details page.
In the Connectivity and security section, click the link to the VPC.
Click the VPC ID.
In the Details section, click the Network ACL link.
Click the Network ACL ID.

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 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.) For additional help, see Amazon's Network ACL documentation.
- 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.

Edit outbound rules

Select Outbound Rules.
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 Amazon Aurora MySQL primary database, create a database user for Fivetran's exclusive use.

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

Continue your setup based on your incremental sync method that you selected in 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 by executing the following SQL command. Replace <username> and password with a username and password of your choice.
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.
```
CREATE USER <username>@'%' IDENTIFIED BY 'password';
GRANT SELECT, REPLICATION CLIENT, REPLICATION SLAVE ON *.* TO <username>@'%';
GRANT EXECUTE ON PROCEDURE mysql.rds_kill to '<username>'@'%';
GRANT SELECT ON mysql.rds_heartbeat2 TO <username>@'%';
GRANT SELECT ON mysql.rds_configuration 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.
Make sure these commands complete without any errors. If there are errors, you may lack sufficient privileges and should contact your database administrator.

Configure binary logging

Configure the primary node in your Amazon Aurora cluster to output binary logs. We need binary logs to perform incremental updates.

NOTE: Grant Fivetran's data processing servers access to your primary/writer database. You must connect Fivetran to your primary/writer database because AWS does not allow you to activate binary logging on an Aurora MySQL reader. We require binary logs to perform incremental updates.

Enable binary logging

In your RDS dashboard, go to your Aurora cluster.
On the Configuration tab, note the name of the DB cluster parameter group. If the name begins with default., it is a default parameter group. Otherwise, it is a custom group.
In the left menu, go to the Parameter groups tab.
Do one of the following to create a new parameter group:
- (Option 1) Click Create parameter group.
  On the Create parameter group page, ensure that the Parameter Group Family value matches the one in your existing default parameter group. Set the Type to "DB Cluster Parameter Group." Give the new group a name and description, then click Create.
- (Option 2) Select your existing cluster parameter group, click Parameter group actions, then select Copy.
  On the Copy DB parameter group page, give the new group a name and description, then click Copy.
Click on the link to the new parameter group (whether you created a new group or copied an existing group). Make sure the Type is "DB Cluster Parameter Group." If not, revisit the previous step.
Click on the name of the new parameter group.
Select the binlog_format parameter, then click Edit parameters.
Change the binlog_format value to ROW.
Click Save changes.
In the left menu, go to the Databases tab.
Select your Amazon Aurora MySQL primary/writer database, then click 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.
At the bottom of the page, click Continue.
Select Apply Immediately.
Click Modify DB Instance.
To make the changes to take effect, you must reboot the DB instance. Select your database, then click Actions > Reboot.
Click Confirm to confirm that you want to reboot the instance.
Wait for the primary node to reboot. The instance's Status will change from "rebooting" to "available" when it is done.
The configuration change will be complete when your database's DB Cluster Parameter Group reflects the new parameter group.

Set binary log retention period

Update your binary log retention period. By default, Amazon Aurora databases cull binary log files as quickly as possible. However, Fivetran cannot perform incremental updates unless your database retains binary logs for at least 24 hours.

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).
Use the function below to view your current settings.
```
CALL mysql.rds_show_configuration;
```
If your binlog retention period is less than 24 hours or if the result of this query is NULL, proceed to the next step to increase your retention period.
NOTE: If the result of this query is NULL, your database has Aurora's default behavior and your database culls binary log files as quickly as possible. If we cannot sync a log before it's deleted, we will need to re-sync your database in full, which can cause significant downtime.
Increase your binary log retention period. Your retention period must be at least 24 hours, though we recommend a retention period of 7 days (168 hours). Use the sample queries below to update your binary log retention period.
IMPORTANT: Increasing the binary log retention period requires additional disk space in your source Amazon Aurora MySQL database, since it increases how many log files are stored at one time.
Set your binlog retention period to 24 hours:
```
CALL mysql.rds_set_configuration('binlog retention hours', 24);
```
or set your binary log retention period to 7 days (168 hours).
```
CALL mysql.rds_set_configuration('binlog retention hours', 168);
```

Create a daily event to rotate binary logs (optional)

Binary logs must be rotated at least once per retention period. Binary log rotation keeps the latest row changes from being written into an expired binary log file that is scheduled for deletion. Your Amazon Aurora MySQL database automatically rotates a binary log when it reaches the maximum size; however, the log does not rotate if it does not reach the maximum size because the database has had too little write activity.

To ensure that your logs rotate frequently enough, you can create an event that automatically rotates binary logs every 24 hours.

Execute the following SQL statement to turn on the event scheduler.
NOTE: The event scheduler runs custom events on an automatic schedule.
```
SET GLOBAL event_scheduler = ON;
```
Execute the following SQL statements to create a custom event that rotates (flushes) the binary logs and schedule it to run every 24 hours.
```
DROP EVENT IF EXISTS rotate_binlog_24_hrs_event;

CREATE EVENT rotate_binlog_24_hrs_event
    ON SCHEDULE EVERY 24 hour
    DO FLUSH BINARY LOGS;
```
NOTE: If you have a low-activity database, you must have at least two binary log files at all times. Having two binary log files ensures that your binary log data isn't rotated before Fivetran can perform an incremental sync. Check your binary log files by looking at the output of SHOW BINARY LOGS in your Amazon Aurora MySQL database.

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';

-- 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>@'%';

Make sure these commands complete without any errors. If there are errors, you may lack sufficient privileges and should contact your database administrator.

Using Aurora Parallel Query (Aurora V1 and V2 only)

Fivetran Teleport Sync can utilize Aurora's Parallel Query feature to improve performance. Fivetran will enable it for the connection session when performing a sync, but we cannot guarantee it will be used because the Aurora query optimizer determines whether to invoke parallel query for a particular query. Review the AWS docs on Aurora Mysql Parallel Query Limitations for details.

Finish Fivetran configuration

In your connector setup form, enter a Destination schema prefix. This is used as the connector name and cannot be modified once the connector is created.
NOTE: Depending on your selection in the Destination schema names field, we will either prefix the connector name to each replicated schema or use the source schema names instead.
In the Host field, enter the endpoint you found in Step 3. Alternatively, you can enter your database host's IP (for example, 1.2.3.4).
Enter your database instance's port number that you found in Step 3. The port will be 3306, unless you changed the default.
Enter the Fivetran-specific user that you created in Step 4.
Enter the password for the Fivetran-specific user that you created in Step 4.
(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, 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.
(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.
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.
Private PreviewIn the Destination schema names field, select Fivetran naming or Source naming to determine how the schema names appear in your destination. For more information, see the Schema information section.
Click Save & Test. Fivetran tests and validates our connection to your Amazon Aurora 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 Aurora 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.
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 an Amazon Aurora 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.
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.