Working with DB cluster parameter groups
Amazon Aurora DB clusters use DB cluster parameter groups. The following sections describe configuring and managing DB cluster parameter groups.
Topics
- Amazon Aurora DB cluster and DB instance parameters
- Creating a DB cluster parameter group
- Associating a DB cluster parameter group with a DB cluster
- Modifying parameters in a DB cluster parameter group
- Resetting parameters in a DB cluster parameter group
- Copying a DB cluster parameter group
- Listing DB cluster parameter groups
- 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:
-
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. This parameter group assigns default values for every configuration value for the corresponding DB engine. The cluster parameter group includes defaults for both the cluster-level and instance-level parameters. Each DB instance within a provisioned or Aurora Serverless v2 cluster inherits the settings from that DB cluster parameter group.
Each DB instance is also associated with a DB parameter group. The values in the DB parameter group can override default values from the cluster parameter group. For example, if one instance in a cluster experienced issues, you might assign a custom DB parameter group to that instance. The custom parameter group might have specific settings for parameters related to debugging or performance tuning.
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 specify custom parameter groups instead. You create those parameter groups yourself, and you can edit the parameter values. You can specify these custom parameter groups at creation time. You can also modify a DB cluster or instance later to use a custom parameter group.
For provisioned and Aurora Serverless v2 instances, 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 operation. 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 configuration parameters. For Aurora Serverless clusters, see additional details in Working with parameter groups for Aurora Serverless v2 and Parameter groups for Aurora Serverless v1. |
Aurora PostgreSQL |
See Amazon Aurora PostgreSQL parameters. For Aurora Serverless clusters, see additional details in Working with parameter groups for Aurora Serverless v2 and Parameter groups for Aurora Serverless v1. |
Note
Aurora Serverless v1 clusters have only DB cluster parameter groups, not DB parameter groups. For Aurora Serverless v2 clusters, you make all your changes to custom parameters in the DB cluster parameter group.
Aurora Serverless v2 uses both DB cluster parameter groups and DB parameter groups. With Aurora Serverless v2, you can modify almost all of the configuration parameters. Aurora Serverless v2 overrides the settings of some capacity-related configuration parameters so that your workload isn't interrupted when Aurora Serverless v2 instances scale down.
To learn more about Aurora Serverless configuration settings and which settings you can modify, see Working with parameter groups for Aurora Serverless v2 and Parameter groups for Aurora Serverless v1.
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.
After you create a DB cluster parameter group, wait at least 5 minutes
before creating a DB cluster that uses that DB cluster parameter group.
Doing this allows Amazon RDS to fully create the parameter group before it
is used by the new DB cluster. You can use the Parameter groups page
in the Amazon RDS console
The following limitations apply to the DB cluster 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.aurora-mysql5.7
. 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 cluster parameter group
-
Sign in to the AWS Management Console and open the Amazon RDS console at https://console.aws.amazon.com/rds/
. -
In the navigation pane, choose Parameter groups.
-
Choose Create parameter group.
The Create parameter group window appears.
-
In the Parameter group family list, select a DB parameter group family
-
In the Type list, select DB Cluster Parameter Group.
-
In the Group name box, enter the name of the new DB cluster parameter group.
-
In the Description box, enter a description for the new DB cluster parameter group.
-
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 Aurora MySQL version 5.7 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-familyaurora-mysql5.7
\ --description"My new cluster parameter group"
For Windows:
aws rds create-db-cluster-parameter-group ^ --db-cluster-parameter-group-name
mydbclusterparametergroup
^ --db-parameter-group-familyaurora-mysql5.7
^ --description"My new cluster parameter group"
This command produces output similar to the following:
{ "DBClusterParameterGroup": { "DBClusterParameterGroupName": "mydbclusterparametergroup", "DBParameterGroupFamily": "aurora-mysql5.7", "Description": "My new cluster parameter group", "DBClusterParameterGroupArn": "arn:aws:rds:us-east-1:123456789012:cluster-pg:mydbclusterparametergroup" } }
To create a DB cluster parameter group, use the RDS API CreateDBClusterParameterGroup
action.
Include the following required parameters:
-
DBClusterParameterGroupName
-
DBParameterGroupFamily
-
Description
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
Sign in to the AWS Management Console and open the Amazon RDS console at https://console.aws.amazon.com/rds/
. -
In the navigation pane, choose Databases, and then select the DB cluster that you want to modify.
-
Choose Modify. The Modify DB cluster page appears.
-
Change the DB cluster parameter group setting.
-
Choose Continue and check the summary of modifications.
The change is applied immediately regardless of the Scheduling of modifications setting.
-
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-namemydbclpg
For Windows:
aws rds modify-db-cluster ^ --db-cluster-identifier
mydbcluster
^ --db-cluster-parameter-group-namemydbclpg
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 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
-
Sign in to the AWS Management Console and open the Amazon RDS console at https://console.aws.amazon.com/rds/
. -
In the navigation pane, choose Parameter groups.
-
In the list, choose the parameter group that you want to modify.
-
For Parameter group actions, choose Edit.
-
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.
-
Choose Save changes.
-
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 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
-
Sign in to the AWS Management Console and open the Amazon RDS console at https://console.aws.amazon.com/rds/
. -
In the navigation pane, choose Parameter groups.
-
In the list, choose the parameter group.
-
For Parameter group actions, choose Edit.
-
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.
-
Choose Reset and then confirm by choosing Reset parameters.
-
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 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 a DB cluster that uses that DB cluster parameter group.
Doing this allows Amazon RDS to fully copy the parameter group before it
is used by the new DB cluster. You can use the Parameter groups page
in the Amazon RDS console
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 cluster parameter group to a different AWS account or AWS Region.
To copy a DB cluster parameter group
-
Sign in to the AWS Management Console and open the Amazon RDS console at https://console.aws.amazon.com/rds/
. -
In the navigation pane, choose Parameter groups.
-
In the list, choose the custom parameter group that you want to copy.
-
For Parameter group actions, choose Copy.
-
In New DB parameter group identifier, enter a name for the new parameter group.
-
In Description, enter a description for the new parameter group.
-
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-identifiermygroup2
\ --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-identifiermygroup2
^ --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 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
-
Sign in to the AWS Management Console and open the Amazon RDS console at https://console.aws.amazon.com/rds/
. -
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 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": [ { "DBClusterParameterGroupName": "mydbclusterparametergroup", "DBParameterGroupFamily": "aurora-mysql5.7", "Description": "My new cluster parameter group", "DBClusterParameterGroupArn": "arn:aws:rds:us-east-1:123456789012:cluster-pg:mydbclusterparametergroup" } ] }
To list all DB cluster parameter groups for an AWS account, use the RDS API DescribeDBClusterParameterGroups
action.
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
-
Sign in to the AWS Management Console and open the Amazon RDS console at https://console.aws.amazon.com/rds/
. -
In the navigation pane, choose Parameter groups.
The DB cluster parameter groups appear in the list with DB cluster parameter group for Type.
-
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": [
{
"ParameterName": "allow-suspicious-udfs",
"Description": "Controls whether user-defined functions that have only an xxx symbol for the main function can be loaded",
"Source": "engine-default",
"ApplyType": "static",
"DataType": "boolean",
"AllowedValues": "0,1",
"IsModifiable": false,
"ApplyMethod": "pending-reboot",
"SupportedEngineModes": [
"provisioned"
]
},
{
"ParameterName": "aurora_binlog_read_buffer_size",
"ParameterValue": "5242880",
"Description": "Read buffer size used by master dump thread when the switch aurora_binlog_use_large_read_buffer is ON.",
"Source": "engine-default",
"ApplyType": "dynamic",
"DataType": "integer",
"AllowedValues": "8192-536870912",
"IsModifiable": true,
"ApplyMethod": "pending-reboot",
"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
In some cases, the allowed values for a parameter aren't shown. These are always parameters where the source is the database engine default.
To view the values of these parameters, you can run the following SQL statements:
-
MySQL:
-- Show the value of a particular parameter mysql
$
SHOW VARIABLES LIKE '%parameter_name
%'; -- Show the values of all parameters mysql$
SHOW VARIABLES; -
PostgreSQL:
-- Show the value of a particular parameter postgresql=> SHOW
parameter_name
; -- Show the values of all parameters postgresql=> SHOW ALL;