This topic describes how to upgrade the major engine version of an ApsaraDB RDS instance from MySQL 5.6 to MySQL 5.7 by using Data Transmission Service (DTS).

Prerequisites

MySQL 5.6 and MySQL 5.7 support different features. If you want to use the syntax or features that are supported by MySQL 5.6 but not by MySQL 5.7, you must manually adjust the code. For more information, see Table 1.

The upgrade requires you to create an RDS instance that runs the destination MySQL version and synchronize data from the source RDS instance to the destination RDS instance. The specifications of the destination RDS instance must be equivalent to or higher than those of the source RDS instance.

The following example describes how to upgrade an RDS instance that resides in the China (Shenzhen) region.

Create an RDS instance that runs MySQL 5.7

  1. Log on to the ApsaraDB RDS console. In the left-side navigation pane, click Instances. Then, find the source RDS instance and click its ID.
  2. On the Basic Information page, view and record the following configuration items of the source RDS instance:
    • Zone
    • CPU
    • Memory
    • Maximum input/output operations per second (IOPS)
    • Storage capacity
    Specifications of the source RDS instance Specifications of the source RDS instance
  3. Return to the Instances page. Then, click Create Instance.
  4. In the Basic Configurations step, configure the destination RDS instance. Make sure that the specifications mentioned in Step 2 are equivalent to or higher than those of the source RDS instance.
    1. Select MySQL 5.7 for the Database Engine parameter and Shenzhen Zone D for the Zone of Primary Node parameter.
      Note We recommend that you set the Zone of Primary Node parameter to the same value as that of the source RDS instance. If the source and destination RDS instances reside in the same zone, the data synchronization between the instances is more stable.
      Create the destination RDS instance - 2
    2. Select an instance type and a storage capacity that are equivalent to or higher than those of the source RDS instance.
      Create the destination RDS instance - 3
    3. Click Next: Instance Configuration.
  5. Confirm the configurations of the destination RDS instance and complete the payment.
    1. In the Instance Configuration step, confirm the configurations and click Next: Confirm Order.
    2. In the Confirm Order step, confirm the order details, read and select Terms of Service, and then click Pay Now.
    3. On the Purchase page, click Purchase.
    Wait for 1 to 5 minutes. Then, return to the RDS console and click Instances in the left-side navigation pane. You can view the information about the created destination RDS instance. Created instance
  6. Enable Secure Sockets Layer (SSL) encryption or transparent data encryption (TDE).
    Note Make sure that the source and destination RDS instances use the same SSL encryption and TDE settings. If SSL encryption or TDE is enabled on the source RDS instance, you must also enable them on the destination RDS instance.
    1. Find the destination RDS instance and click its ID. The Basic Information page appears.
    2. In the left-side navigation pane, click Data Security. On the page that appears, click the SSL Encryption tab and turn on SSL Encryption.
      Note It requires about 5 minutes for SSL encryption to take effect. During the process, the destination RDS instance is unavailable.
      Enable SSL encryption
    3. Click the TDE tab and turn on TDE Status.
      Note

      It requires about 5 minutes for TDE to take effect. During the process, the destination RDS instance is unavailable.

      Enable TDE
  7. Create a privileged account for the destination RDS instance.
    Note The data synchronization from the source RDS instance to the destination RDS instance is performed by using privileged accounts. Therefore, you must create a privileged account for the destination RDS instance.
    1. In the left-side navigation pane, click Accounts. On the page that appears, click the Accounts tab. Then, click Create Account.
      Create the privileged account - 1
    2. In the Create Account panel, configure the parameters, such as Database Account, Account Type, and Password. Then, click Create.
      Create the privileged account - 2

      The created account appears in the account list. If Inactive appears in the Status column of the account, wait until ApsaraDB RDS activates the account.

      Activate the privileged account

Configure DTS

DTS allows you to migrate data between homogeneous or heterogeneous data sources. It also supports different extract, transform, and load (ETL) features, such as data filtering and three-level mappings on databases, tables, and columns. For more information, see What is DTS?.

  1. Create a data synchronization task.
    1. Log on to the Data Transmission Service console.
    2. In the left-side navigation pane, click Data Synchronization. In the Synchronization Tasks section of the page that appears, select the region where the source RDS instance resides. Then, click Create Data Synchronization Task to go to the DTS buy page.
      Create Data Synchronization Task

      In this example, select China (Shenzhen).

    3. On the page that appears, configure the parameters based on the following figure. In this example, select Pay-As-You-Go.
      Data Transmission Service - configurations
      Note We recommend that you select Bidirectional Synchronization to increase fault tolerance and ensure a smooth upgrade. A smooth upgrade prevents possible errors from affecting databases.
    4. Click Buy Now to go to the Confirm Order page.
    5. Read and select Terms of Service. Then, click Activate Now.
      Create a data synchronization task
    6. View the created data synchronization task. On the Pay page, click Console to return to the Data Transmission Service console. In the left-side navigation pane, click Data Synchronization. In the Synchronization Tasks section of the page that appears, select the region where the destination RDS instance resides. In this example, select China (Shenzhen).
      In the lower part of the page, you can view the information about the created data synchronization task. The information includes the task name and the links for data synchronization in both the Forward and Reverse directions. View the information about the created data synchronization task
  2. Configure the data synchronization link in the forward direction.
    1. Find the link in the forward direction. Then, click Configure Task in the Actions column to go to the Create Data Synchronization Task page.
      Go to the page to configure the link in the forward direction
    2. On the Create Data Synchronization Task page, configure the parameters such as Instance ID and Encryption Mode based on your business requirements. Then, click Set Whitelist and Next to go to the Select Objects to Be Synchronized step.
      Create Migration Task - 2
      where:
      • 1: Select the source RDS instance for Instance ID.
      • 2 and 5: Specify Encryption based on the actual configurations of the source and destination RDS instances.
      • 3: Select the destination RDS instance for Instance ID.
      • 4: Configure Database Account and Database Password based on the privileged account of the destination RDS instance.
    3. In the Select Objects to Be Synchronized step, configure the parameters, such as Conflict Resolution Policy and Available. Then, click Next.
      Configure the link in the forward direction
    4. In the Advanced Settings step, select Initial Schema Synchronization and Initial Full Data Synchronization. Then, click Precheck.
      Configure the link in the forward direction - Advanced Settings

      The Precheck message appears. Wait until the precheck is complete.

      After the precheck is complete and the data synchronization link is initialized, the status of the link changes to Synchronizing. Configure the link in the forward direction - complete
  3. Configure the data synchronization link in the reverse direction.
    Note The operations to configure the data synchronization link in the forward and reverse directions are similar.
    1. Find the link in the reverse direction. Then, click Configure Task in the Actions column to go to the Select Source and Destination Instances step.
    2. Configure the parameters for the source and destination RDS instances. Then, click Set Whitelist and Next to go to the Select Objects to Be Synchronized step.
      where:
      • In this step, the source RDS instance refers to the created RDS instance that runs MySQL 5.7.
      • In this step, the destination RDS instance refers to the original RDS instance that runs MySQL 5.6.
    3. Configure the parameters to match those you use to configure the link in the forward direction. Then, click Next to go to the Advanced Settings step.
    4. In the Advanced Settings step, select Initial Schema Synchronization and Initial Full Data Synchronization. Then, click Precheck.
    The Precheck message appears. Wait until the precheck is complete. After the link is synchronized, the status of the link changes to Performing initial synchronization and then Synchronizing.
    Note
    • The time required to synchronize data varies based on the amount of data in the source RDS instance and also the transmission rate of DTS. For example, a source RDS instance has 2.88 GB of data, and the DTS transmission rate is 454 KB/s. In this case, the time required to synchronize the data can be calculated based on the following formula: 2.88 × 1024 × 1024/454 = 6495 (seconds). About 100 minutes are required. If you want to accelerate data synchronization, you can click Upgrade in the Actions column to upgrade the specifications of your links.
    • For more information about how to troubleshoot and fix the issues that occur during the upgrade, see Configure two-way data synchronization between ApsaraDB RDS for MySQL instances.

Synchronize other configurations

After the data is synchronized, you must synchronize other configurations from the source RDS instance to the destination RDS instance.

  1. Synchronize the account configurations to the destination RDS instance.
    1. Log on to the ApsaraDB RDS console. In the left-side navigation pane, click Instances. On the page that appears, find the destination RDS instance and click its ID in the Instance ID/Name column. The Basic Information page appears.
      Synchronize account configurations
    2. In the left-side navigation pane, click Accounts. On the page that appears, synchronize the account configurations.
      Synchronize account configurations - 2
  2. In the left-side navigation pane, click Basic Information. On the page that appears, synchronize the basic configurations, such as whitelist, read-only instance, and disaster recovery instance configurations.
    Synchronize basic configurations
  3. Change the endpoint of the destination RDS instance.
    1. In the left-side navigation pane, click Database Connection. On the page that appears, click Change Endpoint to open the Change Endpoint dialog box.
      Change Endpoint
    2. In the Change Endpoint dialog box, change the endpoint to that of the source RDS instance and click OK.
      Change Endpoint - 2
    3. Change the endpoint of the source RDS instance to another endpoint by using the same method. For example, append -5.6 to the endpoint of the source RDS instance.

Release the source RDS instance

After your workloads run without errors for a specific period, such as several days, you can release the DTS links in both forward and reverse directions. In this case, you can also release the source RDS instance.

FAQ

In the following figure, the source RDS instance that runs MySQL 5.6 is a primary RDS instance. Its secondary RDS instance is a self-managed instance that runs MySQL 5.7 on an Elastic Compute Service (ECS) instance. How do I upgrade the source RDS instance? FAQ scenario - 1
Note
  • A1-1: the source RDS instance that runs MySQL 5.6. This is a primary RDS instance.
  • A1-2: the ECS instance that hosts the MySQL 5.7-running self-managed instance. This is a secondary RDS instance.
  • B1: the newly created RDS instance that runs MySQL 5.7. This is a primary RDS instance after the upgrade.
Troubleshooting:
  1. Create links in the forward and reverse directions between A1-1 and B1 in the DTS console by following the instructions provided in the preceding sections. After data is synchronized from A1-1 to B1, stop writing data to A1-1. Wait for 3 minutes to 5 minutes.
  2. Configure A1-2 as a secondary RDS instance of B1. You can use one of the following methods to perform the configuration on A1-2:
    • Use master_log_pos and master_log_file.
    • Use a global transaction identifier (GTID).
  • Use master_log_pos and master_log_file.
    1. Run the following commands on A1-2:
      stop slave;
      reset slave all;
    2. Run the following command on B1 and record the values of File and Position:
      show master status;
    3. Run the following commands on A1-2:
      change master to master_host='rm-wz9******.mysql.rds.aliyuncs.com', master_user='userName', master_password='yourPassword',master_log_file='mysql-bin.******',master_log_pos=******;
      start slave;

      The values of the master_log_file and master_log_pos are the same as those of File and Position recorded in the previous step.

  • Use a GTID.
    1. Run the following commands on A1-2:
      stop slave;
      reset slave all;
      reset master;
    2. Run the following command on B1 and record the value of Executed_Gtid_Set:
      show master status;
    3. Run the following commands on A1-2:
      change master to master_host='rm-wz9*******.mysql.rds.aliyuncs.com', master_user='userName', master_password='Password';
      set global GTID_PURGED='037*****-****-****-****-************:*-****7';
      start slave

      The value of GTID_PURGED is the same as that of Executed_Gtid_Set recorded in the previous step.

MySQL 5.6 and MySQL 5.7 provide different features. What are the differences between these features?

After you upgrade the major engine version of your RDS instance from MySQL 5.6 to MySQL 5.7, you must adjust the code. This applies if you want to continue using the features and syntax that are supported by MySQL 5.6. The following table lists the features and syntax.

Table 1. Table - Features and syntax that are supported by MySQL 5.6 but not by MySQL 5.7
Feature or syntax MySQL 5.6 MySQL 5.7
The create as select syntax in GTID mode Supported Not supported
Use of temporary tables in transactions in GTID mode Supported Not supported
Multi-source replication Supported Not supported
The select for update wait and select from update syntax Supported Not supported
The update non_affected_rows insert syntax Supported Not supported
select statement_timeout Supported Not supported
The select ENGINE_NO_CACHE syntax Supported Not supported
Invisible indexes Supported Not supported
RocksDB engine Supported Not supported