Working with DB parameter groups - Amazon Relational Database Service
Creating a DB parameter groupAssociating a DB parameter group with a DB instanceModifying parameters in a DB parameter groupResetting parameters in a DB parameter groupCopying a DB parameter groupListing DB parameter groupsViewing parameter values for a DB parameter group
Services or capabilities described in Amazon Web Services documentation might vary by Region. To see the differences applicable to the China Regions, see Getting Started with Amazon Web Services in China (PDF).

Working with DB parameter groups

DB instances use DB parameter groups. The following sections describe configuring and managing DB instance parameter groups.

Creating a DB parameter group

You can create a new DB parameter group using the Amazon Web Services Management Console, the Amazon CLI, or the RDS API.

The following limitations apply to the DB parameter group name:

  • The name must be 1 to 255 letters, numbers, or hyphens.

    Default parameter group names can include a period, such as default.mysql8.0. However, custom parameter group names can't include a period.

  • The first character must be a letter.

  • The name can't end with a hyphen or contain two consecutive hyphens.

To create a DB parameter group

  1. Sign in to the Amazon Web Services Management Console and open the Amazon RDS console at https://console.amazonaws.cn/rds/.

  2. In the navigation pane, choose Parameter groups.

  3. Choose Create parameter group.

    The Create parameter group window appears.

  4. In the Parameter group family list, select a DB parameter group family.

  5. In the Type list, select DB Parameter Group.

  6. In the Group name box, enter the name of the new DB parameter group.

  7. In the Description box, enter a description for the new DB parameter group.

  8. Choose Create.

To create a DB parameter group, use the Amazon CLI create-db-parameter-group command. The following example creates a DB parameter group named mydbparametergroup for MySQL version 8.0 with a description of "My new parameter group."

Include the following required parameters:

  • --db-parameter-group-name

  • --db-parameter-group-family

  • --description

To list all of the available parameter group families, use the following command:

aws rds describe-db-engine-versions --query "DBEngineVersions[].DBParameterGroupFamily"
Note

The output contains duplicates.

Example

For Linux, macOS, or Unix:

aws rds create-db-parameter-group \
    --db-parameter-group-name mydbparametergroup \
    --db-parameter-group-family MySQL8.0 \
    --description "My new parameter group"

For Windows:

aws rds create-db-parameter-group ^
    --db-parameter-group-name mydbparametergroup ^
    --db-parameter-group-family MySQL8.0 ^
    --description "My new parameter group"

This command produces output similar to the following:

DBPARAMETERGROUP  mydbparametergroup  mysql8.0  My new parameter group

To create a DB parameter group, use the RDS API CreateDBParameterGroup operation.

Include the following required parameters:

  • DBParameterGroupName

  • DBParameterGroupFamily

  • Description

Associating a DB parameter group with a DB instance

You can create your own DB parameter groups with customized settings. You can associate a DB parameter group with a DB instance using the Amazon Web Services Management Console, the Amazon CLI, or the RDS API. You can do so when you create or modify a DB instance.

For information about creating a DB parameter group, see Creating a DB parameter group. For information about creating a DB instance, see Creating an Amazon RDS DB instance. For information about modifying a DB instance, see Modifying an Amazon RDS DB instance.

Note

When you associate a new DB parameter group with a DB instance, the modified static and dynamic parameters are applied only after the DB instance is rebooted. However, if you modify dynamic parameters in the DB parameter group after you associate it with the DB instance, these changes are applied immediately without a reboot.

To associate a DB parameter group with a DB instance

  1. Sign in to the Amazon Web Services Management Console and open the Amazon RDS console at https://console.amazonaws.cn/rds/.

  2. In the navigation pane, choose Databases, and then choose the DB instance that you want to modify.

  3. Choose Modify. The Modify DB instance page appears.

  4. Change the DB parameter group setting.

  5. Choose Continue and check the summary of modifications.

  6. (Optional) Choose Apply immediately to apply the changes immediately. Choosing this option can cause an outage in some cases. For more information, see Using the Apply Immediately setting.

  7. On the confirmation page, review your changes. If they are correct, choose Modify DB instance to save your changes.

    Or choose Back to edit your changes or Cancel to cancel your changes.

To associate a DB parameter group with a DB instance, use the Amazon CLI modify-db-instance command with the following options:

  • --db-instance-identifier

  • --db-parameter-group-name

The following example associates the mydbpg DB parameter group with the database-1 DB instance. The changes are applied immediately by using --apply-immediately. Use --no-apply-immediately to apply the changes during the next maintenance window. For more information, see Using the Apply Immediately setting.

Example

For Linux, macOS, or Unix:

aws rds modify-db-instance \
    --db-instance-identifier database-1 \
    --db-parameter-group-name mydbpg \
    --apply-immediately

For Windows:

aws rds modify-db-instance ^
    --db-instance-identifier database-1 ^
    --db-parameter-group-name mydbpg ^
    --apply-immediately

To associate a DB parameter group with a DB instance, use the RDS API ModifyDBInstance operation with the following parameters:

  • DBInstanceName

  • DBParameterGroupName

Modifying parameters in a DB parameter group

You can modify parameter values in a customer-created DB parameter group; you can't change the parameter values in a default DB parameter group. Changes to parameters in a customer-created DB parameter group are applied to all DB instances that are associated with the DB parameter group.

Changes to some parameters are applied to the DB instance immediately without a reboot. Changes to other parameters are applied only after the DB instance is rebooted. The RDS console shows the status of the DB parameter group associated with a DB instance on the Configuration tab. For example, suppose that the DB instance isn't using the latest changes to its associated DB parameter group. If so, the RDS console shows the DB parameter group with a status of pending-reboot. To apply the latest parameter changes to that DB instance, manually reboot the DB instance.

Parameter change pending reboot scenario
To modify a DB parameter group

  1. Sign in to the Amazon Web Services Management Console and open the Amazon RDS console at https://console.amazonaws.cn/rds/.

  2. In the navigation pane, choose Parameter groups.

  3. In the list, choose the parameter group that you want to modify.

  4. For Parameter group actions, choose Edit.

  5. Change the values of the parameters that you want to modify. You can scroll through the parameters using the arrow keys at the top right of the dialog box.

    You can't change values in a default parameter group.

  6. Choose Save changes.

To modify a DB parameter group, use the Amazon CLI modify-db-parameter-group command with the following required options:

  • --db-parameter-group-name

  • --parameters

The following example modifies the max_connections and max_allowed_packet values in the DB parameter group named mydbparametergroup.

Example

For Linux, macOS, or Unix:

aws rds modify-db-parameter-group \
    --db-parameter-group-name mydbparametergroup \
    --parameters "ParameterName=max_connections,ParameterValue=250,ApplyMethod=immediate" \
                 "ParameterName=max_allowed_packet,ParameterValue=1024,ApplyMethod=immediate"

For Windows:

aws rds modify-db-parameter-group ^
    --db-parameter-group-name mydbparametergroup ^
    --parameters "ParameterName=max_connections,ParameterValue=250,ApplyMethod=immediate" ^
                 "ParameterName=max_allowed_packet,ParameterValue=1024,ApplyMethod=immediate"

The command produces output like the following:

DBPARAMETERGROUP  mydbparametergroup

To modify a DB parameter group, use the RDS API ModifyDBParameterGroup operation with the following required parameters:

  • DBParameterGroupName

  • Parameters

Resetting parameters in a DB parameter group to their default values

You can reset parameter values in a customer-created DB parameter group to their default values. Changes to parameters in a customer-created DB parameter group are applied to all DB instances that are associated with the DB parameter group.

When you use the console, you can reset specific parameters to their default values. However, you can't easily reset all of the parameters in the DB parameter group at once. When you use the Amazon CLI or RDS API, you can reset specific parameters to their default values. You can also reset all of the parameters in the DB parameter group at once.

Changes to some parameters are applied to the DB instance immediately without a reboot. Changes to other parameters are applied only after the DB instance is rebooted. The RDS console shows the status of the DB parameter group associated with a DB instance on the Configuration tab. For example, suppose that the DB instance isn't using the latest changes to its associated DB parameter group. If so, the RDS console shows the DB parameter group with a status of pending-reboot. To apply the latest parameter changes to that DB instance, manually reboot the DB instance.

Parameter change pending reboot scenario
Note

In a default DB parameter group, parameters are always set to their default values.

To reset parameters in a DB parameter group to their default values

  1. Sign in to the Amazon Web Services Management Console and open the Amazon RDS console at https://console.amazonaws.cn/rds/.

  2. In the navigation pane, choose Parameter groups.

  3. In the list, choose the parameter group.

  4. For Parameter group actions, choose Edit.

  5. Choose the parameters that you want to reset to their default values. You can scroll through the parameters using the arrow keys at the top right of the dialog box.

    You can't reset values in a default parameter group.

  6. Choose Reset and then confirm by choosing Reset parameters.

To reset some or all of the parameters in a DB parameter group, use the Amazon CLI reset-db-parameter-group command with the following required option: --db-parameter-group-name.

To reset all of the parameters in the DB parameter group, specify the --reset-all-parameters option. To reset specific parameters, specify the --parameters option.

The following example resets all of the parameters in the DB parameter group named mydbparametergroup to their default values.

Example

For Linux, macOS, or Unix:

aws rds reset-db-parameter-group \
    --db-parameter-group-name mydbparametergroup \
    --reset-all-parameters

For Windows:

aws rds reset-db-parameter-group ^
    --db-parameter-group-name mydbparametergroup ^
    --reset-all-parameters

The following example resets the max_connections and max_allowed_packet options to their default values in the DB parameter group named mydbparametergroup.

Example

For Linux, macOS, or Unix:

aws rds reset-db-parameter-group \
    --db-parameter-group-name mydbparametergroup \
    --parameters "ParameterName=max_connections,ApplyMethod=immediate" \
                 "ParameterName=max_allowed_packet,ApplyMethod=immediate"

For Windows:

aws rds reset-db-parameter-group ^
    --db-parameter-group-name mydbparametergroup ^
    --parameters "ParameterName=max_connections,ApplyMethod=immediate" ^
                 "ParameterName=max_allowed_packet,ApplyMethod=immediate"

The command produces output like the following:

DBParameterGroupName  mydbparametergroup

To reset parameters in a DB parameter group to their default values, use the RDS API ResetDBParameterGroup command with the following required parameter: DBParameterGroupName.

To reset all of the parameters in the DB parameter group, set the ResetAllParameters parameter to true. To reset specific parameters, specify the Parameters parameter.

Copying a DB parameter group

You can copy custom DB parameter groups that you create. Copying a parameter group can be convenient solution. An example is when you have created a DB parameter group and want to include most of its custom parameters and values in a new DB parameter group. You can copy a DB parameter group by using the Amazon Web Services Management Console. You can also use the Amazon CLI copy-db-parameter-group command or the RDS API CopyDBParameterGroup operation.

After you copy a DB parameter group, wait at least 5 minutes before creating your first DB instance that uses that DB parameter group as the default parameter group. Doing this allows Amazon RDS to fully complete the copy action before the parameter group is used. This is especially important for parameters that are critical when creating the default database for a DB instance. An example is the character set for the default database defined by the character_set_database parameter. Use the Parameter Groups option of the Amazon RDS console or the describe-db-parameters command to verify that your DB parameter group is created.

Note

You can't copy a default parameter group. However, you can create a new parameter group that is based on a default parameter group.

You can't copy a DB parameter group to a different Amazon Web Services account or Amazon Web Services Region.

To copy a DB parameter group

  1. Sign in to the Amazon Web Services Management Console and open the Amazon RDS console at https://console.amazonaws.cn/rds/.

  2. In the navigation pane, choose Parameter groups.

  3. In the list, choose the custom parameter group that you want to copy.

  4. For Parameter group actions, choose Copy.

  5. In New DB parameter group identifier, enter a name for the new parameter group.

  6. In Description, enter a description for the new parameter group.

  7. Choose Copy.

To copy a DB parameter group, use the Amazon CLI copy-db-parameter-group command with the following required options:

  • --source-db-parameter-group-identifier

  • --target-db-parameter-group-identifier

  • --target-db-parameter-group-description

The following example creates a new DB parameter group named mygroup2 that is a copy of the DB parameter group mygroup1.

Example

For Linux, macOS, or Unix:

aws rds copy-db-parameter-group \
    --source-db-parameter-group-identifier mygroup1 \
    --target-db-parameter-group-identifier mygroup2 \
    --target-db-parameter-group-description "DB parameter group 2"

For Windows:

aws rds copy-db-parameter-group ^
    --source-db-parameter-group-identifier mygroup1 ^
    --target-db-parameter-group-identifier mygroup2 ^
    --target-db-parameter-group-description "DB parameter group 2"

To copy a DB parameter group, use the RDS API CopyDBParameterGroup operation with the following required parameters:

  • SourceDBParameterGroupIdentifier

  • TargetDBParameterGroupIdentifier

  • TargetDBParameterGroupDescription

Listing DB parameter groups

You can list the DB parameter groups you've created for your Amazon account.

Note

Default parameter groups are automatically created from a default parameter template when you create a DB instance for a particular DB engine and version. These default parameter groups contain preferred parameter settings and can't be modified. When you create a custom parameter group, you can modify parameter settings.

To list all DB parameter groups for an Amazon account

  1. Sign in to the Amazon Web Services Management Console and open the Amazon RDS console at https://console.amazonaws.cn/rds/.

  2. In the navigation pane, choose Parameter groups.

    The DB parameter groups appear in a list.

To list all DB parameter groups for an Amazon account, use the Amazon CLI describe-db-parameter-groups command.

Example

The following example lists all available DB parameter groups for an Amazon account.

aws rds describe-db-parameter-groups

The command returns a response like the following:

DBPARAMETERGROUP  default.mysql8.0     mysql8.0  Default parameter group for MySQL8.0
DBPARAMETERGROUP  mydbparametergroup   mysql8.0  My new parameter group

The following example describes the mydbparamgroup1 parameter group.

For Linux, macOS, or Unix:

aws rds describe-db-parameter-groups \
    --db-parameter-group-name mydbparamgroup1

For Windows:

aws rds describe-db-parameter-groups ^
    --db-parameter-group-name mydbparamgroup1

The command returns a response like the following:

DBPARAMETERGROUP  mydbparametergroup1  mysql8.0  My new parameter group

To list all DB parameter groups for an Amazon account, use the RDS API DescribeDBParameterGroups operation.

Viewing parameter values for a DB parameter group

You can get a list of all parameters in a DB parameter group and their values.

To view the parameter values for a DB parameter group

  1. Sign in to the Amazon Web Services Management Console and open the Amazon RDS console at https://console.amazonaws.cn/rds/.

  2. In the navigation pane, choose Parameter groups.

    The DB parameter groups appear in a list.

  3. Choose the name of the parameter group to see its list of parameters.

To view the parameter values for a DB parameter group, use the Amazon CLI describe-db-parameters command with the following required parameter.

  • --db-parameter-group-name

Example

The following example lists the parameters and parameter values for a DB parameter group named mydbparametergroup.

aws rds describe-db-parameters --db-parameter-group-name mydbparametergroup

The command returns a response like the following:

DBPARAMETER  Parameter Name            Parameter Value  Source           Data Type  Apply Type  Is Modifiable
DBPARAMETER  allow-suspicious-udfs                      engine-default   boolean    static      false
DBPARAMETER  auto_increment_increment                   engine-default   integer    dynamic     true
DBPARAMETER  auto_increment_offset                      engine-default   integer    dynamic     true
DBPARAMETER  binlog_cache_size         32768            system           integer    dynamic     true
DBPARAMETER  socket                    /tmp/mysql.sock  system           string     static      false

To view the parameter values for a DB parameter group, use the RDS API DescribeDBParameters command with the following required parameter.

  • DBParameterGroupName