Follow our setup guide to connect MySQL as a destination to Fivetran.
To connect your MySQL database as destination in Fivetran, you need:
- MySQL version 5.5 or above (5.5.40 is the earliest version tested)
- Database host’s IP (e.g.,
18.104.22.168) or host (
- Port (usually
- Database administrator permissions to create a Fivetran-specific MySQL user
- 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_infilesystem variable to ON. For more infomation 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.
Choose connection method link
Decide whether to connect to your MySQL database directly or using an SSH tunnel. For more information, see our destination connection options documentation.
If you connect directly, you must create a rule in a security group that allows Fivetran access to your database port and instance.
Configure your firewall and/or other access control systems to allow:
Connect using an SSH tunnel
If you connect using an SSH tunnel, Fivetran connects to a separate server in your network that provides an SSH tunnel to your MySQL database. You must configure your tunnel server’s security group to allow Fivetran access and configure the instance’s security to allow access from the tunnel.
You must connect through SSH if your database is contained within an inaccessible subnet.
To connect using SSH, do the following:
In the destination setup form, select the Connect via an SSH tunnel option.
Copy Fivetran’s public SSH key.
Add the public key to the
authorized_keysfile of your SSH server. The key must be all on one line, so make sure that you don’t introduce any line breaks when cutting and pasting.
Create Fivetran userlink
Create a Fivetran user in your MySQL database:
Open a connection to your MySQL database using your SQL tool (MySQL Workbench or the mysql command line interface).
Execute the following query to create a user for Fivetran. Choose a memorable username (for example,
<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 link
- Log in to your Fivetran account.
- Go to the Manage Account page.
- In the Destinations tab, click +Destination.
- On the Add Destination To Fivetran page, enter a destination name of your choice.
- Click Continue.
- Select MySQL as the destination type.
- In the destination setup form, enter the Host name or the IP address of the database server.
- Enter the Port number. For example,
- Enter the User name you created in Step 2.
- Enter the Password you created in Step 2.
- Choose your Connection method. You can choose to Connect directly or Connect via an SSH. If you choose Connect via an SSH tunnel, enter the following details:
- SSH Host
- SSH Port
- SSH User
- (Optional) Enable the Require TLS through tunnel toggle if you want to use TLS.
- 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 AWS region as described in our Destinations documentation.
- Choose your Timezone.
- 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.
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
KEY_COLUMN_USAGEtables in the
local_infilevariable is ON
innodb_buffer_pool_sizeis greater than 1024 MB
- we can access the