Data Transmission Service:Migrate a self-managed MySQL to a cross-account RDS for MySQL via a private connection

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

Permissions required for database accounts

For information about how to create database accounts and grant privileges:

• 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.

Before you begin

1. Create an account for a self-managed MySQL instance and configure binary logging.

2. Log on to the console by using the Alibaba Cloud account associated with the private connection. Allow DTS to access the network. For more information, see Connect an on-premises data center to the DTS cloud service by using a VPN gateway.

3. Create a RAM role and grant it permissions to access the cloud resources of the Alibaba Cloud account associated with the private connection. For more information, see How to configure RAM authorization for cross-account data migration of a self-managed database in a VPC.

Procedure

1. Log on to the DTS console by using the Alibaba Cloud account to which the destination RDS instance belongs.

   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. Set **Instance Type** to Self-managed Database Connected Over Express Connect, VPN Gateway, or Smart Access Gateway, and then click VPC in another Alibaba Cloud account.

6. Configure the source and destination databases.

   Section	Parameter	Description
   N/A	Task name	DTS automatically generates a task name. We recommend specifying a descriptive name for easy identification.
   Source Database	Instance type	Select Self-managed Database Connected Over Express Connect, VPN Gateway, or Smart Access Gateway.
   	Instance region	Select the region of the VPC that is connected to the self-managed database.
   	Alibaba Cloud account ID	Enter the ID of the Alibaba Cloud account associated with the private connection for the self-managed database. Note You can log on to the Security Settings page by using the root Alibaba Cloud account to which the VPC is connected. you can find the Account ID.
   	Role name	Enter the name of the RAM role that you created in Step 3 of the Before you begin section.
   	Peer VPC	Select the VPC that is connected to the self-managed database.
   	Database type	Select MySQL.
   	IP address	Enter the endpoint of the self-managed MySQL database.
   	Port	Enter the service port of the self-managed MySQL database. The default value is 3306.
   	Database account	Enter the database account of the self-managed MySQL database. For information about the required privileges, see Permissions required for database accounts.
   	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 of the destination RDS instance.
   	RDS instance ID	Select the ID of the destination RDS instance.
   	Database account	Enter the database account of the destination RDS instance. For information about the required privileges, see Permissions required for database accounts.
   	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 method	Select Non-encrypted or SSL-encrypted based on your requirements. If you select SSL-encrypted, you must enable SSL encryption for the RDS instance beforehand. For more information, see Configure SSL encryption. Note The Encryption parameter is available only in the China (Hong Kong) region and regions in the Chinese mainland.

7. After completing the configuration, click Set Whitelist and Next in the lower-right corner of the page.

   If the source or destination is an Alibaba Cloud database instance, such as ApsaraDB for MySQL or ApsaraDB for MongoDB, DTS automatically adds the IP addresses of its servers in the corresponding region to the instance's whitelist. If the source or destination is a self-managed database on an ECS instance, DTS automatically adds its IP addresses to the security rules of the ECS instance. You must also ensure that the self-managed database does not restrict access from the ECS instance. If the database is a cluster deployed across multiple ECS instances, you must manually add the DTS IP addresses to the security rules for each ECS instance. If the source or destination is a database in an on-premises data center or on another cloud, you must manually add the DTS IP addresses for the corresponding region to allow access from DTS servers. For a list of DTS server IP addresses, see DTS server IP addresses.

   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.

8. 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.

9. 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.

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

11. 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.

12. 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.

13. Switch your workloads to the destination ApsaraDB RDS for MySQL instance.