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:
[[email protected]:~]date Tue Dec 6 08:07:50 EST 2016 [[email protected]:~]
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:
[[email protected]:~]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:
[[email protected]:~]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.