Migrate RDS MySQL to AnalyticDB MySQL 3.0 for Real-Time Analytics - Data Transmission Service

Use Data Transmission Service (DTS) to migrate data from an RDS for MySQL instance to an AnalyticDB for MySQL 3.0 cluster. This is a common path for building business intelligence (BI) systems, interactive analytics, and real-time reporting on top of operational data.

The migration involves four phases:

Confirm prerequisites and review limitations
Configure the DTS migration task (source, destination, and migration objects)
Run the precheck and purchase a migration instance
Monitor the task and switch your application to the destination cluster

Prerequisites

Before you begin, ensure that you have:

A destination AnalyticDB for MySQL 3.0 cluster. For details, see Create a cluster.
Enough storage space in the destination cluster — it must exceed the storage used by the source RDS for MySQL instance.

Billing

Migration type	Instance fee	Internet traffic fee
Schema migration and full data migration	Free	Charged when the destination Access Method is Public IP Address. For details, see Billing overview.
Incremental data migration	Charged. For details, see Billing overview.

Migration types

DTS supports three migration types for this path. For zero-downtime migration, select all three.

Type	What it does	When to use
Schema migration	Copies table schemas from source to destination. This is a heterogeneous migration — not all data types map perfectly. Review Data type mappings between heterogeneous databases before proceeding.	Always include this.
Full data migration	Copies all existing data from source to destination.	Required as a baseline for incremental migration.
Incremental data migration	Continuously replicates changes from source to destination after full migration completes, allowing your application to keep running during migration.	Include this for zero-downtime migration. If you omit it, do not write new data to the source during migration.

Foreign keys are not migrated during schema migration. DTS temporarily disables constraint checks and foreign key cascade operations at the session level during full and incremental migration. If cascade update or delete operations occur on the source while the task runs, data inconsistency may result.

Supported SQL operations for incremental migration

Type	Supported statements
DML	INSERT, UPDATE, DELETE
DDL	CREATE TABLE, DROP TABLE, RENAME TABLE, TRUNCATE TABLE, ADD COLUMN, MODIFY COLUMN, DROP COLUMN

Notes on specific operations:

UPDATE: Automatically converted to REPLACE INTO when written to AnalyticDB for MySQL. If the primary key is updated, it is converted to a DELETE followed by an INSERT.
RENAME TABLE: Can cause data loss if only the renamed table is selected as a migration object. To avoid this, select the entire database as the migration object, covering both the pre- and post-rename database names.

Warning

If a source table's field type is changed during migration, the task stops with an error. To recover: 1. In AnalyticDB for MySQL 3.0, create customer_new with the same schema as the failed table customer. 2. Run INSERT INTO SELECT to copy data from customer to customer_new. 3. Rename or delete customer, then rename customer_new to customer. 4. Restart the migration task in the DTS console.

Limitations

Review these limitations before configuring the task.

Source database requirements

The source server must have enough outbound bandwidth; otherwise migration speed is affected.
Tables to be migrated must have a primary key or UNIQUE constraint with unique field values. Without this, duplicate data may appear in the destination.
If you migrate at the table level and need to map column names, a single task supports a maximum of 1,000 tables. If you exceed this, split the tables across multiple tasks or migrate the entire database instead.
Data from operations not recorded in binary logs — such as physical backup recovery and cascade operations — is not migrated. If this occurs, run a full data migration again when your business allows.
If the source is MySQL 8.0.23 or later and the data contains invisible columns, those columns cannot be read and data loss may occur. Make the columns visible first: ALTER TABLE <table_name> ALTER COLUMN <column_name> SET VISIBLE; For details, see Invisible Columns.

Binary log requirements (incremental migration only):

binlog_format must be row
binlog_row_image must be full
For dual-primary clusters where each node is both primary and secondary: enable log_slave_updates
RDS for MySQL binary logs: retain for at least 3 days (7 days recommended)
Self-managed MySQL binary logs: retain for at least 7 days

Important

A binary log retention period shorter than required is not covered by the DTS SLA (Service-Level Agreement) and may cause task failure, data inconsistency, or data loss. For RDS for MySQL, see Automatically delete binary logs for how to set the Retention Period.

DDL restrictions during migration:

Do not perform DDL operations that change database or table schemas during schema migration or full data migration — the task will fail.
During full data migration, DTS queries the source database, creating a metadata lock that may block DDL operations on the source.
Do not run comment-adding DDL such as ALTER TABLE table_name COMMENT='Table comment'; — the task will fail.

Destination and task requirements

Before you begin, evaluate the performance of the source and destination databases. We recommend that you perform data migration during off-peak hours. During full data migration, DTS consumes some read and write resources of the source and destination databases, which may increase the database load.

Migration of prefix indexes is not supported. Tables with prefix indexes may fail to migrate.
The following object types are not supported: INDEX, PARTITION, VIEW, PROCEDURE, FUNCTION, TRIGGER, FK.
Online DDL operations using temporary tables (such as merging multiple tables) may cause data loss in the destination or task failure.
The destination table must have a custom primary key, or you must configure Primary Key Column in the Configurations for Databases, Tables, and Columns step.
If disk usage on any AnalyticDB for MySQL node exceeds 80%, the DTS task becomes abnormal and latency increases. Estimate storage requirements before starting.
If the AnalyticDB for MySQL 3.0 cluster is being backed up while the DTS task runs, the task fails.
Full data migration uses concurrent INSERT operations, which cause table fragmentation in the destination. After migration, destination storage usage will be larger than the source.
DTS reads FLOAT and DOUBLE columns using ROUND(COLUMN,PRECISION). If precision is not explicitly defined, FLOAT is migrated with 38 digits of precision and DOUBLE with 308 digits. Verify this meets your requirements.
DTS attempts to auto-resume failed migration tasks for up to 7 days. Before switching your application to the destination, end or release the task, or revoke DTS write permissions using the revoke command. Otherwise, the source may overwrite the destination after an automatic resume.
If a DDL statement fails to write to the destination, the task continues. Check the task logs for failed DDL statements. See Query task logs.
RDS for MySQL with always-encrypted (EncDB) enabled: full data migration is not supported. RDS for MySQL with Transparent Data Encryption (TDE) enabled supports schema migration, full data migration, and incremental data migration.
If a task fails, DTS support will attempt recovery within 8 hours, which may involve restarting the task or adjusting DTS task parameters (database parameters are not changed). For parameters that may be modified, see Modify instance parameters.

Primary/unique key conflicts:

If a primary key or unique key conflict is detected:

Consistent table schemas, same primary key value in both source and destination:
- Full migration: DTS keeps the destination record; the source record is skipped.
- Incremental migration: DTS overwrites the destination record with the source record.
Inconsistent table schemas: only some columns may be migrated, or the migration may fail.

Special cases

For self-managed MySQL sources:

A primary/secondary switchover during migration causes the task to fail.
Task latency is calculated using the timestamp of the last migrated record versus the current time. If no DML operations occur on the source for a long period, the displayed latency may be inaccurate. Trigger a DML operation on the source to refresh it. Alternatively, if you are migrating the entire database, create a heartbeat table — DTS updates it every second.
DTS periodically runs CREATE DATABASE IF NOT EXISTS \test\`` to advance the binary log offset.
For Amazon Aurora MySQL or other clustered MySQL instances: the domain name or IP address in the task configuration must always resolve to the read/write (RW) node.

For RDS for MySQL sources:

RDS for MySQL instances that do not record transaction logs, such as read-only instances of RDS for MySQL 5.6, cannot be used as the source for incremental data migration.
DTS periodically runs CREATE DATABASE IF NOT EXISTS \test\`` to advance the binary log offset.

Database account permissions

Database	Schema migration	Full migration	Incremental migration
RDS for MySQL	SELECT	SELECT	REPLICATION SLAVE, REPLICATION CLIENT, and SELECT on the objects to be migrated (DTS grants these automatically; required for incremental migration only)
AnalyticDB for MySQL 3.0	Read and write permissions	Read and write permissions	Read and write permissions

REPLICATION SLAVE and REPLICATION CLIENT are required only for incremental migration. Full-migration-only tasks do not need these permissions.

To create accounts and grant permissions:

For RDS for MySQL: see Create an account. To adjust permissions after creation, see Modify the permissions of an account.
For AnalyticDB for MySQL 3.0: see Create a database account.

Create a migration task

Step 1: Open the migration task list

Use one of the following methods to reach the migration task list for the target region.

From the DTS console:

Log on to the Data Transmission Service (DTS) console.
In the left navigation pane, click Data Migration.
In the upper-left corner, select the region where the migration instance will be located.

From the DMS console:

The exact steps vary based on the DMS console mode and layout. For details, see Simple mode console and Customize the layout and style of the DMS console.

Log on to the Data Management (DMS) console.
In the top menu bar, choose Data + AI > Data Transmission (DTS) > Data Migration.
To the right of Data Migration Tasks, select the region where the migration instance will be located.

Step 2: Configure source and destination databases

Click Create Task, then configure the following settings.

Warning

After selecting the source and destination instances, read the limits displayed at the top of the page carefully. Skipping this step may result in task failure or data inconsistency.

Category	Parameter	Description
—	Task Name	DTS generates a name automatically. Specify a descriptive name for easy identification. The name does not need to be unique.
Source Database	Select Existing Connection	Select a registered database instance from the list (the fields below are filled automatically), or leave blank to configure manually. In the DMS console, this parameter is named Select a DMS database instance.
	Database Type	Select MySQL.
	Connection Type	Select Cloud Instance.
	Instance Region	Select the region where the source RDS for MySQL instance resides.
	Cross-account	For same-account migration, select No.
	RDS Instance ID	Select the source RDS for MySQL instance.
	Database Account	Enter the database account. For required permissions, see Database account permissions.
	Database Password	Enter the password.
	Encryption	Select Non-encrypted or SSL-encrypted. If you select SSL-encrypted, enable SSL on the RDS for MySQL instance first. See Quickly enable SSL encryption using a cloud certificate.
Destination Database	Select Existing Connection	Select a registered instance or configure manually. In the DMS console, this parameter is named Select a DMS database instance.
	Database Type	Select AnalyticDB MySQL 3.0.
	Connection Type	Select Cloud Instance.
	Instance Region	Select the region where the destination AnalyticDB for MySQL 3.0 cluster resides.
	Instance ID	Select the destination AnalyticDB for MySQL 3.0 cluster.
	Database Account	Enter the database account. For required permissions, see Database account permissions.
	Database Password	Enter the password.

After completing the configuration, click Test Connectivity and Proceed.

DTS server IP address ranges must be added to the security settings (whitelist) of both the source and destination databases. See Add DTS server IP addresses to a whitelist.

If the source or destination is a self-managed database (Access Method is not Alibaba Cloud Instance), also click Test Connectivity in the CIDR Blocks of DTS Servers dialog.

Step 3: Configure migration objects

On the Configure Objects page, set the following parameters.

Parameter	Description
Migration Types	For zero-downtime migration, select Schema Migration, Full Migration, and Incremental Migration. For a one-time full migration (with application downtime), select Schema Migration and Full Migration only — and do not write new data to the source during migration.
Processing Mode of Conflicting Tables	Precheck and Report Errors: Reports an error if tables with the same name exist in the destination. The task does not start until conflicts are resolved. To resolve conflicts without deleting the destination table, rename it — see Object name mapping. Ignore Errors and Proceed: Skips the check. This may cause data inconsistency — see Limitations for conflict behavior details.
DDL and DML Operations to Be Synchronized	Select the SQL operations for incremental migration at the instance level. For supported operations, see Supported SQL operations for incremental migration. To set operations at the database or table level, right-click a migration object in the Selected Objects box.
Merge Tables	Yes: DTS adds a `__dts_data_source` column to each table to record the data source. See Enable multi-table merge. No (default): Standard migration without table merging. Table merging is configured at the task level — to merge some tables but not others, create separate tasks. Do not perform DDL operations that change source table schemas while this setting is active.
Capitalization of Object Names in Destination Instance	Configure case sensitivity for migrated object names (databases, tables, columns). The default is DTS default policy. See Case sensitivity of object names in the destination database.
Source Objects	Click objects in the Source Objects box, then click to move them to Selected Objects. Select at the granularity of database, table, or column. Selecting tables does not migrate views, triggers, or stored procedures. When selecting an entire database: tables with a primary key use the primary key as the distribution key; tables without a primary key get an auto-increment primary key column (which may cause data inconsistency between source and destination).
Selected Objects	To rename a single migration object in the destination, right-click it. See Individual table column mapping. To rename multiple objects at once, click Batch Edit. See Map multiple object names at a time. To filter rows with a WHERE clause, right-click the table and set the condition. See Set a filter condition. To add extra columns to a destination table, right-click the table and use the add column feature. See Add an additional column. Using object name mapping may cause dependent objects to fail migration.

Step 4: Configure advanced settings

Click Next: Advanced Settings and configure the following parameters.

Parameter	Description
Dedicated Cluster for Task Scheduling	By default, DTS schedules tasks on a shared cluster. For more stable performance, purchase a dedicated cluster.
Copy the temporary table of the Online DDL tool that is generated in the source table to the destination database	Controls how DTS handles temporary tables from online DDL tools (Data Management (DMS) or gh-ost). Important pt-online-schema-change is not supported — using it causes task failure. This setting only affects the Incremental Data Migration phase; the schema migration and full data migration phases are not controlled by this setting. During Full Data Migration, temporary tables whose names match the regular expressions (`^_(.+)_(?:gho	new)$ `or` ^_(.+)_(?:ghc	del	old)$`) are automatically filtered out and not included in the full migration objects. Options: Yes — migrates data from temporary tables (e.g.,` _table_name_gho`). Note that large temporary tables may increase latency. No, Adapt to DMS Online DDL — filters out temporary table changes and migrates only the original DDL statements from DMS. Tables in the destination are locked during this process. No, Adapt to gh-ost — filters out temporary table changes using regular expressions. Tables in the destination are locked. You can customize the shadow table pattern (`^_(.+)_(?:gho	new)$`) and useless table pattern (`^_(.+)_(?:ghc	del	old)$`).
Retry Time for Failed Connections	If the connection to the source or destination fails after the task starts, DTS retries immediately. Default: 720 minutes. Range: 10–1440 minutes. Set to more than 30 minutes. If DTS reconnects within the retry window, the task resumes automatically; otherwise it fails. Note: multiple DTS instances sharing the same source or destination use the retry time of the last created task. You are charged during the retry period.
Retry Time for Other Issues	If a non-connectivity issue (such as a DDL or DML execution error) occurs, DTS retries immediately. Default: 10 minutes. Range: 1–1440 minutes. Set to more than 10 minutes. This value must be less than Retry Time for Failed Connections.
Enable Throttling for Full Data Migration	Limit the read and write load on source and destination during full migration. Set Queries per second (QPS) to the source database, RPS of Full Data Migration, and Data migration speed for full migration (MB/s). Available only when Full Migration is selected. You can also adjust the speed after the task starts.
Enable Throttling for Incremental Data Migration	Limit the load during incremental migration. Set RPS of Incremental Data Migration and Data migration speed for incremental migration (MB/s). Available only when Incremental Migration is selected. You can also adjust the speed after the task starts.
Whether to delete SQL operations on heartbeat tables of forward and reverse tasks	Yes: DTS does not write heartbeat SQL to the source database. The instance may display latency. No: DTS writes heartbeat SQL to the source database. This may interfere with physical backups and cloning.
Environment Tag	Optional. Select a tag to identify the instance environment.
Configure ETL	Choose whether to enable extract, transform, and load (ETL). Yes: Enter data processing statements in the code editor. See Configure ETL in a data migration or data synchronization task. No: Disables ETL.
Monitoring and Alerting	Yes: Set an alert threshold and notification contacts. DTS sends an alert if the task fails or latency exceeds the threshold. No: No alerts configured.

Step 5: Configure data validation

Click Next: Data Validation to set up a data validation task. For details, see Configure data validation.

Step 6: Configure database and table fields (optional)

Click Next: Configure Database and Table Fields to set the Type, Primary Key Column, Distribution Key, and partition key settings (Partition Key, Partitioning Rules, Partition Lifecycle) for tables in the destination database.

This step is available only when Schema Migration is selected. Set Definition Status to All to see all tables.

Primary Key Column supports multiple columns (composite primary key). At least one column from Primary Key Column must also be selected as the Distribution Key and Partition Key. For details, see CREATE TABLE.

Step 7: Run the precheck

Click Next: Save Task Settings and Precheck.

To preview the API parameters for this configuration, hover over the button and click Preview OpenAPI parameters.
The task starts only after passing the precheck.
If the precheck fails, click View Details next to the failed item, fix the issue, and run the precheck again.
If the precheck reports a warning:
- For warnings that cannot be ignored: click View Details, fix the issue, and rerun the precheck.
- For ignorable warnings: click Confirm Alert Details > Ignore > OK > Precheck Again. Ignored warnings may cause data inconsistency.

Step 8: Purchase the migration instance

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

On the Purchase page, select the instance class for the migration instance.

Parameter	Description
Resource Group Settings	Select the resource group for the instance. Default: default resource group. See What is Resource Management?
Instance Class	Select a specification based on your performance requirements. The instance class affects migration speed. See Data migration link specifications.

Read and select Data Transmission Service (Pay-as-you-go) Service Terms.
Click Buy and Start, then click OK in the confirmation dialog.

Track task progress on the Data Migration Tasks list page.

Full migration only (no incremental): the task stops automatically when complete. Status changes to Completed.

With incremental migration: the task runs continuously. Status remains Running.

FAQ

Why does schema migration fail with `only 500 dimension table allowed, current dimensionTableCount: 500`?

AnalyticDB for MySQL counts both active tables and tables in the recycle bin toward the total limit. Even if your migration objects are within the limit, deleted tables still in the recycle bin may push the total over it.

To diagnose and fix this:

Check whether your migration object count is within the limit shown in the error.

Query the current table counts:

-- Active tables
SELECT COUNT(*) FROM INFORMATION_SCHEMA.tables;
-- Tables in the recycle bin
SELECT COUNT(*) FROM INFORMATION_SCHEMA.KEPLER_META_RECYCLE_BIN;

For details on the recycle bin, see Table recycle bin.

If the recycle bin contains tables that are no longer needed, purge them:

Important

Purged tables cannot be recovered. Confirm they are no longer needed before proceeding.

-- Purge all tables from the recycle bin
PURGE RECYCLE_BIN ALL;
-- Purge a specific table from the recycle bin
PURGE RECYCLE_BIN TABLE <table name in ADB_RECYCLE_BIN database>;

What's next

To monitor migration progress and latency, see Query task logs.
Before switching your application to the destination, end or release the migration task to prevent automatic resume from overwriting destination data.
To validate data consistency between source and destination, see Configure data validation.