【MySQL】参数log_timestamps
环境:mysql 5.7.19
问题描述:日志中的时间戳与系统时间不匹配(相差8个小时)。
其实这个不算个事儿,但是,在查找、定位问题时,还是有些小麻烦的。
MySQL日志中,默认打印的时间是UTC(协调世界时,又称世界统一时间、世界标准时间、国际协调时间。),但是这个时间与咱们服务器时间相差8小时。
log_timestamps 参数就是解决这个问题的,官方文档针对这个参数有如下描述:
roperty | Value |
---|---|
Command-Line Format | --log-timestamps=# |
Introduced | 5.7.2 |
System Variable | log_timestamps |
Scope | Global |
Dynamic | Yes |
Type | enumeration |
Default Value | UTC |
Valid Values |
|
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.
那么,解决这个问题就变得容易了
1:临时修改
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
mysql> SHOW GLOBAL VARIABLES LIKE 'log_timestamps'; +----------------+-------+ | Variable_name | Value | +----------------+-------+ | log_timestamps | UTC | +----------------+-------+ 1 row in set (0.00 sec) mysql> mysql> SET GLOBAL log_timestamps = SYSTEM; Query OK, 0 rows affected (0.00 sec) mysql> SHOW GLOBAL VARIABLES LIKE 'log_timestamps'; +----------------+--------+ | Variable_name | Value | +----------------+--------+ | log_timestamps | SYSTEM | +----------------+--------+ 1 row in set (0.00 sec) mysql> |
2:修改参数
1 2 3 |
[root@localhost ~ ]# vi /etc/my.cnf ## 加上如下参数 log_timestamps = SYSTEM |