Data Transmission Service:Migrate data from RDS MySQL to ApsaraDB for SelectDB

Minimum permissions by migration type

If the source database account was not created through the RDS MySQL console, it must also have the REPLICATION CLIENT, REPLICATION SLAVE, SHOW VIEW, and SELECT privileges.

Grant permissions

• ApsaraDB RDS for MySQL: See Create an account and Modify the permissions of an account.

• ApsaraDB for SelectDB: See Cluster Permission Management and Basic Permission Management.

Source database

• Bandwidth: The server hosting the source database must have sufficient outbound bandwidth. Low bandwidth reduces migration speed.

• Table requirements:

  • Tables with primary keys or UNIQUE constraints: make sure table fields are unique to avoid duplicate data at the destination.

  • Tables without primary keys or UNIQUE constraints: select Schema Migration for Migration Types and set Engine to duplicate in the Configurations for Databases, Tables, and Columns step. Otherwise, the data migration instance may fail or data loss may occur. > Note: During schema migration, DTS adds fields to the destination table. See Additional columns.

• Table-level migration limit: A single data migration task supports up to 1,000 tables when migrating at the table level with object edits (such as name mapping). If you exceed this limit, split the tables into multiple tasks or migrate the entire database.

• DDL during migration: Do not perform DDL operations that change the schema during schema migration or full data migration. DTS queries the source database during full migration, which creates a metadata lock that may block DDL operations.

• Full migration only: If you run only full data migration without incremental migration, do not write new data to the source during migration. For real-time data consistency, use schema migration, full data migration, and incremental data migration together.

• Operations not captured in binary logs: Data changes from physical backup recovery and cascade operations are not migrated. If this occurs, run a full data migration again when your schedule allows.

• Invisible columns (MySQL 8.0.23+): Data in invisible columns cannot be retrieved, which may cause data loss. Run ALTER TABLE <table_name> ALTER COLUMN <column_name> SET VISIBLE; to make invisible columns visible before migration. See Invisible Columns.

Destination database

• Supported engines: You can migrate data only to tables that use the Unique or Duplicate engine in SelectDB.

  Destination table uses the Unique engine

  Make sure all unique keys in the destination table also exist in the source table and are included in the migration objects. Otherwise, data inconsistency may occur.

  Destination table uses the Duplicate engine

  Duplicate data may appear in the destination database in these cases: Use the additional columns (_is_deleted, _version, _record_id) to identify and remove duplicate rows. See Additional columns.

  • The data migration instance was retried or restarted.

  • Two or more DML operations were performed on the same record before migration completed.

    Note

    DTS converts UPDATE and DELETE statements into INSERT statements when the destination table uses the Duplicate engine.

• Unsupported objects: INDEX, PARTITION, VIEW, PROCEDURE, FUNCTION, TRIGGER, and FK objects cannot be migrated.

• `bucket_count` parameter: In the Selected Objects box, you can set only the bucket_count parameter (number of buckets). The value must be a positive integer. The default is auto.

• No new clusters during migration: Do not create a new cluster in the destination SelectDB instance during migration. Restart the data migration instance to recover a failed task.

• Naming requirements: Database and table names in SelectDB must start with a letter. Use the mapping feature to rename any object that does not meet this requirement.

• Chinese characters in names: If a migration object name (database, table, or column) contains Chinese characters, use the mapping feature to rename it to English. Otherwise, the task may fail.

• DDL limitations:

  • DDL operations that modify multiple columns at once cannot be migrated.

  • DDL operations that continuously modify the same table cannot be migrated.

• No backend nodes during migration: Do not add backend (BE) nodes to the SelectDB instance during migration. Restart the data migration instance to recover a failed task.

• Multi-table merge: When migrating data from multiple source tables into a single destination table, the source tables must have the same schema. Otherwise, data inconsistency or task failure may occur.

• VARCHAR length difference: In MySQL, VARCHAR(M) specifies the character length. In SelectDB, VARCHAR(N) specifies the byte length. If you create the destination table manually without schema migration, set the VARCHAR field length in SelectDB to four times the corresponding length in MySQL.

• Online DDL tools:

  • DMS or gh-ost: DTS migrates only the original DDL statements to the destination without migrating temporary table data. This may cause locked tables at the destination.

  • pt-online-schema-change: Online DDL changes from this tool cannot be migrated. If such changes exist on the source, data loss or task failure may occur at the destination.

• Migration timing: Run data migration during off-peak hours. Full data migration reads from the source and writes to the destination concurrently, which increases database workloads.

• Tablespace after full migration: Full data migration executes INSERT operations concurrently, causing table fragmentation at the destination. The destination tablespace will be larger than the source after full migration.

• Encryption:

  • Always-encrypted (EncDB): full data migration is not supported.

  • Transparent Data Encryption (TDE): schema migration, full data migration, and incremental data migration are all supported.

• Incremental migration latency: DTS uses a batch synchronization policy for incremental migration. For a single migration object, DTS writes data at most once every 5 seconds by default, resulting in a normal synchronization latency within 10 seconds. To reduce this latency, adjust the selectdb.reservoir.timeout.milliseconds parameter (range: 1,000–10,000 milliseconds) for the data migration instance in the DTS console.

  A shorter batching time increases DTS write frequency, which may increase write load and response time (RT) at the destination, in turn increasing synchronization latency. Adjust based on the destination's current load.

• Instance recovery: If the data migration instance fails, the DTS helpdesk will attempt recovery within 8 hours. During recovery, the instance may be restarted or its parameters adjusted.

Special cases

Self-managed MySQL as the source:

• A primary/secondary switchover during migration causes the task to fail.

• DTS calculates latency by comparing the timestamp of the last migrated record with the current time. If no DML operations occur on the source for an extended period, the latency display may be inaccurate. Perform a DML operation on the source to refresh the latency. Alternatively, create a heartbeat table — DTS updates it every second.

• DTS periodically runs CREATE DATABASE IF NOT EXISTS \test\`` on the source to advance the binary log offset.

• For Amazon Aurora MySQL or other clustered MySQL instances, make sure the domain name or IP address configured for the task always resolves to the read/write (RW) node.

ApsaraDB RDS for MySQL as the source:

• 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\`` on the source to advance the binary log offset.

Step 1: Go to the Data Migration page

Use one of the following consoles:

Step 2: Configure source and destination databases

1. Click Create Task.

2. Configure the source database:

   Parameter	Value
   Task Name	Enter a descriptive name. Task names do not need to be unique.
   Select Existing Connection	Select a registered instance, or configure the connection manually.
   Database Type	Select MySQL.
   Access Method	Select Alibaba Cloud Instance.
   Instance Region	Select the region of the source RDS for MySQL instance.
   Replicate Data Across Alibaba Cloud Accounts	Select No for a migration within the same account.
   RDS Instance ID	Select the source RDS for MySQL instance.
   Database Account	Enter the database account. See Required permissions.
   Database Password	Enter the account password.
   Encryption	Select Non-encrypted or SSL-encrypted. To use SSL encryption, enable SSL for the RDS for MySQL instance before configuring this task.

3. Configure the destination database:

   Parameter	Value
   Select Existing Connection	Select a registered instance, or configure the connection manually.
   Database Type	Select SelectDB.
   Access Method	Select Alibaba Cloud Instance.
   Instance Region	Select the region of the destination SelectDB instance.
   Replicate Data Across Alibaba Cloud Accounts	Select No for a migration within the same account.
   Instance ID	Select the destination SelectDB instance.
   Database Account	Enter the database account. See Required permissions.
   Database Password	Enter the account password.

4. Click Test Connectivity and Proceed.

   Make sure the DTS server CIDR blocks are added to the security settings (whitelist) of the source and destination databases. See Add DTS server IP addresses to a whitelist. For self-managed databases, click Test Connectivity in the CIDR Blocks of DTS Servers dialog box.

Step 3: Configure migration objects

1. On the Configure Objects page, set the following:

   If you do not select Incremental Data Migration, do not write new data to the source instance during migration.

   Parameter	Description
   Migration Types	Select one of the following combinations: Schema Migration + Full Data Migration for a one-time full migration; or Schema Migration + Full Data Migration + Incremental Data Migration for zero-downtime migration.
   Processing Mode of Conflicting Tables	Precheck and Report Errors: reports an error and stops the task if a table with the same name exists in the destination. Ignore Errors and Proceed: skips the duplicate-table check. Use with caution — this may cause data inconsistency or migration failure if table schemas differ.
   Capitalization of Object Names in Destination Instance	Specifies how database, table, and column names are capitalized at the destination. The default is DTS default policy. See Specify the capitalization of object names in the destination instance.
   Source Objects	Select the objects to migrate. Click to add them to Selected Objects.
   Selected Objects	To rename an object at the destination, right-click it in Selected Objects. See Schema, table, and column name mapping. To set the bucket_count parameter, right-click the table in Selected Objects, enable Parameter Settings, enter the value, and click OK. To filter rows, right-click the table and set a WHERE condition. See Set filter conditions.

2. Click Next: Advanced Settings and configure the following:

   Parameter	Description
   Dedicated Cluster for Task Scheduling	DTS schedules to the shared cluster by default. For higher stability, purchase a dedicated cluster. See What is a DTS dedicated cluster.
   Retry Time for Failed Connections	How long DTS retries after a connection failure. Range: 10–1,440 minutes. Default: 720 minutes. Set to more than 30 minutes.
   Retry Time for Other Issues	How long DTS retries after DDL or DML failures. Range: 1–1,440 minutes. Default: 10 minutes. Set to more than 10 minutes. Must be less than Retry Time for Failed Connections.
   Enable Throttling for Full Data Migration	Limits the QPS, RPS, and data transfer speed for full migration to reduce source and destination load. Available only when Full Data Migration is selected.
   Enable Throttling for Incremental Data Migration	Limits the RPS and data transfer speed for incremental migration. Available only when Incremental Data Migration is selected.
   Environment Tag	Optional. Tag the instance for organizational purposes.
   Whether to delete SQL operations on heartbeat tables of forward and reverse tasks	Yesalert notification settings: DTS does not write heartbeat table operations to the source. A latency offset may appear for the data migration instance. No: DTS writes heartbeat operations to the source. Features like physical backup and cloning may be affected.
   Configure ETL	Yes: enables extract, transform, and load (ETL). 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: configure alert thresholds and notification contacts. See Configure monitoring and alerting. No: no alerting.

3. (Optional) Click Next: Configure Database and Table Fields to set the Primary Key Column, Distribution Key, and Engine for destination tables.

   This step is available only when Schema Migration is selected. Set Definition Status to All to edit all tables. The Primary Key Column can be a composite key — select one or more columns as the Distribution Key. For tables without a primary key or UNIQUE constraint, set Engine to duplicate.

Step 4: 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.

DTS runs a precheck before starting the data migration task. After the precheck completes:

• If an item fails, click View Details, resolve the issue, and click Precheck Again.

• If an item triggers an alert:

  • For alerts that cannot be ignored: click View Details, resolve the issue, and rerun the precheck.

  • For alerts that can be ignored: click Confirm Alert Details, then Ignore, then OK, and then Precheck Again. Ignoring alerts may result in data inconsistency.

Step 5: Purchase and start the instance

1. Wait until Success Rate reaches 100%, then click Next: Purchase Instance.

2. On the Purchase Instance page, configure the following:

   Parameter	Description
   Resource Group	The resource group for the data migration instance. Default: default resource group. See What is Resource Management?
   Instance Class	Select the instance class based on your required migration speed. See Instance classes of data migration instances.

3. Read and agree to Data Transmission Service (Pay-as-you-go) Service Terms.

4. Click Buy and Start, then click OK.

After the task starts, monitor its progress on the Data Migration page:

• Tasks without incremental migration stop automatically when full migration completes. The status shows Completed.

• Tasks with incremental migration run continuously. The status shows Running.