Azure MySQL Database Setup Guide link
NOTE: You can connect Fivetran to a read replica of your Azure MySQL database to ensure that we don't add any strain to your master database. Many production environments include a read replica by default; if yours doesn't, you can create one.
Follow these instructions to replicate your Azure MySQL database to your destination via Fivetran.
Prerequisiteslink
To connect your MySQL database to Fivetran, you need:
- MySQL version 5.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.
- Your database host's IP (for example,
1.2.3.4
) or domain (your.server.com
) - Your database's port (usually
3306
) - 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.
For the prerequisites connecting via an SSH tunnel, see Fivetran's Connection Options page.
Setup instructionslink
Choose connection methodlink
First, decide whether to connect Fivetran to your Azure MySQL database directly, through an SSH tunnel, using Azure Private Link, or using Proxy Agent. How you configure security groups will differ based on your connection method.
Connect directly (TLS required)link
IMPORTANT: You must have TLS enabled on your database to connect directly to Fivetran.
Fivetran connects directly to your database instance.
Connect via SSHlink
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 contained within an inaccessible subnet. If you have an SSH connection, follow these instructions before you proceed to the next step.
Connect using Azure Private Link Betalink
IMPORTANT: You must have a Business Critical plan to use Azure Private Link.
Azure Private Link allows Virtual Networks (VNets) and Azure-hosted or on-premises services to communicate with one another without exposing traffic to the public internet. Learn more in Microsoft's Azure Private Link documentation.
Follow our Azure PrivateLink setup guide to configure Private Link for your database.
Connect using Proxy Agent Betalink
Fivetran connects to your database through the Proxy Agent, providing secure communication between Fivetran processes and your database host. The Proxy Agent is installed in your network and creates an outbound network connection to the Fivetran-managed SaaS.
To learn more about the Proxy Agent, how to install it, and how to configure it, see our Proxy Agent documentation.
Choose incremental sync mechanismlink
To keep your data up to date after the initial sync, we use one of the following incremental sync methods:
- Binary log
- Fivetran Teleport Sync
Each of these methods keeps a record of recent data changes, which allows Fivetran to update only the data that has changed since our last sync.
To learn the differences between the two mechanisms, see our incremental update documentation.
Enable accesslink
You must configure your firewall to grant Fivetran access to your database.
In the Azure console, select your Azure MySQL resource.
Open the Connection security tab.
If you are connecting directly, create a new firewall rule using Fivetran's IP as both the Start IP and End IP. If you are connecting through SSH, use your tunnel server's IP.
Click Save.
Create user and configure incremental updateslink
Next, create a Fivetran user for your Azure MySQL database. If you are connecting Fivetran to a read replica, you must create the user on your master database because read replicas are read-only. Your read replica will inherit the user from your master database.
How you create a user depends on which incremental update mechanism you are using. Follow the instructions below for your incremental update mechanism Step 2:
WARNING: This user must be reserved for Fivetran use only and must be unique to your connector. For more information, see our MySQL documentation.
Binary loglink
Open a connection to your Azure 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
<username>
and'password'
with 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.
If there are errors when running these commands, you may lack sufficient privileges and should contact your database administrator.
Verify that your binary log format is ROW (Azure MySQL's default format). If not, set the binary log format to ROW in your Azure portal. Fivetran needs ROW format binary logs to perform incremental updates. You only need to configure the binary log format on the databases that you want to connect to Fivetran.
Configure replica for history modelink
If you have connected Fivetran to a read replica and plan to run your connector on history mode, do the following:
Connect to your read replica as a user with
SUPER
privileges.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
, do the following:Run the following commands to update the
slave_parallel_type
andslave_preserve_commit_order
values.SET GLOBAL slave_parallel_type = 'LOGICAL_CLOCK'; SET GLOBAL slave_preserve_commit_order = 1;
Next, connect to your master instance as a user with
SUPER
privileges, then run the following command to set thebinlog_order_commits
to1
.SET GLOBAL binlog_order_commits = 1;
Learn more about these variables in MySQL's replica server variables documentation.
Fivetran Teleport Synclink
Open a connection to your MySQL primary 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 SELECT permissions by running the following SQL commands. Replace
<username>
andpassword
with a username and password of your choice.CREATE USER <username>@'%' IDENTIFIED WITH mysql_native_password BY 'password'; -- Option 1: Grant user SELECT permission on all tables and columns GRANT SELECT ON *.* TO <username>@'%'; -- Option 2: Grant user SELECT permission on only specified table and columns GRANT SELECT ON <tables/columns> TO <username>@'%';
If there are errors when running these commands, 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 (for example,
1.2.3.4
) or domain (for example,your.server.com
).Enter your database instance's port number (usually
3306
).In the User field, enter
<username>@<servername>
, where<servername>
is part of your Azure host URL:<servername>.database.windows.net
.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.
(Binary log only) 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 Azure MySQL database. Upon successful completion of the setup tests, you can sync your data using Fivetran.
Setup testslink
Fivetran performs the following tests to ensure that we can connect to your Azure 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.
- (Binary log only) 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 an Azure MySQL connector with a MySQL RDS database. To perform this test, the Fivetran user needs permission to access the
mysql
system database, though we do not sync any data from that DB. - The Validating Speed Setup test checks how quickly Fivetran can fetch data from your source database. During this test, we measure our ability to download sample data from your source database to Fivetran, but we do not perform a historical sync. We start a timer, then download the sample data in memory. We then calculate the connector speed based on how much data we downloaded and how long it took to download. The test shows a warning if the download speed is less than 5MB/sec.
NOTE: The tests may take a few minutes to finish running.
Related articleslink
description Connector Overview
account_tree Schema Information
settings API Connector Configuration