How to view and set the mysql time zone

1. Check the database time zone

show variables like'%time_zone';
mysql> show variables like "%time_zone";
+------------------+--------+
| Variable_name | Value |
+------------------+--------+
| system_time_zone | CEST |
| time_zone | SYSTEM |
+------------------+--------+

1. Global parameter system_time_zone

System time zone. When MySQL starts, it checks the current system time zone and sets the value of the global parameter system_time_zone according to the system time zone.

The value of system_time_zone will vary depending on the current system. In this test, the system time is CEST, so the value is CEST
Check the current operating system time zone

## Use the date command date +"%Z %z" // View the time zone of the current operating system date -R

[vagrant@localhost ~]$ date -R
Wed, 17 Jun 2020 10:48:14 +0200
[vagrant@localhost ~]$ date +"%Z %z"
CEST +0200

CEST means that when MySQL is started, the system time is CEST

CEST is Central European Summer Time, English full name: Central European Summer Time

Central European Summer Time is UTC/GMT +2

2. Global parameter time_zone

Used to set the time zone for each connection session. When the default is system, the value of the global parameter system_time_zone is used. What we need to modify is the value of time_zone

SYSTEM means that time_zone uses the system_time_zone time zone by default, which is CEST here.

My personal idea is that since default-time_zone is not set by default in my.cnf, time_zone defaults to system, which is the value of system_time_zone.
The value of system_time_zone is the time zone of the operating system when MySQL is started, so I personally think that the time zone of MySQL can be determined by setting the time zone of the operating system in advance.

2. Set the database time zone

1. Dynamically modify in MySQL command line mode. This modification is only effective in the current MySQL startup state. If MySQL is restarted, it will be restored to the setting state of my.ini.

set global time_zone = '+8:00';
FLUSH PRIVILEGES;

Check the mysql time zone settings as follows (you need to exit mysql and then log in again, otherwise the result of time_zone may not change and will still be SYSTEM)

mysql> show variables like "%time_zone";
+------------------+--------+
| Variable_name | Value |
+------------------+--------+
| system_time_zone | CEST |
| time_zone | +08:00 |
+------------------+--------+

2. Change the time zone by modifying the configuration file. This change is permanent and will remain effective even if MySQL is restarted.

The configuration file in Windows system is my.ini. The configuration file in the Linux system is /etc/my.cnf

Add or modify the following content under [mysqld]

default-time_zone = '+8:00'

After modifying the configuration file, you need to restart the MySQL server.

The server restart command in the Linux system is as follows

systemctl restart mysqld.service

The modified content of my.cnf is as follows

# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html

[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
default-time_zone = '+9:00'

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

# Recommended in standard MySQL setup
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

This is the end of this article about how to view and set the MySQL time zone. For more information about how to view and set the MySQL time zone, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope you will support 123WORDPRESS.COM in the future!