Home / server / Questions / 759532
Accepted
aaaaa says reinstate Monica
Asked: 2016-02-25 10:56:16 +0800 CST

MySQL logs different time than NOW()

  • 772

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
mysql

2 Answers

  1. 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.

    • 9

  2. If you want to keep the change persistant after mysql restar or a reboot put this into your my.cnf file:

    log_timestamps   = 'SYSTEM'
    • 2