How to change the timezone of MySQL server running on Amazon Lightsail
The Problem
I have some code that needs to run some calculations based on PST, but everytime I’m looking at the debug logs, or output, the time that’s displayed is in UTC, and I don’t like doing the mental calculations.. even worse, when I’m comparing times in the datetime columns of the MySQL database, my calculations are wrong.
So, I wanted a way to standardize on the timezone that’s on the server and the MySQL instance.
Changing Timezone on Ubuntu Server
To change the time zone on the Ubuntu server all you need to do is run the timedatectl command.
1
2
3
4
5
6
date
Fri Mar 3 00:12:14 UTC 2023
sudo timedatectl set-timezone America/Los_Angeles
date
Thu Mar 2 16:12:41 PST 2023
Changing Time zone on the MySQL Server
The MySQL server is actually a Lightsail database, so I can’t just edit the /etc/my.cnf and call it a day.
To make this change I need to use the aws cli.
Assuming you have AWS CLI 2.x on your system here’s what you need to do.
Get List of Databases
Get a list of relational databases in your account, be sure to specify the region if it’s not your default region.
1
aws lightsail get-relational-databases --region us-east-2
The output will be something like
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
{
"relationalDatabases": [
{
"name": "nice-name-you-see-in-lightsail-interface",
"arn": "arn:aws:lightsail:us-east-2:1234567890:RelationalDatabase/qw34sd-m2cd-wxyz-abcd-123040440c",
"supportCode": "123456789/ls-73longstreamofcharacters",
"createdAt": "2022-12-25T23:04:36.655000-06:00",
"location": {
"availabilityZone": "us-east-2a",
"regionName": "us-east-2"
},
"resourceType": "RelationalDatabase",
"tags": [
{
"key": "somekey",
"value": "somevalue"
}
],
"relationalDatabaseBlueprintId": "mysql_8_0",
"relationalDatabaseBundleId": "micro_2_0",
"masterDatabaseName": "nameofmasterdatabase",
"hardware": {
"cpuCount": 2,
"diskSizeInGb": 40,
"ramSizeInGb": 1.0
},
"state": "available",
"backupRetentionEnabled": true,
"pendingModifiedValues": {},
"engine": "mysql",
"engineVersion": "8.0.31",
"latestRestorableTime": "2023-03-02T17:55:00-06:00",
"masterUsername": "nameofuserwithallthegrants",
"parameterApplyStatus": "in-sync",
"preferredBackupWindow": "03:59-04:29",
"preferredMaintenanceWindow": "tue:09:06-tue:09:36",
"publiclyAccessible": false,
"masterEndpoint": {
"port": 3306,
"address": "ls-randomestring.anotherstring.us-east-2.rds.amazonaws.com"
},
"pendingMaintenanceActions": [
{
"action": "system-update",
"description": "New Operating System update is available"
}
],
"caCertificateIdentifier": "rds-ca-2019"
}
]
}
Get the parameters that are set for the database
1
aws lightsail get-relational-database-parameters --relational-database-name DatabaseName --region us-east-2
This will give you output like this, notice the parameterName of system_time_zone.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
{
"parameters": [
{
"applyMethod": "pending-reboot",
"applyType": "static",
"dataType": "string",
"description": "",
"isModifiable": false,
"parameterName": "system_time_zone"
},
{
"allowedValues": "0,1",
"applyMethod": "pending-reboot",
"applyType": "static",
"dataType": "boolean",
"description": "Controls whether user-defined functions that have only an xxx symbol for the main function can be loaded",
"isModifiable": false,
"parameterName": "allow-suspicious-udfs"
},
...
]
}
Update the timezone
Now you can update that parameter by executing the following command
1
aws lightsail --region us-east-2 update-relational-database-parameters --relational-database-name mfsl-streamline --parameters "parameterName=system_time_zone,parameterValue=America/Los_Angeles,applyMethod=pending-reboot"
References
https://aws.amazon.com/premiumsupport/knowledge-center/lightsail-update-database-parameter/
https://lightsail.aws.amazon.com/ls/docs/en_us/articles/amazon-lightsail-updating-database-parameters#get-database-parameters