Migrate from self-managed MySQL to PolarDB-X

Prerequisites

• The source self-managed MySQL database is version 5.1, 5.5, 5.6, 5.7, or 8.0.

• The databases in the destination PolarDB-X instance must be created on ApsaraDB RDS for MySQL. DTS does not support data migration to databases that are created based on PolarDB for MySQL.

• The ApsaraDB RDS for MySQL instance hosting the destination database in PolarDB-X must have more storage space than the source self-managed MySQL database.

Notes

• When you migrate data from MySQL to PolarDB-X, DTS does not support schema migration.

  Note

  Schema migration refers to the migration of the definitions of schema objects, such as table structures, from the source database to the destination database.

• A full data migration consumes read and write resources on both the source and destination databases, increasing their server load. This added load can degrade performance or cause service unavailability, especially if your databases have poor performance, low specifications, or are already busy (for example, with many slow SQL queries, tables without primary keys, or deadlocks in the destination database). Before you start the migration, evaluate the performance of both databases and perform the migration during off-peak hours, for example, when CPU utilization is below 30%.

• If a source table to be migrated lacks a primary key or unique constraint, duplicate data may occur in the destination database.

• For columns of the FLOAT or DOUBLE data type, DTS reads the values using ROUND(COLUMN,PRECISION). If you do not explicitly define a precision, DTS migrates FLOAT data with a precision of 38 digits and DOUBLE data with a precision of 308 digits. Verify that these precisions meet your business requirements.

• If a data migration task fails, DTS automatically tries to resume the task. Stop or release the migration task before switching workloads to the destination instance. This prevents the task from automatically resuming and overwriting data in the destination instance with data from the source database.

Billing

Migration types

• Full data migration

  DTS migrates all existing data from the selected objects in your self-managed MySQL database to the destination PolarDB-X database.

  Note

  During full data migration, concurrent INSERT operations can cause table fragmentation in the destination instance. As a result, the table space in the destination instance might be larger than that in the source instance after the migration is complete.

• Incremental data migration

  After a full data migration, DTS reads the binary logs of the source self-managed MySQL database to synchronize incremental data to the destination PolarDB-X instance. This enables a seamless migration from your self-managed MySQL database to PolarDB-X with minimal downtime for your application.

SQL operations for incremental migration

INSERT, UPDATE, DELETE, and REPLACE

Database account permissions

To create database accounts and grant permissions:

• For a self-managed MySQL database, see Preparations before data migration.

• For PolarDB-X, see Manage accounts.

Migration preparations

1. Create an account and configure binary logging for the self-managed MySQL database.

2. Based on the source table schemas, create the corresponding databases and tables in the destination PolarDB-X instance. For more information, see Create a database and Basic SQL operations.

Procedure

1. Log on to the Data Transmission Service (DTS) console.

   Note

   If you are automatically redirected to the Data Management (DMS) console, you can click the icon in the lower-right corner and then click to return to the classic DTS console.

2. In the navigation pane on the left, click Data Migration.

3. At the top of the Migration Tasks page, select the region where the destination instance is located.

4. In the upper-right corner of the page, click Create Data Migration Task.

5. Configure the source and destination databases.

   Section	Parameter	Description
   N/A	Task Name	DTS automatically generates a task name, but we recommend specifying a descriptive one for easy identification. The name does not need to be unique.
   Source Database	Instance Type	Select the option that matches your source database deployment. This topic uses Self-managed database with public IP as an example. Note If your self-managed database is a different instance type, you must perform additional preparations. For more information, see Preparations.
   	Instance Region	If you select Self-managed database with public IP for Instance Type, you do not need to set Instance Region. Note If a whitelist is configured for your self-managed MySQL database, click Get DTS IP Segments next to the Instance Region parameter to obtain the CIDR blocks of the DTS servers. Then, add the obtained CIDR blocks to the whitelist of your self-managed MySQL database.
   	Database Type	Select MySQL.
   	Hostname or IP Address	Enter the endpoint of the self-managed MySQL database. In this example, enter the public endpoint.
   	Port	Enter the service port of the self-managed MySQL database. The default value is 3306.
   	Database Account	Enter the account for the self-managed MySQL database. For information about the required permissions, see Database account permissions.
   	Database Password	Enter the password for the database account. Note After you enter the source database information, you can click Test Connectivity next to Database Password to verify that the information is correct. If the information is correct, the message Passed is displayed. If the message Failed is displayed, click Diagnose next to the Failed message and adjust the source database information based on the prompts.
   Destination Database	Instance Type	Select DRDS Instance.
   	Instance Region	Select the region where the destination PolarDB-X instance is located.
   	DRDS Instance ID	Select the ID of the destination PolarDB-X instance.
   	Database Name	Select the name of the destination database.
   	Database Account	Enter the account for the destination PolarDB-X database. For information about the required permissions, see Database account permissions.
   	Database Password	Enter the password for the database account. Note After you enter the destination database information, you can click Test Connectivity after Database Password to verify that the entered information is correct. If the information is correct, a Passed message is displayed. If a Failed message is displayed, click Diagnose after Failed and adjust the destination database information based on the prompts.

6. After you complete the configuration, click Set Whitelist and Next.

   If the source or destination database is an Alibaba Cloud database instance such as ApsaraDB RDS for MySQL or ApsaraDB for MongoDB, DTS automatically adds the CIDR blocks of DTS servers in the corresponding region to the whitelist of the Alibaba Cloud database instance. If the source or destination database is a self-managed database on an ECS instance, DTS automatically adds the CIDR blocks of DTS servers in the corresponding region to the security rules of the ECS instance. You must also make sure that your self-managed database allows access from the ECS instance. If your database is deployed in a cluster on multiple ECS instances, you must manually add the CIDR blocks of DTS servers in the corresponding region to the security rules of each ECS instance. If the source or destination database is a self-managed database in an on-premises data center or a database on another cloud, you must manually add the CIDR blocks of DTS servers to allow access from DTS servers. For the CIDR blocks of DTS servers, see CIDR blocks of DTS servers.

   Warning

   Adding public IP addresses of DTS servers, whether automatically or manually, may introduce security risks. By using this product, you acknowledge and accept these potential risks. You are responsible for implementing basic security measures, such as using strong passwords, restricting open ports, using authentication for internal APIs, regularly reviewing and limiting unnecessary network segments, or connecting by using private connections such as Express Connect, VPN Gateway, or Smart Access Gateway.

7. Select migration types and migration objects.

   Setting	Description
   Migration Type	If you require only full data migration, select Full Data Migration. If you want to perform migration with minimal downtime, select both Full Data Migration and Incremental Data Migration. Note If you do not select Incremental Data Migration, do not write new data to the source database during the migration to ensure data consistency.
   Migration Objects	In the Available box, click the objects that you want to migrate and click the icon to move them to the Selected Objects box. Note You can select objects at the database, table, or column level. If you select only tables or columns, other objects such as views, triggers, and stored procedures are not migrated. By default, object names in the destination database are the same as in the source database. To rename an object in the destination database, use the object name mapping feature. For more information, see Object name mapping. Using the object name mapping feature may cause the migration of dependent objects to fail.
   Rename Mapped Objects	To change the name of a migration object in the destination instance, use the object name mapping feature. For more information, see Object name mapping.
   Retry Duration for Connection Failure to Source or Destination Database	The default retry duration is 12 hours. You can also specify a custom retry duration. If DTS reconnects to the source and destination databases within the specified duration, the migration task automatically resumes. Otherwise, the task fails. Note You are charged for the running DTS task during the connection retry period. We recommend that you specify a custom retry duration based on your business needs or release the DTS instance as soon as the source and destination instances are released.
   Whether to replicate temporary tables to the destination database during DMS_ONLINE_DDL operations on the source table	This migration scenario does not support DDL operations, so you must select No. If DDL migration is supported for this scenario in the future and you use Data Management (DMS) to perform online DDL changes on the source database, you can choose whether to migrate the temporary tables generated by these changes. Yes: Migrates the temporary tables generated by online DDL changes. Note If the temporary tables generated by online DDL changes are very large, the migration task may be delayed. No: Does not migrate the temporary tables. Only the original DDL data from the source database is migrated. Note This option may cause tables in the destination database to be locked.

8. After you complete these settings, click Pre-check and Start in the lower-right corner of the page.

   Note

   • DTS performs a precheck before the task starts. The task can start only after it passes the precheck.

   • If the precheck fails, click the icon next to the failed item to view details.

     • Fix the issues based on the details, and then run the precheck again.

     • For warnings that do not require a fix, you can select Ignore or Ignore and Rerun Precheck to run the precheck again.

9. After the task passes the precheck, click Next.

10. On the Confirm Order page, select a Instance Class and select the Data Transmission Service (Pay-As-You-Go) Terms of Service checkbox.

11. Click Buy and Start. The migration task starts.

    • Full data migration

      Do not manually stop the migration task. This can cause data loss. Wait for the task to finish. The task stops automatically.

    • Incremental data migration

      The migration task does not stop automatically. You must stop it manually.

      Note

      Stop the task manually at a suitable time, such as during off-peak hours or when you are ready to switch your business to the destination instance.

      1. Wait until the task status changes to Incremental Data Migration and shows Undelayed. Then, stop writing data to the source database for a few minutes. The status of Incremental Data Migration may show a latency.

      2. Wait until the status of Incremental Data Migration changes back to Undelayed. Then, stop the migration task manually. The progress of schema migration and full data migration both show 100%. To pause the migration task, select the task and click Pause in the batch operations bar at the bottom.

12. Switch your workloads to PolarDB-X.

Next steps

The database account used for data migration has read and write permissions. To ensure database security, please delete the database account from the self-managed MySQL database and PolarDB-X after the data migration is complete.