问题:
新安装的MySQL 5.7.27,发现error log、general log日志里面日志时间的时区不对。
error log
2019-09-11T12:53:23.988658Z 0 [Warning] CA certificate ca.pem is self signed.
2019-09-11T12:53:24.007777Z 0 [Note] Server hostname (bind-address): '*'; port: 3306
2019-09-11T12:53:24.008117Z 0 [Note] IPv6 is available.
2019-09-11T12:53:24.008148Z 0 [Note] - '::' resolves to '::';
2019-09-11T12:53:24.008226Z 0 [Note] Server socket created on IP: '::'.
2019-09-11T12:53:24.088216Z 0 [Note] /usr/local/mysql/libexec/mysqld: ready for connections.
Version: '5.7.27-debug-log' socket: '/usr/local/mysql/tmp/mysql.sock' port: 3306 Source distribution
general log
2019-09-11T12:56:32.547294Z 2 Query show global variables like 'log_timestamps'
2019-09-11T13:01:04.907420Z 2 Query select DATABASE(), USER() limit 1
2019-09-11T13:01:04.908582Z 2 Query select @@character_set_client, @@character_set_connection, @@character_set_server, @@character_set_database limit 1
2019-09-11T13:01:04.908974Z 2 Statistics
原因:
mysql 5.7.2新增了参数 log_timestamps,用于控制error log、general log、slow log日志记录使用的时区,只影响写入文件的时区,不影响写入表中记录的时区。默认值是UTC,支持设置为SYSTEM使用系统的时区。
• log_timestamps
Introduced | 5.7.2 | |
Command-Line Format | | |
System Variable | Name | |
Variable Scope | Global | |
DynamicYes Variable | ||
Permitted Values | Type | enumeration |
Default | UTC | |
Valid Values | UTC | |
SYSTEM |
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). 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)执行 set global log_timestamps=SYSTEM
执行之后,新的日志的时区就会变成系统默认的时区。
2)在my.cnf中添加 log_timestamps=SYSTEM