Data Transmission Service:Migrate a self-managed MySQL database to PolarDB for MySQL

Source database

• The server must have sufficient outbound bandwidth; otherwise migration speed decreases.

• Tables must have PRIMARY KEY or UNIQUE constraints with all fields unique; otherwise duplicate records may appear in the destination.

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

• During schema migration and full data migration, do not run DDL statements that change database or table schemas; the task will fail.

• During full-only migration (no incremental), do not write to the source database; this causes data inconsistency. To maintain consistency, select schema migration, full data migration, and incremental data migration together.

• Data generated by cascade operations or restored from a physical backup is not recorded in binary logs and will not be migrated.

• If the source is MySQL 8.0.23 or later and the data includes invisible columns, those columns cannot be read and data loss occurs. To make a column visible, run: ALTER TABLE <table_name> ALTER COLUMN <column_name> SET VISIBLE;

Tables without primary keys automatically generate invisible primary keys. Make those invisible primary keys visible before migration. See Generated Invisible Primary Keys.

Binary log requirements for incremental migration:

Self-managed MySQL source: special cases

• If a primary/secondary switchover occurs on the source while the task is running, the task fails.

• Migration latency is calculated based on the timestamp of the latest migrated data in the destination versus the current time in the source. If no DML operations are performed on the source for an extended period, the reported latency may be inaccurate. Run a DML operation on the source to refresh the latency value. If you migrate an entire database, create a heartbeat table that is updated every second.

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

ApsaraDB RDS for MySQL source: special cases

• In incremental data migration, an ApsaraDB RDS for MySQL instance that does not record transaction logs, such as a read-only ApsaraDB RDS for MySQL V5.6 instance, cannot be used as the source database.

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

Destination database (PolarDB for MySQL)

• DTS automatically creates databases in the destination cluster. If the source database name does not comply with PolarDB for MySQL naming conventions, manually create the database before configuring the task. See Manage databases.

• Throttling is not available for full data migration to PolarDB for MySQL.

Other limitations

• The source and destination MySQL versions must match.

• DTS does not migrate data that uses parser definitions specified via comments.

• Schedule migration during off-peak hours. Full data migration uses read and write resources of both databases and increases server load.

• After full data migration, concurrent INSERT operations create table fragmentation in the destination, making its tablespace larger than the source.

• If data contains four-byte characters (rare characters, emojis), the destination database and tables must use the UTF8mb4 character set. If you use DTS schema migration, set character_set_server to UTF8mb4 in the destination database.

• DTS uses ROUND(COLUMN, PRECISION) to read FLOAT and DOUBLE columns. Default precision: 38 digits for FLOAT, 308 digits for DOUBLE. Verify that precision settings meet your requirements.

• DTS retries failed tasks for up to 7 days. Before switching workloads to the destination, stop or release any failed task, or revoke write permissions from DTS accounts. Otherwise, the source database may overwrite destination data when a failed task resumes.

• DATETIME values cannot be converted to VARCHAR.

• If DDL statements fail in the destination, the DTS task continues running. View failed DDL statements in task logs.

• If the source is an ApsaraDB RDS for MySQL instance with EncDB enabled, full data migration is not supported. Transparent Data Encryption (TDE) is supported for all three migration types.

• To migrate accounts, see Migrate database accounts.

• If a DTS task fails, DTS technical support attempts to restore it within 8 hours. The task may be restarted and certain task parameters may be modified during restoration. Database parameters are not modified.

Step 1: Open the Data Migration page

Use either console to start:

Step 2: Configure source and destination databases

Click Create Task to open the task configuration page.

Task Name

Source Database

Destination Database

Step 3: Test connectivity

At the bottom of the page, click Test Connectivity and Proceed, then click Test Connectivity in the CIDR Blocks of DTS Servers dialog.

Make sure the CIDR blocks of DTS servers can be added (automatically or manually) to the security settings of both databases. See Add the CIDR blocks of DTS servers.

Step 4: Select migration objects and types

On the Configure Objects page, configure the following:

Parameter	Description
Migration Types	Select Schema Migration and Full Data Migration for a one-time migration. To maintain service continuity, also select Incremental Data Migration. If you omit Schema Migration, manually create the target database and tables, and enable object name mapping in Selected Objects.
Method to migrate triggers in source database	Select a trigger migration method based on your requirements. Available only when Migration Types includes Schema Migration. See Configure the method to synchronize or migrate triggers.
Processing mode of conflicting tables	Precheck and Report Errors: fails the precheck if identical table names exist in source and destination (use object name mapping to rename conflicting tables). Ignore Errors and Proceed: skips the precheck for identical table names. Note that data inconsistency may result: during full data migration, DTS does not migrate the conflicting record and the existing destination record is retained; during incremental data migration, DTS migrates the record and the existing destination record is overwritten.
Whether to migrate event	Select Yesalert notification settings to migrate events, then complete the follow-up steps. See Synchronize or migrate events.
Capitalization of object names in destination instance	Controls case of database, table, and column names in the destination. Default: DTS default policy. See Specify the capitalization of object names.
Source Objects	Select objects and click to move them to Selected Objects. You can select columns, tables, or schemas. Selecting tables or columns excludes views, triggers, and stored procedures.
Selected Objects	Right-click an object to rename it individually, or click Batch Edit for bulk renaming. To filter rows, right-click a table and set a WHERE condition. To restrict SQL operations at the database or table level, right-click and select the operations to include.

Step 5: Configure advanced settings

Click Next: Advanced Settings and configure:

Step 6: Configure data verification (optional)

Click Next Step: Data Verification to set up a data verification task. See Configure a data verification task.

Step 7: Run the precheck and purchase an instance

1. Click Next: Save Task Settings and Precheck.

   To preview the API parameters for this task configuration, hover over Next: Save Task Settings and Precheck and click Preview OpenAPI parameters.

2. Wait for the precheck to complete.

   • If an item fails, click View Details, fix the issue, then click Precheck Again.

   • If an alert is triggered for an item that can be ignored, click Confirm Alert Details > Ignore > OK, then click Precheck Again.

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

4. On the Purchase Instance page, set the Instance Class:

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

5. Select the Data Transmission Service (Pay-as-you-go) Service Terms check box, then click Buy and Start > OK.

View the task progress on the Data Migration page:

• Schema migration or full-only migration: the task stops automatically. Status shows Completed.

• Incremental data migration: the task runs continuously and does not stop automatically. Status shows Running.