Data Transmission Service:Migrate data from an ApsaraDB RDS for MySQL instance to a MaxCompute project

Prerequisites

Before you begin, ensure that you have:

• A MaxCompute project with available storage space larger than the total size of data in the source ApsaraDB RDS for MySQL database. For more information, see Create a MaxCompute project.

• An IP address whitelist configured for the MaxCompute project to allow access from DTS. For more information, see Configure an IP address whitelist to allow access from Alibaba Cloud services to MaxCompute.

• An AccessKey pair for the Alibaba Cloud account that owns the destination MaxCompute project. For more information, see Create an AccessKey pair.

Alternatively, create a RAM user and set it as the super administrator for the MaxCompute project.

Migration types

DTS supports three migration types for this scenario. Select the combination that fits your requirements.

Choose your migration combination:

• Full migration only (Schema Migration + Full Data Migration): Use when you can pause writes to the source database during migration. Lower cost — schema and full data migration are free.

• Full + incremental migration (Schema Migration + Full Data Migration + Incremental Data Migration): Use when you need continuous service during migration. Incremental data migration is charged separately.

During incremental data migration, DTS migrates the following SQL operations:

Permissions required

Grant the following permissions to the database accounts used by DTS.

For more information about how to create a database account and grant the required permissions, see Create an account and Modify account permissions.

Limitations

DTS does not migrate foreign keys. Cascade and delete operations defined in the source database are not applied to the destination.

Source database limitations:

• Tables must have PRIMARY KEY or UNIQUE constraints, with all fields unique. Otherwise, the destination database may contain duplicate records.

• When renaming tables or columns during migration, a single task supports up to 1,000 tables. For more than 1,000 tables, configure multiple tasks or migrate the entire database at once.

• For incremental data migration, binary logging must be enabled with the following configuration: To verify that binary logging is correctly configured, run:

  • binlog_format = row

  • binlog_row_image = full

  • For self-managed MySQL in a dual-primary cluster: log_slave_updates = ON

  • Binary logs on ApsaraDB RDS for MySQL: retain for at least 3 days (7 days recommended)

  • Binary logs on self-managed MySQL: retain for at least 7 days

  Important

  If binary logs are not retained for the minimum required period, DTS may fail to obtain them, causing task failure or data loss. Failure to meet these requirements may affect the service reliability and performance guaranteed by the DTS Service Level Agreement (SLA). For details on setting the retention period for ApsaraDB RDS for MySQL binary logs, see Configure parameters based on which the system automatically deletes the binary log files of an RDS instance.

  SHOW VARIABLES LIKE 'binlog_format';
  SHOW VARIABLES LIKE 'binlog_row_image';

  Both values must match the required settings before starting the migration task.

• Do not perform DDL operations during schema migration or full data migration. Doing so causes task failure.

• For MySQL 8.0.23 and later: invisible columns cannot be migrated, resulting in data loss.

  • To make a column visible: ALTER TABLE <table_name> ALTER COLUMN <column_name> SET VISIBLE;. See Invisible Columns.

  • Tables without primary keys generate invisible primary keys automatically — make those visible too. See Generated Invisible Primary Keys.

• Read-only ApsaraDB RDS for MySQL V5.6 instances cannot be used as the source for incremental data migration (no transaction logs).

Other limitations:

• The following object types cannot be migrated: INDEX, PARTITION, VIEW, PROCEDURE, FUNCTION, TRIGGER, and foreign key (FK).

• MaxCompute does not support primary key constraints. If DTS re-transmits data (for example, after a network interruption), duplicate records may appear.

• Do not use tools such as pt-online-schema-change for DDL operations during migration. Doing so may cause migration failure.

• DTS retrieves FLOAT and DOUBLE values using the ROUND(COLUMN, PRECISION) function. Default precision: 38 digits for FLOAT, 308 digits for DOUBLE. Verify that these precision settings meet your business requirements.

• Schedule migration during off-peak hours. Full data migration uses read and write resources on both the source and destination databases and may increase server load.

• After full data migration completes, the tablespace used in the destination database is larger than in the source database due to fragmentation from concurrent INSERT operations.

• DTS attempts to resume failed tasks for up to 7 days. Before switching workloads to the destination, stop or release the migration task — or revoke write permissions from the DTS account — to prevent source data from overwriting destination data.

• ApsaraDB RDS for MySQL instances with EncDB enabled do not support full data migration.

• ApsaraDB RDS for MySQL instances with Transparent Data Encryption (TDE) enabled support schema migration, full data migration, and incremental data migration.

• If a DTS instance fails, the DTS helpdesk attempts recovery within 8 hours. During recovery, instance parameters may be adjusted. For details, see Modify instance parameters.

Special cases for self-managed MySQL:

• A primary/secondary switchover during migration causes task failure.

• If no DML operations occur on the source database for an extended period, migration latency readings may be inaccurate. Run a DML operation to refresh the latency display.

• DTS periodically executes CREATE DATABASE IF NOT EXISTS \test\`` on the source database to advance the binary log position.

Special cases for ApsaraDB RDS for MySQL:

• Read-only V5.6 instances cannot be used for incremental data migration.

• DTS periodically executes CREATE DATABASE IF NOT EXISTS \test\`` on the source database to advance the binary log position.

Billing

Create a migration task

Step 3: Configure objects to migrate

On the Configure Objects page, configure the following parameters:

Parameter	Description
Migration Types	Select the migration types based on your scenario: <br>- Schema Migration + Full Data Migration: full migration only (no ongoing sync) <br>- Schema Migration + Full Data Migration + Incremental Data Migration: continuous migration with minimal downtime <br><br> Note If you skip Schema Migration, create the destination database and table manually and enable object name mapping in Selected Objects. If you skip Incremental Data Migration, do not write to the source database during migration to maintain data consistency.
Naming Rules of Additional Columns	After migration, DTS adds extra columns to the destination table. Select New Rule or Previous Rule. Check for naming conflicts with existing columns before configuring. See Naming rules for additional columns.
Partition Definition of Incremental Data Table	Select partition names based on your requirements. See Partition.
Processing Mode of Conflicting Tables	Precheck and Report Errors: fails the precheck if tables with identical names exist in both databases. Use object name mapping to resolve conflicts. See Map object names. <br><br>Ignore Errors and Proceed: skips the identical-name check. <br> Warning Data inconsistency risks: during full migration, DTS skips conflicting records; during incremental migration, DTS overwrites them. If schemas differ, only specific columns may migrate or the task may fail.
Capitalization of Object Names in Destination Instance	Controls the case of database, table, and column names in the destination. Default: DTS default policy. See Specify the capitalization of object names in the destination instance.
Source Objects	Select the objects to migrate. Click to move them to Selected Objects.
Selected Objects	To rename a single object, right-click it. To rename multiple objects, click Batch Edit. See Map object names. To filter rows, right-click a table and specify WHERE conditions. See Specify filter conditions. Note Renaming an object may cause dependent objects to fail migration.

Schema of an incremental data table

DTS migrates incremental data to a _log table in MaxCompute. Before querying this table, run the following command to enable full table scan:

set odps.sql.allow.fullscan=true;

The incremental data table has the following schema:

What's next

• Synchronize data from an ApsaraDB RDS for MySQL instance to a MaxCompute project — learn about the incremental data table schema in detail

• Map object names — rename objects during migration

• Specify filter conditions — migrate a subset of rows using SQL WHERE conditions

• Billing overview — understand DTS pricing