You may need to change the character set of a table (for example, from GBK to UTF8mb4) based on your business requirements. However, an ALTER statement that is used to change the character set of a large table will have negative impacts on your business.

This topic describes how to change the character set without affecting your business. You can create a table schema in the destination instance based on the new character set, and then use DTS to migrate table data to the destination instance. To further ensure service continuity, you can prepare a rollback solution or a dual-write solution.

Precautions

  • Before you change the character set, ensure that your business system and SQL statements are compatible with the new character set and the features of the business system will not be affected.
  • During full data migration, DTS uses read/write resources of the source and destination instances. This may increase the database load. Before you migrate data, evaluate the performance of the source and destination instances. We recommend that you migrate data during off-peak hours.
  • The source instance must have PRIMARY KEY or UNIQUE constraints and all fields must be unique. Otherwise, duplicate data may exist in the destination instance.

Step 1: Import the table schema from the source instance to the destination instance

  1. Export the script of the target table schema from the source instance. The target table schema refers to the schema of the table whose character set you want to change.
    1. Log on to the source instance by using Data Management (DMS). For more information, see Use DMS to log on to an ApsaraDB for RDS instance.
    2. In the top navigation bar, choose Data Operation > Export.
      Export
    3. On the page that appears, choose New > Export Database.
    4. In the dialog box that appears, specify the parameters.
      Export a table schema
      Note Select the destination database, select Structure for the Content parameter, and specify the other parameters based on your business requirements.
    5. Click OK. In the dialog box that appears, click YES.
  2. Decompress the exported file, and change the character set in the script of the table schema.
    Change the character set
    Note The example shown in this figure changes the GBK character set to UTF8mb4.
  3. Create an ApsaraDB RDS for MySQL instance of the same type as the source instance. The new instance is the destination instance. For more information about how to create an ApsaraDB RDS for MySQL instance, see Create an ApsaraDB RDS for MySQL instance.
  4. Import the edited script of the table schema to the new ApsaraDB RDS for MySQL instance.
    1. Log on to the destination ApsaraDB RDS for MySQL instance by using DMS. For more information, see Use DMS to log on to an ApsaraDB for RDS instance.
    2. In the top navigation bar, choose Data Operation > Import.
      Import
    3. On the page that appears, click New Task.
    4. In the dialog box that appears, specify the parameters, as shown in the following figure. Then, click Start.
      Import the table schema
      Note After the table schema is imported, you can execute the show create table <Table name>; statement to verify the new character set of the table.

Step 2: Migrate table data from the source instance to the destination instance

  1. Log on to the DTS console.
  2. In the left-side navigation pane, click Data Migration.
  3. At the top of the Migration Tasks page, select the region where the destination cluster resides.
    Select a region
  4. In the upper-right corner of the page, click Create Migration Task.
  5. Configure the source and destination databases of the data migration task.
    Configure the source and destination databases
    Region Parameter Description
    N/A Task Name DTS automatically generates a task name. We recommend that you use an informative name for easy identification. You do not need to use a unique task name.
    Source Database Instance Type Select RDS Instance.
    Instance Region Select the region where the source RDS instance resides.
    RDS Instance ID Select the ID of the source RDS instance.
    Database Account Enter the database account of the source RDS instance. The account must have the read/write permissions on the database to be migrated.
    Database Password Enter the password of the database account.
    Note After you specify the source database parameters, click Test Connectivity next to Database Password to verify whether the specified parameters are valid. If the specified parameters are valid, the Passed message appears. If the Failed message appears, click Check next to Failed. Modify the source database parameters based on the check results.
    Encryption Select Non-encrypted or SSL-encrypted. If you want to select SSL-encrypted, you must have enabled SSL encryption for the RDS instance. For more information, see Configure SSL encryption for an RDS for MySQL instance.
    Note The Encryption parameter is available only for data migration instances that reside in mainland China and Hong Kong (China).
    Destination Database Instance Type Select RDS Instance.
    Instance Region Select the region where the destination RDS instance resides.
    RDS Instance ID Select the ID of the destination RDS instance.
    Database Account Enter the database account of the destination RDS instance. The account must have the read/write permissions on the destination database.
    Database Password Enter the password of the database account.
    Note After you specify the destination database parameters, click Test Connectivity next to Database Password to verify whether the parameters are valid. If the specified parameters are valid, the Passed message appears. If the Failed message appears, click Check next to Failed. Modify the destination database parameters based on the check results.
    Encryption Select Non-encrypted or SSL-encrypted. If you want to select SSL-encrypted, you must have enabled SSL encryption for the RDS instance. For more information, see Configure SSL encryption for an RDS for MySQL instance.
    Note The Encryption parameter is available only for data migration instances that reside in mainland China and Hong Kong (China).
  6. In the lower-right corner of the page, click Set Whitelist and Next.
    Note The CIDR blocks of DTS servers are automatically added to the whitelist of the source ApsaraDB RDS for MySQL instance. This ensures that DTS servers can connect to the source RDS instance.
  7. Select the migration types and objects to be migrated.
    Select the migration types and objects to be migrated
    Parameter Description
    Migration Types Schema migration is completed in Step 1. Therefore, you do not need to select Schema Migration in this step.
    • To perform only full data migration, select only Full Data Migration.
    • To migrate data with minimal downtime, select Full Data Migration and Incremental Data Migration.
    Note
    • If Incremental Data Migration is not selected, do not write data into the source database during full data migration. This ensures data consistency between the source and destination databases.
    • The following SQL operations can be migrated during incremental data migration: INSERT, UPDATE, DELETE, REPLACE, CREATE FUNCTION, CREATE INDEX, CREATE PROCEDURE, CREATE TABLE, CREATE VIEW, DROP INDEX, DROP TABLE, RENAME TABLE, and TRUNCATE TABLE.
    Objects In the Available section, select the objects whose schema has been migrated in Step 1, and click the Right arrow icon to add the objects to the Selected section.
    Warning We recommend that you do not use the object name mapping feature. Otherwise, the data migration task fails.
  8. Start the data migration task.
    1. In the lower-right corner of the page, click Precheck.
      Note
      • Before you can start the data migration task, a precheck is performed. A data migration task can be started only if it passes the precheck.
      • If the task fails to pass the precheck, click the Info icon icon next to each failed item to view details. Fix the issues as instructed and run the precheck again.
    2. After the task passes the precheck, click Next.
    3. In the Confirm Settings dialog box that appears, specify the Channel Specification parameter and select Data Transmission Service (Pay-As-You-Go) Service Terms.
    4. Click Buy and Start to start the data migration task.

Step 3: Switch your workloads

Switch your workloads by using one of the following solutions:

  • Rollback solution: You do not need to edit the code of your application. However, a rollback failure may occur.
    1. After data migration is completed, verify the data in the destination ApsaraDB RDS for MySQL instance.
    2. Prepare a rollback solution, and then switch your workloads to the destination instance. The rollback solution is based on a data migration task in the opposite direction. The task allows you to switch workloads back to the original source instance. For more information, see Switch workloads to the destination database.
    3. Test the features of your business. If the features function as expected, delete the data migration task in the opposite direction.
    4. If you no longer need the source ApsaraDB RDS for MySQL instance, release the instance.
  • Dual-write solution: This solution ensures a high success rate of rollback. However, great efforts are required to edit your application code.
    1. Edit the application code to implement the dual-write solution. The dual-write solution writes data changes to both the source and destination instances.
    2. Stop the data 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

        The task does not automatically end during incremental data migration. You must manually stop the migration task.

        1. Wait until the task progress bar shows Incremental Data Migration and The migration task is not delayed. Then, stop writing data to the source database for a few minutes. In some cases, the progress bar shows the delay time of incremental data migration.
        2. After the status of incremental data migration changes to The migration task is not delayed, manually stop the migration task.Stop a task during incremental migration
      Note Make sure that no sessions are performing write operations. To retrieve session information, you can Log on to the source instance and execute the show processlist; statement.
    3. Verify the data in the destination ApsaraDB RDS for MySQL instance.
    4. Enable the dual-write solution for your application. Then, data changes will be written to both the source and destination instances.
    5. Test the features of your business on the destination instance. If the features function as expected, disable the dual-write solution.
    6. If you no longer need the source ApsaraDB RDS for MySQL instance, release the instance.