Generally, to update database parameters in a Lightsail MySQL we use the AWS Command Line Interface.

In order to perform this, we need to also ensure we have the most recent AWS Command Line Interface.

Here, at Bobcares, we assist our customers with several AWS queries as part of our AWS Support Services.

Today, let us see how we can update database parameters in an Amazon Lightsail MySQL or PostgreSQL database.

Update Database Parameters in a Lightsail MySQL

When we create a Lightsail database, it uses a custom parameter group named after the instance endpoint, unlike Amazon RDS DB instances that use a default DB parameter group.

We can use the AWS Command Line Interface (AWS CLI) to modify the database parameters for a Lightsail database instance.

However, if we receive any errors while we run AWS CLI commands, we need to make sure we use the most recent version of the AWS CLI.

Moving ahead, let us see how our Support Techs modify the DB parameters.

1. Initially, we install the AWS CLI in the same AWS Region as the Lightsail database.

2. Then we get a list of the available database parameters that we can modify.

3. Once we identify the parameter that to change, we go ahead and update the parameter.

Suppose, we update a static parameter and we set the application method to pending-reboot. In that case, the parameter update will only apply the instance is rebooted.

Here is an example command for Lightsail Amazon RDS MySQL 5.7.26:

aws lightsail update-relational-database-parameters --relational-database-name Lightsail-Database-Ireland-1 --parameters "parameterName=connect_timeout,parameterValue=30,applyMethod=immediate"

The example output for this operation is as follows:

{
"operations": [
{
"status": "Succeeded", 
"resourceType": "RelationalDatabase", 
"isTerminal": true, 
"statusChangedAt": 1579868316.024, 
"location": {
"availabilityZone": "eu-west-1a", 
"regionName": "eu-west-1" 
}, 
"operationType": "UpdateRelationalDatabaseParameters", 
"resourceName": "Lightsail-Database-Ireland-1", 
"id": "23a7de77-aa6c-4831-8525-8c6d97921676", 
"createdAt": 1579868316.024 
}
]
}

Similarly, here is an example command for Lightsail Amazon RDS PostgreSQL 10.10:

aws lightsail update-relational-database-parameters --relational-database-name lightsail-postgres --parameters "parameterName=deadlock_timeout,parameterValue=30,applyMethod=immediate"

And the example output for this operation will look like this:

{
"operations": [
{
"status": "Succeeded",
"resourceType": "RelationalDatabase",
"isTerminal": true,
"statusChangedAt": 1579869403.669,
"location": {
"availabilityZone": "eu-west-1a",
"regionName": "eu-west-1"
},
"operationType": "UpdateRelationalDatabaseParameters",
"resourceName": "lightsail-postgres",
"id": "e18a2827-b140-4872-b90c-ab7850a7b6df",
"createdAt": 1579869403.669
}
]
}

[Need help with the modification? We can help you]

Conclusion

To conclude, with AWS CLI we can modify the database parameters of a Lightsail MySQL. Here, in this article, we saw how our Support Techs perform the same.