MySQL Setup Guidelink
Follow these instructions to replicate your generic MySQL database to your destination using Fivetran.
Prerequisiteslink
To connect your MySQL database to Fivetran, you need:
- MySQL version 5.1.5 or above for non-RDS databases (5.5.40 is the earliest version tested). MySQL version 5.6.13 or above for RDS databases.
- IP (for example, 1.2.3.4) or host (your.server.com)
- Port (usually 3306)
For the prerequisites connecting using an SSH tunnel, see Fivetran's Connection Options page.
Replica ID
The replica ID is a unique ID within the MySQL replica set. It must be an integer different from all other master and replica server IDs within the same group. By default, the replica ID is a random integer greater than 1000.
Allow port accesslink
Fivetran can connect directly to your master database. Because we use the same internal system as a read replica, we have the same load impact. If you're concerned about unnecessary strain on your master database, you can connect Fivetran to a read replica instead. Most production deployments already have a replica; if yours doesn't, follow MySQL's documentation to create one.
If you're connecting directly (your database is not behind a publicly inaccessible VPC), configure your firewall and/or other access control systems to allow incoming connections from Fivetran's IP to your MySQL port (usually 3306
) .
If your MySQL database is behind a publicly inaccessible VPC, connect to Fivetran using an SSH tunnel. 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 3306
). We do not support connecting using Load Balancer.
Configure serverlink
Configure your server according to the instructions below before you click the Next button.
-
Access your MySQL server configuration file (usually
/etc/my.cnf
). -
Make sure this file has the following lines in the mysqld section to enable ROW format binary log replication. We need ROW format binary log replication to perform incremental updates.
[mysqld] binlog-format=ROW log-bin=mysql-binlog server-id=123456789 expire-logs-days=1 log-slave-updates=1
- The name of the binary log doesn't have to be
mysql-binlog
. - If your configuration already has a
log-bin
entry, you don't need to change it. - If your configuration already has a
server-id
entry, you don't need to change it. Otherwise, choose any number between 1 and 4294967295 as theserver-id
. - Set the log expiration to a minimum of one day. We recommend setting the log expiration to seven days.
- The name of the binary log doesn't have to be
-
Restart your MySQL server for these changes to take effect.
Create userlink
Next, you must create a Fivetran user in your MySQL master database. You can't create this user on a read replica, because the replica is read-only. When you create a user in the master database, it is automatically replicated to the replica.
WARNING: This user must be reserved for Fivetran use only and must be unique to your connector. For more information, see our MySQL documentation.
-
Open a connection to your MySQL database using your favorite SQL tool (for example, MySQL Workbench or the
mysql
command in your operating system's terminal window). -
Create a Fivetran user and grant replication permissions by running the following SQL commands. Replace
'password'
with a password of your choice.
CREATE USER fivetran@'%' IDENTIFIED WITH mysql_native_password BY 'password';
GRANT SELECT, REPLICATION CLIENT, REPLICATION SLAVE ON *.* TO fivetran@'%';
Make sure these commands complete without any errors. If there are errors, you may lack sufficient privileges and should contact your database administrator.
Configuring a MySQL read replica (optional)link
You can connect Fivetran to a read replica if you're concerned about overloading your master database. Most production environments already have a read replica; if yours doesn't, follow MySQL's documentation to create one.
Configure replica for history mode (optional)link
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
slave_parallel_workers
value.-
If the
slave_parallel_workers
value is0
, you do not need to any additional configuration. -
If the
slave_parallel_workers
value is not0
, run the following commands to update theslave_parallel_type
andslave_preserve_commit_order
values.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
binlog_order_commits
value to1
.SET GLOBAL binlog_order_commits = 1;
-
Learn more about these variables in MySQL's replica server variables documentation.
Select schema and tableslink
Select which schemas and tables you'd like to sync to your destination.
Related articleslink
description Connector Overview
account_tree Schema Information
settings API Connector Configuration