Modify parameter configurations for an ApsaraDB RDS for MySQL instance - ApsaraDB RDS

In ApsaraDB RDS for MySQL, you can modify parameter configurations and query the parameter modification history in the console or using an API. This lets you adjust instance parameters to meet your business needs. To query the parameter configuration of an instance, see View instance parameters.

Precautions

To ensure instance stability, you can modify only the parameters that are available in the ApsaraDB RDS console. You cannot use the set global command to modify parameters. Parameters that are not displayed or editable in the console cannot be modified.
When you modify a parameter value, refer to the Running Values column on the Modifiable Parameters page in the console. You can also set an instance parameter as an expression to allow the parameter to automatically adapt to changes in the instance configuration.
Some parameters are automatically synchronized to all read-only instances of a primary instance after they are modified on the primary instance. For a list of these parameters, see Appendix: List of parameters that are automatically synchronized to read-only instances.
Notes on modifying the lower_case_table_names parameter:
- For MySQL 8.0: You must specify whether table names are case-sensitive when you create an instance. The default value is 1, which indicates that table names are case-insensitive. You cannot modify this parameter after the instance is created.
- For MySQL 5.7 and 5.6:
  - This parameter can be modified after the instance is created, but the modification requires an instance restart. We recommend that you modify this parameter during off-peak hours.
  - If you want to change the parameter value from 0 to 1, you must convert the uppercase table names in the instance to lowercase beforehand. Otherwise, the existing tables that have uppercase names become inaccessible.
  - You cannot modify this parameter on read-only instances. You must modify this parameter on the primary instance. The change is then synchronized to the read-only instances.

Important

Modifying and applying parameters may require an instance restart or a database reconnection. Perform these operations during off-peak hours and make sure your application has a reconnection mechanism.

Some parameters require an instance restart to take effect. For more information, see the Force Restart column on the Modifiable Parameters page in the console. Restarting the instance causes a connection interruption that lasts for about 30 seconds. Make sure that your application is configured with an automatic reconnection mechanism.
For parameter modifications that do not require an instance restart, the changes take effect only for new database connections. For the changes to take effect on existing database connections, you must disconnect and then reconnect to the database.

Modify instance parameters

The modifiable instance parameters vary based on the MySQL engine version. For more information, see List of parameters available for ApsaraDB RDS for MySQL.

Modify the parameters of a single instance

Go to the Instances page. In the top navigation bar, select the region in which the RDS instance resides. Then, find the RDS instance and click the ID of the instance.
In the navigation pane on the left, click Parameters.

You can perform the following operations:

Modify a single parameter

On the Modifiable Parameters tab, find the parameter that you want to modify and change its value in one of the following ways:
- Click the icon in the Running Value column and enter a value based on the valid value range.
- Select a value from the drop-down list in the Running Value column.
Click OK.
Click Apply Changes. In the dialog box that appears, select Take Effect Immediately, Take Effect Within the Maintenance Window02:00-06:00, or Take Effect at Specified Time.
Note
On the Scheduled Tasks tab, you can view the parameter modification tasks for which you selected Take Effect Within the Maintenance Window02:00-06:00 or Take Effect At A Specified Time.
(Optional) Before you submit the parameter modifications, you can click Cancel Changes to cancel them.

Apply and export parameter templates

Apply a parameter template

ApsaraDB RDS provides various system parameter templates to meet the requirements of different business scenarios, such as scenarios that require high performance. You can also create custom parameter templates to configure multiple parameters at a time. For more information, see Use a parameter template.

Click Apply Template.
Select a parameter template and click OK.
Note
- You can view the number of parameters in the template and check whether the instance must be restarted for the new parameter values to take effect. To view more details about the parameter changes, click View Changed Parameters.
- If you cannot find the desired parameter template, check whether the template and the instance are in the same region. If they are in different regions, you can copy the parameter template to the region where the instance resides. For more information, see Use a parameter template.

Export a parameter template

Click Export As Template.

Configure the following parameters.

Parameter	Description
Template Name	The name of the template. The name must start with a letter and can contain letters, digits, periods (.), and underscores (_). The name cannot contain Chinese characters and must be 8 to 64 characters in length.
Description	The description of the template. This helps you manage the template. The description can be up to 200 characters in length.

Click OK. A new parameter template is created in the current region.

Import and export parameters (TXT format)

To modify multiple parameters at a time or obtain the parameter configuration of an instance, you can export the parameters to a local TXT file. This file contains all parameter values of the instance. After you modify the parameters in the file, you can import the file to the ApsaraDB RDS instance to apply the changes.

Click Export Parameters to export the parameter configuration to a local TXT file.
After you modify the parameter configuration file, click Import Parameters and paste the content of the file into the dialog box.
Click OK.
Click Submit Parameters. In the dialog box that appears, select an effective time. You can select Take Effect Immediately, Take Effect During Maintenance Window, or Take Effect At A Specified Time.
Note
On the Parameter Modification Scheduled Tasks tab, you can view the parameter modification tasks for which you selected Take Effect During Maintenance Window or Take Effect At A Specified Time.
(Optional) Before you submit the changes, you can click Revoke to cancel them.
Important
If the Operation failed. The specified parameter format is invalid. error message appears, check whether the value that you entered for the parameter is within the valid range specified in the Parameter Value Range column.

Modify the parameters of multiple instances in a batch

Log on to the ApsaraDB RDS console. In the left-side navigation pane, click Instances. In the top navigation bar, select the region in which the RDS instance resides.
Select the check boxes of the target instances and click Modify Parameters at the bottom of the page.
Note
The instances that you want to modify in a batch must use the same database engine version and RDS edition. Otherwise, the Modify Parameters button is grayed out.
In the Parameter Settings dialog box, click the icon next to the name of the parameter that you want to modify.
In the Current Value field, select or enter a new value and click OK.
In the Parameter Settings dialog box, confirm the changes and click OK.

Query parameter modification history

Go to the Instances page. In the top navigation bar, select the region in which the RDS instance resides. Then, find the RDS instance and click the ID of the instance.
In the navigation pane on the left, click Parameters.
Click the Edit History tab.
Select a time range and click OK.

FAQ

Do parameter modifications take effect immediately? Is an instance restart required?

Some parameter modifications take effect in about 5 minutes and do not require an instance restart. Other parameter modifications require an instance restart to take effect. For more information, see the Force Restart column on the Modifiable Parameters page in the ApsaraDB RDS console. Before you submit the changes, you must confirm whether a restart is required.

Why is there no response after I modify a parameter?

The parameter modification task is executed only after you set the parameter values and click Apply Changes.

If a read-only instance is a high-availability (HA) instance that has a primary node and a secondary node, how do I set the parameters for these two nodes?

You need to set the parameters only on the primary node of the read-only instance. The parameter values are automatically synchronized to the secondary node. You cannot directly modify the parameters of the secondary node.

I want to modify the expire_logs_days or binlog_expire_logs_seconds parameter, but I cannot find it in the ApsaraDB RDS console. What do I do?

The expire_logs_days and binlog_expire_logs_seconds parameters are used in open-source MySQL to control the retention period of local binary logs, but ApsaraDB RDS for MySQL does not use these parameters for this purpose. To modify the binary log retention period for an ApsaraDB RDS for MySQL instance, you can set the binary log retention policy in the ApsaraDB RDS console.

Note

For more information about how to delete or reduce log and data backups for an ApsaraDB RDS for MySQL instance, see Delete or reduce backups.

How do I modify the lower_case_table_names parameter?

For more information, see Notes on modifying the lower_case_table_names parameter.

What do I do if the Access denied; you need (at least one of) the SUPER or SYSTEM_VARIABLES_ADMIN privilege(s) for this operation error is reported when I use the set global command to modify parameters?

ApsaraDB RDS for MySQL does not support modifying parameters using the set global command. You can modify only the parameters that are available in the ApsaraDB RDS console. Parameters that are not displayed or editable in the console cannot be modified.

How do I modify the collation_connection and collation_database parameters?

ApsaraDB RDS for MySQL does not allow you to directly modify the collation_connection and collation_database parameters. However, you can modify them in the ApsaraDB RDS console by setting the init_connect parameter to a supported value, such as 'set names utf8mb4', 'set names utf8', 'set default_collation_for_utf8mb4=utf8mb4_general_ci', 'set default_collation_for_utf8mb4=utf8mb4_general_ci;set names utf8mb4', 'set names utf8mb4 collate utf8mb4_general_ci', or 'set names utf8mb4 COLLATE utf8mb4_bin'.

After you configure the parameter, the system automatically runs the specified command each time a new connection is created. This modifies the character set and collation. However, make sure that your application does not separately adjust the collation of the session. Otherwise, subsequent session-level collation settings in the application overwrite the current configuration.

Can I modify the sql_log_bin parameter?

No. ApsaraDB RDS for MySQL enforces sql_log_bin for all sessions by default to ensure that binary log records from each session are persisted to the disk. This guarantees data consistency for primary/secondary replication.

Why do changes to the wait_timeout parameter not take effect?

Changes to the wait_timeout parameter take effect only for new connections. For the change to take effect on existing connections, you must disconnect from and then reconnect to the database.

Appendix: List of parameters that are automatically synchronized to read-only instances

If you modify the following parameters on a primary ApsaraDB RDS for MySQL instance, the modifications are automatically synchronized to all read-only instances of the primary instance.

lower_case_table_names
Note
For ApsaraDB RDS for MySQL 8.0, you can specify whether table names are case-sensitive only when you create an instance. After the instance is created, you cannot modify the lower_case_table_names parameter.
innodb_large_prefix
loose_validate_password_length
default_time_zone
rpl_semi_sync_slave_enabled
character_set_server
collation_server

Related API operations

API	Description
Modify instance parameters	Call the ModifyParameter operation to modify the parameters of an RDS instance.
Query the valid values and default value of a parameter	Call the DescribeParameterTemplates operation to view database parameter templates.
Query parameter configurations	Call the DescribeParameters operation to query the current parameter configurations of an instance.