Set MySQL Timezone

The timezone used in MySQL may not necessarily match up with whatever your host is set up to use. It's best to explicitly set the default that you want, and this can be important in preventing confusion in tools such as Zoneminder where timestamps of events are very important.

This tutorial was written for Ubuntu users, but should be the same or similar for other distros.

Steps

Import Timezone Labels

Run this command to import the "names" of timezones into MySQL:

mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root mysql

You may see some warnings such as below, but don't worry about this.

Warning: Unable to load '/usr/share/zoneinfo/iso3166.tab' as time zone. Skipping it.
Warning: Unable to load '/usr/share/zoneinfo/leap-seconds.list' as time zone. Skipping it.
Warning: Unable to load '/usr/share/zoneinfo/zone.tab' as time zone. Skipping it.
Warning: Unable to load '/usr/share/zoneinfo/zone1970.tab' as time zone. Skipping it.

Update the Config

Now you need to add the timezone to your configuration file, in Ubuntu 16.04 with MySQL 5.7 you would run:

editor /etc/mysql/mysql.conf.d/mysqld.cnf

Other common setups would use:

editor /etc/mysql/my.cnf

Add the line below to the [mysqld] section with your appropriate timezone label. If you are unsure of the exact naming for your zone, refer to this list. For example, because I live in England, I need to use Europe/London.

[mysqld]
... 
default-time-zone = "Europe/London"

Restart MySQL

Now restart your mysql service for the changes to take effect.

sudo service mysql restart

References

• MySQL 5.7 Docs - 10.6 MySQL Server Time Zone Support