MySQL

MySQL is an open-source relational database. Fivetran's integration platform replicates data from your MySQL source database and loads it into your destination.

NOTE: Fivetran supports MySQL as both a database connector and a destination.

Fivetran offers the following solutions for replicating data from your MySQL source database:

• Fivetran native connector
• HVR - MySQL

Supported services

Fivetran supports the following MySQL database services:

• Generic MySQL
• Amazon Aurora MySQL
• Amazon Aurora Serverless v2 MySQL
• Amazon Aurora Serverless v1 MySQL
• Amazon RDS MySQL
• Azure Database for MySQL
• Google Cloud SQL for MySQL

Supported configurations

Fivetran supports the following MySQL configurations:

Which MySQL instance types we can connect to depends on your database service:

Features

Setup guide

This overview will give you a general idea of the kind of work needed to set up a MySQL connector. For specific instructions on how to set up your database, see the guide for your MySQL database type:

• Generic MySQL
• Amazon Aurora MySQL
• Amazon RDS MySQL
• Azure Database for MySQL
• Google Cloud SQL for MySQL

In your primary database, you need to do the following:

• Allow access to your MySQL database using Fivetran's IP
• (Optional) Allow access to a read replica of your MySQL database if you do not want to connect Fivetran to your production instance
• Create a Fivetran-specific MySQL user with read-level and other replication permissions as mentioned in the setup guides above.

  WARNING: This user must be reserved for Fivetran use and unique to your connector.

  • Reserved: Create a user for Fivetran's exclusive use. At the beginning of each sync, a Fivetran user attempts to kill any zombie processes it left behind on previous syncs. If you try to run any operations as this user, they may be killed.
  • Unique: If you are creating multiple connectors targeting the same source database, create a unique user for each connector. If you set up multiple connectors with the same Fivetran user, they can kill one another's connections, slowing down your sync unnecessarily. NOTE: The Fivetran MySQL connector only supports the authentication plugin caching_sha2_passwordover TLS.

• If you connect Fivetran to a multithreaded replica and plan to run your connector on history mode, configure your read replica to allow history mode to run.

Sync overview

Once Fivetran is connected to your MySQL primary database or read replica, we pull a full dump of all selected data from your database. We then connect to your binary log to pull all your new and changed data at regular intervals. The binary log is the same change tracking mechanism that MySQL uses to perform its own replication for backups. If data in the source changes (for example, you add new tables or change a data type), Fivetran automatically detects and persists these changes into your destination.

Syncing empty tables and columns

Fivetran can sync empty tables and columns for your MySQL connector. An empty table has no rows, and an empty column has no data. A table that contains zero columns is invalid and can't be synced. For more information, see our Features documentation.

Schema information

Fivetran tries to replicate the exact schema and tables from your MySQL source database to your destination according to our standard database update strategies. For every schema in the MySQL database that you connect, we create a schema in your destination that maps directly to its native schema. This ensures that the data in your destination is in a familiar format to work with.

Fivetran-generated columns

Fivetran adds the following columns to every table in your destination:

• _fivetran_deleted (BOOLEAN) marks rows that were deleted in the source database.
• _fivetran_synced (UTC TIMESTAMP) indicates the time when Fivetran last successfully synced the row.
• _fivetran_index (INTEGER) shows the order of updates for tables that do not have a primary key.
• _fivetran_id (STRING) is the hash of the non-Fivetran values of each row. It's a unique ID that Fivetran uses to avoid duplicate rows in tables that do not have a primary key.

We add these columns to give you insight into the state of your data and the progress of your data syncs. For more information about these columns, see our System Columns and Tables documentation.

Type transformations and mapping

As we extract your data, we match MySQL data types to types that Fivetran supports. If we don't support a certain data type, we automatically change that type to the closest supported type or, for some types, don't load that data at all. Our system automatically skips columns with data types that we don't accept or transform.

The following table illustrates how we transform your MySQL data types into Fivetran supported types:

If we are missing an important data type that you need, reach out to support.

In some cases, when loading data into your destination, we may need to convert Fivetran data types into data types that are supported by the destination. For more information, see the individual data destination pages.

Unparsable values

When we encounter an unparsable value of one of the following data types, we substitute it with a default value. Which default value we use depends on whether the unparsable value is in a primary key column or non-primary key column:

Although we may be able to read some values outside the supported DATE, DATETIME, and TIMESTAMP ranges as defined by MySQL's documentation, there is no guarantee. Additionally, the special zero value 0000-00-00 00:00:00 is subject to this rule.

Supported character sets

Fivetran supports the following character sets in MySQL:

Using an unsupported character set will cause your sync to fail.

Excluding source data

If you don’t want to sync all the data from your primary database, you can exclude schemas, tables, or columns from your syncs on your Fivetran dashboard. To do so, go to your connector details page and uncheck the objects you would like to omit from syncing. For more information, see our Data Blocking documentation.

Alternatively, you can change the permissions of the Fivetran user you created and restrict its access to certain tables or columns.

IMPORTANT: We recommend blocking or hashing data in the Fivetran dashboard instead of restricting permissions. If you exclude a column by restricting the Fivetran user's permissions, your sync logs will indicate that the number of columns in the destination does not match the number of columns in the source table - in the reason field of the data object of the forced_resync_table log event.

Note that column permissions will not shield confidential data, such as PII (Personally Identifiable Information), from Fivetran because they do not apply to binary logs (binlogs). Using your binlogs, Fivetran has access to the full contents of any changed rows, including columns that have no SELECT permissions. However, we filter out the prohibited columns at the earliest possible stage of our syncs and do not load them into your destination.

Unsupported schemas

Fivetran does not support the following system schemas:

• performance_schema
• information_schema
• mysql
• innodb
• looker_scratch
• Percona
• tmp

Initial sync

When Fivetran connects to a new database, we first copy all rows from every table in every schema for which we have SELECT permission (except those you have excluded in your Fivetran dashboard) and add Fivetran-generated columns. Tables are copied in ascending size order (from smallest to largest). We copy rows by performing a SELECT statement on each table. For large tables, we copy a limited number of rows at a time so that we don't have to start the sync over from the beginning if our connection is lost midway.

The duration of initial syncs can vary depending on the number and size of tables to be imported. We therefore interleave incremental updates with the table imports during the initial sync.

Updating data

Fivetran performs incremental updates of any new or modified data from your source database. We use one of the following incremental sync methods for incremental updates: binary log and Fivetran Teleport Sync.

Both methods keep a record of recent data changes, which allows Fivetran to update only the data that has changed since our last sync.

WARNING: Switching the incremental sync method will require a historical re-sync to guarantee data integrity.

Binary log

NOTE: We recommend the binary log method because it provides the most efficient mechanism for capturing change data from the database.

Fivetran uses your binary logs (also called binlogs) to request only the data that has changed since our last sync. We require the binlogs to be in row format, so that they contain a separate event in each row. We pull INSERT, UPDATE, and DELETE events from your binlogs at regular intervals and upload them to your destination.

How we load UPDATE events into your destination depends on whether or not the table has a primary key. To find out which of your tables have primary keys, run this query in your source database:

SELECT
  max(tc.CONSTRAINT_TYPE = 'PRIMARY KEY') IS NOT NULL
  AND max(tc.CONSTRAINT_TYPE = 'PRIMARY KEY') = TRUE AS has_primary_key,
  c.TABLE_SCHEMA                                     AS table_schema,
  c.TABLE_NAME                                       AS table_name
FROM information_schema.COLUMNS c
  LEFT JOIN information_schema.KEY_COLUMN_USAGE k ON k.TABLE_SCHEMA = c.TABLE_SCHEMA AND
                                             k.TABLE_NAME = c.TABLE_NAME AND
                                             k.COLUMN_NAME = c.COLUMN_NAME
  LEFT JOIN information_schema.TABLE_CONSTRAINTS tc ON tc.TABLE_SCHEMA = k.TABLE_SCHEMA AND
                                             tc.TABLE_NAME = k.TABLE_NAME AND
                                             tc.CONSTRAINT_NAME = k.CONSTRAINT_NAME
  LEFT JOIN information_schema.TABLES t ON t.TABLE_SCHEMA = c.TABLE_SCHEMA AND
                                             t.TABLE_NAME = c.TABLE_NAME
WHERE t.TABLE_TYPE = 'BASE TABLE'
  AND c.TABLE_SCHEMA NOT IN ('performanceschema', 'informationschema', 'mysql', 'innodb', 'lookerscratch', 'tmp')
GROUP BY c.TABLE_SCHEMA, c.TABLE_NAME ORDER BY has_primary_key, c.TABLE_SCHEMA, c.TABLE_NAME;

Setup guide

In your primary database, you need to do the following:

• Enable ROW format binary log replication
• Set the binlog_row_image binary logging system variable to FULL in global scope
• Set binary log expiration to a minimum of one day (24 hours). We recommend setting the log expiration for seven days.

  WARNING: Do not purge the binary log files manually. If the binary log files that the connectors look for are missing, the sync will fail and you will need to re-sync to fix the issue.

Limitations

Corrupted binary log files cause incremental updates to fail. Our updates fail because we cannot parse the entry events of corrupted binary log files, which is a limitation of MySQL database technology. A binary log can be corrupted when it is manually edited, the size of the event is more than 1 GB, or the network is unreliable. To avoid corrupted binary logs, do the following:

• Do not edit the binary log files manually.
• Write binlog events that are smaller than 1 GB.
• Make sure that the size of the event is less than the max_allowed_packet.

You can retry a failed incremental sync by manually triggering a full re-sync of the table.

NOTE: Full table re-syncs increase your MAR. To learn more, see our resync pricing documentation.

GTID mode

We also support replication using global transaction identifiers (GTIDs). In GTID mode, we stream incremental updates from your database using each transaction's GTID instead of its binary log position. Using GTIDs has the following advantages over normal binary log replication:

• You do not have to re-sync your connector if you switch to another replica with identical data
• Your database can failover to another replica with identical data without a binary log mismatch
• You do not have to manually rotate logs if you have a low-activity connector where all logs expire because of low transaction activity

GTID mode still uses your binary logs, so you must follow the binary log setup steps in your MySQL connector setup guide. In addition, you must set the GTID_MODE configuration parameter to ON in your source database (or for MariaDB, set GTID_STRICT_MODE to ON). GTID mode cannot function with GTID_MODE set to ON_PERMISSIVE. If GTID_MODE isn't set to ON in our database, we fall back to using only your binary log positions as described in the binary log section.

Learn more about GTIDs in MySQL's Replication with Global Transaction Identifiers documentation.

Replication filtering

Fivetran supports MySQL replicas configured to use replication filtering. Replication filtering may be beneficial if your database produces a large volume of binary logs, but you only want to sync a small portion of those events from your database (for example, include a single schema or exclude a high-volume schema or table).

This option may not be supported on hosted MySQL services such as Google Cloud SQL for MySQL, Amazon RDS MySQL, Amazon Aurora MySQL, or Azure Database for MySQL because it requires SUPER privileges to configure. See the documentation for your host's version of MySQL for more configuration information.

For maximum compatibility with Fivetran while using replication filtering, we recommend connecting to a MySQL replica and only configuring replicate-* options. For example, if you have multiple schemas but you want Fivetran to see and replicate only the my-example schema, you can configure a replication filter REPLICATE_DO_DB = 'my-example' using either the CHANGE REPLICATION FILTER statement, the MySQL command line arguments, or the my.cnf configuration properties for this filter. Creating a replication filter ensures the MySQL replica ignores binary log events for all other schemas in the main MySQL database. The MySQL replica still requires binary logging to be enabled, so you must follow the binary log setup steps in your MySQL connector setup guide. Once configured, the MySQL replica contains only the schemas and tables configured (or excludes the configured schemas and tables if you use one of the *-ignore-* options). The Fivetran connector connected to the MySQL replica has access to and replicates only those schemas and tables in the MySQL replica.

Fivetran Teleport Sync

Fivetran Teleport Sync is a proprietary incremental sync method that can incrementally replicate your database with no additional setup other than a read-only SQL connection.

Use Fivetran Teleport Sync when the binary log method is not an option for the following reasons:

• You cannot or do not want to provide Fivetran access to your database's change logs
• You need to limit the tables or columns that Fivetran can read from
• Binary log syncing is slow or resource intensive due to the number of updates
• You load data into the source database using a TRUNCATE/LOAD or BACKUP/RESTORE process that invalidates change logs
• You currently use snapshot syncing without incremental updates and want a faster method

Fivetran Teleport Sync's queries perform the following operations on your database:

• Do a full table scan of each synced table
• Perform calculations on all values in each synced table's rows
• Aggregate a compressed table snapshot in the database's memory

We do not support syncing more than 400 tables or tables with more than 20 million rows with Fivetran Teleport Sync for MySQL.

For optimum Fivetran Teleport Sync performance, we recommend that you make the following resources available in your database:

• 1 GB Free RAM
• 1 Free CPU Core
• IOPS (Teleport Sync times decrease linearly with an increase of available IOPS)

Private Preview Fivetran Teleport Sync supports syncing tables without primary keys in Private Preview for existing connectors. All new connectors have this ability enabled automatically. Reach out to Fivetran Support if you're interested in trying this functionality for existing connectors.

IMPORTANT: Tables without a primary key can have many exact duplicates of a row, but duplicate rows create data discrepancy issues in Fivetran Teleport Sync. We generate a _fivetran_id column for tables without a primary key, so many identical source rows may share a _fivetran_id. In that case, only one copy of the duplicate rows is synced to the destination.

Fivetran Teleport Sync for MySQL supports the following data types:

IMPORTANT: Teleport for MySQL also supports tables with composite primary keys.

Tables with a primary key

We merge changes to tables with primary keys into the corresponding tables in your destination:

• An INSERT in the source table generates a new row in the destination with _fivetran_deleted = FALSE.
• A DELETE in the source table updates the corresponding row in the destination with _fivetran_deleted = TRUE.
• An UPDATE in the source updates the data in the corresponding row in the destination.

Tables without a primary key

NOTE: Fivetran Teleport Sync support for tables without a primary key is currently in Private Preview for existing connectors. All new connectors have this ability enabled automatically.

Fivetran Teleport Sync performs the following steps to sync tables without a primary key:

• Copy the table to a TEMPORARY TABLE on the MySQL source server. This table includes a generated primary key column, _fivetran_id. Because this value depends on the values of the other columns in the row, any duplicate rows in the original table are removed.
• Use the TEMPORARY TABLE to sync the data to the destination.
• Once the sync is complete, drop the TEMPORARY TABLE.

NOTE: To sync tables without a primary key, Fivetran Teleport Sync requires the Fivetran database user to have the CREATE TEMPORARY TABLES permission.

For the binary log method, we handle changes to tables without a primary key differently:

• An INSERT in the source table generates a new row in the destination with _fivetran_deleted = FALSE.
• The _fivetran_id column helps us handle DELETE operations:
  • If there is a row in the destination that has a corresponding _fivetran_id value, that row will be updated with _fivetran_deleted = TRUE.
  • If there is not a row in the destination that has a corresponding _fivetran_id value, a new row will be added with _fivetran_deleted = TRUE.
• An UPDATE in the source table is treated as a DELETE followed by an INSERT, so it results in two rows in the destination:
  • A row containing the old values with _fivetran_deleted = TRUE
  • A row containing the new values with _fivetran_deleted = FALSE

As a result, one record in your source database may have several corresponding rows in your destination. For example, suppose you have a products table in your source database with no primary key:

You load this table into your destination during your initial sync, creating this destination table:

You then update a row:

UPDATE products SET quantity = 4 WHERE description = 'Cookie robot';

After your UPDATE operation, your destination table will look like this:

You then delete a row:

DELETE FROM products WHERE description = 'Boogie robot';

After your DELETE operation, your destination table will look like this:

So, while there may be just one record in your source database where description = Cookie robot, there are two in your destination - an old version where _fivetran_deleted = TRUE, and a new version where _fivetran_deleted = FALSE.

We also de-duplicate rows before we load them into your destination. We use the _fivetran_id field, which is the hash of the non-Fivetran values in every row, to avoid creating multiple rows with identical contents. If, for example, you have the following table in your source:

Then your destination table will look like this:

Deleted rows

We don't delete rows from the destination, though the way for how we process deletes differs for tables with primary keys and tables without a primary key. We handle deletes as part of streaming changes from the binlog. Note that we only process DELETE events from the binlog.

Deleted columns

We don't delete columns from your destination. When a column is deleted from the source table, we replace the existing values in the corresponding destination column with NULL values.

Unsupported MySQL commands

Fivetran does not support several MySQL commands:

• DROP (and re-creating the dropped table with CREATE)
• LOAD
• RENAME
• ON DELETE CASCADE
• ON UPDATE CASCADE

If you use any of these unsupported commands to delete or update the records in a table, those records won't be replicated correctly in your destination.

Adding and dropping columns

When you add or drop a column, we attempt to automatically migrate your destination schema to the new table structure. In some cases, we won't be able to do this and instead perform an automatic re-sync of the changed table.

In the following scenarios, Fivetran will re-sync your table instead of automatically migrating it:

• Adding or dropping a column on a table without a primary key
• Changing column order
• Changing primary keys
• Modifying ENUM or SET columns
• Adding or dropping a column using a DDL statement that we're unable to parse properly

NOTE: For tables in History mode with a DROP COLUMN DDL applied, updates to the dropped column that occur during the DDL-containing sync (but before the DDL statement is processed) will appear as NULL.

Renaming tables or columns

Renaming involves following three queries:

ALTER TABLE tbl_name RENAME [TO | AS] new_tbl_name
ALTER TABLE tbl_name CHANGE [COLUMN] old_col_name new_col_name column_definition
        [FIRST | AFTER col_name]
ALTER TABLE tbl_name RENAME COLUMN old_col_name TO new_col_name

When you rename a table, we reimport the table as a new table, but we will not soft delete the old table in the destination. When you rename a column, both the old column and new column will appear in the table. We only update data in the new column moving forward.

Adding a column in Block All mode

When our connector detects a new column in a source table, it automatically performs a schema migration on that table unless you've chosen to block all newly-added tables and columns from syncing. If you excluded newly-added tables and columns from your syncs, you must manually re-sync your table whenever you add a new column.

Migrating service providers

If you want to migrate service providers, we will need to do a full re-sync of your data because the new service provider won't retain the same change tracking data as your original MySQL database.