From On-Premises MySQL to RDS MySQL

Diagram

Figure 1 VPN

Figure 2 Public network+SSL connection

Migration Process

Figure 3 Flowchart

Migration Suggestions (Important)

• Database migration is closely impacted by a wide range of environmental and operational factors. To ensure the migration goes smoothly, perform a test run before the actual migration to help you detect and resolve any potential issues in advance. Recommendations on how to minimize any potential impacts on your data base are provided in this section.
• It is strongly recommended that you start your migration task during off-peak hours. A less active database is easier to migrate successfully. If the data is fairly static, there is less likely to be any severe performance impacts during the migration.

Notes on Migration (Important)

Before creating a migration task, read the migration notes carefully.

For details, see precautions on using specific migration tasks in Data Replication Service Real-Time Migration.

Preparations

1. Permissions
   Table 1 lists the permissions required for the source and destination databases when migrating data from on-premises MySQL databases to the RDS MySQL instances on the current cloud.

   Table 1 Required permissions

   Database	Full Migration	Full+Incremental Migration
   Source	SELECT, SHOW VIEW, and EVENT	SELECT, SHOW VIEW, EVENT, LOCK TABLES, REPLICATION SLAVE, and REPLICATION CLIENT
   Destination	SELECT, CREATE, ALTER, DROP, DELETE, INSERT, UPDATE, INDEX, EVENT, CREATE VIEW, CREATE ROUTINE, TRIGGER, and WITH GRANT OPTION.	SELECT, CREATE, ALTER, DROP, DELETE, INSERT, UPDATE, INDEX, EVENT, CREATE VIEW, CREATE ROUTINE, TRIGGER, and WITH GRANT OPTION.

   • Source database permissions:

     The source database user must have all the required permissions listed in Table 1. If the permissions are insufficient, create a user that has all of the permissions on the source database.

   • Destination database permissions:

     If the destination database is an RDS MySQL database on the current cloud, the initial account can be used.

2. Network settings
   • Source database network settings:

     You can migrate data from on-premises MySQL databases to an RDS MySQL instance on the current cloud through a VPN or public network. Enable public accessibility or establish a VPN for the on-premises MySQL databases based on your service requirements. You are advised to migrate data through a public network, which is more convenient and cost-effective.

   • Destination database network settings:
     • If the source database attempts to access the destination database through a VPN, you need to enable the VPN service first so that the source database can communicate with the destination RDS MySQL.
     • If the source database attempts to access the destination database through a public network, you do not need to configure the destination database.
3. Security rules
   1. Source database security group settings:
      • The replication instance needs to be able to access the source DB. That means that the EIP of the replication instance must be on the whitelist of the source MySQL DB instance. Before configuring the network whitelist, you need to obtain the EIP of the replication instance.

        After creating a replication instance on the DRS console, you can find the EIP on the Configure Source and Destination Databases page.

      • If the migration is performed over a VPN network, add the private IP address of the DRS replication instance to the network whitelist of the source MySQL database to enable the source MySQL database to communicate with the current cloud. The IP address on the Configure Source and Destination Databases page is the private IP address of the replication instance.

      If you do take this step, then once the migration is complete, you should delete this item from the whitelist or your system will insecure.

   2. Destination database security group settings:

      By default, the destination database and the DRS replication instance are in the same VPC and can communicate with each other. No further configuration is required.

4. Other

   DRS supports migration of some parameters that are closely related to services and performance. For details about these parameters, see Parameters for Comparison. If you need to migrate other parameters, configure them manually based on service requirements.

Migration Procedure

The following describes how to use DRS to migrate data from an on-premises MySQL database to an RDS MySQL instance on the current cloud over a public network.

1. Create a migration task.
   1. Log in to the management console and choose Databases > Data Replication Service to go to the DRS console.
   2. On the Online Migration Management page, click Create Migration Task.
   3. On the Create Replication Instance page, configure the task details and the replication instance, and click Next.
      Figure 4 Replication instance information
      

      Table 2 Task settings

      Parameter	Description
      Region	The region where your service is running. You can change the region.
      Task Name	The task name consists of 4 to 50 characters, starts with a letter, and can contain only letters (case-insensitive), digits, hyphens (-), and underscores (_).
      Description	The description consists of a maximum of 256 characters and cannot contain the following special characters: =<>&'\"

      Table 3 Replication instance settings

      Parameter	Description
      Data Flow	To the cloud
      Source DB Engine	MySQL
      Destination DB Engine	MySQL
      Network Type	Select Public network. Enabling SSL is recommended. It may slow down the migration by 20% to 30% but it ensures data security.
      Destination DB Instance	The MySQL DB instance you purchased.
      Destination Database Access	You can select Read-only or Read/Write. Read-only During migration, the destination database is read-only. After the migration is complete, it restores to the read/write status. This option ensures the integrity and success rate of data migration. Read/Write During migration, the destination database can be queried or modified. Data may be modified when operations are performed or applications are connected. It should be noted that background processes can often generate or modify data, which may result in data conflicts, task faults, and upload failures. Do not select this option if you do not fully understand the risks.
      Migration Type	Select Full+Incremental as an example. Full: This migration type is suitable for scenarios where service interruption is acceptable. All objects and data in non-system databases are migrated to the destination database at one time. The objects include tables, views, and stored procedures. NOTE: If you are performing a full migration, do not perform operations on the source database. Otherwise, data generated in the source database during the migration will not be synchronized to the destination database. Full+Incremental: This migration type allows you to migrate data without interrupting services. After a full migration initializes the destination database, an incremental migration initiates and parses logs to ensure data consistency between the source and destination databases. NOTE: If you select the Full+Incremental migration type, data generated during the full migration will be synchronized to the destination database with zero downtime, ensuring that the source database remain accessible.

   4. On the Configure Source and Destination Databases page, wait until the replication instance is created. Then, specify source and destination database information and click Test Connection for both the source and destination databases to check whether they have been connected to the replication instance. After the connection tests are successful, select the check box before the agreement and click Next.
      Figure 5 Source and destination database details
      

      Table 4 Source database settings

      Parameter	Description
      IP Address or Domain Name	The IP address or domain name of the source database.
      Port	Enter an integer ranging from 1 to 65535, which indicates the port number of the source database.
      Database Username	A username for the source database.
      Database Password	The password for the database username.
      SSL Connection	To improve data security during a migration on a public network, you are advised to enable SSL to encrypt migration links and upload a CA certificate.

      Table 5 Destination database settings

      Parameter	Description
      DB Instance Name	The RDS MySQL instance you selected during the replication instance creation is displayed by default and cannot be changed.
      Database Username	The username for accessing the destination MySQL instance.
      Database Password	The password for the database username.
      Migrate Definer to User	Yes The Definers of all source database objects will be migrated to the user. Other users do not have permissions for database objects unless these users are authorized. For details on authorization, see How Do I Maintain the Original Service User Permission System After Definer Is Forcibly Converted During MySQL Migration? in Data Replication Service FAQs. No The Definers of all source database objects will not be changed. You need to migrate all accounts and permissions of the source database in the next step.

   5. On the Set Task page, set the flow control mode and select migration accounts and objects.
      Figure 6 Migration object
      

      Table 6 Migration types and objects-public network

      Parameter	Description
      Flow Control	You can choose whether to control the flow. You can customize the maximum migration speed. In addition, you can set the time range based on your service requirements. The traffic rate setting usually includes setting of a rate limiting time period and a traffic rate value. Flow can be controlled all day or during specific time ranges. The default value is All day. A maximum of three time ranges can be set, and they cannot overlap. The flow rate must be set based on the service scenario and cannot exceed 9,999 MB/s. If the migration speed is not limited, the outbound bandwidth of the source database is maximally used, which will increase the read burden on the source database. For example, if the outbound bandwidth of the source database is 100 MB/s and 80% bandwidth is used, the I/O consumption on the source database is 80 MB/s. NOTE: Flow control mode takes effect only during a full migration. You can also change the flow control mode after creating a task. In the task list on the Online Migration Management page, locate the target task and choose More > Speed or Speed in the Operation column.
      Filter DROP DATABASE	During synchronization, executing DDL operations on the source database may affect the data synchronization performance to some extent. To reduce data synchronization risks, DRS allows you to filter out DDL operations. The database deletion operation can be filtered out by default. If you select Yes, the database deletion operation performed on the source database is not synchronized during data synchronization. If you select No, related operations are synchronized to the destination database during data synchronization. NOTE: Only the database deletion operation can be filtered.
      Migrate Account	During a database migration, accounts need to be migrated separately. There are accounts that can be migrated completely, accounts whose permissions need to be reduced, and accounts that cannot be migrated. You can choose whether to migrate the accounts based on your service requirements. Yes If you choose to migrate accounts, see Migrating Accounts in Data Replication Service User Guide to migrate database users, permissions, and passwords. No During the migration, accounts and permissions are not migrated.
      Migrate Object	All database objects can be migrated. After the objects are migrated to the destination DB instance, the object names remain the same as those in the source database and cannot be modified. You can migrate all objects or specified objects based on your service requirements. All: All objects in the source database are migrated to the destination database. Self-defined: Only self-defined objects are migrated to the destination database. NOTE: If you choose not to migrate all of the databases, the migration may fail because the objects, such as stored procedures and views, in the database to be migrated may have dependencies on other objects that are not migrated. To ensure a successful migration, you are advised to migrate all of the databases.

   6. Click Next. On the Check Task page, check the migration task.
      • If any check fails, review the cause and rectify the fault. After the fault is rectified, click Check Again.
      • If all check items are successful, click Next.
   7. Compare the source and target parameters.
      By comparing common and performance parameters for the source databases against those of the destination databases, you can help ensure that services will not change after a migration is completed. You can determine whether to use this function based on service requirements. It mainly ensures that services are not affected after a migration is completed.

      • As this process is optional, you can click Next if you wish to skip this step.
      • Compare common parameters:

        If the parameter values in the list shown are inconsistent, you can click Save Change to change the destination database values to match those of the source database.

        Figure 7 Modifying common parameters
        

        Performance parameter values in both the source and destination can be the same or different.

        • There is a value that is consistent, but you still want to change it in the destination, locate the parameter, enter the value in the Change To column, and click Save Change in the upper left corner.
        • If you want to change a destination database value to match the source same:
          1. Click Use Source Database Value.

             The system automatically updates the destination database value to match the source.

             Figure 8 One-click modification
             
             

             You can also manually enter parameter values.

          2. Click Save Change to save your changes.

             The system changes the parameter values based on your settings for the destination database values. After the modification, the comparison results are automatically updated.

             Figure 9 Performance parameters
             

             Some parameters in the destination database require a restart before the changes can take effect. The system will display these as being inconsistent. You will need to restart the destination database after either before the migration starts or after it has completed. To minimize the impact of this restart on your services, it is recommended that you schedule a specific time to restart the destination database after the migration is complete.

             For details about parameter comparison, see Parameters for Comparison in the Data Replication Service User Guide.

          3. Click Next.
   8. On the displayed page, specify Start Time, Send Notification, SMN Topic, Synchronization Delay Threshold, and Stop Abnormal Tasks After and confirm that the configured information is correct and click Submit to submit the task.
      Figure 10 Task startup settings
      

      Table 7 Task startup settings

      Parameter	Description
      Start Time	Set Start Time to Start upon task creation or Start at a specified time based on site requirements. The Start at a specified time option is recommended. NOTE: The migration task may affect the performance of the source and destination databases. You are advised to start the task in off-peak hours and reserve two to three days for data verification.
      Send Notifications	SMN topic. This parameter is optional. If an exception occurs during migration, the system will send a notification to the specified recipients.
      SMN Topic	This parameter is available only after you enable Send Notification and create a topic on the SMN console and add a subscriber. For details, see Simple Message Notification User Guide.
      Synchronization Delay Threshold	During an incremental migration, a synchronization delay indicates a time difference (in seconds) of synchronization between the source and destination database. If the synchronization delay exceeds the threshold you specify, DRS will send alarms to the specified recipients. The value ranges from 0 to 3,600. To avoid repeated alarms caused by the fluctuation of delay, an alarm is sent only after the delay has exceeded the threshold for six minutes. NOTE: In the early stages of an incremental migration, there is more delay because more data is waiting to be synchronized. In this situation, no notifications will be sent. Before setting the delay threshold, enable Send Notification. If the delay threshold is set to 0, no notifications will be sent to the recipient.
      Stop Abnormal Tasks After	Number of days after which an abnormal task is automatically stopped. The value must range from 14 to 100. The default value is 14. NOTE: Tasks in the abnormal state are still charged. If tasks remain in the abnormal state for a long time, they cannot be resumed. Abnormal tasks run longer than the period you set (unit: day) will automatically stop to avoid unnecessary fees.

   9. After the task is submitted, go back to the Online Migration Management page to view the task status.

2. Manage the migration task.
   The migration task contains two phases: full migration and incremental migration. You can manage them in different phases.

   • Full migration
     • Viewing the migration progress: Click the target full migration task, and on the Migration Progress tab, you can see the migration progress of the structure, data, indexes, and migration objects. When the progress reaches 100%, the migration is complete.
     • Viewing migration details: In the migration details, you can view the migration progress of a specific object. If the number of objects is the same as that of migrated objects, the migration is complete. You can view the migration progress of each object in detail. Currently, this function is available only to whitelisted users. You can submit a service ticket to apply for this function.
   • Incremental Migration Permission
     • Viewing the synchronization delay: After the full migration is complete, an incremental migration starts. On the Online Migration Management page, click the target migration task. On the displayed page, click Migration Progress to view the synchronization delay of the incremental migration. If the synchronization delay is 0s, the destination database is being synchronized with the source database in real time. You can also view the data consistency on the Migration Comparison tab.
       Figure 11 Viewing the synchronization delay
       
     • Viewing the migration results: On the Online Migration Management page, click the target migration task. On the displayed page, click Migration Comparison and perform a migration comparison in accordance with the comparison process, which should help you determine an appropriate time for migration to minimize service downtime.
       Figure 12 Database comparison process
       

       For details, see Comparing Migration Items in Data Replication Service User Guide.

3. Cut over services.

   You are advised to start the cutover process during off-peak hours. At least one complete data comparison is performed during off-peak hours. To obtain accurate comparison results, start data comparison at a specified time point during off-peak hours. If it is needed, select Start at a specified time for Comparison Time. Due to slight time difference and continuous operations on data, inconsistent comparison results may be generated, reducing the reliability and validity of the results.

   1. Interrupt services first. If the workload is not heavy, you may not need to interrupt the services.
   2. Run the following statement on the source database and check whether any new sessions execute SQL statements within the next 1 to 5 minutes. If there are no new statements executed, the service has been stopped.

      show processlist

      

      The process list queried by the preceding statement includes the connection of the DRS replication instance. If no additional session executes SQL statements, the service has been stopped.

   3. On the Migration Progress page, view the synchronization delay. When the delay is displayed as 0s and remains stable for a period, then you can perform a data-level comparison between the source and destination databases. For details about the time required, refer to the results of the previous comparison.
      • If there is enough time, compare all objects.
      • If there is not enough time, use the data-level comparison to compare the tables that are frequently used and that contain key business data or inconsistent data.
   4. Determine an appropriate time to cut the services over to the destination database. After services are restored and available, the migration is complete.

4. Stop or delete the migration task.
   1. Stopping the migration task. After databases and services are migrated to the destination database, to prevent operations on the source database from being synchronized to the destination database to overwrite data, you can stop the migration task. This operation only deletes the replication instance, and the migration task is still displayed in the task list. You can view or delete the task. After the task is stopped, DRS will not charge you anymore.
   2. Delete the migration task. After the migration task is complete, you can delete it. After the migration task is deleted, it will no longer be displayed in the task list.