:Guarantee the availability of database character encoding for an apsaradb RDS for MySQL instance

Overview

This article describes how to ensure database character encoding are correct for an apsaradb RDS for MySQL instance.

Description

Alibaba Cloud reminds you that:

• When you perform operations that have risks, such as modifying instances or data, check the disaster recovery and fault tolerance capabilities of the instances to ensure data security.
• Before you modify the configurations and data of instances including but not limited to ECS and RDS instances, we recommend that you create snapshots or enable RDS log backup.
• If you have authorized or submitted security information such as the logon account and password in the Alibaba Cloud Management console, we recommend that you modify such information in a timely manner.

The character set is one of the things that you need to consider when designing a database. It is important to consider the character set based on the business scenario and user data.

Introduction to character encoding

1. Log on to the RDS instance. For more information, see connect to an apsaradb RDS for MySQL instance.
2. Run the following SQL statements in sequence to view the character set of the corresponding database:

   use [$DB_Name];
   show variables like '%character%';
   

   Note:[$DB_Name] is the database name.

   The command output is as follows:
   
   

   Note:
   

   • The above parameters must be guaranteed character_set_filesystem all parameters are in a unified manner so that garbled characters will not appear.
   • character_set_client, character_set_connection and character_set_results is the setting of the client.
   • character_set_system, character_set_server and character_set_database is a server-side setting.
   • The server-side parameter priority is character_set_database > character_set_server > character_set_system.

   
   

   
   
   

Ensure that the database character encoding is correct.

1. Run the following SQL statement to modify the character set of the client:

   set names [$Character_Set]

   Note:[$Character_Set] is the character set.

2. Run the following SQL statement to modify character_set_database parameters:

   ALTER DATABASE [$DB_Name] CHARACTER SET = [$Character_Set] COLLATE = [$Rules];

   Note:[$Rules] are character set Rules.

3. Login RDS console in the upper-left corner of the page, select the region where the instance is located. Find the target RDS instance and click the instance ID. In the left-side navigation pane, choose parameters.
4. On the modifiable parameters tab, find the character_set_server make modifications and then click confirm.
   

   Tips: modifying character_set_server parameters requires the instance to be restarted. We recommend that you perform this operation during off-peak hours.

   
   
5. In the upper-right corner, click submit parameters. In the dialog box that appears, click confirm. Wait until the instance is restarted.
   

   Tips after you modify this parameter, this parameter is valid only for databases created under the privileged account but not for the current database.

   
   
6. character_set_system does not provide changes for the time being, but because of its lowest priority, the impact is small. After completing the above settings, it is basically guaranteed that there will be no garbled characters. When setting the client character encoding in the code, it is recommended to modify the client settings through the SQL statement of step 1.

Application scope

• Apsaradb for MySQL