Migrate MySQL data to ApsaraDB for SelectDB - Data Transmission Service

Prerequisites

You have a source ApsaraDB RDS for MySQL instance and a target ApsaraDB for SelectDB instance.

Limitations

Type	Description
Source database	Bandwidth requirement: The server hosting the source database must have sufficient outbound bandwidth. Otherwise, migration speed will drop. Requirements for migration objects: If all tables to be migrated have a primary key or unique constraint: Make sure that the table columns are unique. Otherwise, duplicate data may occur in the destination database. If the migration objects include tables that have no primary key or unique constraint: When you configure the instance, we recommend that you select Schema Migration for Migration Types and, in the Configurations for Databases, Tables, and Columns stage, set the table's Engine to duplicate. Otherwise, the instance may fail or data loss may occur. Note During schema migration, DTS adds an additional column to the destination tables. For more information, see Additional Column Information. If you migrate data at the table level and need to edit the objects, such as by using object name mapping, a single data migration task can migrate a maximum of 1,000 tables. If this limit is exceeded, the task reports an error when you submit it. In this case, split the tables into multiple migration tasks or configure a task to migrate the entire database. If you need incremental migration, enable binary logging: Set binlog_format to ROW and binlog_row_image to FULL. Otherwise, the precheck fails and the task cannot start. Important If your self-managed MySQL source is a dual-master cluster—where each instance acts as both master and slave—enable the log_slave_updates parameter. This ensures DTS can read all binary logs. For RDS for MySQL instances, retain local binary logs for at least three days (seven days recommended). For self-managed MySQL databases, retain local binary logs for at least seven days. If DTS cannot access binary logs, the task fails. In extreme cases, data inconsistency or data loss may occur. Issues caused by binary log retention periods shorter than DTS requires are not covered under the DTS SLA. Note To set the retention period for local binary logs on an RDS for MySQL instance, see Automatically delete local logs. During the schema migration and full migration phases, do not perform DDL operations that change the database or table schema. Otherwise, the data migration task will fail. Note During the full migration phase, DTS queries the source database. This creates a metadata lock, which may block DDL operations on the source database. If you perform only a full data migration, do not write new data to the source database. Otherwise, data inconsistency will occur between the source and destination databases. To maintain real-time data consistency, select schema migration, full data migration, and incremental data migration. DTS does not migrate data generated by changes that do not write to binary logs. Examples include data restored from physical backups or created by cascade operations. Note If this occurs, re-run full migration after your business allows it. If your source MySQL database is version 8.0.23 or later and contains invisible hidden columns, DTS cannot read those columns. This may cause data loss. Note Run `ALTER TABLE <table_name> ALTER COLUMN <column_name> SET VISIBLE;` to make the hidden column visible. For more information, see Invisible Columns.
Other limitations	You can migrate data only to tables that use the Unique or Duplicate model in an ApsaraDB for SelectDB instance. Unique model If the destination table is a Unique model table, make sure that all unique keys in the destination table exist in the source table and are included in the migration objects. Otherwise, data inconsistency may occur. Duplicate model If the destination table is a Duplicate model table, duplicate data may occur in the destination database in the following cases. You can manually deduplicate the data based on additional columns such as _is_deleted, _version, and _record_id. The migration instance was retried. The migration instance was restarted. You performed two or more DML operations on the same row of data after the migration instance started. Note If the destination table is a Duplicate model table, DTS converts UPDATE or DELETE statements into INSERT statements. DTS does not support migrating indexes, partitions, views, procedures, functions, triggers, or foreign keys. When you configure parameters in the Selected Objects box, you can currently only set the `bucket_count` (bucket count) parameter. Note The `bucket_count` parameter can be set only to a positive integer. The default value is auto. During data migration, do not create a cluster in the destination ApsaraDB for SelectDB instance. Otherwise, the task fails. You can restart the migration instance to resume the failed task. ApsaraDB for SelectDB instances support only database and table names that start with a letter. If the name of a database or table to be migrated does not start with a letter, you must use object name mapping to rename it. If the name of a migration object, such as a database, table, or column, contains Chinese characters, you must use object name mapping to rename it, for example, to a name in English. Otherwise, the task may fail. DTS does not support migrating DDL operations that modify multiple columns at once or that perform consecutive modifications on the same table. During data migration, do not add a backend node to the ApsaraDB for SelectDB database. Otherwise, the task fails. You can restart the migration instance to resume the failed task. In a multi-table merge scenario where you migrate data from multiple source tables to a single destination table, ensure that all source tables have the same schema. Otherwise, data inconsistency or task failure may occur. In MySQL, M in the `VARCHAR(M)` type specifies the length in characters. In ApsaraDB for SelectDB, N in the `VARCHAR(N)` type specifies the length in bytes. If you do not use the schema migration feature of DTS, we recommend that you set the length of a VARCHAR column in ApsaraDB for SelectDB to four times its length in MySQL. When you use DMS or gh-ost to perform online DDL operations on the source, DTS migrates only the original DDL statements to the destination. In this scenario, DTS does not need to migrate large volumes of temporary table data, but the operation may cause table locks on the destination. Note DTS does not support migrating online DDL changes made by tools such as pt-online-schema-change on the source. If the source has such changes, data loss or task failure may occur on the destination. Evaluate the performance of the source and destination databases before migrating data. We recommend performing data migration during off-peak hours. During full data migration, DTS consumes read and write resources on both the source and destination databases, which may increase their load. Full data migration runs concurrent INSERT operations, which causes table fragmentation in the destination database. As a result, the table space used in the destination instance is larger than that in the source instance after the full migration is complete. During data migration, if sources other than DTS write data to the destination database, data inconsistency between the source and destination databases may occur. If your RDS for MySQL instance has Always-Encrypted enabled, full migration is not supported. Note RDS for MySQL instances with Transparent Data Encryption (TDE) enabled support schema migration, full migration, and incremental migration. During incremental migration, DTS uses a batch synchronization strategy to reduce the load on the destination. By default, DTS writes to a single synchronization object at most once every 5 seconds. Therefore, DTS migration tasks may experience regular synchronization latency—typically within 10 seconds. To reduce this regular migration latency, modify the DTS instance parameter `selectdb.reservoir.timeout.milliseconds` in the console to adjust the batch interval. The allowed range is [1000, 10000] milliseconds. Note When you adjust the batch interval, a smaller value increases the write frequency of DTS. This may increase the load and write response time (RT) on the destination, which in turn increases DTS synchronization latency. Adjust the value based on the load on the destination. If a task fails, DTS support staff will attempt to restore it within eight hours. During restoration, they may restart the task or adjust its parameters. Note Only DTS task parameters are modified—not database parameters. Parameters that may be adjusted include those listed in Modify instance parameters.
Special cases	For self-managed MySQL sources: A master–standby switchover on the source database causes the migration task to fail. DTS calculates latency by comparing the timestamp of the last record migrated to the destination database with the current time. If no DML operations run on the source for a long time, latency reporting becomes inaccurate. If latency appears too high, run a DML operation on the source to update the latency value. Note If you select full-database migration, create a heartbeat table. Update or write to it every second. DTS periodically runs CREATE DATABASE IF NOT EXISTS `test` on the source database to advance the binary log offset. If your source is Amazon Aurora MySQL or another clustered MySQL instance, ensure the domain name or IP address configured for the task—and its DNS resolution—always points to a read–write (RW) node. Otherwise, the migration task may fail. For RDS for MySQL sources: If you need incremental migration, RDS for MySQL instances that do not record transaction logs—such as RDS for MySQL 5.6 read-only instances—are not supported as sources. DTS periodically runs CREATE DATABASE IF NOT EXISTS `test` on the source database to advance the binary log offset.

Billing

Migration type	Task configuration fee	Public traffic fee
Schema migration and full data migration	Free.	Free in this example. Note An internet data transfer fee applies if the Access Method for the destination database is Public IP Address. For more information, see Billing overview.
Incremental data migration	Billed. For more information, see Billing overview.

Supported SQL for incremental migration

Operation type	SQL statement
DML	`INSERT`, `UPDATE`, and `DELETE`
DDL	`ADD COLUMN` `MODIFY COLUMN` `CHANGE COLUMN` `DROP COLUMN` and `DROP TABLE` `TRUNCATE TABLE` RENAME TABLE Important A RENAME TABLE operation may cause data inconsistency. For example, if you select only one table as the migration object and rename the table in the source instance during migration, the data of this table is not migrated to the destination database. To prevent this issue, select the entire database to which the table belongs as the migration object when you configure the data migration task. Make sure that the databases to which the table belongs before and after the RENAME TABLE operation are both included in the migration objects.

Permission requirements for database accounts

Database	Schema migration	Full data migration	Incremental data migration
Source ApsaraDB RDS for MySQL	SELECT permission	SELECT permission	Read and write permissions
Target ApsaraDB for SelectDB	Cluster access permission (`Usage_priv`) and read and write permissions for the database (`Select_priv`, `Load_priv`, `Alter_priv`, `Create_priv`, and `Drop_priv`)

To create a database account and grant permissions:

ApsaraDB RDS for MySQL instance: See Create an account and Modify account permissions.
ApsaraDB for SelectDB instance: See Cluster permission management and Basic permission management.

Note

If the source database account was created outside the ApsaraDB RDS for MySQL console, ensure it has the REPLICATION CLIENT, REPLICATION SLAVE, SHOW VIEW, and SELECT permissions.

Procedure

Navigate to the migration task list page for the destination region using one of the following methods.
From the DTS console
1. Log on to the Data Transmission Service (DTS) console.
2. In the navigation pane on the left, click Data Migration.
3. In the upper-left corner of the page, select the region where the migration instance is located.
From the DMS console

Note
The actual operations may vary based on the mode and layout of the DMS console. For more information, see Simple mode console and Customize the layout and style of the DMS console.
1. Log on to the Data Management (DMS) console.
2. In the top menu bar, choose Data + AI > Data Transmission (DTS) > Data Migration.
3. To the right of Data Migration Tasks, select the region where the migration instance is located.
Click Create Task to navigate to the task configuration page.

Configure the source and destination databases.

Section	Parameter	Description
N/A	Task Name	DTS automatically generates a task name. We recommend that you specify a descriptive name for easy identification. The name does not need to be unique.
Source Database	Select Existing Connection	To use a database instance that has been added to the system (created or saved), select the desired database instance from the drop-down list. The database information below will be automatically configured. Note In the DMS console, this parameter is named Select a DMS database instance.. If you have not registered the database instance with the system, or do not need to use a registered instance, manually configure the database information below.
	Database Type	Select MySQL.
	Access Method	Select Alibaba Cloud Instance.
	Instance Region	Select the region where the source ApsaraDB RDS for MySQL instance resides.
	Replicate Data Across Alibaba Cloud Accounts	This example assumes data migration within a single Alibaba Cloud account. Select No.
	RDS Instance ID	Select the instance ID of the source ApsaraDB RDS for MySQL instance.
	Database Account	Enter the database account of the source ApsaraDB RDS for MySQL instance. For more information about the required permissions, see Permission requirements for database accounts.
	Database Password	Enter the password for the specified database account.
	Encryption	Select Non-encrypted or SSL-encrypted as needed. If you set this to SSL-encrypted, you must enable SSL encryption for the RDS for MySQL instance beforehand. For more information, see Use a cloud certificate to quickly enable SSL link encryption.
Destination Database	Select Existing Connection	To use a database instance that has been added to the system (created or saved), select the desired database instance from the drop-down list. The database information below will be automatically configured. Note In the DMS console, this parameter is named Select a DMS database instance.. If you have not registered the database instance with the system, or do not need to use a registered instance, manually configure the database information below.
	Database Type	Select ApsaraDB for SelectDB.
	Access Method	Select Alibaba Cloud Instance.
	Instance Region	Select the region where the destination ApsaraDB for SelectDB instance resides.
	Replicate Data Across Alibaba Cloud Accounts	This example assumes data migration within a single Alibaba Cloud account. Select No.
	Instance ID	Select the instance ID of the destination ApsaraDB for SelectDB instance.
	Database Account	Enter the database account of the destination ApsaraDB for SelectDB instance. For more information about the required permissions, see Permission requirements for database accounts.
	Database Password	Enter the password for the database account.

After completing the configuration, click Test Connectivity and Proceed at the bottom of the page.
Note
- Ensure that you add the CIDR blocks of the DTS servers (either automatically or manually) to the security settings of both the source and destination databases to allow access. For more information, see Add the IP address whitelist of DTS servers.
- If the source or destination is a self-managed database (i.e., the Access Method is not Alibaba Cloud Instance), you must also click Test Connectivity in the CIDR Blocks of DTS Servers dialog box.

Configure the task objects.

On the Configure Objects page, configure the objects that you want to migrate.

Parameter	Description
Migration Types	For a full data migration only, select both Schema Migration and Full Data Migration. To perform data migration without service interruption, select Schema Migration, Full Data Migration, and Incremental Data Migration. Important When you migrate data from MySQL to ApsaraDB for SelectDB, data types are converted. If you do not select Schema Migration, you must create tables that use the Unique Key or Duplicate Key model in the destination ApsaraDB for SelectDB instance in advance. For more information, see Data type mappings, Additional columns, and Data models. If you do not select Incremental Data Migration, do not write data to the source instance during data migration to ensure data consistency.
Processing Mode of Conflicting Tables	Precheck and Report Errors: DTS checks whether a table with the same name exists in the destination database. If no table with the same name exists, the pre-check passes. If a table with the same name exists, the pre-check reports an error and the data migration task does not start. Note If you cannot delete or rename the table in the destination database, you can change the name of the table in the destination database. For more information, see Map schema object names. Ignore Errors and Proceed: DTS skips the check for tables with the same name in the destination database. Warning If you select Ignore Errors and Proceed, data inconsistency may occur and pose risks to your business. Examples: If the table schemas are consistent and a record in the destination database has the same primary key value as a record in the source database, DTS does not retain the destination record. The source record overwrites the destination record. If the table schemas are inconsistent, only data from some columns may be migrated, or the migration may fail. Proceed with caution.
Capitalization of Object Names in Destination Instance	You can configure the case sensitivity policy for the names of migrated objects, such as databases, tables, and columns, in the destination instance. By default, DTS default policy is selected. You can also choose to keep the case sensitivity consistent with the default policy of the source or destination database. For more information, see Case sensitivity of object names in the destination database.
Source Objects	In the Source Objects box, click the objects to migrate, and then click to move them to the Selected Objects box. Note You can select databases or tables as migration objects.
Selected Objects	To change the names of the migration objects in the destination instance, right-click a migration object in the Selected Objects section. For more information, see Map schema object names. If you select Schema Migration for Migration Types, select tables, and need to configure the number of buckets (the `bucket_count` parameter), right-click a table in the Selected Objects section. In the Parameter Settings area, set Enable Parameter Settings to Yes, specify the Value based on your business requirements, and then click OK. Note To select the SQL operations for incremental migration at the database or table level, right-click a migration object in the Selected Objects section and select the SQL operations that you want to migrate in the dialog box that appears. To set WHERE conditions to filter data, right-click a table in the Selected Objects section and specify the filter conditions in the dialog box that appears. For more information, see Filter data. If you use the object name mapping feature, other objects that depend on the remapped object may fail to be migrated.

Click Next: Advanced Settings to configure advanced parameters.

Parameter	Description
Dedicated Cluster for Task Scheduling	By default, DTS schedules tasks on a shared cluster. You do not need to select one. If you want more stable tasks, you can purchase a dedicated cluster to run DTS migration tasks.
Retry Time for Failed Connections	After the migration task starts, if the connection to the source or destination database fails, DTS reports an error and immediately begins to retry the connection. The default retry duration is 720 minutes. You can customize the retry time to a value from 10 to 1440 minutes. We recommend that you set the duration to more than 30 minutes. If DTS reconnects to the source and destination databases within the specified duration, the migration task automatically resumes. Otherwise, the task fails. Note For multiple DTS instances that share the same source or destination, the network retry time is determined by the setting of the last created task. Because you are charged for the task during the connection retry period, we recommend that you customize the retry time based on your business needs, or release the DTS instance as soon as possible after the source and destination database instances are released.
Retry Time for Other Issues	After the migration task starts, if a non-connectivity issue, such as a DDL or DML execution exception, occurs in the source or destination database, DTS reports an error and immediately begins to retry the operation. The default retry duration is 10 minutes. You can customize the retry time to a value from 1 to 1440 minutes. We recommend that you set the duration to more than 10 minutes. If the related operations succeed within the specified retry duration, the migration task automatically resumes. Otherwise, the task fails. Important The value of Retry Time for Other Issues must be less than the value of Retry Time for Failed Connections.
Enable Throttling for Full Data Migration	During full migration, DTS consumes read and write resources on the source and destination databases, which may increase the database load. If required, you can enable throttling for the full migration task. You can set Queries per second (QPS) to the source database, RPS of Full Data Migration, and Data migration speed for full migration (MB/s) to reduce the load on the destination database. Note This configuration item is available only if you select Full Data Migration for Migration Types. You can also adjust the full migration speed after the migration instance is running.
Enable Throttling for Incremental Data Migration	If required, you can also choose to set speed limits for the incremental migration task. You can set RPS of Incremental Data Migration and Data migration speed for incremental migration (MB/s) to reduce the load on the destination database. Note This configuration item is available only if you select Incremental Data Migration for Migration Types. You can also adjust the incremental migration speed after the migration instance is running.
Environment Tag	You can select an environment tag to identify the instance based on your business requirements. This parameter is not required in this example.
Whether to delete SQL operations on heartbeat tables of forward and reverse tasks	Choose whether DTS writes heartbeat SQL information to the source database while the instance is running. Yes: Does not write heartbeat SQL information to the source database. The DTS instance may display latency. No: Writes heartbeat SQL information to the source database. This may interfere with source database operations like physical backups and cloning.
Configure ETL	Based on your business needs, select whether to configure the ETL feature to process data. Yes: Configures the ETL feature. You must also enter data processing statements in the text box. No: Does not configure the ETL feature.
Monitoring and Alerting	Select whether to set alerts and receive alert notifications based on your business needs. No: Does not set an alert. Yes: Configure alerts by setting an alert threshold and an alert notifications. If a migration fails or the latency exceeds the threshold, the system sends an alert notification.

Optional: After you complete the preceding configurations, click Next: Configure Database and Table Fields to set Primary Key Column, Distribution Key, and Engine for the destination tables.
Note
- This step is available only if you select Schema Migration for Migration Types when you configure the task objects. You can set Definition Status to All and modify the settings.
- For Primary Key Column, you can select multiple columns to form a composite primary key. You must also select one or more columns from the Primary Key Column as the Distribution Key.
- For a table that has no primary key or unique constraint, you must select duplicate for Engine. Otherwise, the instance may fail or data loss may occur.

Save the task and run a precheck.
- To view the parameters for configuring this instance when you call the API operation, move the pointer over the Next: Save Task Settings and Precheck button and click Preview OpenAPI parameters in the bubble that appears.
- If you do not need to view or have finished viewing the API parameters, click Next: Save Task Settings and Precheck at the bottom of the page.
Note
- Before the migration task starts, DTS performs a precheck. The task starts only after it passes the precheck.
- If the precheck fails, click View Details next to the failed check item, fix the issue based on the prompt, and then run the precheck again.
- If a warning is reported during the precheck:
  
  For check items that cannot be ignored, click View Details next to the failed item, fix the issue based on the prompt, and then run the precheck again.
  
  For check items that can be ignored, you can click Confirm Alert Details, Ignore, OK, and Precheck Again to skip the alert item and run the precheck again. If you choose to ignore a warning, it may cause issues such as data inconsistency and pose risks to your business.

Purchase an instance.

When the Success Rate is 100%, click Next: Purchase Instance.

On the Purchase page, select the link specification for the data migration instance. For more information, see the following table.

Category	Parameter	Description
New Instance Class	Resource Group Settings	Select the resource group to which the instance belongs. The default value is default resource group. For more information, see What is Resource Management?
New Instance Class	Instance Class	DTS provides migration specifications with different performance levels. The link specification affects the migration speed. You can select a specification based on your business scenario. For more information, see Data migration link specifications.

After the configuration is complete, read and select Data Transmission Service (Pay-as-you-go) Service Terms.
Click Buy and Start. In the OK dialog box that appears, click OK.

You can view the progress of the migration task on the Data Migration Tasks list page.
Note
- If the migration task does not include incremental migration, it stops automatically after the full migration is complete. After the task stops, its Status changes to Completed.
- If the migration task includes incremental migration, it does not stop automatically. The incremental migration task continues to run. While the incremental migration task is running, the Status of the task is Running.

Data type mappings

Category	MySQL type	SelectDB type
NUMERIC	TINYINT	TINYINT
	TINYINT UNSIGNED	SMALLINT
	SMALLINT	SMALLINT
	SMALLINT UNSIGNED	INT
	MEDIUMINT	INT
	MEDIUMINT UNSIGNED	BIGINT
	INT	INT
	INT UNSIGNED	BIGINT
	BIGINT	BIGINT
	BIGINT UNSIGNED	LARGEINT
	BIT(M)	INT
	DECIMAL	DECIMAL Note ZEROFILL is not supported.
	NUMERIC	DECIMAL
	FLOAT	FLOAT
	DOUBLE	DOUBLE
	BOOL BOOLEAN	BOOLEAN
DATE AND TIME	DATE	DATEV2
	DATETIME[(fsp)]	DATETIMEV2
	TIMESTAMP[(fsp)]	DATETIMEV2
	TIME[(fsp)]	VARCHAR
	YEAR[(4)]	INT
STRING	CHAR VARCHAR	VARCHAR Important During data migration to ApsaraDB for SelectDB, CHAR and VARCHAR(n) data types are converted to VARCHAR(4*n) to prevent data loss. If you do not specify a data length, the default is VARCHAR(65533). If the data length exceeds 65,533, the data type becomes STRING.
	BINARY VARBINARY	STRING
	TINYTEXT TEXT MEDIUMTEXT LONGTEXT	STRING
	TINYBLOB BLOB MEDIUMBLOB LONGBLOB	STRING
	ENUM	STRING
	SET	STRING
	JSON	STRING

Additional columns

Note

This table describes the additional columns for target tables that use the Duplicate key model. These columns are either added automatically by DTS or must be added manually.

Parameter	Type	Default	Description
_is_deleted	Int	0	Indicates whether the record is deleted. Insert: 0 Update: 0 Delete: 1
_version	Bigint	0	For full data migration, the value is 0. For incremental data migration, the value is the timestamp (in seconds) of the corresponding entry in the source database's binary log.
_record_id	Bigint	0	For full data migration, the value is 0. For incremental data migration, the value is the unique ID of the record from the incremental log. Note The ID is unique and auto-incrementing.

Prerequisites

Limitations

Unique model

Duplicate model