Amazon Aurora MySQL Setup Guidelink

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

---

Prerequisiteslink

IMPORTANT: We do not support serverless Aurora.

To connect your Amazon Aurora 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. Fivetran provides 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 instructionslink

Choose connection methodlink

First, decide whether to connect Fivetran to your Amazon Aurora MySQL database directly or using an SSH tunnel. 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.

Find endpoint and portlink

Find the endpoint and port for the database that you want to connect to Fivetran. You must connect Fivetran to your master/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.

1. 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."

   

2. (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.

   

3. Find the endpoint and port and make a note of them. You will need them to configure Fivetran.

   

Enable database accesslink

Grant Fivetran's data processing servers access to your master/writer database.

Configure security groups

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

1. In the Security column, click the link to your database's security group.

   

2. On the Security Groups page, click on the security group ID.

   

3. On the Inbound tab, click Edit inbound rules.

   

4. Click Add Rule. This creates a new Custom TCP Rule at the bottom of the list.

5. 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.

   

6. Click Save rules.

Configure Network ACLs (VPC only)

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

1. Return to the instance details page.

2. In the Connectivity and security section, click the link to the VPC.

   

3. Click the VPC ID.

   

4. In the Details section, click the Network ACL link.

   

5. Click the Network ACL ID.

   

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

Edit inbound rules

1. Select Inbound Rules.

   

2. 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.

   

3. 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**

1. Select Outbound Rules.

   

2. 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.

   

3. 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 userlink

In your Amazon Aurora MySQL master 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.

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

2. Create a user for Fivetran and grant replication permissions by executing the following SQL command. Choose a memorable name (for example, fivetran). Replace password with a password of your choice.

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

Configure binary logginglink

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

Enable binary logging

1. In your RDS dashboard, go to your Aurora cluster.

2. 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.

   

3. In the left menu, go to the Parameter groups tab.

   

4. 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.

     

5. 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.

   

6. Click on the name of the new parameter group.

7. Select the binlog_format parameter, then click Edit parameters.

   

8. Change the binlog_format value to ROW.

   

9. Click Save changes.

10. 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 step 11 of this section.

    • If the slave_parallel_workers value is not 0, set the binlog_order_commits value to 1.

      

      Next, log in to your read replica as a user with SUPER privileges and run the following commands.

      SET GLOBAL slave_preserve_commit_order = 1;
      SET GLOBAL slave_parallel_type = 'LOGICAL_CLOCK';
      

      Learn more about these variables in MySQL's replica server variables documentation.

11. In the left menu, go to the Databases tab.

12. Select your Amazon Aurora MySQL master/writer database, then click Modify.

    

13. 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.

    

14. At the bottom of the page, click Continue.

15. Select Apply Immediately.

    

16. Click Modify DB Instance.

17. To make the changes to take effect, you must reboot the DB instance. Select your database, then click Actions > Reboot.

    

18. Click Confirm to confirm that you want to reboot the instance.

    

19. Wait for the master node to reboot. The instance's Status will change from "rebooting" to "available" when it is done.

    

20. 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.

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

2. 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.

3. 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);
   

Finish Fivetran configurationlink

1. 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.

2. In the Host field, enter the endpoint you found in Step 2. Alternatively, you can enter your database host's IP (for example, 1.2.3.4).

3. Enter your database instance's port number that you found in Step 2. The port will be 3306, unless you changed the default.

4. Enter the Fivetran-specific user that you created in Step 4 (for example, fivetran).

5. Enter the password for the Fivetran-specific user that you created in Step 4.

6. 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.

7. Enter your database's replica ID.

8. Click Save & Test . Fivetran will take it from here and sync your data from your Amazon Aurora MySQL database.

---

Related articleslink

description Connector Overview

account_tree Schema Information

assignment Release Notes

settings API Connector Configuration

home Documentation Home