mysql时区查看与设置方法

一.查看数据库时区

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

system_time_zone的值根据当前系统的不同会有所不同，此处测试时系统时间为CEST时间，所以值为CEST
查看当前的操作系统的时区

## 使用date命令date +"%Z %z"　　　　//查看当前操作系统的时区date -R

CEST为欧洲中部夏令时间，英文全名: Central European Summer Time

欧洲中部夏令时间所属时区: UTC/GMT +2

2.全局参数time_zone

用来设置每个连接会话的时区，默认为system时，使用全局参数system_time_zone的值。我们需要修改的就是time_zone的值

SYSTEM 表示time_zone默认使用system_time_zone的时区，此处即CEST

个人思路
因为my.cnf中默认没有设置default-time_zone，所以time_zone默认为system，即system_time_zone的值，
而system_time_zone的值为mysql启动时的操作系统的时区，所以个人认为可以通过提前设置操作系统的时区来决定mysql的时区

二.设置数据库时区

1.通过mysql命令行模式下动态修改，这种修改只在当前的mysql启动状态生效，如果mysql重启，则恢复到my.ini的设置状态

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

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

windows系统中配置文件为my.ini。linux系统中配置文件为/etc/my.cnf

在[mysqld]的下面添加或者修改如下内容

default-time_zone = '+8:00'

linux系统中服务器重启命令如下

systemctl restart mysqld.service

# 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 = 2Mdatadir=/var/lib/mysqlsocket=/var/lib/mysql/mysql.sockdefault-time_zone = '+9:00'# Disabling symbolic-links is recommended to prevent assorted security riskssymbolic-links=0# Recommended in standard MySQL setupsql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES[mysqld_safe]log-error=/var/log/mysqld.logpid-file=/var/run/mysqld/mysqld.pid

一.查看数据库时区

2.全局参数time_zone

二.设置数据库时区

更多相关文章

随机推荐