Join to Get Free Linux Server Hacks & cPanel Tips

How can I change the default MYSQL time zone?

How can I change the default MYSQL time zone?
4.8 (96.25%) 16 votes

Is there any way to change the default time zone in MySQL? That was the question made from one of my customers. And the answer is simple, yes, the MySQL time zone can be changed without any problems using the command line. This change will affect all your MySQL databases, and can not be applied to only one database, it’s a global change.

Setting up a custom MySQL time zone

By default the MySQL time zone is the same as the operating system your server is running.
You can check your Linux server time by running the date command:

date

Output example:

[root@server.nixcp.com:~]date
Tue Dec 6 08:07:50 EST 2016
[root@server.nixcp.com:~]

How can I verify MySQL time zone settings?

As I said before, it should be the same as your system date and time, but you can run this query against your MySQL server to get your MySQL server time:

mysql -e "SELECT @@global.time_zone;"

Output example:

[root@server.nixcp.com:~]mysql -e "SELECT @@global.time_zone;" -u root -p
+--------------------+
| @@global.time_zone |
+--------------------+
| SYSTEM |
+--------------------+

If you see ‘SYSTEM’, then your MySQL server time uses the system date and time.

How can I change the MySQL server time from the Linux terminal?

You just need to add one single directive into your my.cnf configuration file:

"default-time-zone"

Edit your /etc/my.cnf file

nano -w /etc/my.cnf

Add default-time-zone to set your new MySQL time zone

default-time-zone = '-03:00'

Restart MySQL to apply changes

For plain CentOS servers:

service mysqld restart

For cPanel servers:

service mysql restart

Now let’s verify again your MySQL server time using:

mysql -e "SELECT @@global.time_zone;"

Output example:

[root@server.nixcp.com:~]mysql -e "SELECT @@global.time_zone;" -u root -p
+--------------------+
| @@global.time_zone |
+--------------------+
| -03:00 |
+--------------------+

Conclusion

As you see, your custom MYSQL server time now uses the custom time zone you specified at /etc/my.cnf file. Be aware that as we said before, this custom MySQL server time affects all MySQL databases and can not be set for only one database. Please let me know if you have any questions about this tutorial.