Follow these instructions to replicate your generic MySQL database to your destination using Fivetran.
To connect your MySQL database to Fivetran, you need:
- MySQL version 5.5 or above
NOTE: If you upgrade your database version to 8.0.23 or above, you must set the
MINIMALbefore you do the upgrade. If you set
MINIMALafter the upgrade, your existing MySQL connectors will fail and you may need to re-sync them.
- Your database host’s IP (e.g.,
220.127.116.11) or domain (
- Your database’s port (usually
- 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.
Choose connection methodlink
First, decide whether to connect Fivetran to your MySQL database directly or using an SSH tunnel.
NOTE: We do not support connecting to a load balancer.
Connect directly (TLS required)
IMPORTANT: You must have TLS enabled on your database to connect directly to Fivetran. Learn how to enable TLS on your database in the Security section of the MySQL reference manual for your database version.
Fivetran connects directly to your MySQL database. This is the simplest method.
If you connect directly, configure your firewall and/or other access control systems to allow incoming connections to your MySQL port (usually
3306) from Fivetran’s IPs for your database’s region. How you do this will vary based on how your MySQL database is hosted (cloud platform, on-premises, etc.)
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.
To connect using SSH, configure your firewall and/or other access control systems to allow incoming connections from your SSH tunnel server’s IP address to your MySQL port (usually
Before you proceed to the next step, you must follow our SSH connection instructions. If you want Fivetran to tunnel SSH over TLS, you must first enable TLS on your database. Learn how in the Security section of the MySQL reference manual for your database version.
Create read replica (optional)link
If you’d like, create a read replica for Fivetran’s exclusive use. Using a read replica allows Fivetran to integrate your data without putting extra strain on your primary database. Because we use the same internal system as a read replica, we have the same load impact.
Most production deployments already have a read replica. If yours doesn’t, follow MySQL’s instructions to create one.
Configure replica for history mode (optional)
If you have connected Fivetran to a read replica and plan to run your connector on history mode, do the following:
Open a connection to your read replica.
Check your read replica’s
0, you do not need to any additional configuration.
slave_parallel_workersvalue is not
0, run the following commands to update the
SET GLOBAL slave_parallel_type = 'LOGICAL_CLOCK'; SET GLOBAL slave_preserve_commit_order = 1;
Open a connection to your master database, then run the following command to set the
SET GLOBAL binlog_order_commits = 1;
Learn more about these variables in MySQL’s replica server variables documentation.
Open a connection to your MySQL database’s server.
Access your MySQL server configuration file (usually
Ensure that this file has the following lines in the mysqld section. These lines enable ROW format binary log replication, which Fivetran needs to perform incremental updates.
[mysqld] binlog-format=ROW log-bin=mysql-binlog server-id=123456789 expire-logs-days=1 log-slave-updates=1
- Name the binary log (for example,
- If your configuration already has a
log-binentry, you don’t need to change it.
- If your configuration already has a
server-identry, you don’t need to change it. Otherwise, choose any number between 1 and 4294967295 as the
- Set the log expiration to a minimum of one day. We recommend setting the log expiration to seven days.
- Name the binary log (for example,
Restart your MySQL server to effect these changes.
In your MySQL primary database, create a database user for Fivetran’s exclusive use. You cannot create a user in the read replica because it is read-only. Once you create the user in the primary database, it will automatically be replicated to the replica.
WARNING: This user must be reserved for Fivetran’s use only and must be unique to your connector. For more information, see our MySQL setup overview.
Open a connection to your MySQL primary database using your favorite SQL tool (for example, MySQL Workbench or the
mysqlcommand in your operating system’s terminal window).
Create a Fivetran user and grant replication permissions by running the following SQL commands. Replace
passwordwith a username and password of your choice.
CREATE USER <username>@'%' IDENTIFIED WITH mysql_native_password BY 'password'; GRANT SELECT, REPLICATION CLIENT, REPLICATION SLAVE ON *.* 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.
Make sure these commands complete without any errors. If there are errors, you may lack sufficient privileges and should contact your database administrator.
Finish Fivetran configurationlink
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.
In the Host field, enter your database host’s IP (e.g.,
18.104.22.168) or domain (e.g.,
Enter your database instance’s port number. 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.
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.
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.
Click Save & Test. Fivetran tests and validates our connection to your MySQL database. Upon successful completion of the setup tests, you can sync your data using Fivetran.
Fivetran performs the following tests to ensure that we can connect to your generic 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 fail if you try to set up a generic MySQL connector with a MySQL RDS database.
NOTE: The tests may take a few minutes to finish running.