文档章节

新特性解读 | MySQL 8.0 错误日志增强特性

爱可生
 爱可生
发布于 09/05 13:47
字数 1525
阅读 23
收藏 1

作者:杨涛涛

MySQL 8.0 重新定义了错误日志输出和过滤,改善了原来臃肿并且可读性很差的错误日志。

比如增加了 JSON 输出,在原来的日志后面以序号以及 JSON 后缀的方式展示

比如我机器上的 MySQL 以 JSON 保存的错误日志 mysqld.log.00.json:

[root@centos-ytt80 mysql80]# jq . mysqld.log.00.json
{
  "log_type": 1,
  "prio": 1,
  "err_code": 12592,
  "subsystem": "InnoDB",
  "msg": "Operating system error number 2 in a file operation.",
  "time": "2019-09-03T08:16:12.111808Z",
  "thread": 8,
  "err_symbol": "ER_IB_MSG_767",
  "SQL_state": "HY000",
  "label": "Error"
}
{
  "log_type": 1,
  "prio": 1,
  "err_code": 12593,
  "subsystem": "InnoDB",
  "msg": "The error means the system cannot find the path specified.",
  "time": "2019-09-03T08:16:12.111915Z",
  "thread": 8,
  "err_symbol": "ER_IB_MSG_768",
  "SQL_state": "HY000",
  "label": "Error"
}
{
  "log_type": 1,
  "prio": 1,
  "err_code": 12216,
  "subsystem": "InnoDB",
  "msg": "Cannot open datafile for read-only: './ytt2/a.ibd' OS error: 71",
  "time": "2019-09-03T08:16:12.111933Z",
  "thread": 8,
  "err_symbol": "ER_IB_MSG_391",
  "SQL_state": "HY000",
  "label": "Error"
}

以 JSON 输出错误日志后可读性和可操作性增强了许多。这里可以用 Linux 命令 jq 或者把这个字串 COPY 到其他解析 JSON 的工具方便处理。

只想非常快速的拿出错误信息,忽略其他信息。

[root@centos-ytt80 mysql80]#  jq   '.msg' mysqld.log.00.json
"Operating system error number 2 in a file operation."
"The error means the system cannot find the path specified."
"Cannot open datafile for read-only: './ytt2/a.ibd' OS error: 71"
"Cannot calculate statistics for table `ytt2`.`a` because the .ibd file is missing. Please refer to http://dev.mysql.com/doc/refman/8.0/en/innodb-troubleshooting.html for how to resolve the issue."
"Cannot calculate statistics for table `ytt2`.`a` because the .ibd file is missing. Please refer to http://dev.mysql.com/doc/refman/8.0/en/innodb-troubleshooting.html for how to resolve the issue."

使用 JSON 输出的前提是安装 JSON 输出部件。

INSTALL COMPONENT 'file://component_log_sink_json';

完了在设置变量 SET GLOBAL log_error_services = 'log_filter_internal; log_sink_json';

格式为:过滤规则;日志输出;[过滤规则]日志输出;

查看安装好的部件

mysql> select * from mysql.component;
+--------------+--------------------+---------------------------------------+
| component_id | component_group_id | component_urn                         |
+--------------+--------------------+---------------------------------------+
|            2 |                  1 | file://component_log_sink_json        |
+--------------+--------------------+---------------------------------------+
3 rows in set (0.00 sec)

现在设置 JSON 输出,输出到系统日志的同时输出到 JSON 格式日志。

mysql> SET persist log_error_services = 'log_filter_internal; log_sink_internal; log_sink_json';
Query OK, 0 rows affected (0.00 sec)

来测试一把。我之前已经把表 a 物理文件删掉了。

mysql> select * from a;
ERROR 1812 (HY000): Tablespace is missing for table `ytt2`.`a`.

现在错误日志里有 5 条记录。

[root@centos-ytt80 mysql80]# tailf  mysqld.log

2019-09-03T08:16:12.111808Z 8 [ERROR] [MY-012592] [InnoDB] Operating system error number 2 in a file operation.
2019-09-03T08:16:12.111915Z 8 [ERROR] [MY-012593] [InnoDB] The error means the system cannot find the path specified.
2019-09-03T08:16:12.111933Z 8 [ERROR] [MY-012216] [InnoDB] Cannot open datafile for read-only: './ytt2/a.ibd' OS error: 71
2019-09-03T08:16:12.112227Z 8 [Warning] [MY-012049] [InnoDB] Cannot calculate statistics for table `ytt2`.`a` because the .ibd file is missing. Please refer to http://dev.mysql.com/doc/refman/8.0/en/innodb-troubleshooting.html for how to resolve the issue.
2019-09-03T08:16:14.902617Z 8 [Warning] [MY-012049] [InnoDB] Cannot calculate statistics for table `ytt2`.`a` because the .ibd file is missing. Please refer to http://dev.mysql.com/doc/refman/8.0/en/innodb-troubleshooting.html for how to resolve the issue.

JSON 日志里也有 5 条记录。

[root@centos-ytt80 mysql80]# tailf mysqld.log.00.json

{ "log_type" : 1, "prio" : 1, "err_code" : 12592, "subsystem" : "InnoDB", "msg" : "Operating system error number 2 in a file operation.", "time" : "2019-09-03T08:16:12.111808Z", "thread" : 8, "err_symbol" : "ER_IB_MSG_767", "SQL_state" : "HY000", "label" : "Error" }
{ "log_type" : 1, "prio" : 1, "err_code" : 12593, "subsystem" : "InnoDB", "msg" : "The error means the system cannot find the path specified.", "time" : "2019-09-03T08:16:12.111915Z", "thread" : 8, "err_symbol" : "ER_IB_MSG_768", "SQL_state" : "HY000", "label" : "Error" }
{ "log_type" : 1, "prio" : 1, "err_code" : 12216, "subsystem" : "InnoDB", "msg" : "Cannot open datafile for read-only: './ytt2/a.ibd' OS error: 71", "time" : "2019-09-03T08:16:12.111933Z", "thread" : 8, "err_symbol" : "ER_IB_MSG_391", "SQL_state" : "HY000", "label" : "Error" }
{ "log_type" : 1, "prio" : 2, "err_code" : 12049, "subsystem" : "InnoDB", "msg" : "Cannot calculate statistics for table `ytt2`.`a` because the .ibd file is missing. Please refer to http://dev.mysql.com/doc/refman/8.0/en/innodb-troubleshooting.html for how to resolve the issue.", "time" : "2019-09-03T08:16:12.112227Z", "thread" : 8, "err_symbol" : "ER_IB_MSG_224", "SQL_state" : "HY000", "label" : "Warning" }
{ "log_type" : 1, "prio" : 2, "err_code" : 12049, "subsystem" : "InnoDB", "msg" : "Cannot calculate statistics for table `ytt2`.`a` because the .ibd file is missing. Please refer to http://dev.mysql.com/doc/refman/8.0/en/innodb-troubleshooting.html for how to resolve the issue.", "time" : "2019-09-03T08:16:14.902617Z", "thread" : 8, "err_symbol" : "ER_IB_MSG_224", "SQL_state" : "HY000", "label" : "Warning" }

那可能有人就问了,这有啥意义呢?只是把格式变了,过滤的规则我看还是没变。

那我们现在给第二条日志输出加过滤规则

先把过滤日志的部件安装起来

INSTALL COMPONENT 'file://component_log_filter_dragnet';

mysql> SET persist log_error_services = 'log_filter_internal; log_sink_internal; log_filter_dragnet;log_sink_json';
Query OK, 0 rows affected (0.00 sec)

只保留 error,其余的一律过滤掉。

SET GLOBAL dragnet.log_error_filter_rules = 'IF prio>=WARNING THEN drop.';

检索一张误删的表

mysql> select * from a;
ERROR 1812 (HY000): Tablespace is missing for table `ytt2`.`a`.

查看错误日志和 JSON 错误日志

发现错误日志里有一条 Warning,JSON 错误日志里的被过滤掉了。

2019-09-03T08:22:32.978728Z 8 [Warning] [MY-012049] [InnoDB] Cannot calculate statistics for table `ytt2`.`a` because the .ibd file is missing. Please refer to http://dev.mysql.com/doc/refman/8.0/en/innodb-troubleshooting.html for how to resolve the issue.

再举个例子,每 60 秒只允许记录一个 Warning 事件

mysql> SET GLOBAL dragnet.log_error_filter_rules = 'IF prio==WARNING THEN throttle 1/60.';
Query OK, 0 rows affected (0.00 sec)

多次执行

mysql> select * from b;
ERROR 1812 (HY000): Tablespace is missing for table `ytt2`.`b`.
mysql> select * from b;
ERROR 1812 (HY000): Tablespace is missing for table `ytt2`.`b`.
mysql> select * from b;
ERROR 1812 (HY000): Tablespace is missing for table `ytt2`.`b`.

现在错误日志里有三条 warning 信息

2019-09-03T08:49:06.820635Z 8 [Warning] [MY-012049] [InnoDB] Cannot calculate statistics for table `ytt2`.`b` because the .ibd file is missing. Please refer to http://dev.mysql.com/doc/refman/8.0/en/innodb-troubleshooting.html for how to resolve the issue.

2019-09-03T08:49:31.455907Z 8 [Warning] [MY-012049] [InnoDB] Cannot calculate statistics for table `ytt2`.`b` because the .ibd file is missing. Please refer to http://dev.mysql.com/doc/refman/8.0/en/innodb-troubleshooting.html for how to resolve the issue.
2019-09-03T08:50:00.430867Z 8 [Warning] [MY-012049] [InnoDB] Cannot calculate statistics for table `ytt2`.`b` because the .ibd file is missing. Please refer to http://dev.mysql.com/doc/refman/8.0/en/innodb-troubleshooting.html for how to resolve the issue.


mysqld.log.00.json 只有一条

{ "log_type" : 1, "prio" : 2, "err_code" : 12049, "subsystem" : "InnoDB", "msg" : "Cannot calculate statistics for table `ytt2`.`b` because the .ibd file is missing. Please refer to http://dev.mysql.com/doc/refman/8.0/en/innodb-troubleshooting.html for how to resolve the issue.", "time" : "2019-09-03T08:49:06.820635Z", "thread" : 8, "err_symbol" : "ER_IB_MSG_224", "SQL_state" : "HY000", "and_n_more" : 3, "label" : "Warning" }

总结,我这里简单介绍了下 MySQL 8.0 的错误日志过滤以及 JSON 输出。MySQL 8.0 的 component_log_filter_dragnet 部件过滤规则非常灵活,可以参考手册,根据它提供的语法写出自己的过滤掉的日志输出。

© 著作权归作者所有

爱可生
粉丝 11
博文 145
码字总数 272227
作品 1
徐汇
私信 提问
新特性解读 | MySQL 最新的release notes(转载)

原创作者公众号:yangyidba 从MySQL发布正式版本8.0.11以来,MySQL又相继发布8.0.12-8.0.15四个版本,本文着重介绍MySQL 8.0.13和8.0.14 版本中值得关注的改进点。 为什么不关注8.0.15,因为...

爱可生
07/04
24
0
What is new? 阿里云 RDS for MySQL 8.0 上线 独家技术解读

原作者: 阿里云高级技术专家 冷香 MySQL 8.0 简介 RDS MySQL 8.0 产品是阿里云推出的 MySQL 系列云产品之一,使用完全兼容 MySQL 8.0 的阿里云 AliSQL 8.0 分支,除了官方在 MySQL 8.0 推出...

权一
05/20
0
0
【MySQL】【翻译】8.0 GA版本的新特性 What’s New in MySQL 8.0

嗦一嗦 MySQL 8.0的新特性 What’s New in MySQL 8.0? (Generally Available) 原文链接:https://mysqlserverteam.com/whats-new-in-mysql-8-0-generally-available 原文作者:Geir Hoydalsv......

对唔住
2018/06/29
0
0
MySQL性能基准测试对比:MySQL 5.7与MySQL 8.0

版权声明:本文由腾讯云数据库产品团队整理,页面原始内容来自于db weekly英文官网,若转载请注明出处。翻译目的在于传递更多全球最新数据库领域相关信息,并不意味着腾讯云数据库产品团队赞...

腾讯技术
03/09
0
0
MySQL性能基准测试对比:5.7 和 8.0

MySQL性能基准测试对比:5.7 和 8.0 在Oracle MySQL团队的推动下,MySQL 8.0发生了巨大的变化和修改。 物理文件已更改。例如,.frm, .TRG,.TRN和 .par 不再存在。添加了大量的新特性,如通...

幸运券发放
03/07
0
0

没有更多内容

加载失败,请刷新页面

加载更多

Spring Cloud Sleuth 整合 feign 源码分析

org.springframework.cloud.sleuth.instrument.web.client.feign.TraceFeignClient 包括创建span一些参数

xiaomin0322
17分钟前
2
0
Less 延伸

extend 是一个 Less 伪类,它通过使用 :extend 选择器在一个选择器中扩展其他选择器样式。 扩展语法 扩展可以是附加到选择器,也可以是集中放置在规则,看上去像是带有选择器参数的可选伪类,...

凌兮洛
18分钟前
2
0
RedHat 7.0系统中安装mysql 5.7.22

在安装之前,首先要查看的是,你的系统中有没有已经安装过的情况。键入rpm -qa|grep mysql,如果无任何显示,则表示没有安装过相关组件,如果有,则根据显示出来的名字,键入rpm -e --nodeps...

最菜最菜之小菜鸟
23分钟前
2
0
RPA:企业信息孤岛的“克星”

为了降本增效,近来世界范围内掀起一股流程优化的热潮,转型升级成为众多企业时刻挂在嘴边的热词。不过在企业数字化转型的过程中,信息孤岛的出现,往往成为了企业升级的绊脚石。 信息孤岛:...

UiBot
23分钟前
2
0
我的测试

我的测试

daiison
23分钟前
2
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部