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
Step 2: Migrate table data from the source instance to the destination instance
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.
- After data migration is completed, verify the data in the destination ApsaraDB RDS for MySQL instance.
- 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.
- Test the features of your business. If the features function as expected, delete the data migration task in the opposite direction.
- 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.
- Edit the application code to implement the dual-write solution. The dual-write solution writes data changes to both the source and destination instances.
- 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.
- 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.
- After the status of incremental data migration changes to The migration task is not delayed, manually stop the migration task.
Note Make sure that no sessions are performing write operations. To retrieve session information, you can Log on to the source instance and execute theshow processlist;
statement. - Full data migration
- Verify the data in the destination ApsaraDB RDS for MySQL instance.
- Enable the dual-write solution for your application. Then, data changes will be written to both the source and destination instances.
- Test the features of your business on the destination instance. If the features function as expected, disable the dual-write solution.
- If you no longer need the source ApsaraDB RDS for MySQL instance, release the instance.