MySQL error log 中记录的时间与系统时间不一致的原因与解决办法

已归录

这两天打长对 mysql 的 error log 进行监控时，发现日志文件中记录的时间与操作系统时间不一致。记录如下：

数据库的时区配置是正确的：

3306 [(none)]>show variables like '%time%zone%';
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| system_time_zone | CST    |
| time_zone        | +08:00 |
+------------------+--------+
2 rows in set (0.00 sec)

在数据库中查询时间也是正确的：

3306 [(none)]>select now();
+---------------------+
| now()               |
+---------------------+
| 2020-10-28 17:50:11 |
+---------------------+
1 row in set (0.00 sec)

3306 [(none)]>system date;
Wed Oct 28 17:50:16 CST 2020

造成该问题的原因是 log_timestamps 参数被设置为了 UTC：

3306 [(none)]>show variables like 'log_timestamps';
+----------------+-------+
| Variable_name  | Value |
+----------------+-------+
| log_timestamps | UTC   |
+----------------+-------+
1 row in set (0.00 sec)

下面是该参数的解释：

This variable controls the timestamp time zone of error log messages, and of 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).
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.