Working with DB parameter groups and DB cluster parameter groups - Amazon Aurora
DB cluster and DB instance parametersCreating a DB parameter groupCreating a DB cluster parameter groupAssociating a DB parameter group with a DB instanceAssociating a DB cluster parameter group with a DB clusterModifying parameters in a DB parameter groupModifying parameters in a DB cluster parameter groupResetting parameters in a DB parameter groupResetting parameters in a DB cluster parameter groupCopying a DB parameter groupCopying a DB cluster parameter groupListing DB parameter groupsListing DB cluster parameter groupsViewing parameter values for a DB parameter groupViewing parameter values for a DB cluster parameter groupComparing parameter groupsSpecifying DB parameters

Working with DB parameter groups and DB cluster parameter groups

You manage your DB engine configuration by associating your DB instances and Aurora DB clusters with parameter groups. Amazon RDS defines parameter groups with default settings that apply to newly created DB instances and Aurora DB clusters.

Important

You can define your own parameter groups with customized settings. Then you can modify your DB instances and Aurora clusters to use your own parameter groups.

For information about modifying a DB cluster or DB instance, see Modifying an Amazon Aurora DB cluster.

A DB parameter group acts as a container for engine configuration values that are applied to one or more DB instances. DB parameter groups apply to DB instances in both Amazon RDS and Aurora. These configuration settings apply to properties that can vary among the DB instances within an Aurora cluster, such as the sizes for memory buffers.

A DB cluster parameter group acts as a container for engine configuration values that are applied to every DB instance in an Aurora DB cluster. For example, the Aurora shared storage model requires that every DB instance in an Aurora cluster use the same setting for parameters such as innodb_file_per_table. Thus, parameters that affect the physical storage layout are part of the cluster parameter group. The DB cluster parameter group also includes default values for all the instance-level parameters

If you create a DB instance without specifying a DB parameter group, the DB instance uses a default DB parameter group. Likewise, if you create an Aurora DB cluster without specifying a DB cluster parameter group, the DB cluster uses a default DB cluster parameter group. Each default parameter group contains database engine defaults and Amazon RDS system defaults based on the engine, compute class, and allocated storage of the instance. You can't modify the parameter settings of a default parameter group. Instead, you create your own parameter group where you choose your own parameter settings. Not all DB engine parameters can be changed in a parameter group that you create.

If you want to use your own parameter group, you create a new parameter group and modify the parameters that you want to. You then modify your DB instance or DB cluster to use the new parameter group. If you update parameters within a DB parameter group, the changes apply to all DB instances that are associated with that parameter group. Likewise, if you update parameters within a DB cluster parameter group, the changes apply to all Aurora clusters that are associated with that DB cluster parameter group.

You can copy an existing DB parameter group with the AWS CLI copy-db-parameter-group command. You can copy an existing DB cluster parameter group with the AWS CLI copy-db-cluster-parameter-group command. Copying a parameter group can be convenient when you want to include most of an existing parameter group's custom parameters and values in a new parameter group.

Here are some important points about working with parameters in a parameter group:

  • When you change a dynamic parameter and save the parameter group, the change is applied immediately regardless of the Apply Immediately setting. When you change a static parameter and save the DB parameter group, the parameter change takes effect after you manually reboot the DB instance. You can reboot a DB instance using the RDS console, by calling the reboot-db-instance CLI command, or by calling the RebootDbInstance API operation. The requirement to reboot the associated DB instance after a static parameter change helps mitigate the risk of a parameter misconfiguration affecting an API call, such as calling ModifyDBInstance to change DB instance class or scale storage.

    If a DB instance isn't using the latest changes to its associated DB parameter group, the AWS Management Console shows the DB parameter group with a status of pending-reboot. The pending-reboot parameter groups status doesn't result in an automatic reboot during the next maintenance window. To apply the latest parameter changes to that DB instance, manually reboot the DB instance.

  • When you change the DB parameter group associated with a DB instance, you must manually reboot the instance before the DB instance can use the new DB parameter group. For more information about changing the DB parameter group, see Modifying an Amazon Aurora DB cluster.

    Note

    After you change the DB cluster parameter group associated with a DB cluster, reboot the primary DB instance in the cluster to apply the changes to all of the DB instances in the cluster.

    To determine whether the primary DB instance of a DB cluster must be rebooted to apply changes, run the following AWS CLI command:

    aws rds describe-db-clusters --db-cluster-identifier db_cluster_identifier

    Check the DBClusterParameterGroupStatus value for the primary DB instance in the output. If the value is pending-reboot, then reboot the primary DB instance of the DB cluster.

  • You can specify the value for a parameter as an integer or as an integer expression built from formulas, variables, functions, and operators. Functions can include a mathematical log expression. For more information, see Specifying DB parameters.

  • Set any parameters that relate to the character set or collation of your database in your parameter group before creating the DB instance and before you create a database in your DB instance. This ensures that the default database and new databases in your DB instance use the character set and collation values that you specify. If you change character set or collation parameters for your DB instance, the parameter changes are not applied to existing databases.

    You can change character set or collation values for an existing database using the ALTER DATABASE command, for example: 

    
ALTER DATABASE database_name CHARACTER SET character_set_name COLLATE collation;

  • Improperly setting parameters in a parameter group can have unintended adverse effects, including degraded performance and system instability. Always exercise caution when modifying database parameters and back up your data before modifying a parameter group. Try out parameter group setting changes on a test DB instance before applying those parameter group changes to a production DB instance.

  • For an Aurora global database, you can specify different configuration settings for the individual Aurora clusters. Make sure that the settings are similar enough to produce consistent behavior if you promote a secondary cluster to be the primary cluster. For example, use the same settings for time zones and character sets across all the clusters of an Aurora global database.

  • To determine the supported parameters for your DB engine, you can view the parameters in the DB parameter group and DB cluster parameter group used by the DB cluster. For more information, see Viewing parameter values for a DB parameter group and Viewing parameter values for a DB cluster parameter group.

Amazon Aurora DB cluster and DB instance parameters

Aurora uses a two-level system of configuration settings, as follows:

  • Parameters in a DB cluster parameter group apply to every DB instance in a DB cluster. Your data is stored in the Aurora shared storage subsystem. Because of this, all parameters related to physical layout of table data must be the same for all DB instances in an Aurora cluster. Likewise, because Aurora DB instances are connected by replication, all the parameters for replication settings must be identical throughout an Aurora cluster.

  • Parameters in a DB parameter group apply to a single DB instance in an Aurora DB cluster. These parameters are related to aspects such as memory usage that you can vary across DB instances in the same Aurora cluster. For example, a cluster often contains DB instances with different AWS instance classes.

Every Aurora cluster is associated with a DB cluster parameter group. Each DB instance within the cluster inherits the settings from that DB cluster parameter group, and is associated with a DB parameter group. Aurora assigns default parameter groups when you create a cluster or a new DB instance, based on the specified database engine and version. You can change the parameter groups later to ones that you create, where you can edit the parameter values.

The DB cluster parameter groups also include default values for all the instance-level parameters from the DB parameter group. These defaults are mainly intended for configuring Aurora Serverless clusters, which are only associated with DB cluster parameter groups, not DB parameter groups. You can modify the instance-level parameter settings in the DB cluster parameter group. Then, Aurora applies those settings to each new DB instance that's added to a Serverless cluster. To learn more about configuration settings for Aurora Serverless clusters and which settings you can modify, see Parameter groups and Aurora Serverless v1.

For non-Serverless clusters, any configuration values that you modify in the DB cluster parameter group override default values in the DB parameter group. If you edit the corresponding values in the DB parameter group, those values override the settings from the DB cluster parameter group.

Any DB parameter settings that you modify take precedence over the DB cluster parameter group values, even if you change the configuration parameters back to their default values. You can see which parameters are overridden by using the describe-db-parameters AWS CLI command or the DescribeDBParameters RDS API. The Source field contains the value user if you modified that parameter. To reset one or more parameters so that the value from the DB cluster parameter group takes precedence, use the reset-db-parameter-group AWS CLI command or the ResetDBParameterGroup RDS API operation.

The DB cluster and DB instance parameters available to you in Aurora vary depending on database engine compatibility.

Database engine Parameters

Aurora MySQL

See Aurora MySQL parameters.

For Aurora Serverless clusters, see additional details in Parameter groups and Aurora Serverless v1.

Aurora PostgreSQL

See Amazon Aurora PostgreSQL parameters.

Creating a DB parameter group

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

To create a DB parameter group

  1. Sign in to the AWS Management Console and open the Amazon RDS console at https://console.aws.amazon.com/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 AWS CLI create-db-parameter-group command. The following example creates a DB parameter group named mydbparametergroup for MySQL version 5.6 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 aurora5.6 \
    --description "My new parameter group"

For Windows:

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

This command produces output similar to the following:

DBPARAMETERGROUP  mydbparametergroup  aurora5.6  My new parameter group

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

Include the following required parameters:

  • DBParameterGroupName

  • DBParameterGroupFamily

  • Description

Creating a DB cluster parameter group

You can create a new DB cluster parameter group using the AWS Management Console, the AWS CLI, or the RDS API.

To create a DB cluster parameter group

  1. Sign in to the AWS Management Console and open the Amazon RDS console at https://console.aws.amazon.com/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 Cluster Parameter Group.

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

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

  8. Choose Create.

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

Include the following required parameters:

  • --db-cluster-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-cluster-parameter-group \
    --db-cluster-parameter-group-name mydbclusterparametergroup \
    --db-parameter-group-family aurora5.6 \
    --description "My new cluster parameter group"

For Windows:

aws rds create-db-cluster-parameter-group ^
    --db-cluster-parameter-group-name mydbclusterparametergroup ^
    --db-parameter-group-family aurora5.6 ^
    --description "My new cluster parameter group"

This command produces output similar to the following:

DBCLUSTERPARAMETERGROUP  mydbclusterparametergroup  mysql5.6  My cluster new parameter group

To create a DB cluster parameter group, use the RDS API CreateDBClusterParameterGroup action.

Include the following required parameters:

  • DBClusterParameterGroupName

  • 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 AWS Management Console, the AWS 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 modifying a DB instance, see Modify a DB instance in a DB cluster.

Note

When you change the DB parameter group associated with a DB instance, you must manually reboot the instance before the DB instance can use the new DB parameter group.

To associate a DB parameter group with a DB instance

  1. Sign in to the AWS Management Console and open the Amazon RDS console at https://console.aws.amazon.com/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.

  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 AWS 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.

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

Associating a DB cluster parameter group with a DB cluster

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

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

Note

After you change the DB cluster parameter group associated with a DB cluster, reboot the primary DB instance in the cluster to apply the changes to all of the DB instances in the cluster.

To determine whether the primary DB instance of a DB cluster must be rebooted to apply changes, run the following AWS CLI command:

aws rds describe-db-clusters --db-cluster-identifier db_cluster_identifier

Check the DBClusterParameterGroupStatus value for the primary DB instance in the output. If the value is pending-reboot, then reboot the primary DB instance of the DB cluster.

To associate a DB cluster parameter group with a DB cluster

  1. Sign in to the AWS Management Console and open the Amazon RDS console at https://console.aws.amazon.com/rds/.

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

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

  4. Change the DB cluster parameter group setting.

  5. Choose Continue and check the summary of modifications.

    The change is applied immediately regardless of the Scheduling of modifications setting.

  6. On the confirmation page, review your changes. If they are correct, choose Modify cluster to save your changes.

    Alternatively, choose Back to edit your changes, or choose Cancel to cancel your changes.

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

  • --db-cluster-name

  • --db-cluster-parameter-group-name

The following example associates the mydbclpg DB parameter group with the mydbcluster DB cluster.

Example

For Linux, macOS, or Unix:

aws rds modify-db-cluster \
    --db-cluster-identifier mydbcluster \
    --db-cluster-parameter-group-name mydbclpg

For Windows:

aws rds modify-db-cluster ^
    --db-cluster-identifier mydbcluster ^
    --db-cluster-parameter-group-name mydbclpg

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

  • DBClusterIdentifier

  • DBClusterParameterGroupName

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, if the DB instance isn't using the latest changes to its associated DB parameter group, 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 AWS Management Console and open the Amazon RDS console at https://console.aws.amazon.com/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 AWS 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

Modifying parameters in a DB cluster parameter group

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

To modify a DB cluster parameter group

  1. Sign in to the AWS Management Console and open the Amazon RDS console at https://console.aws.amazon.com/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 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.

  7. Reboot the primary DB instance in the cluster to apply the changes to all of the DB instances in the cluster.

To modify a DB cluster parameter group, use the AWS CLI modify-db-cluster-parameter-group command with the following required parameters:

  • --db-cluster-parameter-group-name

  • --parameters

The following example modifies the server_audit_logging and server_audit_logs_upload values in the DB cluster parameter group named mydbclusterparametergroup.

Example

For Linux, macOS, or Unix:

aws rds modify-db-cluster-parameter-group \
    --db-cluster-parameter-group-name mydbclusterparametergroup \
    --parameters "ParameterName=server_audit_logging,ParameterValue=1,ApplyMethod=immediate" \
                 "ParameterName=server_audit_logs_upload,ParameterValue=1,ApplyMethod=immediate"

For Windows:

aws rds modify-db-cluster-parameter-group ^
    --db-cluster-parameter-group-name mydbclusterparametergroup ^
    --parameters "ParameterName=server_audit_logging,ParameterValue=1,ApplyMethod=immediate" ^
                 "ParameterName=server_audit_logs_upload,ParameterValue=1,ApplyMethod=immediate"

The command produces output like the following:

DBCLUSTERPARAMETERGROUP  mydbclusterparametergroup

To modify a DB cluster parameter group, use the RDS API ModifyDBClusterParameterGroup command with the following required parameters:

  • DBClusterParameterGroupName

  • 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, but you can't easily reset all of the parameters in the DB parameter group at once. When you use the AWS CLI or RDS API, you can reset specific parameters to their default values, and you can 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, if the DB instance isn't using the latest changes to its associated DB parameter group, 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 AWS Management Console and open the Amazon RDS console at https://console.aws.amazon.com/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 AWS 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.

Resetting parameters in a DB cluster parameter group

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

Note

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

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

  1. Sign in to the AWS Management Console and open the Amazon RDS console at https://console.aws.amazon.com/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.

  7. Reboot the primary DB instance in the DB cluster to apply the changes to all of the DB instances in the DB cluster.

To reset parameters in a DB cluster parameter group to their default values, use the AWS CLI reset-db-cluster-parameter-group command with the following required option: --db-cluster-parameter-group-name.

To reset all of the parameters in the DB cluster 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-cluster-parameter-group \
    --db-cluster-parameter-group-name mydbparametergroup \
    --reset-all-parameters

For Windows:

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

The following example resets the server_audit_logging and server_audit_logs_upload to their default values in the DB cluster parameter group named mydbclusterparametergroup.

Example

For Linux, macOS, or Unix:

aws rds reset-db-cluster-parameter-group \
    --db-cluster-parameter-group-name mydbclusterparametergroup \
    --parameters "ParameterName=server_audit_logging,ApplyMethod=immediate" \
                 "ParameterName=server_audit_logs_upload,ApplyMethod=immediate"

For Windows:

aws rds reset-db-cluster-parameter-group ^
    --db-cluster-parameter-group-name mydbclusterparametergroup ^
    --parameters "ParameterName=server_audit_logging,ParameterValue=1,ApplyMethod=immediate" ^
                 "ParameterName=server_audit_logs_upload,ParameterValue=1,ApplyMethod=immediate"

The command produces output like the following:

DBClusterParameterGroupName  mydbclusterparametergroup

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

To reset all of the parameters in the DB cluster 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 is a convenient solution when you have already created a DB parameter group and you want to include most of the custom parameters and values from that group in a new DB parameter group. You can copy a DB parameter group by using the AWS Management Console, the AWS 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.

Currently, you can't copy a parameter group to a different AWS Region.

To copy a DB parameter group

  1. Sign in to the AWS Management Console and open the Amazon RDS console at https://console.aws.amazon.com/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 AWS 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

Copying a DB cluster parameter group

You can copy custom DB cluster parameter groups that you create. Copying a parameter group is a convenient solution when you have already created a DB cluster parameter group and you want to include most of the custom parameters and values from that group in a new DB cluster parameter group. You can copy a DB cluster parameter group by using the AWS CLI copy-db-cluster-parameter-group command or the RDS API CopyDBClusterParameterGroup operation.

After you copy a DB cluster parameter group, wait at least 5 minutes before creating your first DB cluster that uses that DB cluster parameter group as the default parameter group. Doing this allows Amazon RDS to fully complete the copy action before the parameter group is used as the default for a new DB cluster. You can use the Parameter Groups option of the Amazon RDS console or the describe-db-cluster-parameters command to verify that your DB cluster 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.

To copy a DB cluster parameter group

  1. Sign in to the AWS Management Console and open the Amazon RDS console at https://console.aws.amazon.com/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 cluster parameter group, use the AWS CLI copy-db-cluster-parameter-group command with the following required parameters:

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

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

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

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

Example

For Linux, macOS, or Unix:

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

For Windows:

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

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

  • SourceDBClusterParameterGroupIdentifier

  • TargetDBClusterParameterGroupIdentifier

  • TargetDBClusterParameterGroupDescription

Listing DB parameter groups

You can list the DB parameter groups you've created for your AWS 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 AWS account

  1. Sign in to the AWS Management Console and open the Amazon RDS console at https://console.aws.amazon.com/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 AWS account, use the AWS CLI describe-db-parameter-groups command.

Example

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

aws rds describe-db-parameter-groups

The command returns a response like the following:

DBPARAMETERGROUP  default.mysql5.5     mysql5.5  Default parameter group for MySQL5.5
DBPARAMETERGROUP  default.mysql5.6     mysql5.6  Default parameter group for MySQL5.6
DBPARAMETERGROUP  mydbparametergroup   mysql5.6  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  mysql5.5  My new parameter group

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

Listing DB cluster parameter groups

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

Note

Default parameter groups are automatically created from a default parameter template when you create a DB cluster 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 cluster parameter groups for an AWS account

  1. Sign in to the AWS Management Console and open the Amazon RDS console at https://console.aws.amazon.com/rds/.

  2. In the navigation pane, choose Parameter groups.

    The DB cluster parameter groups appear in the list with DB cluster parameter group for Type.

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

Example

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

aws rds describe-db-cluster-parameter-groups

The command returns a response like the following:


DBCLUSTERPARAMETERGROUPS        arn:aws:rds:us-west-2:1234567890:cluster-pg:default.aurora5.6 default.aurora5.6       aurora5.6       Default cluster parameter group for aurora5.6
DBCLUSTERPARAMETERGROUPS        arn:aws:rds:us-west-2:1234567890:cluster-pg:mydbclusterparametergroup mydbclusterparametergroup       aurora5.6       My new cluster parameter group

The following example describes the mydbclusterparametergroup parameter group.

For Linux, macOS, or Unix:

aws rds describe-db-cluster-parameter-groups \
    --db-cluster-parameter-group-name mydbclusterparametergroup

For Windows:

aws rds describe-db-cluster-parameter-groups ^
    --db-cluster-parameter-group-name mydbclusterparametergroup

The command returns a response like the following:

DBCLUSTERPARAMETERGROUPS        arn:aws:rds:us-west-2:1234567890:cluster-pg:mydbclusterparametergroup mydbclusterparametergroup       aurora5.6       My new cluster parameter group

To list all DB cluster parameter groups for an AWS account, use the RDS API DescribeDBClusterParameterGroups action.

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 AWS Management Console and open the Amazon RDS console at https://console.aws.amazon.com/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 AWS 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

Viewing parameter values for a DB cluster parameter group

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

To view the parameter values for a DB cluster parameter group

  1. Sign in to the AWS Management Console and open the Amazon RDS console at https://console.aws.amazon.com/rds/.

  2. In the navigation pane, choose Parameter groups.

    The DB cluster parameter groups appear in the list with DB cluster parameter group for Type.

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

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

  • --db-cluster-parameter-group-name

Example

The following example lists the parameters and parameter values for a DB cluster parameter group named mydbclusterparametergroup, in JSON format.

The command returns a response like the following:

aws rds describe-db-cluster-parameters --db-cluster-parameter-group-name mydbclusterparametergroup

{
    "Parameters": [
        {
            "ApplyMethod": "pending-reboot",
            "Description": "Controls whether user-defined functions that have only an xxx symbol for the main function can be loaded",
            "DataType": "boolean",
            "AllowedValues": "0,1",
            "SupportedEngineModes": [
                "provisioned"
            ],
            "Source": "engine-default",
            "IsModifiable": false,
            "ParameterName": "allow-suspicious-udfs",
            "ApplyType": "static"
        },
        {
            "ApplyMethod": "pending-reboot",
            "Description": "Enables new features in the Aurora engine.",
            "DataType": "boolean",
            "IsModifiable": true,
            "AllowedValues": "0,1",
            "SupportedEngineModes": [
                "provisioned"
            ],
            "Source": "engine-default",
            "ParameterValue": "0",
            "ParameterName": "aurora_lab_mode",
            "ApplyType": "static"
        },
...

The following example lists the parameters and parameter values for a DB cluster parameter group named mydbclusterparametergroup, in plain text format. 

aws rds describe-db-cluster-parameters --db-cluster-parameter-group-name mydbclusterparametergroup --output text

The command returns a response like the following:


PARAMETERS  0,1  pending-reboot  static  boolean  Controls whether user-defined functions that have only an xxx symbol for the main function can be loaded  False  allow-suspicious-udfs    engine-default SUPPORTEDENGINEMODES  provisioned
PARAMETERS  0,1  pending-reboot  static  boolean  Enables new features in the Aurora engine.  True  aurora_lab_mode  0  engine-default SUPPORTEDENGINEMODES  provisioned
...

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

  • DBClusterParameterGroupName

Comparing parameter groups

You can use the AWS Management Console to view the differences between two parameter groups for the same DB engine and version.

To compare two parameter groups

  1. Sign in to the AWS Management Console and open the Amazon RDS console at https://console.aws.amazon.com/rds/.

  2. In the navigation pane, choose Parameter groups.

  3. In the list, choose the two parameter groups that you want to compare.

  4. For Parameter group actions, choose Compare.

    Note

    If the items you selected aren't equivalent, you can't choose Compare. For example, you can't compare a MySQL 5.6 and a MySQL 5.7 parameter group. You can't compare a DB parameter group and an Aurora DB cluster parameter group.

Specifying DB parameters

DB parameter types include the following:

  • integer

  • Boolean

  • string

  • long

  • double

  • timestamp

  • object of other defined data types

  • array of values of type integer, Boolean, string, long, double, timestamp, or object

You can also specify integer and Boolean DB parameters using expressions, formulas, and functions.

DB parameter formulas

A DB parameter formula is an expression that resolves to an integer value or a Boolean value. You enclose the expression in braces: {}. You can use a formula for either a DB parameter value or as an argument to a DB parameter function.

Syntax
{FormulaVariable}
{FormulaVariable*Integer}
{FormulaVariable*Integer/Integer}
{FormulaVariable/Integer}

DB parameter formula variables

Each formula variable returns an integer or a Boolean value. The names of the variables are case-sensitive.

AllocatedStorage

Returns an integer representing the size, in bytes, of the data volume.

DBInstanceClassMemory

Returns an integer of the number of bytes of memory allocated to the DB instance class associated with the current DB instance, less the memory used by RDS processes that manage the instance.

EndPointPort

Returns an integer representing the port used when connecting to the DB instance.

DB parameter formula operators

DB parameter formulas support two operators: division and multiplication.

Division operator: /

Divides the dividend by the divisor, returning an integer quotient. Decimals in the quotient are truncated, not rounded.

Syntax

dividend / divisor

The dividend and divisor arguments must be integer expressions.

Multiplication operator: *

Multiplies the expressions, returning the product of the expressions. Decimals in the expressions are truncated, not rounded.

Syntax

expression * expression

Both expressions must be integers.

DB parameter functions

You specify the arguments of DB parameter functions as either integers or formulas. Each function must have at least one argument. Specify multiple arguments as a comma-separated list. The list can't have any empty members, such as argument1,,argument3. Function names are case-insensitive.

IF

Returns an argument.

Syntax

IF(argument1, argument2, argument3)

Returns the second argument if the first argument evaluates to true. Returns the third argument otherwise.

GREATEST

Returns the largest value from a list of integers or parameter formulas.

Syntax

GREATEST(argument1, argument2,...argumentn)

Returns an integer.

LEAST

Returns the smallest value from a list of integers or parameter formulas.

Syntax

LEAST(argument1, argument2,...argumentn)

Returns an integer.

SUM

Adds the values of the specified integers or parameter formulas.

Syntax

SUM(argument1, argument2,...argumentn)

Returns an integer.

DB parameter log expressions

You can set an integer DB parameter value to a log expression. You enclose the expression in braces: {}. For example: 

{log(DBInstanceClassMemory/8187281418)*1000}

The log function represents log base 2. This example also uses the DBInstanceClassMemory formula variable. See DB parameter formula variables.

DB parameter value examples

These examples show using formulas, functions, and expressions for the values of DB parameters.

Note

DB Parameter functions are currently supported only in the console and aren't supported in the AWS CLI.

Warning

Improperly setting parameters in a DB parameter group can have unintended adverse effects. These might include degraded performance and system instability. Use caution when modifying database parameters and back up your data before modifying your DB parameter group. Try out parameter group changes on a test DB instance, created using point-in-time-restores, before applying those parameter group changes to your production DB instances.

Example using the DB parameter function LEAST

You can specify the LEAST function in an Aurora MySQL table_definition_cache parameter value. Use it to set the number of table definitions that can be stored in the definition cache to the lesser of DBInstanceClassMemory/393040 or 20,000. 

LEAST({DBInstanceClassMemory/393040}, 20000)