MYSQL8.0.19慢日志时间与系统时间相差8小时问题的解决

MYSQL8.0.19慢日志时间与系统时间相差8小时问题的解决

一、msyql5.7开启慢日志后发现日志的时间与系统时间相差8小时

[root@zabbix_server mysql]# vim zabbix_server-slow.log 17  LIMIT 0, 1000;18 # Time: 2020-04-07T09:16:18.175882Z19 # User@Host: root[root] @ zabbix_server [172.28.18.75]  Id: 581570320 # Query_time: 0.105811  Lock_time: 0.000000 Rows_sent: 0  Rows_examined: 021 use slow_query_log;22 SET timestamp=1586250978;23 CREATE TABLE `global_query_review` (1 2 # Time: 2020-04-07T06:34:40.688996Z3 # User@Host: root[root] @ zabbix_server [172.28.18.75]  Id: 58157034 # Query_time: 0.293296  Lock_time: 0.000120 Rows_sent: 1  Rows_examined: 05 SET timestamp=1586241280;6 select count(*) from history_log;7 # Time: 2020-04-07T06:38:39.484322Z8 # User@Host: root[root] @ zabbix_server [172.28.18.75]  Id: 58157039 # Query_time: 91.377653  Lock_time: 0.000102 Rows_sent: 1  Rows_examined: 1526777610 SET timestamp=1586241519;11 select count(*) from trends;

Time: 2020-04-07T06:34:40.688996Z，实际时间应该是14：34:40
这是由于log_timestamps这个参数设置造成的，查询当前设置

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

UTC是世界统一时间，而我现在的系统为北京时间是东八区，比UTC早了8个小时，所以这里设置为SYSTEM

mysql> set global log_timestamps=SYSTEM;
Query OK, 0 rows affected (0.00 sec)mysql> show variables like '%log_time%';
+----------------+--------+
| Variable_name  | Value  |
+----------------+--------+
| log_timestamps | SYSTEM |
+----------------+--------+
1 row in set (0.01 sec)

在mysql里执行一条测试慢日志语句

mysql> select sleep(5);
+----------+
| sleep(5) |
+----------+
|        0 |
+----------+
1 row in set (4.99 sec)mysql>

再次查看慢日志

 51  LIMIT 0, 1000;52 # Time: 2020-04-08T14:25:16.200913+08:0053 # User@Host: root[root] @ zabbix_server [172.28.18.75]  Id: 587020454 # Query_time: 5.000269  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 055 use slow_query_log;56 SET timestamp=1586327116;57 select sleep(5);
[root@zabbix_server mysql]# 

时间跟系统时间一样了。