MySQL Betalink
WARNING: MySQL database can fail to perform basic queries for even medium volumes of data and is not appropriate as a data warehouse. We support MySQL data warehouse as a test environment. If you run into these limitations, you will need to migrate to a supported destination.
Fivetran supports MySQL data warehouse as a destination. MySQL is not typically used as a data warehouse because it is a row-based database more suited to transactional use-cases. Large analytical querying is much faster on columnar warehouses.
Setup requirementslink
Please make sure the following items are true before getting setup or make sure you have the permissions and ability to configure the following before going through the setup process:
- Version 5.5 or above (5.5.40 is the earliest version tested)
- IP (e.g. 1.2.3.4) or host (your.server.com)
- Port (usually 3306)
- Access to your MySQL Database via Fivetran's IP
- The
innodb_buffer_pool_size
should at least be 1024MB. Refer The InnoDB Buffer Pool to configure theinnodb_buffer_pool_size
. - The system variable
local_infile
should be set to ON. Refer local_infile for more details. You can check the status viaSHOW GLOBAL VARIABLES LIKE 'local_infile'
and switch it on viaSET GLOBAL local_infile = true
. - A Fivetran-specific MySQL user with proper permissions
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, ALTER, CREATE TEMPORARY TABLES, CREATE VIEW ON *.* TO fivetran@'%';
Type transformation mappinglink
The data types in your MySQL warehouse follow Fivetran's standard data type storage.
We use the following data type conversions:
Fivetran Data Type | Destination Data Type | Notes |
---|---|---|
BOOLEAN | BOOLEAN | |
SHORT | SMALLINT | |
INT | INTEGER | |
LONG | BIGINT | |
FLOAT | REAL | |
DOUBLE | DOUBLEPRECISION | |
BIGDECIMAL | DECIMAL | |
LOCALDATE | DATE | |
INSTANT | TIMESTAMP | For MySQL version 5.7 or later, we convert INSTANT to TIMESTAMP(6). MySQL's TIMESTAMP data type range begins with 1970-01-01 00:00:01.000000 and ends with 2038-01-19 03:14:07.999999 . We convert an epoch TIMESTAMP of 1970-01-01 00:00:00 to 1970-01-01 00:00:01 because there are often primary keys with this value. We convert an epoch TIMESTAMP beyond 2038-01-19 03:14:07.999999 to null because primary keys rarely have such values and futuristic dates capped at 2038 can be misleading. |
LOCALDATETIME | DATETIME | |
STRING | LONGTEXT or VARCHAR(b) | We convert STRING:
|
JSON | JSON | For versions prior to MySQL version 5.7.8, we convert JSON to TEXT because previous versions don't support JSON. |
BINARY | LONGBLOB or VARBINARY(b) | We convert BINARY:
|
Setup instructionslink
Fivetran supports connecting with different MySQL implementations. We have specific instructions for connecting to each:
Primary key creationlink
Fivetran creates primary keys for a MySQL warehouse in either of the following scenarios:
-
MySQL version earlier than 5.7.7, or MariaDB version earlier than 10.2:
- If the total number of primary keys provided by the source is less than 16, and the sum of the byte length of String primary keys is less than 191.
-
MySQL version 5.7.7 and later, or MariaDB version 10.2 and later:
- If the total number of primary keys provided by the source is less than 16, and the sum of the byte length of String primary keys is less than 768.
Data load costslink
Whether you are self-hosting MySQL or using a managed service, you will not be charged extra when we load data into your warehouse.