Data Transmission Service:Seamlessly change the character set for an ApsaraDB RDS for MySQL instance

This topic describes a method to change the character set for an ApsaraDB RDS for MySQL instance with minimal service disruption. The process involves creating a table schema with the new character set in a destination instance, migrating data from the source instance using Data Transmission Service (DTS), and implementing a rollback solution or a dual-write solution to ensure a smooth transition.

Precautions

• Before changing the character set, test your applications and SQL statements to ensure they are compatible with the new character set.

• During a full data migration, DTS consumes read and write resources on both the source and destination instances, which can increase the database load. Before you start, evaluate the performance of both instances and consider performing the data migration during off-peak hours.

• If a table on the source instance lacks a primary key or unique constraint and its columns do not guarantee uniqueness, the migration might create duplicate data in the destination instance.

Step 1: Export and import the table schema

In this example, all instances accessed through Data Management (DMS) use the Flexible Control Mode.

1. Export the schema script for the tables whose character set you want to change from the source instance.

   1. Log on to the source instance using DMS. For more information, see Log on to an ApsaraDB RDS instance by using DMS.

   2. In the top navigation bar, choose Database Development > Data Export > Database Export.

      Note

      To perform this operation, you must first enable Drop-down Menus in Top Navigation Bar in the settings menu, accessible via the

   3. Configure the export settings.

      Note

      The required parameters and configuration process may vary depending on the management mode of the destination instance. For more information, see Export databases.

      Setting	Description
      Data Export Type in Application	Select Database Export.
      Database name	Select the database that you want to export. Note You must have the required permissions to export the database. For more information, see View my permissions.
      Exported table	In this example, Specific Tables is selected. You can select Specific Tables or All Tables based on your requirements. Note If you select Specific Tables, you must select the tables and configure filter conditions on the right side of the page.
      Export content	Select Code Structure.
      File Format	In this example, SQL is selected by default and cannot be changed.
      Exported Structure Type	Select the schema types to export based on your business requirements. Procedure Function Trigger View Event Note The supported schema types vary depending on the database type. The options displayed in the console are definitive.
      More Options	Click Big Data Export Options or SQL Script Options, and then select the required options.

   4. Click Submit an application and wait for the ticket to be approved.

   5. After the ticket is approved, in the Download section, click Download Exported File. The download link is valid for 24 hours. Be sure to download the file within this period.

2. Unzip the downloaded file. Then, open the schema script and modify the character set configuration to your target character set.

3. Purchase a new ApsaraDB RDS for MySQL instance with the same specifications as the source instance. For more information, see Create an ApsaraDB RDS for MySQL instance.

4. Import the modified schema script into the new ApsaraDB RDS for MySQL instance.

   1. Log on to the new ApsaraDB RDS for MySQL instance using DMS. For more information, see Log on to an ApsaraDB RDS instance by using DMS.

   2. In the top navigation bar, choose Database Development > Data Change > Data Import.

   3. Configure the import settings.

      Note

      The required parameters and configuration process may vary depending on the management mode of the instance. For more information, see Import data.

      Parameter	Description
      Data Change Type in Application	Select Large Data Import.
      Database	The destination database for the import. Note For instances in Flexible or Stable Control Mode, you must log on to the database before you can select it.
      File Encoding	Select the character encoding of the import file: UTF-8 GBK ISO-8859-1 Auto-detect
      Import Mode	Select an import mode. Two modes are supported: Speed Mode: During the execution phase, the system reads the file and directly runs the SQL statements on the destination database. This mode is faster but less secure than Security Mode. Note By default, security rules for the Speed Mode mode do not allow data import in Speed Mode. You can enable this feature for the relevant security rule in Security Rules > SQL Correct > Basic Configuration Item. Security Mode: During the precheck phase, the system parses the file and caches the SQL or CSV data. During the execution phase, it reads from the cache and executes the statements on the destination database. This mode offers better security but is slower than Speed Mode.
      File Type	Select the type of file you are importing. SQL script: In Security Collaboration mode, only INSERT and REPLACE command types are allowed by default. A DBA or administrator can modify the settings in Security Rules > SQL Correct > Large Data Import. CSV: The delimiter in the file must be a comma. Excel: The Excel file can contain headers (attributes) or just data.
      Attachment	Click File to upload the attachment. Note SQL, CSV, TXT, XLSX, and ZIP file types are supported. The maximum file size is 5 GB.
      Other Options	Select whether to ignore errors. Do not ignore (cleared): Default. If an error occurs, the system stops executing subsequent SQL statements and reports an error. Ignore (selected): If an error occurs, the system ignores it and continues to execute subsequent SQL statements.

   4. Click Submit an application. Wait for the system precheck to pass. If the precheck fails, identify and fix the issue based on the prompt, and then submit the ticket again.

      Note

      The system prechecks your uploaded SQL script. If you upload a CSV file, it generates the corresponding INSERT statements.

   5. After the ticket is approved, in the Perform section, click Execute Change.

   6. In the Task Settings dialog box, choose when to execute the ticket. You can choose to Execute Immediately. If not, turn off the Execute Immediately switch and select a start time. Then, click Confirm Execution. The dialog box also displays a Note for Security Change: Try to execute during off-peak hours.

      The procedure consists of the following steps:

      • Execute Immediately: This is the default setting. The ticket is executed immediately after you click Confirm Execution.

      • Scheduled Execution: Select a start time for the task. The system automatically runs the task at the specified time after you click Confirm Execution.

      Note

      • During execution, the system reads the parsed SQL in a stream and runs it on the database in batches (1 MB per batch).

      • In the Execution section, you can view the task execution status, task SQL check details, and scheduling logs.

      • If a paused task is restarted, it runs from the beginning or from the point where it was paused, depending on the import mode.

        • Speed mode: If a paused task is restarted, the script is run or the data file is imported from the beginning.

        • Security mode: If a paused task is restarted, the script is run or the data file is imported from the point where it was paused.

   7. After the import is successful, you can verify the table's character set by running the show create table <table_name>; command.

Step 2: Migrate data to the new instance

1. Log on to the Data Transmission Service (DTS) console.

   Note

   • If you are automatically redirected to the Data Management (DMS) console, click the icon in the chatbot in the lower-right corner to return to the previous version of the DTS console.

   • If you are on the new DTS console, click the icon in the lower-right corner to return to the previous version.

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. Configure the source and destination databases for the migration task.

   Category	Setting	Description
   Task name	-	DTS automatically generates a task name. Specify a descriptive name for easy identification. The name does not need to be unique.
   Source database	Instance type	Select RDS Instance.
   	Instance region	Select the region where the source RDS instance is located.
   	RDS instance ID	Select the ID of the source RDS instance.
   	Database account	Enter the database account for the source RDS instance. This account must have read and write permissions on the databases to be migrated.
   	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.
   	Connection method	Select Non-encrypted or SSL-encrypted. If you choose SSL-encrypted, you must first enable SSL encryption for the RDS instance. For more information, see Configure SSL encryption. Note The Encryption setting is available only in regions in the Chinese mainland.
   Destination database	Instance type	Select RDS Instance.
   	Instance region	Select the region where the destination RDS instance is located.
   	RDS instance ID	Select the ID of the destination RDS instance.
   	Database account	Enter the database account for the destination RDS instance. This account must have read and write permissions on the destination databases.
   	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. If you choose SSL-encrypted, you must first enable SSL encryption for the RDS instance. For more information, see Configure SSL encryption. Note The Encryption setting is available only in regions in the Chinese mainland.

6. After you complete the configuration, click Add Whitelist and Proceed to Next Step in the lower-right corner of the page.

   Note

   In this step, the IP addresses of the DTS servers are automatically added to the whitelist of the ApsaraDB RDS for MySQL instance to ensure that the DTS servers can connect to the source and destination instances.

7. Select migration types and objects.

   Setting	Description
   Migration types	Because schema migration was completed in Step 1, you do not need to select Schema Migration here. To perform only a full migration, select only Full Data Migration. Warning To ensure data consistency, do not write new data to the source instance during the full data migration. For a zero-downtime migration, select both Full Data Migration and Incremental Data Migration. Note The SQL operations supported during the incremental data migration phase are INSERT, UPDATE, DELETE, CREATE TABLE, ALTER TABLE, RENAME TABLE, TRUNCATE TABLE, and DROP TABLE.
   Migration objects	In the Migration Object box, select the objects whose schemas you migrated in Step 1. Then, click the icon to move them to the Selected Objects box. Warning Do not perform object name mapping, as the schema is already migrated. This will cause the migration to fail.

8. Start the migration task.

   1. In the lower-right corner of the page, click Precheck and Start.

      Note

      • DTS performs a precheck before starting the migration task. The task can proceed only after passing the precheck.

      • If the precheck fails, click the icon next to a specific check item to view its details. Fix the issue based on the prompt, and then run the precheck again.

   2. After the precheck passes, click Next.

   3. In the Confirm Settings dialog box, select the Instance Class and select the Data Transmission Service (Pay-As-You-Go) Terms of Service checkbox.

   4. Click Buy and Start to begin the migration task.

Step 3: Perform the workload switchover

Perform the workload switchover using one of the following solutions, based on your business needs.

• Rollback solution (no application changes required, but rollback may fail)

  1. Verify the migrated data in the destination ApsaraDB RDS for MySQL instance.

  2. Follow the procedure in the Workload switchover procedure document to switch your workload and establish a rollback plan. This involves migrating incremental data from the new destination instance back to the original source instance.

  3. Keep the reverse data migration task running for one business cycle. After the cycle, test all functionalities. If they work correctly, you can stop the reverse data migration task created for the rollback plan.

  4. Optional: If the source instance is no longer needed, you can release it (if it is a pay-as-you-go instance) or unsubscribe from it (if it is a subscription instance).

• Dual-write solution (higher rollback success rate, but requires significant application changes)

  1. Modify your application to implement a dual-write logic, where data changes are written to both the source and destination instances simultaneously.

  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

       This type of task does not stop automatically; you must stop it manually.

       1. Wait until the task enters the Incremental Data Migration stage with a Undelayed status. Then, stop writes to the source database for a few minutes. During this period, the Incremental Data Migration status might report a latency.
       2. Wait for the Incremental Data Migration status to return to Undelayed. Once this is confirmed in the list, select the task's checkbox and click the StopEnd button in the batch operations bar at the bottom.
     Note

     You can log on to the source instance and run show processlist; to check session information and ensure that no new sessions are performing write operations.

  3. Verify the migrated data in the destination ApsaraDB RDS for MySQL instance.

  4. Configure your application for dual-write, where data changes are written to both the source and destination instances.

  5. Keep the dual-write state for one business cycle. After the cycle, test all functionalities. If they work correctly, you can adjust the application to write only to the destination instance.

  6. Optional: If the source instance is no longer needed, you can release it (if it is a pay-as-you-go instance) or unsubscribe from it (if it is a subscription instance).