Amazon Aurora MySQL Setup Guide

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.

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.

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.

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.

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

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

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

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. In the left menu, go to the Databases tab.

11. Select your Amazon Aurora MySQL primary/writer database, then click Modify.

    

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

    

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

14. Select Apply Immediately.

    

15. Click Modify DB Instance.

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

    

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

    

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

    

19. 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 primary 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);
   

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.

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

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

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

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

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