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.
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:
Other common setups would use:
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
[mysqld] ... default-time-zone = "Europe/London"
Now restart your mysql service for the changes to take effect.
sudo service mysql restart
First published: 16th August 2018