How to change default time zone in MySQL?

Is it possible to change the time zone in MySQL?

Yes we can change the default time zone in MySQL via command-line. We can’t change the time zone for a single DB, it’s a global change. To change this feature, you must have the root privilege to the server. We have to edit the MySQL configuration file to change the time zone of MySQL server from its default.

By default the MySQL server time is same as the SYSTEM time. We can check this by using the following commands:

To check the server time

>> date

Example

[root@vps ~]# date
Mon Feb 23 07:04:32 EST 2015

To check MySQL server time

>> mysql -e “SELECT NOW();”

[root@vps ~]# mysql -e "SELECT NOW();"
+---------------------+
| NOW()               |
+---------------------+
| 2015-02-23 07:04:30 |
+---------------------+

mysql-time-zone

How to change the MySQL server time from command line?

We can change this by adding the following directives to the MySQL configuration file. Please do follow the steps pasted below:

>> Log into server as root user.
>> To check MySQL’s current time zone settings, execute the following command:

mysql -e “SELECT @@global.time_zone;”

Example

[root@vps ~]# mysql -e "SELECT @@global.time_zone;"
+--------------------+
| @@global.time_zone |
+--------------------+
| SYSTEM             |
+--------------------+

It means MySQL is using server SYSTEM default time. To change this, you need to add the following directive into the MySQL conf file.

>> “default-time-zone”

1, Open MySQL conf by using your favorite editor;

vi /etc/my.cnf

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

2, Restart MySQL

service mysql restart

That’s it!!! Now check the current MySQL server time by using the command mysql -e “SELECT NOW();”.

Try it and let me know if you have any questions on it.

Also read

1, Impossible to create database’s user::MySQL governor
2, Performance tuning script for MySQL – Command-line Mysql Tuner
3, Simple PHP script to check the MySQL database connection from web-browser

Post navigation

Arunlal Ashok

Cloud Infrastructure / DevOps Engineer. I know her (Linux) since many years. Linux lover. Like to play on Linux console. I started this blog to share and discuss Linux thoughts.

Always happy for an open discussion! Write to arun (@) crybit (dot) com. Check about me for more details. About this blog and our strong members, check The team CryBit.com

4 thoughts on “How to change default time zone in MySQL?

  1. Thank you so much! I had to open thousand tabs of my browser until I get here! I am using Windows and changing the file /mysql/my.ini after the section [mysqld] putting this one line code ‘default-time-zone=”+00:00″‘ This saved my life!

  2. It’s not working for me in 5.7 windows environment.

    default-time-zone=’-06:00′ or
    default-time-zone=-06:00 or

    default-time-zone=”-06:00″

Leave a Reply

Your email address will not be published. Required fields are marked *