Generic MySQL Destination Setup Guide

Follow our setup guide to connect MySQL database as a destination to Fivetran.

Prerequisites

To connect MySQL to Fivetran, you need the following:

• MySQL version 5.5 or above (5.5.40 is the earliest version tested)
• Database host's IP (e.g., 1.2.3.4) or host (your.server.com)
• Port (usually 3306)
• Database administrator permissions to create a Fivetran-specific MySQL user
• A Fivetran role with the Create Destinations or Manage Destinations permissions
• Provide at least 1024MB for innodb_buffer_pool_size. For more information about innodb_buffer_pool_size, see MySQL's Buffer Pool documentation.
• Set the local_infile system variable to ON. For more information about local_infile, see Server System Variables documentation. Check the variable status with SHOW GLOBAL VARIABLES LIKE 'local_infile' and switch the status to ON with SET GLOBAL local_infile = true.

Setup instructions

Choose your deployment model

Before setting up your destination, decide which deployment model best suits your organization's requirements. This destination supports both SaaS and Hybrid deployment models, offering flexibility to meet diverse compliance and data governance needs.

See our Architecture documentation to understand the use cases of each model and choose the model that aligns with your security and operational requirements.

NOTE: You must have an Enterprise or Business Critical plan to use the Hybrid Deployment model.

Choose connection method

IMPORTANT: Do not perform this step if you want to use the Hybrid Deployment model for your data pipeline.

Decide whether to connect to your MySQL database directly, using an SSH tunnel, or using private link. For more information, see our destination connection options documentation.

Create Fivetran user

Create a Fivetran user in your MySQL database:

1. Open a connection to your MySQL database using your SQL tool (MySQL Workbench or the mysql command line interface).

2. Execute the following query to create a user for Fivetran. Choose a memorable username (for example, fivetran). Replace <password> with a password of your choice:

   CREATE USER fivetran@'%' IDENTIFIED BY 'password';
   GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, ALTER, CREATE TEMPORARY TABLES, CREATE VIEW ON *.* TO fivetran@'%';
   

Complete Fivetran configuration

1. Log in to your Fivetran account.

2. Go to the Destinations page and click Add destination.

3. Enter a Destination name of your choice and then click Add.

4. Select MySQL as the destination type.

5. (Enterprise and Business Critical accounts only) Select the deployment model of your choice:

   • SaaS Deployment
   • Hybrid Deployment

6. If you choose Hybrid Deployment, select an existing Hybrid Deployment Agent in the Select an existing agent drop-down menu or configure a new agent.

   NOTE: For more information about configuring a new agent, see our Hybrid Deployment setup guides.

7. In the destination setup form, enter the Host name or the IP address of the database server.

8. Enter the Port number. For example, 3306.

9. Enter the User name you created in Step 3.

10. Enter the Password you created in Step 3.

11. (Not applicable to Hybrid Deployment) Choose your Connection method:

    • Connect directly
    • Connect via an SSH
    • Connect via PrivateLink

12. (Not applicable to Hybrid Deployment) If you choose Connect via an SSH tunnel in the Connection method drop-down menu, enter the following details:

    • SSH Host
    • SSH Port
    • SSH User
    • (Optional) Enable the Require TLS through tunnel toggle if you want to use TLS.

13. (Not applicable to Hybrid Deployment) Choose the Data processing location. Depending on the plan you are on and your selected cloud service provider, you may also need to choose a Cloud service provider and cloud region as described in our Destinations documentation.

    IMPORTANT: If you are using AWS PrivateLink, Azure Private Link, or Google Cloud Private Connect Service, select the corresponding Cloud service provider.

14. Choose your Time zone.

15. (Optional for Business Critical accounts) To enable regional failover, set the Use Failover toggle to ON, and then select your Failover Location and Failover Region. Make a note of the IP addresses of the secondary region and safelist these addresses in your firewall.

16. Click Save & Test.

Fivetran tests and validates the MySQL destination connection. On successful completion of the setup tests, you can sync your data using Fivetran connectors to the MySQL destination.

In addition, Fivetran automatically configures a Fivetran Platform Connector to transfer the connector logs and account metadata to a schema in this destination. The Fivetran Platform Connector enables you to monitor your connectors, track your usage, and audit changes. The connector sends all these details at the destination level.

IMPORTANT: If you are an Account Administrator, you can manually add the Fivetran Platform Connector on an account level so that it syncs all the metadata and logs for all the destinations in your account to a single destination. If an account-level Fivetran Platform Connector is already configured in a destination in your Fivetran account, then we don't add destination-level Fivetran Platform Connectors to the new destinations you create.

Setup tests

Fivetran performs the following MySQL connection tests:

• The SSH Tunnel Test validates the SSH tunnel details you provided in the setup form and then checks the connectivity to the instance using the SSH Tunnel if you are connecting using an SSH tunnel.
• The Database Host Connection Test validates the database credentials you provided in the setup form. The test verifies that the host is not private and then checks the connectivity to the host.
• The Database Certificate Validation Test generates a pop-up window where you must choose which certificate you want Fivetran to use. The test then validates that certificate and checks that we can connect to your database using TLS. We skip this test if you aren't connecting directly.
• The Permission Test checks that we have the correct permissions to create schemas and tables in your database.
• The Warehouse User Test checks if:
  • we can access the TABLES, COLUMNS, and KEY_COLUMN_USAGE tables in the INFORMATION_SCHEMA
  • the local_infile variable is ON
  • the innodb_buffer_pool_size is greater than 1024 MB

Related articles

description Destination Overview

assignment Release Notes

settings API Destination Configuration

home Documentation Home