Azure MySQL Database Setup Guide

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

Enable database accesslink

You must configure your firewall to grant Fivetran access to your database.

Open the SQL database firewall settings in the Azure consolelink

1. In the Azure console, select your Azure MySQL resource.

2. Open the Connection security tab.

Add a new firewall rulelink

1. If you are connecting directly, create a new 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.

2. Click Save.

CREATE USER <username>@'%' IDENTIFIED WITH mysql_native_password BY 'password';
GRANT SELECT, REPLICATION CLIENT, REPLICATION SLAVE ON *.* TO
<username>@‘%’;

Verify binary log format is set to ROW (binary log only)link

Azure MySQL’s binary log format is set to ROW by default. Verify that your binary log format is ROW. 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.

Select schema and tableslink

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

Choose a schema prefixlink

Fivetran maps the schemas we discover in your source database to your destination and prepends the destination schema names with a prefix of your choice. For example, if your original database contains schemas foo and bar and if you choose the prefix pre, then your destination schemas will be pre_foo and pre_bar.

Enter user and password in setup formlink

In your Fivetran setup form, enter your user and password.

• For the User, enter <username>@<servername>, where <servername> is part of your Azure host URL: <servername>.database.windows.net.
• For the Password, enter the password you set when you created the Fivetran user.

Configure replica (history mode only)link

If you have connected Fivetran to a read replica and plan to run your connector on history mode, do the following:

1. Connect to your read replica as a user with SUPER privileges.

2. Check your read replica’s slave_parallel_workers value.

   • If the slave_parallel_workers value is 0, you do not need to any additional configuration.

   • If the slave_parallel_workers value is not 0, do the following:

     Run the following commands to update the slave_parallel_type and slave_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 the binlog_order_commits to 1.

     SET GLOBAL binlog_order_commits = 1;
     

Learn more about these variables in MySQL’s replica server variables documentation.

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 full 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 Contentlink

description Connector Overview

account_tree Schema Information

assignment Release Notes

settings API Connector Configuration

home Documentation Home