MySQL Setup Guide

Follow these instructions to replicate your generic MySQL database to your destination using Fivetran.

Prerequisites

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 access

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 server

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 the server-id.
- Set the log expiration to a minimum of one day. We recommend setting the log expiration to seven days.
Restart your MySQL server for these changes to take effect.

Create user

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.

Select schema and tables

Select which schemas and tables you'd like to sync to your destination.

Creating a MySQL read replica

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.

Documentation