How to change the time zone setting of MySQL running on cloud server such as DigitalOcean

I ran into an issue with the time stamp of the data stored in the MySQL database running on DigitalOcean droplet. The time stamp is 8 hours off from my current time. This post is a quick summary of how to solve this issue.

Check the current local time setting of the LAMP server

Ref.: https://www.tecmint.com/check-linux-timezone/

So, the time zone of the server is set to UTC. It needs to be changed to my time zone so that the time stamp could be corrected.

wei48221@lamp-s-1vcpu-1gb-sfo2-01:/etc/mysql$ timedatectl
      Local time: Tue 2018-09-18 12:39:28 UTC
  Universal time: Tue 2018-09-18 12:39:28 UTC
        RTC time: Tue 2018-09-18 12:39:28
       Time zone: Etc/UTC (UTC, +0000)
 Network time on: yes
NTP synchronized: yes
 RTC in local TZ: no

Change the local time setting of the LAMP server

Ref.: https://linuxacademy.com/blog/linux/changing-the-time-zone-in-linux-command-line/

Location of the local time file

Linux looks at /etc/localtime to determine the current time of your machine. This can either be a symbolic link to the correct time zone or a direct copy of the time zone file.

Timezone files are located in /usr/share/zoninfo/

Assuming the server is located in America and is under the Chicago CST zone. You could change the Linux time zone by copying or making a symbolic link from /usr/share/zoneinfo/America/Chicago to /etc/localtime with the below command.

sudo cp /usr/share/zoneinfo/America/Chicago /etc/localtime

In my case, I want to change the time zone to Taiwan (ROC).

So, below is the command used.

sudo cp /usr/share/zoneinfo/ROC /etc/localtime

Below is the content of the ORIGINAL  "/etc/localtime".

wei48221@lamp-s-1vcpu-1gb-sfo2-01:/etc$ cat localtime
TZif2UTCTZif2▒UTC
UTC0

Below is the result of running "timedatectl" BEFORE making the change. The local time is set to UTC.

wei48221@lamp-s-1vcpu-1gb-sfo2-01:/etc/mysql$ timedatectl
      Local time: Tue 2018-09-18 12:39:28 UTC
  Universal time: Tue 2018-09-18 12:39:28 UTC
        RTC time: Tue 2018-09-18 12:39:28
       Time zone: Etc/UTC (UTC, +0000)
 Network time on: yes
NTP synchronized: yes
 RTC in local TZ: no

Below is the content of "/etc/localtime" AFTER making the change.

wei48221@lamp-s-1vcpu-1gb-sfo2-01:/etc$ cat localtime
TZif2)▒▒UI▒▒TY▒Ӌ{▒▒B▒▒▒E"▒L▒▒▒<▒▒fp▒▒▒▒▒▒▒&▒▒▒p▒▒Y▒ݪ▒▒rsߵdp▒|▒ᖗ▒▒]▒▒▒w▒p▒>▒▒0 p▒!q▒▒p▒▒▒▒▒▒▒▒▒▒▒▒
p▒▒
   ▒▒?▒▒▒▒▒▒▒▒▒/▒▒y▒p▒▒p        ݉▒
ν▒ۡ▒T▒pq▒p▒~~▒
p▒LMTCSTJSTCDTTZif2*▒▒▒▒▒t▒▒▒▒▒▒▒UI▒▒▒▒▒▒TY▒▒▒▒▒Ӌ{▒▒▒▒▒▒B▒▒▒▒▒▒▒E"▒▒▒▒▒L▒▒▒▒▒▒▒<▒▒▒▒▒▒fp▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒&▒▒▒▒▒▒▒p▒▒▒▒▒▒Y▒▒▒▒▒ݪ▒▒▒▒▒▒rs▒▒▒▒ߵdp▒▒▒▒▒|▒▒▒▒▒ᖗ▒▒▒▒▒▒]▒▒▒▒▒▒▒w▒p▒▒▒▒▒>▒▒▒▒▒▒0 p▒▒▒▒▒!q▒▒▒▒▒▒p▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒
p▒▒▒▒▒▒
       ▒▒▒▒▒▒?▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒/▒▒▒▒▒▒y▒p▒▒p        ݉▒
ν▒ۡ▒T▒pq▒p▒~~▒
p▒LMTCSTJSTCDT
CST-8

Below is the result of running "timedatectl" AFTER making the change (the local time is now set to CST).

The server's local time is now changed to my current time zone (see the clock at the bottom right corner).

With the server time set to Taiwan (ROC), follow the steps below to check the time of MySQL again.

1. Remove the 2 lines from the bottom of my.cnf.

[mysqld]
default-time-zone = '+8:00'

Below is the original content of "my.cnf".

wei48221@lamp-s-1vcpu-1gb-sfo2-01:/etc/mysql$ cat my.cnf
#
# The MySQL database server configuration file.
#
# You can copy this to one of:
# - "/etc/mysql/my.cnf" to set global options,
# - "~/.my.cnf" to set user-specific options.
#
# One can use all long options that the program supports.
# Run program with --help to get a list of available options and with
# --print-defaults to see which it would actually understand and use.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html

#
# * IMPORTANT: Additional settings that can override those from this file!
#   The files must end with '.cnf', otherwise they'll be ignored.
#

!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mysql.conf.d/

2. Restart MySQL by issuing "sudo /etc/init.d/mysql restart".

3. In phpMyAdmin, issue "show variables like '%time_zone%';". The result below shows that the system_time_zone is now changed from UTC to CST.

Result AFTER the change.

Result BEFORE the change.

4. In phpMyAdmin, the time stamp of the data uploaded to the database is now in line with my system clock.

What to do if the time zone returns to the default setting?

1. Issue "timedatectl" to check for the time setting;

2. Issue the command "sudo cp /usr/share/zoneinfo/ROC /etc/localtime" to set the time zone to the desired time zone (note. the above command will set the time zone to that of Taiwan).

3. Restart MySQL by issuing "sudo /etc/init.d/mysql restart".

========================================================================

The problem occurs because systemd-timesyncd.service active: yes in the output which is why the timezone is getting reset to UTC.

wei48221@lamp-s-1vcpu-1gb-sfo2-01:~$ timedatectl
      Local time: Tue 2018-10-30 15:13:51 CST
  Universal time: Tue 2018-10-30 07:13:51 UTC
        RTC time: Tue 2018-10-30 07:13:50
       Time zone: Etc/UTC (CST, +0800)
 Network time on: yes
NTP synchronized: yes
 RTC in local TZ: no
wei48221@lamp-s-1vcpu-1gb-sfo2-01:~$

To set the sync to no, issue the command "sudo timedatectl set-ntp no", then follow by "timedatectl" to check the result.

wei48221@lamp-s-1vcpu-1gb-sfo2-01:~$ sudo timedatectl set-ntp no
[sudo] password for wei48221:
wei48221@lamp-s-1vcpu-1gb-sfo2-01:~$ timedatectl
      Local time: Tue 2018-10-30 15:25:12 CST
  Universal time: Tue 2018-10-30 07:25:12 UTC
        RTC time: Tue 2018-10-30 07:25:11
       Time zone: Etc/UTC (CST, +0800)
 Network time on: no
NTP synchronized: yes
 RTC in local TZ: no
wei48221@lamp-s-1vcpu-1gb-sfo2-01:~$

Once the above steps are done, set the timezone to ROC again using the below command:

ln -sf /usr/share/zoneinfo/ROC /etc/localtime 

Note, couple hours after executing "sudo timedatectl set-ntp no", below is what the output of running timedatectl looks like.

wei48221@lamp-s-1vcpu-1gb-sfo2-01:~$ timedatectl
      Local time: Wed 2018-10-31 07:09:11 CST
  Universal time: Tue 2018-10-30 23:09:11 UTC
        RTC time: Tue 2018-10-30 23:09:11
       Time zone: Etc/UTC (CST, +0800)
 Network time on: no
NTP synchronized: no
 RTC in local TZ: no
wei48221@lamp-s-1vcpu-1gb-sfo2-01:~$

Reference:
https://www.digitalocean.com/community/tutorials/how-to-set-up-time-synchronization-on-ubuntu-16-04

========================================================================

References:

Linux 如何設定時區？
https://magiclen.org/linux-timezone/

6- Changing Time Zone for MySQL
https://www.youtube.com/watch?v=eZc557BbDDM

Common MySQL Commands
      - Start: sudo /etc/init.d/mysql start
      - Stop: sudo /etc/init.d/mysql stop
      - Restart / reload configs: sudo /etc/init.d/mysql restart
      - Check run status: sudo /etc/init.d/mysql status