MySQL之log_timestamps

评论
563 views

今天有同学在群里问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一下。

继续阅读