MySQL 5.7日志时间与本地时间不一致的问题解决

郑全

启动MYSQL数据库，发现日志时间比系统时间慢8个小时，

2022-12-08T12:54:07.137055Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2022-12-08T12:54:07.137159Z 0 [Note] --secure-file-priv is set to NULL. Operations related to importing and exporting data are disabled
2022-12-08T12:54:07.137186Z 0 [Note] /usr/local/mysql/bin/mysqld (mysqld 5.7.40-enterprise-commercial-advanced) starting as process 3409 ...
2022-12-08T12:54:07.141225Z 0 [Note] InnoDB: PUNCH HOLE support available
2022-12-08T12:54:07.141267Z 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2022-12-08T12:54:07.141275Z 0 [Note] InnoDB: Uses event mutexes
2022-12-08T12:54:07.141278Z 0 [Note] InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier
2022-12-08T12:54:07.141281Z 0 [Note] InnoDB: Compressed tables use zlib 1.2.12
2022-12-08T12:54:07.141285Z 0 [Note] InnoDB: Using Linux native AIO
2022-12-08T12:54:07.141472Z 0 [Note] InnoDB: Number of pools: 1
2022-12-08T12:54:07.141678Z 0 [Note] InnoDB: Using CPU crc32 instructions
2022-12-08T12:54:07.143601Z 0 [Note] InnoDB: Initializing buffer pool, total size = 128M, instances = 1, chunk size = 128M
2022-12-08T12:54:07.151469Z 0 [Note] InnoDB: Completed initialization of buffer pool
2022-12-08T12:54:07.154372Z 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority().
2022-12-08T12:54:07.166031Z 0 [Note] InnoDB: Highest supported file format is Barracuda.
2022-12-08T12:54:07.172266Z 0 [Note] InnoDB: Creating shared tablespace for temporary tables
2022-12-08T12:54:07.172411Z 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
2022-12-08T12:54:07.193180Z 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB.
2022-12-08T12:54:07.193858Z 0 [Note] InnoDB: 96 redo rollback segment(s) found. 96 redo rollback segment(s) are active.
2022-12-08T12:54:07.193882Z 0 [Note] InnoDB: 32 non-redo rollback segment(s) are active.
2022-12-08T12:54:07.194794Z 0 [Note] InnoDB: Waiting for purge to start
2022-12-08T12:54:07.245745Z 0 [Note] InnoDB: 5.7.40 started; log sequence number 2754964
2022-12-08T12:54:07.246793Z 0 [Note] InnoDB: Loading buffer pool(s) from /var/lib/mysql/ib_buffer_pool
2022-12-08T12:54:07.246952Z 0 [Note] Plugin 'FEDERATED' is disabled.
2022-12-08T12:54:07.248130Z 0 [Note] InnoDB: Buffer pool(s) load completed at 221208 20:54:07
2022-12-08T12:54:07.251241Z 0 [Note] Found ca.pem, server-cert.pem and server-key.pem in data directory. Trying to enable SSL support using them.
2022-12-08T12:54:07.251276Z 0 [Note] Skipping generation of SSL certificates as certificate files are present in data directory.
2022-12-08T12:54:07.251282Z 0 [Warning] A deprecated TLS version TLSv1 is enabled. Please use TLSv1.2 or higher.
2022-12-08T12:54:07.251285Z 0 [Warning] A deprecated TLS version TLSv1.1 is enabled. Please use TLSv1.2 or higher.
2022-12-08T12:54:07.251881Z 0 [Warning] CA certificate ca.pem is self signed.
2022-12-08T12:54:07.251940Z 0 [Note] Skipping generation of RSA key pair as key files are present in data directory.
2022-12-08T12:54:07.252212Z 0 [Note] Server hostname (bind-address): '*'; port: 3306
2022-12-08T12:54:07.253014Z 0 [Note] IPv6 is available.
2022-12-08T12:54:07.253042Z 0 [Note]   - '::' resolves to '::';
2022-12-08T12:54:07.253069Z 0 [Note] Server socket created on IP: '::'.
2022-12-08T12:54:07.261255Z 0 [Note] Event Scheduler: Loaded 0 events
2022-12-08T12:54:07.261567Z 0 [Note] /usr/local/mysql/bin/mysqld: ready for connections.
Version: '5.7.40-enterprise-commercial-advanced'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  MySQL Enterprise Server - Advanced Edition (Commercial)


原因是 在MySQL 5.7.2 新增了 log_timestamps 这个参数，该参数主要是控制 error log、genera log，等等记录日志的显示时间参数。 在 5.7.2 之后改参数为默认 UTC 这样会导致日志中记录的时间比中国这边的慢，导致查看日志不方便。修改为 SYSTEM 就能解决问题。

2个办法：

1：修改【 mysqld】

log_timestamps = SYSTEM

重启

2：登入命令行界面：

SHOW GLOBAL VARIABLES LIKE 'log_timestamps';

Variable_name | Value  |

| log_timestamps | UTC   

SET GLOBAL log_timestamps = SYSTEM;

sHOW GLOBAL VARIABLES LIKE 'log_timestamps';

log_timestamps | SYSTEM

不过这个修改了，重启又没有了。

我们这里使用改参数，重启后，正常了。

2022-12-08T21:09:22.736986+08:00 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).

2022-12-08T21:09:22.737076+08:00 0 [Note] --secure-file-priv is set to NULL. Operations related to importing and exporting data are disabled

2022-12-08T21:09:22.737104+08:00 0 [Note] /usr/local/mysql/bin/mysqld (mysqld 5.7.40-enterprise-commercial-advanced) starting as process 3935 ...

2022-12-08T21:09:22.741130+08:00 0 [Note] InnoDB: PUNCH HOLE support available

2022-12-08T21:09:22.741178+08:00 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins

2022-12-08T21:09:22.741188+08:00 0 [Note] InnoDB: Uses event mutexes

2022-12-08T21:09:22.741194+08:00 0 [Note] InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier

2022-12-08T21:09:22.741197+08:00 0 [Note] InnoDB: Compressed tables use zlib 1.2.12

2022-12-08T21:09:22.741202+08:00 0 [Note] InnoDB: Using Linux native AIO

2022-12-08T21:09:22.741405+08:00 0 [Note] InnoDB: Number of pools: 1

2022-12-08T21:09:22.741541+08:00 0 [Note] InnoDB: Using CPU crc32 instructions

2022-12-08T21:09:22.743035+08:00 0 [Note] InnoDB: Initializing buffer pool, total size = 128M, instances = 1, chunk size = 128M

2022-12-08T21:09:22.748589+08:00 0 [Note] InnoDB: Completed initialization of buffer pool

2022-12-08T21:09:22.750639+08:00 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority().

2022-12-08T21:09:22.764440+08:00 0 [Note] InnoDB: Highest supported file format is Barracuda.

2022-12-08T21:09:22.770536+08:00 0 [Note] InnoDB: Creating shared tablespace for temporary tables

2022-12-08T21:09:22.770679+08:00 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...

2022-12-08T21:09:22.789250+08:00 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB.

2022-12-08T21:09:22.789834+08:00 0 [Note] InnoDB: 96 redo rollback segment(s) found. 96 redo rollback segment(s) are active.

2022-12-08T21:09:22.789859+08:00 0 [Note] InnoDB: 32 non-redo rollback segment(s) are active.

2022-12-08T21:09:22.790678+08:00 0 [Note] InnoDB: Waiting for purge to start

2022-12-08T21:09:22.841417+08:00 0 [Note] InnoDB: 5.7.40 started; log sequence number 2755020

2022-12-08T21:09:22.841979+08:00 0 [Note] InnoDB: Loading buffer pool(s) from /var/lib/mysql/ib_buffer_pool

2022-12-08T21:09:22.842276+08:00 0 [Note] Plugin 'FEDERATED' is disabled.

2022-12-08T21:09:22.843349+08:00 0 [Note] InnoDB: Buffer pool(s) load completed at 221208 21:09:22

2022-12-08T21:09:22.846935+08:00 0 [Note] Found ca.pem, server-cert.pem and server-key.pem in data directory. Trying to enable SSL support using them.

2022-12-08T21:09:22.846965+08:00 0 [Note] Skipping generation of SSL certificates as certificate files are present in data directory.

2022-12-08T21:09:22.846972+08:00 0 [Warning] A deprecated TLS version TLSv1 is enabled. Please use TLSv1.2 or higher.

2022-12-08T21:09:22.846975+08:00 0 [Warning] A deprecated TLS version TLSv1.1 is enabled. Please use TLSv1.2 or higher.

2022-12-08T21:09:22.847520+08:00 0 [Warning] CA certificate ca.pem is self signed.

2022-12-08T21:09:22.847598+08:00 0 [Note] Skipping generation of RSA key pair as key files are present in data directory.

2022-12-08T21:09:22.847836+08:00 0 [Note] Server hostname (bind-address): '*'; port: 3306

2022-12-08T21:09:22.847898+08:00 0 [Note] IPv6 is available.

2022-12-08T21:09:22.847910+08:00 0 [Note]   - '::' resolves to '::';

2022-12-08T21:09:22.847963+08:00 0 [Note] Server socket created on IP: '::'.

2022-12-08T21:09:22.854435+08:00 0 [Note] Event Scheduler: Loaded 0 events

2022-12-08T21:09:22.855589+08:00 0 [Note] /usr/local/mysql/bin/mysqld: ready for connections.

Version: '5.7.40-enterprise-commercial-advanced'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  MySQL Enterprise Server - Advanced Edition (Commercial)