:Migrate from PolarDB for MySQL to self-hosted MySQL

Usage notes

• During a full data migration, DTS consumes read and write resources on the source and destination databases, increasing their load. If your databases have poor performance, low specifications, or high workloads (for example, if the source database has many slow SQL queries or tables without primary keys, or if deadlocks occur in the destination database), the increased load can strain your databases or even cause service interruptions. Perform the data migration during off-peak hours, such as when the CPU utilization of both databases is below 30%.

• If a source table lacks a primary key, unique constraint, or any field combination that ensures uniqueness, DTS may generate duplicate data in the destination.
• Full data migration uses concurrent INSERT operations that cause table fragmentation. The destination tablespace will be larger than the source after migration.
• DTS automatically recovers failed tasks. Before switching workloads to the destination, stop or release the migration task to prevent it from resuming and overwriting destination data.
• You cannot adjust the rate of a full data migration.

Limitations

• DTS supports schema migration for tables, views, triggers, stored procedures, and stored functions.
  Note During schema migration, DTS changes the DEFINER for views, stored procedures, or functions to INVOKER.
• DTS does not migrate user information. To call views, stored procedures, or functions in the destination, grant read and write permissions to the caller.

Migration types

Billing

SQL operations for incremental migration

Required permissions for database accounts

For information about how to create and grant permissions to a database account, see Create a PolarDB for MySQL database account and Create a database account for a self-hosted MySQL database.

Procedure

1. Log on to the 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 left-side navigation pane, click Data Migration.

3. At the top of the Migration Tasks page, select the region of the destination cluster.

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

5. Configure the source and destination databases.

   Category	Setting	Description
   N/A	Task Name	DTS automatically generates a task name. We recommend that you specify a descriptive name for easy identification. The name does not have to be unique.
   Source Database	Instance Type	Select PolarDB.
   	Instance Region	Select the region where the source PolarDB for MySQL cluster is located.
   	PolarDB Instance ID	Select the ID of the source PolarDB for MySQL cluster.
   	Database Account	Enter the database account of the PolarDB for MySQL cluster. For information about permission requirements, see Required permissions for database accounts.
   	Database Password	Enter the password for the database account. Note After you configure the source database, click Database Password next to Test Connectivity to verify the settings. If the message Test Passed appears, the information is correct. If the message Test Failed appears, click Test FailedDiagnostics next to the message and adjust the source database settings based on the diagnosis.
   Destination Database	Instance Type	Select an instance type based on where your destination database is deployed. This topic uses ECS-Hosted Database as an example. Note If your self-hosted database is of a different instance type, you must perform additional preparations. For more information, see Preparations.
   	Instance Region	Select the region where the ECS instance is located.
   	ECS Instance ID	Select the ID of the ECS instance that hosts the destination MySQL database.
   	Database Type	Select MySQL.
   	Port	Enter the port number used by the database service. In this example, enter 3306.
   	Database Account	Enter the database account of the self-hosted MySQL database. For information about permission requirements, see Required permissions for database accounts.
   	Database Password	Enter the password for the database account. Note After you configure the destination database, click Database Password next to Test Connectivity to verify the settings. If the message Test Passed appears, the information is correct. If the message Test Failed appears, click Test FailedDiagnostics next to the message and adjust the destination database settings based on the diagnosis.
   	Connection Method	Select Non-encrypted or SSL-encrypted based on your needs. In this example, Non-encrypted is selected.

6. After you configure the settings, click Add Whitelist and Go to Next Step.
   Note This step automatically adds the IP addresses of the DTS servers to the IP address whitelist of the source PolarDB for MySQL cluster and the inbound security rules of the security group for the destination ECS instance. This ensures that the DTS servers can connect to the source cluster and the destination instance.

7. Select the migration types and the objects to be migrated.

   Parameter	Description
   Migration types	To perform only a full migration, select both Schema Migration and full data migration. To perform a migration with minimal downtime, select Schema Migration, 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 migration to ensure data consistency.
   Migration objects	In the Available box, click the objects that you want to migrate, and then click the icon to move them to the Selected Objects box. Note You can select databases, tables, and columns as migration objects. 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. If you use the object name mapping feature, dependent objects might fail to migrate.
   Mapped name change	To rename migration objects in the destination instance, use the object name mapping feature. For more information, see Object name mapping.
   Retry time for failed connection to source or destination database	By default, if DTS cannot connect to the source or destination database, it retries for 720 minutes (12 hours). You can customize the retry time. If DTS reconnects within the specified time, the migration task automatically resumes. Otherwise, the migration task fails. Note You are charged for the task while DTS retries the connection. We recommend that you set the retry time based on your business needs or release the DTS instance as soon as possible after the source and destination database instances are released.
   Replicate temporary table to destination during online DDL in source table by DMS	If you use Data Management (DMS) to perform online DDL operations on the source database, choose whether to migrate the temporary tables generated by these operations. Yes: Migrate the data from the temporary tables generated by online DDL operations. Note If the temporary tables generated by online DDL operations contain a large amount of data, the migration task may be delayed. No: Do not migrate data from 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 the configuration, click Precheck and Start in the lower-right corner of the page.

   Note

   • Before the migration task starts, DTS runs a precheck. 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 as prompted and run the precheck again.

     • If you do not need to fix the warning items, you can select Ignore and then click Ignore Warnings and Rerun Precheck to run the precheck again.

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

10. In the Confirm Settings dialog box that appears, select a Instance Class and select the Data Transmission Service (pay-as-you-go) Service Terms checkbox.

11. Click Buy and Start to begin the migration.

    • Schema migration + Full data migration

      Allow the task to complete automatically. Stopping it manually may result in incomplete data.

    • Schema migration + Full data migration + Incremental data migration

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

      Important

      Choose an appropriate time to stop the task manually, such as during off-peak hours or when you are ready to switch your business to the destination cluster.

      1. Wait until the migration task enters the Incremental Data Migration phase and the status shows Undelayed. Then, stop writing data to the source database for several minutes. During this time, the status of Incremental Data Migration may show a latency.

      2. Wait for the Incremental Data Migration status to show Undelayed again. Then, manually stop the migration task.