MySQL logs different time than NOW()

loggingMySQLtime

MySQL server logs entries with different time than system or mysql itself:

mysql> select @@global.time_zone;
+--------------------+
| @@global.time_zone |
+--------------------+
| SYSTEM             |
+--------------------+
mysql> select NOW();
+---------------------+
| NOW()               |
+---------------------+
| 2016-02-24 10:47:30 |
+---------------------+

# /var/log/mysql/mysql_queries.log
2016-02-24T18:47:11.191126Z    2 Connect        root@localhost on  using Socket
2016-02-24T18:47:11.191421Z    2 Query  select @@version_comment limit 1
2016-02-24T18:47:16.769493Z    2 Query  select @@global.time_zone
2016-02-24T18:47:30.503214Z    2 Query  select NOW()

Timestamp seems synced with log:

mysql> show variables;
timestamp               | 1456339753.062182 # 18:47
log_timestamps          | UTC

Best Answer

Solution was super simple: look through the variables and try to locate relevant:

log_timestamps                   | UTC

So I changed it to 'SYSTEM':

mysql> SET GLOBAL  log_timestamps  = 'SYSTEM';

# /var/log/mysql/mysql_queries.log
2016-02-24T18:54:23.837289Z    2 Query  SET GLOBAL  log_timestamps  = 'SYSTEM'
2016-02-24T10:54:25.949232-08:00    2 Query     show variables

Time in log switched from 18:54 to 10:54 as should.

From MySQL docs:

  • log_timestamps

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.

Related Topic