Data Transmission Service:Migrate a self-managed MySQL database to an RDS for MySQL instance

Prerequisites

• An ApsaraDB RDS for MySQL instance is created.

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

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

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 or a unique constraint and contains non-unique data, duplicate data may be created in the destination database.

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

• If a source database name violates ApsaraDB RDS naming conventions, you must create a compliant database in the destination instance before configuring the task. Otherwise, DTS creates the database automatically.

  Note

  For more information about naming conventions and how to create a database, see Create a database and accounts.

• If a data migration task fails, DTS automatically attempts to resume it. Before you switch your business workloads to the destination instance, make sure to stop or release the task. This prevents an automatically resumed task from overwriting data in the destination instance with data from the source database.

Billing

Migration types

• Schema migration

  DTS migrates the schema definitions of selected objects to the destination instance. DTS supports schema migration for tables, views, triggers, stored procedures, and stored functions.

  Note

  • During schema migration, DTS converts the DEFINER attribute to INVOKER for views, stored procedures, and functions.

  • Because DTS does not migrate user information, you must grant read and write permissions to the invoker to call views, stored procedures, and functions in the destination database.

• Full data migration

  DTS migrates all existing data from the selected objects in your self-managed MySQL database to the destination ApsaraDB RDS for MySQL instance.

  Note

  During a full data migration, concurrent INSERT operations can cause table fragmentation in the destination instance. After the migration is complete, the table space in the destination database is larger than that in the source database.

• Incremental data migration

  After the full data migration, DTS reads the binary log (binlog) of the source self-managed MySQL database to capture and synchronize data changes to the destination ApsaraDB RDS for MySQL instance. Incremental data migration enables a smooth, minimal-downtime migration to the cloud.

SQL operations for incremental migration

Database account permissions

To create database accounts and grant permissions:

• For a self-managed MySQL database, see Create an account for a self-managed MySQL database and configure binary logging.

• For an ApsaraDB RDS for MySQL instance, see Create a database and accounts and Modify the permissions of a standard account on an ApsaraDB RDS for MySQL instance.

Preparations

Create an account for a self-managed MySQL database and enable binary logging

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. Specify a descriptive name for easy identification. The name does not have to be unique.
   Source Database	Instance Type	Select the instance type based on your source database's deployment. This topic uses User-Created Database with Public IP Address as an example. Note If your self-managed database is of a different instance type, you may need to complete additional preparations. For more information, see Preparations for data migration.
   	Instance Region	When you select User-Created Database with Public IP Address as the instance type, you do not need to set the Instance Region. Note If your self-managed MySQL database is protected by a whitelist, click Get CIDR Blocks of DTS Servers next to the Instance Region field to get the CIDR blocks of DTS servers. Then, add these CIDR blocks to the whitelist of your self-managed MySQL database.
   	Database Type	Select MySQL.
   	Hostname or IP Address	Enter the connection endpoint for the self-managed MySQL database. In this example, a public endpoint is used.
   	Port	Enter the service port for the self-managed MySQL database, which must be accessible over the internet. The default value is 3306.
   	Database Account	Enter the database account for the self-managed MySQL database. For permission requirements, 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 RDS Instance.
   	Instance Region	Select the region where the destination RDS instance is located.
   	RDS Instance ID	Select the ID of the destination RDS instance.
   	Database Account	Enter the database account for the destination RDS instance. For permission requirements, 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.
   	Connection Mode	Select Non-encrypted or SSL-encrypted. If you select SSL-encrypted, you must enable SSL encryption on your RDS instance beforehand. For more information, see Configure SSL encryption. Note The Encryption setting is currently supported only in regions in the Chinese mainland and the China (Hong Kong) region.

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

   For Alibaba Cloud sources and destinations (such as ApsaraDB RDS or ECS-hosted databases), DTS automatically adds its server CIDR blocks to the whitelist or security group. For external databases, such as those in a data center or on another cloud, you must manually add the CIDR blocks to grant DTS access. For the IP addresses to add, see CIDR blocks of DTS servers.

   Warning

   Adding the public CIDR blocks 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, including but not limited to using strong passwords, restricting open ports, using authentication for internal API calls, regularly reviewing and restricting unnecessary network segments, or connecting through private networks such as Express Connect, VPN Gateway, or Smart Access Gateway.

7. Select the migration types and objects.

   Parameter	Description
   Migration Types	For a full migration only, select both Schema Migration and Full Data Migration. For a minimal-downtime migration, 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 the migration to ensure data consistency.
   Migration Objects	In the Available box, click the objects you want to migrate, and then 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 can cause the migration of dependent objects to fail.
   Object Name Mapping	To change the names of migrated objects in the destination instance, use the object name mapping feature. For more information, see Object name mapping.
   Retry Duration for Failed Connections	The default retry duration is 12 hours. You can also specify a custom duration. If DTS reconnects to the databases within the specified time, the data migration task automatically resumes. Otherwise, the task fails. Note You are charged for the DTS task while it is retrying the connection. We recommend that you set a custom retry duration based on your business needs or release the DTS instance as soon as possible after the source and destination instances are released.
   Migrate Temporary Tables Created by DMS Online DDL	If you use Data Management (DMS) to perform online DDL operations on the source database, you can choose whether to migrate the temporary tables generated by these operations. Yes: DTS migrates data from the temporary tables created by online DDL operations. Note If the temporary tables from online DDL operations are very large, the data migration task may experience delays. No: DTS does not migrate data from temporary tables, and migrates only the original DDL data from the source database. Note This option can cause table locks on the destination database.

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 to start the data migration task.

Stop the migration task

• Full data migration

  Do not manually stop a task during full data migration. Otherwise, the system may fail to migrate all data. Wait until the migration task automatically ends.

• Incremental data migration

  This type of task does not stop automatically; you must stop it manually.

  1. Wait until the task enters the Incremental Data Migration stage with a Undelayed status. Then, stop writes to the source database for a few minutes. During this period, the Incremental Data Migration status might report a latency.
  2. Wait for the Incremental Data Migration status to return to Undelayed. Once this is confirmed in the list, select the task's checkbox and click the StopEnd button in the batch operations bar at the bottom.

What to do next

The database accounts used for data migration have read and write permissions. To ensure database security, delete these accounts after the migration is complete.

FAQ

• Q: What should I do if the precheck fails?

  A: For more information, see Troubleshoot source database connectivity issues.

• Q: What should I do if a data migration task fails?

  A: For more information, see Fix a failed data migration task.