MySQL as Source
1 Applicable Scenarios
This document applies to scenarios where you migrate data in a self-built MySQL instance or a Cloud Database RDS MySQL instance to a DTS-supported data target using Baidu AI Cloud Data Transmission Service (DTS).
2 Restrictions on Using MySQL Database as DTS Source
- If the primary key or unique constraint does not exist for the table to migrate in the source database and all fields are not unique, there may be duplicate data in the target database.
- If you want to perform incremental migration, you need to enable binlog at the source end and set the binlog_format parameter value as “ROW”.
- If you want to perform incremental migration, you need to set the binlog_row_image parameter value at the source end as “FULL” under the version of MySQL 5.6 and above.
- If you want to perform incremental migration, you need to set the server_id parameter value at the source end as an integer larger than 1.
3 Prerequisites for Using MySQL Database as DTS Source
3.1 Environment Requirements
DTS allows you to use a database of MySQL 5.1, 5.5, 5.6, 5.7, or 8.0 version as a source.
3.2 Authorization of Database Account
| Database | Structure Migration | Total Migration | Incremental migration |
|---|---|---|---|
| If the source MySQL version is 5.1, | SELECT,SHOW VIEW,LOCK TABLES,SUPER | SELECT,SHOW VIEW,LOCK TABLES,SUPER | SELECT,SHOW VIEW,LOCK TABLES,SUPER,REPLICATION SLAVE |
| If the source MySQL version is 5.5 or above | SELECT,SHOW VIEW,LOCK TABLES,REPLICATION CLIENT | SELECT,SHOW VIEW,LOCK TABLES,REPLICATION CLIENT | SELECT,SHOW VIEW,LOCK TABLES,REPLICATION CLIENT,REPLICATION SLAVE |
4 Application of MySQL Database as DTS Source
For the operation process of task creation, task configuration, pre-check, task start, task pause, and task termination during the use of a MySQL database as the source, see Best Practice Documentation. The Task Configuration Parameters section is different from other data sources.
Linking Setup
As shown above, the DTS source end supports a self-built MySQL instance and a Cloud database RDS for MySQL instance. Taking the self-built MySQL instance in the public network as an example, the instructions for configuration parameters at the source end are as follows:
Access Type: “Public Network” indicates the self-built MySQL instance by the public network.
Data Type: Select the “MySQL” permanently.
CVM Server Name/IP: Enter the access IP of the MySQL database.
Port: Enter the service port of the MySQL database.
Account: Enter the database account of the MySQL database.
Password: Enter the password corresponding to the account of the MySQL database
After you enter the information above, click the “Test Connectivity” button to verify whether you enter the correct information.
Object Mapping
Click the “Authorize a Whitelist to Enter Next Step” button to enter the Select Migration Objects page.
- On this page, you can combine Structure Migration, Total Migration, and Incremental Synchronization as needed.
- If you select the Incremental Synchronization, you can designate the SQL statement type synchronized in the incremental synchronization process. The SQL statement types available for your selection include INSERT, UPDATE, DELETE, and DDL. Besides, if you select the synchronization of DDL statements, you can designate whether to filter particular DDL synchronization, such as DROP and TRUNCATE statements, as shown in the following figure.
- The transmission object includes two types for your selection: “Whole Instance” and “Manual Selection”. Suppose you select “Manual Selection”. In that case, you need to select a database table you want to migrate on the left side of the page so that the database table information may be added to the right side of the page automatically. Then, click the “Edit” button next to the database table names to set up database table name mapping, row filtering, blacklist/whitelist column filtering, and other features, as shown in the following figure.
- After you finish the configurations, click “Save and Pre-check” to pre-check.
5 MySQL Source Data Types Supported by DTS
The following table lists MySQL source data types supported by DTS and default mapping with the DTS data types. For any information on how to view the data types mapped at the target, see Data Type Mapping of Target Data Sources.
| MySQL Database Type | DTS Data Type | Whether DTS supports or not |
|---|---|---|
| BOOLEAN | DTS_TYPE_INT1 | Yes |
| TINYINT | DTS_TYPE_INT1 | Yes |
| SMALLINT | DTS_TYPE_INT2 | Yes |
| MEDIUMINT | DTS_TYPE_INT4 | Yes |
| INT | DTS_TYPE_INT4 | Yes |
| BIGINT | DTS_TYPE_INT8 | Yes |
| DECIMAL | DTS_TYPE_DECIMAL | Yes |
| FLOAT | DTS_TYPE_FLOAT | Yes |
| BIT | DTS_TYPE_BYTES (8) | Yes |
| YEAR | DTS_TYPE_INT2 | Yes |
| DATE | DTS_TYPE_DATE | Yes |
| TIME(fsp) | DTS_TYPE_STRING | Yes |
| DATETIME(fsp) | DTS_TYPE_DATETIME(fsp) | Yes |
| TIMESTAMP(fsp) | DTS_TYPE_DATETIME(fsp) | Yes |
| CHAR(N) | DTS_TYPE_STRING(N) | Yes |
| VARCHAR(N) | DTS_TYPE_STRING(N) | Yes |
| BINARY(N) | DTS_TYPE_BYTES(N) | Yes |
| VARBINARY(N) | DTS_TYPE_BYTES(N) | Yes |
| TINYTEXT | DTS_TYPE_STRING(255) | Yes |
| TEXT | DTS_TYPE_STRING(65,535) | Yes |
| MEDIUMTEXT | DTS_TYPE_STRING(16,777,215) | Yes |
| LONGTEXT | DTS_TYPE_STRING(4,294,967,295) | Yes |
| TINYBLOB | DTS_TYPE_BYTES (255) | Yes |
| BLOB | DTS_TYPE_BYTES (65,535) | Yes |
| MEDIUMBLOB | DTS_TYPE_BYTES (16,777,215) | Yes |
| LONGBLOB | DTS_TYPE_BYTES (4,294,967,295) | Yes |
| SET | - | Supported only when the downstream is MySQL |
| ENUM | - | Supported only when the downstream is MySQL |
| JSON | DTS_TYPE_STRING(4,294,967,295) | Yes |