Migrate self-managed MySQL to ApsaraDB MyBase for MySQL

This topic describes the migration procedure in the new Data Transmission Service (DTS) console. For instructions on how to migrate a self-managed MySQL database to ApsaraDB MyBase for MySQL in the classic DTS console, see Migrate from a self-managed MySQL database to ApsaraDB MyBase for MySQL.

Prerequisites

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

• You have created a destination database instance. For more information, see Create an ApsaraDB MyBase for MySQL instance.
• The destination instance must have more storage space than the source database.
• If your self-managed MySQL database is deployed on-premises, you must add the CIDR blocks of DTS servers to its IP whitelist. This action allows DTS to access your database. For more information, see Add the CIDR blocks of DTS servers to an IP whitelist.

Supported source and destination databases

Limitations

Billing

Migration types

• Schema migration

  Data Transmission Service (DTS) migrates the schema definitions of the migration objects from the source database to the destination database.

  • DTS supports schema migration for tables, views, triggers, stored procedures, and functions.

    Note

    The routine_body of stored procedures, the routine_body of functions, and the select_statement of views are not modified.

  • During schema migration, DTS changes the `DEFINER` to `INVOKER` for the views, stored procedures, and functions to be migrated. This action changes the value of `SQL SECURITY` to `INVOKER`. DTS also sets the `DEFINER` to the destination database account that is used for the migration task.

    Note

    The security authentication method and definer of the source database are not modified.

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

  DTS migrates all historical data of the specified migration objects from the source database to the destination database.

• Incremental migration

  After a full migration is complete, DTS migrates incremental data updates from the source database to the destination database. Incremental migration lets you smoothly migrate data without interrupting your self-managed applications.

Supported 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 MyBase for MySQL instance, see Create a database account and Modify the permissions of a database account.

Procedure

1. Log on to the Data Migration Tasks page of the new DTS console.

   Note

   You can also log on to the Data Management (DMS) console. In the top menu bar, choose Data + AI > Data Migration.

2. In the upper-left corner of the page, select the region where the migration instance is located.

3. Click Create Task to configure the source and destination databases.
   Warning

   After you select the source and destination instances, we recommend that you carefully read the limits displayed at the top of the page. Otherwise, the task may fail or data inconsistency may occur.

   Category	Parameter	Description
   None	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	Select Instance	You can choose whether to use an existing instance, as needed. If you use an existing instance, the database information below is automatically filled in. You do not need to enter it again. If you do not use an existing instance, you must enter the database information below.
   	Database Type	Select MySQL.
   	Access Method	Select an access method based on the deployment location of your source database. In this example, we select Self-managed Database on ECS.
   	Instance Region	Select the region where the source MySQL database is located.
   	ECS Instance ID	Select the ECS instance from which you want to migrate data.
   	Port	Enter the service port of the source MySQL database. The port must be accessible over the public internet. The default port is 3306.
   	Database Account	Enter the account for the source MySQL database. For permission requirements, see Database account permissions.
   	Database Password	Enter the password for the database account.
   	Connection Method	Select Non-encrypted or SSL-encrypted based on your security requirements. In this example, Non-encrypted is selected.
   Destination Database	Select a DMS database instance.	You can choose whether to use an existing instance, as needed. If you use an existing instance, the database information is automatically filled in. You do not need to enter it again. If you do not use an existing instance, you must enter the database information below.
   	Database Type	Select MySQL.
   	Connection Type	Select cloud instance.
   	Instance Region	Select the region where the destination ApsaraDB MyBase for MySQL instance is located.
   	RDS Instance ID	Select the ID of the destination ApsaraDB MyBase for MySQL instance.
   	Database Account	Enter the account for the destination ApsaraDB MyBase for MySQL instance. For permission requirements, see Database account permissions.
   	Database Password	Enter the password for the database account.
   	Connection Method	Select Non-encrypted or SSL-encrypted based on your security requirements. In this example, Non-encrypted is selected.

4. After you complete the configuration, click Test Connectivity and Proceed.
5. If your database uses an IP whitelist, copy the DTS server IP addresses from the pop-up dialog box and add them to the whitelist. Then, click Test Connectivity and Proceed.
   Note

   • If the source or destination database is an Alibaba Cloud database instance, such as ApsaraDB MyBase for MySQL, or a self-managed database on an ECS instance, DTS automatically adds the IP addresses of DTS servers in the corresponding region to the instance's IP whitelist or the ECS security group rules. You do not need to manually add them. For more information, see IP addresses of DTS servers. If the source or destination database is a self-managed database in a data center or another cloud provider, you must manually add the IP addresses of the DTS servers to allow access.
   • Adding public IP address ranges of DTS servers, whether automatically or manually, may pose security risks. By using this product, you acknowledge these potential risks and are responsible for implementing basic security measures. These measures include, but are not limited to, using strong passwords, restricting open ports for each network segment, using authentication for internal API calls, regularly reviewing and restricting unnecessary network segments, and using private network connections such as Express Connect, VPN Gateway, or Smart Access Gateway.
   • After the DTS task is complete or released, we recommend that you manually check for and remove the IP address ranges related to DTS.

6. If your self-managed database uses a whitelist for security, copy the DTS server IP addresses from the dialog box and add them to the whitelist of your self-managed database. Then, click Test Connectivity.

   Warning

   Adding the public IP address blocks of DTS servers, whether automatically or manually, may introduce security risks. Using this product, you acknowledge and accept these potential risks. You must implement basic security measures, such as using strong passwords, limiting open ports for each CIDR block, using authentication for internal API calls, and regularly checking and restricting unnecessary CIDR blocks. You can also connect using an internal network, such as a leased line, VPN Gateway, or Smart Access Gateway.

7. Configure the task objects and advanced settings.
   • Basic settings:

     Parameter	Description
     Task Stages	If you only need to perform a full migration, select both Schema Migration and Full Data Migration. To perform a migration with no downtime, select Schema Migration, Full Data Migration, and Incremental Data Migration. Note If you do not select Schema Migration, you must ensure that a database and tables to receive the data exist in the destination database. You can also use the object name mapping feature in the Selected Objects box as needed. If you do not select Incremental Data Migration, do not write new data to the source instance during data migration to ensure data consistency.
     Processing Mode of Conflicting Tables	Precheck and Report Errors: Checks whether tables with the same names exist in the destination database. If no tables with the same names exist, the precheck is passed. If tables with the same names exist, an error is reported during the precheck, and the data migration task does not start. Note If a table in the destination database has the same name but cannot be easily deleted or renamed, you can change the name of the table in the destination database. For more information, see Object name mapping. Ignore Errors and Proceed: Skips the check for tables with the same names. Warning Selecting Ignore Errors and Proceed may cause data inconsistency and business risks. For example: If the table schemas are consistent and a record in the destination database has the same primary key value as a record in the source database: During full migration, DTS keeps the record in the destination database. The record from the source database is not migrated. During incremental migration, DTS does not keep the record in the destination database. The record from the source database overwrites the record in the destination database. If the table schemas are inconsistent, only some columns of data may be migrated, or the migration may fail. Proceed with caution.
     Select Objects	In the Source Objects box, click the objects to migrate, and then click to move them to the Selected Objects box.

     Note

     • To change the name of a single migration object in the destination instance, right-click the object in the Selected Objects box. For more information about how to set the name, see Map a single table or column name.

     • To change the names of multiple migration objects in a batch, click Batch Edit in the upper-right corner of the Selected Objects box. For more information about how to set the names, see Map multiple table or column names in a batch.

     Note

     If you use the object name mapping feature, other objects that depend on the mapped object may fail to migrate.

   • Advanced settings:

     Parameter	Description
     Monitoring and Alerting	Select whether to set alerts and receive alert notifications based on your business needs. No: Does not set an alert. Yes: Configure alerts by setting an alert threshold and an alert contact. If a migration fails or the latency exceeds the threshold, the system sends an alert notification.
     Capitalization of Object Names in Destination Instance	You can configure the case sensitivity policy for the names of migrated objects, such as databases, tables, and columns, in the destination instance. By default, DTS default policy is selected. You can also choose to keep the case sensitivity consistent with the default policy of the source or destination database. For more information, see Case sensitivity of object names in the destination database.
     Copy temporary tables created by online DDL tools from the source table to the destination database	If you use Data Management (DMS) or gh-ost to perform online DDL changes in the source database, you can choose whether to migrate the data from the temporary tables generated by the online DDL changes. Important DTS tasks do not support using tools such as pt-online-schema-change to perform online DDL changes. Otherwise, the DTS task fails. The processing methods for each phase are as follows: The Schema Migration and Full Data Migration phases do not allow DDL operations that change the database or table structure. Therefore, they are not controlled by the online DDL policy. Schema Migration: Not controlled by the online DDL policy. Related temporary tables are created. Full Data Migration: Not controlled by the online DDL policy. The migration of temporary tables is not included in the full migration objects. All tables whose names match the regular expression (^_(.+)_(?:gho|new)$ or ^_(.+)_(?:ghc|del|old)$) are filtered out. Incremental Data Migration: Controlled by the online DDL policy. Yes: Migrates data changes from temporary tables (for example, _table_name_gho) generated by online DDL operations. No, Adapt to DMS Online DDL and No, Adapt to gh-ost: Filters out data changes from temporary tables (for example, _table_name_gho) generated by tools such as gh-ost based on regular expression rules. Yes: Migrates the data from the temporary tables generated by online DDL changes. Note If online DDL changes generate a large amount of data in temporary tables, it may cause task latency. No, Adapt to DMS Online DDL: Does not migrate the data from the temporary tables generated by online DDL changes. It only migrates the original DDL statements executed using Data Management (DMS). Note This option causes tables in the destination database to be locked. No, Adapt to gh-ost: Does not migrate the data from the temporary tables generated by online DDL changes. It supports custom filtering rules. DTS filters out data changes from temporary tables (for example, _table_name_gho) generated by tools such as gh-ost based on regular expression rules. You can modify the default regular expressions used to match shadow and useless tables as needed: Shadow table: ^_(.+)_(?:gho|new)$ Useless table: ^_(.+)_(?:ghc|del|old)$ Note This option causes tables in the destination database to be locked.
     Retry Time for Failed Connections	After the migration task starts, if the connection to the source or destination database fails, DTS reports an error and immediately begins to retry the connection. The default retry duration is 720 minutes. You can customize the retry time to a value from 10 to 1440 minutes. We recommend that you set the duration to more than 30 minutes. If DTS reconnects to the source and destination databases within the specified duration, the migration task automatically resumes. Otherwise, the task fails. Note For multiple DTS instances that share the same source or destination, the network retry time is determined by the setting of the last created task. Because you are charged for the task during the connection retry period, we recommend that you customize 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.

8. After you complete the preceding configurations, click Next: Save Task Settings and Precheck.
   Note

   • Before the migration task starts, DTS performs a precheck. The task can start only after it passes the precheck.
   • If the precheck fails, click the hint icon next to the failed item for details.
     • You can fix the issue based on the hint and then run the precheck again.
     • If you do not need to fix a failed warning item, you can select Confirm Masking or Ignore and Re-Precheck to skip the warning item and run the precheck again.
9. When the Precheck Pass Rate reaches 100%, click Next: Purchase Instance.
10. On the Purchase page, select the Instance Class for the data migration instance. The following table describes the parameter.

    Category	Parameter	Description
    Parameters	Instance Class	DTS provides migration instances with different specifications and performance levels. The migration speed varies depending on the Instance Class you select. Choose an Instance Class based on your business scenario. For more information, see Specifications of data migration instances.

11. After the configuration is complete, read and select Data Transmission Service (Pay-as-you-go) Service Terms.

12. Click Buy and Start. In the OK dialog box that appears, click OK.

    You can view the progress of the migration task on the Data Migration Tasks list page.

    Note

    • If the migration task does not include incremental migration, it stops automatically after the full migration is complete. After the task stops, its Status changes to Completed.

    • If the migration task includes incremental migration, it does not stop automatically. The incremental migration task continues to run. While the incremental migration task is running, the Status of the task is Running.

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 data migration is complete.

FAQ

• Q: What do I do if a precheck fails?

  A: For more information, see Source database connectivity check.

• Q: How do I fix a failed migration task?

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