How to set timezone [mysql]

MySQL lets you specify which timezone to use in the my.cnf file, using the following config line:

default-time-zone=Europe/Oslo

However, you might find, after you do this, that it ends up failing to restart the service with the following error in the logs:

Fatal error: Illegal or unknown default time zone

Obviously Europe/London is a valid timezone name, so why does it not work?

Basically this error is caused when MySQL hasn't populated the timezone table within the mysql database, so it doesn't know what is or isn't a valid timezone name.

The way to fix this is, simply, to populate the timezone table, which can be done using the following command via ssh (You will need root access in mysql for this, and to have removed the line and gotten mysql back up and running of course):

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

Pop in your mysql root password and wait for it to finish. Re-add the line to the my.cnf, restart the service and you should be all set!