MySQL 5.7 时间显示修改(log_timestamps UTC)

本文涉及的产品
云数据库 RDS SQL Server,独享型 2核4GB
云数据库 RDS MySQL Serverless,0.5-2RCU 50GB
简介:

在MySQL 5.7版本中,日志记录时间发生了变化,使用了UTC方式来记录日志时间,也就是说这是个世界统一时间,与我们常用的本地时间不协调,因此,初始化MySQL 5.7之后,需要对此做出调整,如下本文的描述。

一、错误日志的时间格式

当前环境
[robin@ydq-mnt ~]$ more /etc/redhat-release
CentOS Linux release 7.4.1708 (Core)
[robin@ydq-mnt ~]$ mysql -V
mysql Ver 14.14 Distrib 5.7.19-17, for Linux (x86_64) using 6.2

[root@ydq-mnt ~]#  date  ###系统时间
Mon Dec 18 14:23:16 CST 2018

[root@ydq-mnt ~]# more /var/log/mysqld.log   ###mysql 日志输出信息
2017-12-18T07:50:40.575098Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. 
Please use --explicit_defaults_for_timestamp server option (see do
cumentation for more details).
2017-12-18T07:50:40.576375Z 0 [Note] /usr/sbin/mysqld (mysqld 5.7.19-17-57-log) starting as process 9268 ...
2017-12-18T07:50:40.578287Z 0 [Warning] No argument was provided to --log-bin, and --log-bin-index was not used; 
so replication may break when this MySQL serve
r acts as a master and has his hostname changed!! Please use '--log-bin=ydq-mnt-bin' to avoid this problem.
2017-12-18T07:50:40.578320Z 0 [Note] WSREP: Setting wsrep_ready to false
2017-12-18T07:50:40.578330Z 0 [Note] WSREP: No pre-stored wsrep-start position found. Skipping position initialization.
2017-12-18T07:50:40.578335Z 0 [Note] WSREP: wsrep_load(): loading provider library '/usr/lib64/galera3/libgalera_smm.so'
2017-12-18T07:50:40.582014Z 0 [Note] WSREP: wsrep_load(): Galera 3.22(r8678538) by Codership Oy 
<info@codership.com> loaded successfully.
从上所示,当前的系统时间为mysql日志记录的时间不一致。

二、系统变量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.
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.
从上描述可知,这个变量是在MySQL 5.7.2中添加的。缺省值为UTC。
如果如要使用缺省时区的时间,修改该参数的值为SYSTEM

三、修改及验证

mysql> set global log_timestamps='SYSTEM';
Query OK, 0 rows affected (0.00 sec)

[root@ydq-mnt mysql]# vim /etc/percona-xtradb-cluster.conf.d/mysqld.cnf
log_timestamps=SYSTEM

[root@ydq-mnt ~]# systemctl restart mysql
2017-12-18T07:52:41.543983Z 0 [Note] Beginning of list of non-natively partitioned tables
2017-12-18T07:52:41.544128Z 0 [Note] WSREP: Member 0.0 (ydq-mnt) synced with group.
2017-12-18T07:52:41.544138Z 0 [Note] WSREP: Shifting JOINED -> SYNCED (TO: 4163)
2017-12-18T07:52:41.550738Z 8 [Note] WSREP: Synchronized with group, ready for connections
2017-12-18T07:52:41.550755Z 8 [Note] WSREP: This node is synced, setting wsrep_ready to true
2017-12-18T07:52:41.550761Z 8 [Note] WSREP: wsrep_notify_cmd is not defined, skipping notification.
2017-12-18T07:52:41.582207Z 0 [Note] End of list of non-natively partitioned tables
2017-12-18T07:52:43.178826Z 0 [Note] InnoDB: Buffer pool(s) load completed at 171218 15:52:43
2017-12-18T16:24:23.588881+08:00 0 [Note] WSREP: Received shutdown signal. Will sleep for 10 secs before initiating shutdown.
pxc_maint_mode switched to SHUTDOWN
2017-12-18T16:24:33.591354+08:00 0 [Note] WSREP: Stop replication
2017-12-18T16:24:33.591408+08:00 0 [Note] WSREP: Closing send monitor...
2017-12-18T16:24:33.591429+08:00 0 [Note] WSREP: Closed send monitor.
2017-12-18T16:24:33.591453+08:00 0 [Note] WSREP: gcomm: terminating thread
2017-12-18T16:24:33.591477+08:00 0 [Note] WSREP: gcomm: joining thread
2017-12-18T16:24:33.591700+08:00 0 [Note] WSREP: gcomm: closing backend
再次启动及验证,时间显示与系统时间一致。
相关实践学习
基于CentOS快速搭建LAMP环境
本教程介绍如何搭建LAMP环境,其中LAMP分别代表Linux、Apache、MySQL和PHP。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
相关文章
|
2月前
|
运维 监控 关系型数据库
百度搜索:蓝易云【MYSQL四种管理日志详细介绍】
这四种管理日志对于MySQL服务器的性能监控、故障排查以及主从复制等方面都非常重要。在使用这些日志时,应根据具体需求来选择开启和配置,并定期清理和维护日志文件,以免占用过多磁盘空间。
52 0
|
2月前
|
关系型数据库 MySQL 数据库
Mysql数据库redo log及binlog的写入
Mysql数据库redo log及binlog的写入
|
3天前
|
存储 监控 关系型数据库
ELK架构监控MySQL慢日志
ELK架构监控MySQL慢日志
|
9天前
|
SQL 运维 关系型数据库
MySQL中常见的几种日志类型
MySQL中常见的几种日志类型
|
23天前
|
关系型数据库 MySQL 数据库
MySQL员工打卡日志表——数据库练习
MySQL员工打卡日志表——数据库练习
83 0
|
23天前
|
SQL 关系型数据库 MySQL
MySQL技能完整学习列表11、日志和备份——1、查看日志——2、数据备份和恢复(mysqldump, mysqlbinlog)
MySQL技能完整学习列表11、日志和备份——1、查看日志——2、数据备份和恢复(mysqldump, mysqlbinlog)
27 0
|
25天前
|
存储 SQL 关系型数据库
MySQL - 日志系统
MySQL - 日志系统
|
25天前
|
存储 监控 关系型数据库
MySQL Redo Log解密:事务故事的幕后英雄
MySQL Redo Log解密:事务故事的幕后英雄
18 0
|
25天前
|
SQL 监控 关系型数据库
数据库日志解析:深入了解MySQL中的各类日志
数据库日志解析:深入了解MySQL中的各类日志
51 0
|
1月前
|
存储 缓存 关系型数据库
Mysql专栏 - redo log日志细节
Mysql专栏 - redo log日志细节
55 0