澳门在线威尼斯官方 > 电脑数据库 > 威尼斯澳门在线log时间显示问题

原标题:威尼斯澳门在线log时间显示问题

浏览次数:51 时间:2019-12-01

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.

 

 

日志显示时间

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

root@localhost [(none)]>SHOW GLOBAL VARIABLES LIKE 'log_timestamps';
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:3
Current database: *** NONE ***
+----------------+-------+
| Variable_name| Value |
+----------------+-------+
| log_timestamps | UTC |
+----------------+-------+
1 row in set (0.01 sec)
root@localhost [(none)]>set global log_timestamps=SYSTEM;
Query OK, 0 rows affected (0.00 sec)
root@localhost [(none)]>SHOW GLOBAL VARIABLES LIKE 'log_timestamps';
+----------------+--------+
| Variable_name| Value|
+----------------+--------+
| log_timestamps | SYSTEM |
+----------------+--------+
1 row in set (0.00 sec) 

 

最近有两三套环境升级到了5.7.16,发现mysql.err中的时间好像有些问题,经查是mysql 5.7后的变更,如下:

 

root@localhost [(none)]>select now();
+---------------------+
| now() |
+---------------------+
| 2017-01-01 14:52:05 |
+---------------------+
1 row in set (0.00 sec) 

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

[root@bogon data]# /etc/init.d/mysqld start
Starting MySQL.... SUCCESS! 
You have new mail in /var/spool/mail/root
[root@bogon data]# tailf error.log 
2017-01-01T06:49:38.202954Z 0 [Note] InnoDB: Loading buffer pool(s) from /data/mysql/mysql3306/data/ib_buffer_pool
2017-01-01T06:49:38.440257Z 0 [Warning] Failed to set up SSL because of the following SSL library error: SSL context is not usable without certificate and private key
2017-01-01T06:49:38.440314Z 0 [Note] Server hostname (bind-address): '*'; port: 3306
2017-01-01T06:49:38.440426Z 0 [Note] IPv6 is available.
2017-01-01T06:49:38.440443Z 0 [Note] - '::' resolves to '::';
2017-01-01T06:49:38.440471Z 0 [Note] Server socket created on IP: '::'.
2017-01-01T06:49:38.627893Z 0 [Note] InnoDB: Buffer pool(s) load completed at 161116 14:49:38
2017-01-01T06:49:38.871533Z 0 [Note] Event Scheduler: Loaded 0 events
2017-01-01T06:49:38.871938Z 0 [Note] /usr/local/mysql/bin/mysqld: ready for connections.
Version: '5.7.14-log'socket: '/tmp/mysql3306.sock'port: 3306MySQL Community Server (GPL)

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 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.

这个参数是5.7.2引进来的,主要控制记录日志的时间戳(得加到默认配置中了),不影响general log和slow log写表。

问题原因:

 

它的默认设置是UTC,如果你想改变默认设置,set log_timestamps=SYSTEM就可以了。注意这个变量的作用域是global

本文由澳门在线威尼斯官方发布于电脑数据库,转载请注明出处:威尼斯澳门在线log时间显示问题

关键词:

上一篇:没有了

下一篇:中NULL和空值的区分