You may need to change the character set of a table (for example, from GBK to UTF8mb4). However, an ALTER statement that is used to change the character set of a large table will lock the table for a while and disrupt your normal operations. This topic describes how to change the character set without disrupting your normal operations. You can create a table definition in the target instance using the new character set, and then use Data Transmission Service (DTS) to migrate table data to the target instance.
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 definition from the source instance to the target instance
- 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.
- Decompress the exported file, and change the character set in the script of the table schema.Note The example shown in this figure changes the GBK character set to UTF8mb4.
- 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.
- Import the edited script of the table schema to the new ApsaraDB RDS for MySQL instance.
Step 2: Migrate table data from the source instance to the destination instance
- Log on to the DTS console. Note If you are redirected to the Data Management (DMS) console, you can click the icon in the lower-right corner to go to the previous version of the DTS console.
- In the left-side navigation pane, click Data Migration.
- At the top of the Migration Tasks page, select the region where the destination cluster resides.
- In the upper-right corner of the page, click Create Migration Task.
- Configure the source and destination databases of the data migration task.
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 information about the self-managed Oracle database, you can click Test Connectivity next to Database Password to check whether the information is valid. If the information is valid, the Passed message appears. If the Failed message appears, click Check next to Failed. Then, modify the information 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 information about the destination database, you can click Test Connectivity next to Database Password to check whether the information is valid. If the information is valid, the Passed message appears. If the Failed message appears, click Check next to Failed. Then, modify the information 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). - 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.
- 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 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. - 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.
- 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.
- Optional: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.
- Optional:If you no longer need the source ApsaraDB RDS for MySQL instance, release the instance.