问题描述:mysql中发现告警日志时间与系统时间相差8小时,如下所示:
数据库:mysql 8.0.27
1、异常重现
告警日志如下:
2023-06-11T12:54:57.227141Z 8 [System] [MY-010597] [Repl] 'CHANGE MASTER TO FOR CHANNEL '' executed'. Previous state master_host='', master_port= 3306, master_log_file='', master_log_pos= 4, master_bind=''. New state master_host='192.168.133.112', master_port= 3306, master_log_file='slave_log.000003', master_log_pos= 156, master_bind=''.
2023-06-11T12:56:16.870953Z 9 [Warning] [MY-010897] [Repl] Storing MySQL user name or password information in the master info repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START SLAVE; see the 'START SLAVE Syntax' in the MySQL Manual for more information.
2023-06-11T12:56:16.879603Z 9 [System] [MY-010562] [Repl] Slave I/O thread for channel '': connected to master 'repluser@192.168.133.112:3306',replication started in log 'slave_log.000003' at position 156
2023-06-11T13:39:36.109121Z 11 [ERROR] [MY-010584] [Repl] Slave SQL for channel '': Worker 1 failed executing transaction 'ANONYMOUS' at master log slave_log.000003, end_log_pos 430; Error executing row event: 'Cannot execute statement: impossible to write to binary log since statement is in row format and BINLOG_FORMAT = STATEMENT.', Error_code: MY-001666
2023-06-11T13:39:36.109365Z 10 [Warning] [MY-010584] [Repl] Slave SQL for channel '': ... The slave coordinator and worker threads are stopped, possibly leaving data in inconsistent state. A restart should restore consistency automatically, although using non-transactional storage for data or info tables or DDL queries could lead to problems. In such cases you have to examine your data (see documentation for details). Error_code: MY-001756
系统时间:
[root@leo-mysql-master ~]# date
Sun Jun 11 21:49:58 CST 2023
2、解决过程
2.1、修改配置文件
--查参数log_timestamps取值
mysql> show variables like '%log_time%';
+----------------+-------+
| Variable_name  | Value |
+----------------+-------+
| log_timestamps | UTC   |
+----------------+-------+
1 row in set (0.00 sec)

说明:UTC为世界统一时间,而当前系统为北京时间东八区,相比UTC早8小时.
--修改log_timestamps参数为SYSTEM
[root@leo-mysql-master etc]# vi my.cnf
添加如下配置:
log_timestamps=SYSTEM

2.2、重启mysql服务
--此后重启mysql服务.
[root@leo-mysql-master etc]# service mysql stop
Shutting down MySQL.... SUCCESS! 
[root@leo-mysql-master etc]# service mysql start
Starting MySQL. SUCCESS! 

mysql> show variables like '%log_time%';
+----------------+--------+
| Variable_name  | Value  |
+----------------+--------+
| log_timestamps | SYSTEM |
+----------------+--------+
1 row in set (0.00 sec)

3、时间验证
--以下为最新告警日志.
2023-06-11T14:00:54.788981Z 0 [System] [MY-013172] [Server] Received SHUTDOWN from user <via user signal>. Shutting down mysqld (Version: 8.0.27).
2023-06-11T14:00:56.808823Z 0 [Warning] [MY-010909] [Server] /opt/mysql/bin/mysqld: Forcing close of thread 8  user: 'root'.
2023-06-11T14:00:58.188192Z 0 [System] [MY-010910] [Server] /opt/mysql/bin/mysqld: Shutdown complete (mysqld 8.0.27)  MySQL Community Server - GPL.
2023-06-11T22:01:03.922705+08:00 0 [System] [MY-010116] [Server] /opt/mysql/bin/mysqld (mysqld 8.0.27) starting as process 6620
2023-06-11T22:01:03.932725+08:00 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2023-06-11T22:01:04.075911+08:00 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2023-06-11T22:01:04.280674+08:00 0 [Warning] [MY-013746] [Server] A deprecated TLS version TLSv1 is enabled for channel mysql_main
2023-06-11T22:01:04.280733+08:00 0 [Warning] [MY-013746] [Server] A deprecated TLS version TLSv1.1 is enabled for channel mysql_main

说明:告警日志时间与系统保持一致.

参考文档:https://www.cnblogs.com/connected/p/14083282.html