MySQL之log_timestamps

今天有同学在群里问MySQL的error log里记录的时间怎么和当前系统时间不一致,日志里的时间要比当前晚了好些。我以前也遇到过这类问题,是在crontab这个地方遇到的,一直也没搞明白怎么回事,最后重装系统,重新ntp同步后,发现时间又正常了。最初查询google还以为是rsyslog程序的问题。

后来经群里另一个同学提示,发现MySQL 5.7.2以后的版本新增了一个log_timestamps参数,原来问题就出在这里。在MySQL官方文档里有下面一段话:

Error Log Message Format

As of MySQL 5.7.2, the log_timestamps system variable controls the timestamp time zone of messages written to the error log (as well as to general query log and slow query log files). Permitted values are UTC (the default) and SYSTEM (local system time zone). Before MySQL 5.7.2, messages use the local system time zone.

在MySQL 5.7.2版本以后用log_timestamps参数来控制写入错误日志消息的时间戳的时间区(以及通用查询日志和慢查询日志文件),时区参数分为UTC(系统默认)和SYSTEM(本地系统时区)。

mysql  Ver 14.14 Distrib 5.7.13, for linux-glibc2.5 (x86_64) using  EditLine wrapper

Connection id:          117
Current database:
Current user:           root@localhost
SSL:                    Not in use
Current pager:          stdout
Using outfile:          ''
Using delimiter:        ;
Server version:         5.7.13-log MySQL Community Server (GPL)
Protocol version:       10
Connection:             Localhost via UNIX socket
Server characterset:    utf8
Db     characterset:    utf8
Client characterset:    utf8
Conn.  characterset:    utf8
UNIX socket:            /data/mysql3306/tmp/mysql3306.sock
Uptime:                 2 days 11 hours 49 min 28 sec

Threads: 3  Questions: 20  Slow queries: 0  Opens: 110  Flush tables: 1  Open tables: 103  Queries per second avg: 0.000
--------------

mysql> exit 
Bye
[root@node2 ~]# mysql -uroot -p12345678 -S /data/mysql3306/tmp/mysql3306.sock
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
[root@node2 ~]# mysql -uroot -p12345678 -S /data/mysql3306/tmp/mysql3306.sock
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
[root@node2 ~]# cat /data/mysql3306/data/error.log |grep "using password"
2016-09-03T02:19:26.048001Z 118 [Note] Access denied for user 'root'@'localhost' (using password: YES)
2016-09-03T02:19:29.125050Z 119 [Note] Access denied for user 'root'@'localhost' (using password: YES)
[root@node2 ~]# date
Sat Sep  3 10:21:04 CST 2016

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

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

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

mysql> exit
Bye
[root@node2 ~]# mysql -uroot -p12345678 -S /data/mysql3306/tmp/mysql3306.sock
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
[root@node2 ~]# cat /data/mysql3306/data/error.log |grep "using password"
2016-09-03T02:19:26.048001Z 118 [Note] Access denied for user 'root'@'localhost' (using password: YES)
2016-09-03T02:19:29.125050Z 119 [Note] Access denied for user 'root'@'localhost' (using password: YES)
2016-09-03T10:30:48.548075+08:00 121 [Note] Access denied for user 'root'@'localhost' (using password: YES)
[root@node2 ~]# date
Sat Sep 3 10:30:55 CST 20

测试发现,就是这个问题,改完log_timestamps参数为SYSTEM后,日志记录时间戳就和本机系统时间一致了,在这里mark一下。

继续阅读
shunzi
  • 本文由 发表于 2018-02-0812:36:39
  • 除非特殊声明,本站文章均为原创,转载请务必保留本文链接
在线音乐

在线音乐

Oracle Spool数据 BLOG

Oracle Spool数据

昨天有个需求,让我导出数据到txt文本,数据有150w 数据库是oracle,试了下spool方式导出非常快: Oracle Spool常用设置 命令 描述 set arraysize 1000; 此...
Mysqld_multi Stop Bug BLOG

Mysqld_multi Stop Bug

最近在做mysqld_multi管理多实例的时候,发现stop不了节点 这是什么原因呢? 多实例相关配置如下: mysqld = /usr/local/mysql/bin/mysqld_safe my...
匿名

发表评论

匿名网友 填写信息

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen: