Use DTS to migrate a self-managed MySQL database to ApsaraDB MyBase for MySQL - ApsaraDB for MyBase - Alibaba Cloud - ApsaraDB for MyBase

Prerequisites

Before you begin, ensure that you have:

A self-managed MySQL database running version 5.1, 5.5, 5.6, 5.7, or 8.0
An ApsaraDB MyBase for MySQL instance. To create one, see Create an ApsaraDB MyBase for MySQL instance
Available storage space on the destination instance that exceeds the total data size of the source database
(If the source is on-premises) DTS server CIDR blocks added to the source database IP whitelist. See Add the CIDR blocks of DTS servers to the security settings of on-premises databases

Supported source and destination databases

This topic uses a self-managed MySQL database hosted on an Elastic Compute Service (ECS) instance as the source and an ApsaraDB MyBase for MySQL instance as the destination. The same procedure applies to other source and destination combinations listed below.

Role	Supported types
Source	Alibaba Cloud instances; self-managed databases with a public IP address; self-managed databases on ECS; databases connected over Express Connect, VPN Gateway, or Smart Access Gateway; databases connected over Database Gateway
Destination	Same as source types above

Billing

Migration type	Task configuration fee	Internet traffic fee
Schema migration and full data migration	Free	Charged only when data egresses from Alibaba Cloud over the Internet. See Pricing
Incremental data migration	Charged. See Pricing	—

Migration types

DTS supports three migration types for this scenario:

Schema migration — Migrates schemas for tables, views, triggers, stored procedures, and functions. During schema migration, DTS changes the SECURITY attribute from DEFINER to INVOKER for views, stored procedures, and functions. User information is not migrated. To call a migrated view, stored procedure, or function on the destination database, grant the required read and write permissions to INVOKER.
Full data migration — Migrates all historical data from the source to the destination.
Incremental data migration — After full data migration completes, continuously replicates changes from the source to the destination. This keeps the destination in sync and lets you cut over with minimal downtime.

SQL operations supported for incremental data migration

Type	Statements
DML	INSERT, UPDATE, DELETE
DDL	ALTER TABLE, ALTER VIEW; CREATE FUNCTION, CREATE INDEX, CREATE PROCEDURE, CREATE TABLE, CREATE VIEW; DROP INDEX, DROP TABLE; RENAME TABLE; TRUNCATE TABLE

Permissions required for database accounts

Database	Schema migration	Full data migration	Incremental data migration
Self-managed MySQL on ECS	SELECT	SELECT	SELECT, REPLICATION CLIENT, REPLICATION SLAVE, SHOW VIEW
ApsaraDB MyBase for MySQL instance	Read and write permissions

For full setup instructions, see:

Self-managed MySQL: Create an account for a self-managed MySQL database and configure binary logging
ApsaraDB MyBase for MySQL: Create a database account and Manage a database account

Limitations

Source database requirements

Tables to be migrated must have a PRIMARY KEY or UNIQUE constraint with all fields unique. Without this, the destination database may contain duplicate records.
If migrating tables and you need to rename tables or columns in the destination database, a single task can migrate at most 1,000 tables. For more than 1,000 tables, split them across multiple tasks or migrate the entire database in one task.
The source database must have sufficient egress bandwidth. Insufficient bandwidth slows migration.

Incremental migration: binary logging requirements

For incremental data migration, the source database must have binary logging enabled and correctly configured. DTS reads the binary log to capture changes — incorrect settings cause the precheck to fail.

Parameter	Required value	Why
`binlog_format`	`ROW`	Row-based format captures the exact data changes. Statement-based and mixed formats can produce inconsistent data on the destination.
`binlog_row_image`	`FULL`	Full image logging ensures DTS captures all column values, not just changed columns.
`log_slave_updates`	`ON`	Required only for dual-primary clusters. Ensures DTS can read all binary logs from both primary nodes.

Binary log retention period:

Migration scope	Minimum retention
Incremental data migration only	More than 24 hours
Full data migration + incremental data migration	At least 7 days. After full data migration completes, reduce to more than 24 hours.

Insufficient binary log retention causes DTS to fail to obtain the binary logs, which can result in task failure or, in extreme cases, data inconsistency or loss. Maintaining the required retention period is a prerequisite for DTS Service Level Agreement (SLA) coverage.

Source database operations during migration

Migration phase	Restriction
Schema migration and full data migration	Do not perform DDL operations. DDL changes during this phase cause the migration task to fail.
Full data migration only (no incremental)	Do not write to the source database. Writes during full-only migration cause data inconsistency between source and destination. To avoid this, run schema migration, full data migration, and incremental data migration together.

Other limitations

Use the same MySQL engine version for source and destination to avoid compatibility issues.
Run migrations during off-peak hours. DTS uses read and write resources on both databases during full data migration, which increases load on the database servers.
Full data migration causes table fragmentation on the destination. After full migration completes, the destination tablespace is larger than the source.
DTS retrieves FLOAT and DOUBLE values using ROUND(COLUMN, PRECISION). Default precision: FLOAT = 38 digits, DOUBLE = 308 digits. Verify these defaults meet your business requirements before migrating.
DTS automatically retries failed tasks for up to 7 days. Before switching application traffic to the destination, stop or release the migration task — or run revoke to remove DTS write permissions on the destination. Otherwise, DTS may resume and overwrite data on the destination with stale source data.

Special cases

Source failover during migration — If a failover occurs on the source database while the migration task is running, the task fails.
Migration latency accuracy — DTS calculates latency based on the timestamp of the latest migrated record versus the current time on the source. If no DML operations occur on the source for an extended period, the reported latency may be inaccurate. Perform a DML operation on the source to force a latency update. If migrating an entire database, create a heartbeat table that updates every second.
Destination database naming — DTS automatically creates the destination database in the ApsaraDB MyBase for MySQL instance. If the source database name does not meet ApsaraDB MyBase for MySQL naming conventions, create the destination database manually before configuring the migration task. See Create a database.

Migrate data

Step 1: Open the DTS console

Go to the Data Migration page of the new DTS console.

Alternatively, log on to the Data Management (DMS) console and click DTS in the top navigation bar.

Step 2: Select a region

In the upper-left corner of the page, select the region where the migration instance resides.

Step 3: Configure source and destination databases

Click Create Task, then configure the source and destination database connections.

Warning

Read the limits displayed at the top of the page after selecting the source and destination instances. These limits affect task creation and execution.

Configure source and destination databases

Task settings:

Parameter	Description
Task Name	DTS generates a name automatically. Specify a descriptive name to make the task easy to identify. The name does not need to be unique.

Source database:

Parameter	Description
Select Instance	Select an existing instance to have DTS apply its settings automatically, or configure parameters manually.
Database Type	Select MySQL.
Access Method	Select the connection type that matches your source database location. This example uses Self-managed Database on ECS.
Instance Region	Select the region where the source MySQL database is deployed.
ECS Instance ID	Select the ECS instance hosting the source database.
Port Number	Enter the MySQL service port. Default: 3306.
Database Account	Enter the source database account. See Permissions required for database accounts.
Database Password	Enter the account password.
Encryption	Select Non-encrypted or SSL-encrypted based on your security requirements.

Destination database:

Parameter	Description
Select Instance	Select an existing instance to have DTS apply its settings automatically, or configure parameters manually.
Database Type	Select MySQL.
Access Method	Select Cloud Instance.
Instance Region	Select the region where the destination ApsaraDB MyBase for MySQL instance is deployed.
RDS Instance ID	Select the destination ApsaraDB MyBase for MySQL instance.
Database Account	Enter the destination database account. See Permissions required for database accounts.
Database Password	Enter the account password.
Encryption	Select Non-encrypted or SSL-encrypted based on your security requirements.

Step 4: Test connectivity

Click Test Connectivity and Proceed.

If the source database has a whitelist configured, add the DTS server CIDR blocks to the whitelist first.

For Alibaba Cloud database instances and self-managed databases on ECS, DTS automatically adds its CIDR blocks to the instance whitelist or ECS security group rules. For databases in data centers or on other cloud providers, add the CIDR blocks manually. See Add the CIDR blocks of DTS servers to the security settings of on-premises databases.

Warning

Adding DTS CIDR blocks to a whitelist or security group creates potential security exposure. Before using DTS, understand and mitigate these risks: strengthen account passwords, restrict exposed ports, authenticate API calls, regularly audit whitelist and security group rules, and remove unauthorized CIDR blocks. Consider connecting through Express Connect, VPN Gateway, or Smart Access Gateway for higher security. After the migration task completes or is released, remove the added CIDR blocks from the whitelist or security group rules.

Step 5: Select objects and configure settings

Basic settings:

Parameter	Options and description
Task Stages	Select Schema Migration and Full Data Migration to perform only full data migration. Select all three types — Schema Migration, Full Data Migration, and Incremental Data Migration — to keep the source database live during migration and cut over with minimal downtime. <br><br> Note Without incremental data migration, do not write to the source database during migration to maintain data consistency.
Processing Mode of Conflicting Tables	Precheck and Report Errors — fails the precheck if the destination contains tables with the same names as source tables. Use object name mapping to resolve conflicts if needed. See Map object names. <br><br>Ignore Errors and Proceed — skips the precheck for duplicate table names. Warning This may cause data inconsistency. If schemas match, DTS skips records with duplicate primary keys. If schemas differ, only specific columns are migrated or the task fails.
Select Objects	In the Source Objects section, select the databases or tables to migrate, then click the rightwards arrow icon to move them to Selected Objects.

To rename a single object in the destination, right-click it in the Selected Objects section. To rename multiple objects at once, click the batch edit icon in the upper-right corner. See Map object names. Renaming an object may cause dependent objects to fail to migrate.

Advanced settings:

Parameter	Description
Set Alerts	Select Yes to receive notifications when the task fails or migration latency exceeds your threshold. Configure the threshold and alert contacts. Select No to skip alerts.
Capitalization of Object Names in Destination Instance	Controls the capitalization of database, table, and column names in the destination. Defaults to DTS default policy. Adjust to match source or destination database capitalization as needed. See Specify the capitalization of object names in the destination instance.
Replicate Temporary Tables When DMS Performs DDL Operations	Controls whether DTS migrates temporary tables generated by Data Management (DMS) online DDL operations. Select Yes to migrate temporary table data (may increase latency). Select No to migrate only the original DDL (destination tables may be locked temporarily).
Retry Time for Failed Connections	The time DTS retries failed connections before marking the task as failed. Valid range: 10–720 minutes. Default: 720 minutes. Set this to at least 30 minutes. If DTS reconnects within the retry window, it resumes the task automatically. <br><br> Note If an instance serves multiple tasks, the most recently set retry time takes effect. DTS charges for the instance during retry periods.

Step 6: Run the precheck

Click Next: Save Task Settings and Precheck.

DTS runs a precheck before starting the migration. Wait for the precheck to complete.

If any items fail, click the info icon next to the failed item for details. Fix the issue, then run the precheck again. Items that do not require fixing can be ignored to proceed.

Step 7: Purchase a migration instance

Wait for the precheck Success Rate to reach 100%, then click Next: Purchase Instance.

On the Purchase Instance page, select an Instance Class for the migration instance. Higher specifications provide better migration performance. See Specifications of data migration instances.

Step 8: Start the migration

Read and accept the Data Transmission Service (Pay-as-you-go) Service Terms, then click Buy and Start.

The migration task starts. Track progress in the task list.

What to do next

After the migration completes, delete the database accounts that were used for data migration. These accounts have read and write permissions — deleting them ensures database security.