海阔中文网

235 views
7月 3 2021

修改mysql 5.7.27日志时区从UTC到SYSTEM(数据库时间 和 日志记录时间不一样解决办法)

问题:mysql的日志显示时间少8个小时,明显是时区问题。

先查看数据库时间是对的:

mysql> select now();
+---------------------+
| now() |
+---------------------+
| 2021-07-03 11:03:15 |
+---------------------+
1 row in set (0.01 sec)

再次查看默认的系统时区system_time_zone是CST,time_zone是跟随SYSTEM,所以也是CST,mysql数据库采用的时区没有问题,都是中国时区的时间。

mysql> show variables like "%time_zone%";
+------------------+--------+
| Variable_name | Value |
+------------------+--------+
| system_time_zone | CST |
| time_zone | SYSTEM |
+------------------+--------+
2 rows in set (0.01 sec)

但是mysql的日志时间是不对的,比正常时间晚了8个小时,查看日志采用的时区log_timestamps,发现是UTC

mysql> SHOW global variables like 'log_timestamps';
+----------------+-------+
| Variable_name | Value |
+----------------+-------+
| log_timestamps | UTC |
+----------------+-------+
1 row in set (0.01 sec)

MySQL 5.7.2 之后日志文件里面的时间戳从默认的本地系统时区改为了UTC格式。MySQL 5.7.2多了一个参数log_timestamps ,这个参数主要是控制错误日志、慢查询日志等日志中的显示时间。但它不会影响查询日志和慢日志写到表 (mysql.general_log, mysql.slow_log) 中的显示时间。在查询记录的时候,可以使用 CONVERT_TZ() 函数,或者设置会话级别的系统变量 time_zone 来转换成所需要的时区。官方资料详细介绍如下所示:

This variable controls the time zone of timestamps in messages written to the error log, and in general query log and slow query log messages written to files. It does not affect the time zone of general query log and slow query log messages written to tables(mysql.general_log, mysql.slow_log). Rows retrieved from those tables can be converted from the local system time zone to any desired time zone with CONVERT_TZ() or by setting the session time_zone system variable.

Permitted log_timestamps values are UTC (the default) and SYSTEM (local system time zone).

Timestamps are written using ISO 8601 / RFC 3339 format: YYYY-MM-DDThh:mm:ss.uuuuuu plus a tail value of Z signifying Zulu time (UTC) or ±hh:mm (an offset from UTC).

This variable was added in MySQL 5.7.2. Before 5.7.2, timestamps in log messages were written using the local system time zone by default, not UTC. If you want the previous log message time zone default, set log_timestamps=SYSTEM.

此参数是全局的,可以动态修改,修改参数log_timestamps的值非常简单,如下所示,不过最好在参数文件my.cnf设置该参数值,以防MySQL服务重启失效。

改为和SYSTEM一样,也就是CST

mysql> set global log_timestamps=SYSTEM;
Query OK, 0 rows affected (0.00 sec)

再次查看日志的时区,变为SYSTEM了

mysql> SHOW global variables like 'log_timestamps';
+----------------+--------+
| Variable_name | Value |
+----------------+--------+
| log_timestamps | SYSTEM |
+----------------+--------+
1 row in set (0.00 sec)

回去再看mysql的query log的时间记录,是正确的:

2021-07-03T11:06:42.587522+08:00

以上是即时生效,如果永久生效,必须改配置文件:

[mysqld]
log_timestamps = SYSTEM

Written by 海阔中文网

海阔中文网真棒!


京ICP备11047313号-20