Configure a MySQL data source
MySQL is an open-source relational database management system that efficiently stores, manages, and queries large amounts of data. It supports multiple operating systems and is widely used in all kinds of software and website development.
Offline Sync plans in the AE DataOps Platform support reading (Reader) from and writing (Writer) to MySQL data sources. This article describes the data synchronization capabilities supported for MySQL.
Supported versions
mysql 5.5+
Limitations
Currently, only the following configuration is supported:
- Reading from MySQL data sources (offline read) and writing to AE Built-in Warehouse workspace tables
- Writing from AE Built-in Warehouse workspace tables to MySQL (offline write)
Note: Writing directly from a MySQL database to databases other than the AE Built-in Warehouse isn't supported yet.
Supported field types
| Field type | Offline read (MySQL Reader) | Offline write (MySQL Writer) |
|---|---|---|
| TINYINT | Supported | Supported |
| SMALLINT | Supported | Supported |
| INTEGER | Supported | Supported |
| BIGINT | Supported | Supported |
| FLOAT | Supported | Not supported |
| DOUBLE | Supported | Supported |
| DECIMAL | Supported | Supported |
| DATE | Supported | Supported |
| DATETIME | Supported | Supported |
| TIMESTAMP | Supported | Supported |
| REAL | Supported | Not supported |
| VARCHAR | Supported | Supported |
| JSON | Supported | Not supported |
| TEXT | Supported | Not supported |
| MEDIUMTEXT | Supported | Not supported |
| LONGTEXT | Supported | Not supported |
| ENUM | Supported | Not supported |
| SET | Supported | Not supported |
| BIT | Supported | Not supported |
| TIME | Supported | Not supported |
| YEAR | Supported | Not supported |
| VARBINARY | Not supported | Not supported |
| BINARY | Not supported | Not supported |
| TINYBLOB | Not supported | Not supported |
| MEDIUMBLOB | Not supported | Not supported |
| LONGBLOB | Not supported | Not supported |
| Blob | Not supported | Not supported |
| BOOLEAN | Not supported | Not supported |
| MULTIPOLYGON | Not supported | Not supported |
| LINESTRING | Not supported | Not supported |
| POLYGON | Not supported | Not supported |
| MULTIPOINT | Not supported | Not supported |
| MULTILINESTRING | Not supported | Not supported |
| GEOMETRYCOLLECTION | Not supported | Not supported |
Create a MySQL data source
In the DataOps Platform - Integration module, you can choose to add a MySQL data source.
Data source configuration parameters
Fill in the configuration required by the data source and pass the connectivity test to create the MySQL data source.
Parameters whose names start with * are required; parameters without * are optional.
| Field name | Description |
|---|---|
| Basic Information | |
| *Datasource Name | Must be unique within the DataOps Platform space. Can contain only letters, digits, and underscores, and can't start with a digit or an underscore |
| Remarks | Optional |
| Data source configuration | |
| not distinguish environment / Independent setting | Choose one: not distinguish environment means the production and development environments share one configuration; Independent setting means the two environments are configured independently |
| *Connection string | A connection string is a combination of strings used to establish a connection to a MySQL database Note: Parsing parameters in the string isn't supported yet |
| *Database | Name of the MySQL database to connect to |
| *Username | Username with permission to access the database |
| *Password | Password of the username |
| Advanced | Other advanced parameters required to connect to the database; you can customize them |
Advanced parameter blocklist
To keep integration secure, the following parameters can't be configured in Advanced.
| Parameter name | Purpose | Security pitfall |
|---|---|---|
| allowLoadLocalInfile | Purpose: Controls whether files can be loaded from the client machine (the server that runs your Java application) into the MySQL database through the LOAD DATA LOCAL INFILE SQL statement. | Security pitfall: This is an extremely high-risk parameter. If it's enabled, a malicious MySQL server (or a compromised legitimate server) can request arbitrary files from the client when the client connects. An attacker can fake a MySQL server; when your application tries to connect, it sends a LOAD DATA LOCAL request, and the JDBC driver unconditionally sends sensitive files on the client machine (such as /etc/passwd, ~/.ssh/id_rsa, application configuration files, and database connection settings) to the attacker's server. This is commonly known as exploiting the "MySQL client file read vulnerability." |
| allowUrlInLocalInfile | Purpose: Controls whether the LOAD DATA LOCAL INFILE statement can specify a URL (such as http://, ftp://) as the file source. | Security pitfall: This parameter amplifies the risk of
|
| allowLoadLocalInfileInPath | Purpose: A security-enhanced alternative to allowLoadLocalInfile (requires a newer driver version). Instead of allowing arbitrary files to be read, it restricts file reading to the specified directory and its subdirectories. | Security pitfall: If the configured path scope is too broad (such as /tmp), or if attackers can write files to the allowed path through vulnerabilities in other applications, they can still read those files. A misconfiguration (such as /) makes it equivalent to allowLoadLocalInfile=true. |
| initCommand | Purpose: Lets you specify an SQL statement that runs immediately after each new connection is created. It's commonly used to set session variables, such as SET NAMES 'utf8mb4' or SET time_zone = '+8:00' | Security pitfall: The risk comes from control over user input. If the value of this parameter is built from external user input (rather than a safe string hard-coded in the code), it can lead to SQL injection. Once an attacker can control initCommand in the connection string, they can perform any SQL operation they have permission for, such as dropping tables, modifying data, or adding users. |
| clientFlags | Purpose: A low-level parameter that sets the capability flags the client uses during the handshake with the MySQL server. It's a bitmask whose numeric value enables or disables specific client capabilities. | Security pitfall: The risk is that it may contain dangerous flags, in particular:
|
These restrictions follow the security best practices of the "principle of least privilege" and "defense in depth" and are intended to protect your applications and underlying infrastructure from malicious attacks.
If your business requires any of these features, contact the ThinkingAI operations team for a separate assessment.
Create an offline sync task
After you create the MySQL data source and pass the connectivity test as described above, you can configure offline sync tasks such as MySQL offline read and MySQL offline write for your scenario.
MySQL as the Data Source
Select MySQL as the Data Source and configure the following parameters:
| Field name | Description |
|---|---|
| *Source Type | Select MySQL as the type of the Data Source |
| *Datasource Name | A MySQL data source registered on the data source management page; select it from the drop-down list. If you haven't created the data source yet, click the Data sources management button to create a MySQL data source. |
| *Method |
|
| *Database | Name of the database to read |
| *Table name | Information of the table to read |
| Shard Key | If the source table you select has a sharding field, you can select the shard key |
| Data Filtering | Turn on the data filtering switch to enter a WHERE filter statement in SQL syntax |
| Advanced | Supports parameters such as the batch size |
Parameters whose names start with * are required; parameters without * are optional.
MySQL as the Data Target
Select MySQL as the Data Target and configure the following parameters:
| Field name | Description |
|---|---|
| *Source Type | Select MySQL as the target type of the Data Target |
| *Datasource Name | A MySQL data source registered on the data source management page; select it from the drop-down list. If you haven't created the data source yet, click the Data sources management button to create a MySQL data source. |
| *Database | Name of the database to read |
| *Table name | Information of the table to read |
| *Write Mode |
|
| Advanced | Supports parameters such as the batch size |
Parameters whose names start with * are required; parameters without * are optional.
Field mapping
After configuring the data source and the target, create field mappings. The system automatically syncs data from source fields to target fields based on the mappings. You can configure field mappings in three ways:
- Method 1: Custom selection. Select a source table field, then select the target field in the target table
- Method 2: Name Mapping. The system automatically maps fields with the same name in the source and target tables
- Method 3: Line Mapping. The system automatically maps fields in the same row
Note that each target field can correspond to only one source field
Field type conversion
During data synchronization, the system uses the CAST() function to try to convert source table fields to the types of the target fields. If the conversion fails, the value of the target field is set to NULL
Notes on data type conversion
- √ means the type conversion is supported
- √, null means the type conversion is supported; if the conversion fails, the value is set to NULL
| Source type/Target type | ROW | ARRAY | MAP | STRING | BOOLEAN | TINYINT | SMALLINT | INT | BIGINT | FLOAT | DOUBLE | DECIMAL | BYTES | DATE | TIMESTAMP | TIME |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| ROW | √, null | |||||||||||||||
| ARRAY | √, null | |||||||||||||||
| MAP | √, null | |||||||||||||||
| STRING | √ | √, null | √, null | √, null | √, null | √, null | √, null | √, null | √, null | √, null | √, null | √, null | ||||
| BOOLEAN | √, null | √ | ||||||||||||||
| TINYINT | √, null | √ | √, null | √, null | √, null | |||||||||||
| SMALLINT | √, null | √, null | √ | √, null | √, null | |||||||||||
| INT | √, null | √, null | √, null | √ | √, null | |||||||||||
| BIGINT | √, null | √, null | √, null | √, null | √ | |||||||||||
| FLOAT | √, null | √ | √, null | √, null | ||||||||||||
| DOUBLE | √, null | √, null | √ | √, null | ||||||||||||
| DECIMAL | √, null | √, null | √, null | √ | ||||||||||||
| BYTES | √, null | √ | ||||||||||||||
| DATE | √, null | √ | √, null | |||||||||||||
| TIMESTAMP | √, null | √, null | √ | |||||||||||||
| TIME | √, null | √ |
Basic information settings
Finally, set the basic information of the integration plan, including the plan name, owner, synchronization rate, and remarks.
When you're done, click Save to create the integration plan.
Note: The plan name can't be changed after it's saved
The details page looks like this:
Mount an offline sync plan on a Flow
Mount on Flow
1. Start mounting
- On the integration plan details page, click Mount on task flow in the upper-right corner
2. Select a Flow
-
Select the target Flow from the drop-down menu
-
To create a new Flow:
- Click the New Flow shortcut button below the drop-down menu
- Or go to the Dev module to create one
-
Tip: If the target Flow isn't shown, click the ↻ refresh button on the right
3. Create a sync node
- In the Flow, create a node of the Offline sync plan type
- The node is linked to the current integration plan. When the node runs, it triggers a run of that integration plan
4. Complete mounting
- Click Create node and mount on it to complete the configuration
- After mounting succeeds, click Go to flow page to view the result right away
Note: The task node created by mounting is in the unreleased state. We recommend going to the Flow and releasing the node.
Unmount from a Flow
To unmount an offline sync plan from a Flow:
- If the Flow hasn't been released yet, go to the Flow that the plan is mounted on and delete the Offline sync plan task node in Dev Mode.
- If the Flow has already been released, after deleting the Offline sync plan task node in Dev Mode, release the Flow again. This also unmounts the node from the Production environment.