Azure Database for MariaDB Setup Guide

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

Prerequisites

To connect your MariaDB database to Fivetran, you need:

MariaDB 10.1.2 or above*
Your database host's IP (e.g., 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 MariaDB replica set.
(If you're using TLS) Your server must support at least TLSv1.0, but we recommend TLSv1.2 or above

* MariaDB versions earlier than 10.1.2 may function correctly with the exception of fractional seconds in TIME, TIMESTAMP, and DATETIME columns. Prior to version 10.1.2, fractional seconds were stored differently, which causes problems with our incremental updates.

Setup instructions

IMPORTANT: Do not perform the Choose connection method step if you want to use Hybrid Deployment for your data pipeline.

Choose incremental sync method

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 methods, see our incremental sync documentation.

Choose connection method

First, decide whether to connect Fivetran to your MariaDB database directly, using an SSH tunnel, or using Proxy Agent.

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 MariaDB reference manual for your database version.

Fivetran connects directly to your MariaDB 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 MariaDB port (usually 3306) from Fivetran's IPs for your database's region. How you do this will vary based on how your MariaDB 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 port (usually 3306).

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 MariaDB reference manual for your database version.

Connect using Proxy Agent

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.

Create read replica (optional)

Expand for instructions

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 MariaDB'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 slave_parallel_threads value.
- If the slave_parallel_threads value is 0, you do not need to any additional configuration.
- If the slave_parallel_threads value is not 0, you must set the slave_parallel_mode value to optimistic, conservative, aggressive or minimal to use in-order parallel replication. Learn more about parallel replication in MariaDB's parallel replication documentation.

Configure server (binary log only)

Expand for instructions

Open a connection to your MariaDB database's server.
Access your MariaDB server configuration file (usually /etc/my.cnf).
Ensure that this file has the following lines in the mariadb section. These lines enable ROW format binary log replication, which Fivetran needs to perform incremental updates.
```
[mariadbd]
binlog-format=ROW
log-bin=mariadb-bin
server-id=123456789
expire-logs-days=1
log-slave-updates=1
```
- Name the binary log (for example, mariadb-bin).
- 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 MariaDB server to effect these changes.

Create user

In your MariaDB 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.

How you create a user depends on which incremental sync method you are using. Follow the instructions below for your incremental sync method.

WARNING: This user must be reserved for Fivetran's use only and must be unique to your connector. For more information, see our MariaDB setup overview.

Binary log

Open a connection to your MariaDB 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 replication permissions by running the following SQL commands. Replace <username> and password with a username and password of your choice.
```
CREATE USER <username>@'%' IDENTIFIED BY 'password';
GRANT SELECT, REPLICATION CLIENT, REPLICATION SLAVE ON *.* TO <username>@'%';
```
Make sure these commands complete without any errors. If there are errors, you may lack sufficient privileges and should contact your database administrator.

Fivetran Teleport Sync

Open a connection to your MariaDB 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> and password with a username and password of your choice.

CREATE USER <username>@'%' IDENTIFIED 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>@'%';

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 configuration

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.
(Hybrid Deployment only) If your destination is configured for Hybrid Deployment, the Hybrid Deployment Agent associated with your destination is pre-selected in the Select an existing agent drop-down menu. To use a different agent, select the agent of your choice, and then select the same agent for your destination.
(Not applicable to Hybrid Deployment) Choose your connection method. If you selected Connect via an SSH tunnel, copy or make a note of the Public Key and add it to the authorized_keys file while configuring the SSH tunnel, and 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 2, 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.
(Optional for Hybrid Deployment) If you want to use a TLS connection between the connector and your source database in your own environment, set the Require TLS toggle to ON.
IMPORTANT: Before you set this toggle to ON, you must first enable TLS on your database. Learn how in the Security section of the MariaDB reference manual for your database version.
Click Save & Test. Fivetran tests and validates our connection to your MariaDB database. Upon successful completion of the setup tests, you can sync your data using Fivetran.

Setup tests

Fivetran performs the following tests to ensure that we can connect to your generic MariaDB 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 MariaDB connector with a MariaDB database instance in Amazon RDS.
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.

Connector Overview

Schema Information

Release Notes

API Connector Configuration

Documentation Home