你好,游客 登录 注册 搜索
背景:
阅读新闻

InnoDB monitor被莫名开启的问题分析

[日期:2017-02-27] 来源:Linux社区  作者:Linux [字体: ]

1.存在问题

近日发现某库错误日志里产生大量日志输出,经分析是数据库的InnoDB监控被莫名开启后未及时关闭引起的,影响日志记录和数据库性能。查看innodb_status_output和innodb_status_output_locks状态均为ON。

mysql>  show variables like"innodb_status_output%";

+----------------------------+-------+

| Variable_name              | Value |

+----------------------------+-------+

| innodb_status_output      | ON |

| innodb_status_output_locks| ON  |

+----------------------------+-------+

2 rows in set (0.00 sec)

2. InnoDB监控相关的两个参数

        InnoDB有四种监控类型,包括StandardMonitor、LockMonitor、TablespaceMonitor、TableMonitor,其中后面两类监控在5.7版本中被移除,移除后通过information_schema的表获取。Standard
 Monitor监视活动事务持有的表锁、行锁,事务锁等待,线程信号量等待,文件IO请求,buffer
 pool统计信息,InnoDB主线程purge和change
 buffer merge活动;Lock
 Monitor提供额外的锁信息。 

InnoDB的monitor只在需要的时候开启,它会造成性能开销,观察结束后切记关闭监控。

      StandardMonitor开启关闭方法如下,innodb_status_output参数就是用来控制InnoDB的monitor开启或关闭的。这种开启方法会将监控结果输出到数据目录下的MySQL错误日志中,每隔15秒产生一次输出,这也就是发现错误日志下产生大量输出的原因。

set GLOBAL innodb_status_output=ON/OFF; 

        Lock Monitor开启关闭方法如下,注意开启前必须先开启innodb_status_output,而关闭时只需要直接关闭innodb_status_output_locks,如果关闭了innodb_status_output,那么Standard
 Monitor也会被一同关闭。

        set GLOBALinnodb_status_output=ON;

        set GLOBAL innodb_status_output_locks=ON;

3.安全审计日志追溯分析

上述莫名开启操作考虑通过安全审计日志来追溯,安全审计日志样例字段分析如下:

<AUDIT_RECORD>

 <NAME>Query</NAME>

 <RECORD>12050XXXXX_2016-08-08T08:07:52</RECORD>

 <TIMESTAMP>2016-09-17T06:10:40
 UTC</TIMESTAMP>

 <COMMAND_CLASS>select</COMMAND_CLASS>

 <CONNECTION_ID>1618XXX</CONNECTION_ID>

 <STATUS>0</STATUS>

 <SQLTEXT>select 1</SQLTEXT>

  <USER>XX[XX]@  [172.XX.XX.XXX]</USER>

 <HOST></HOST>

 <OS_USER></OS_USER>

 <IP>172.XX.XX.XXX </IP>

</AUDIT_RECORD>

上述日志按官网文档解析如下:

<NAME>A string representing
 the typeof instruction that generated the audit event, such as a command that theserver received from a client.操作类型;

<RECORD_ID>A unique identifier
 forthe audit record. The value is composed from a sequence number and timestamp,in the format SEQ_TIMESTAMP. The sequence number is initialized to the size ofthe audit log file at the time the audit log plugin opens it and increments by1 for each record logged.
 The timestamp is a UTC value in yyyy-mm-ddThh:mm:ss formatindicating the time when the audit log plugin opened the file.记录ID;

<TIMESTAMP>The date and time
 that theaudit event was generated. For example, the event corresponding to execution ofan SQL statement received from a client has a <TIMESTAMP> value occurringafter the statement finishes, not when it is received. The value has the formatyyyy-mm-ddThh:mm:ss
 UTC (with T, no decimals). The format includes a time zonespecifier at the end. The time zone is always UTC.语句执行完成的时间;

<COMMAND_CLASS>A string thatindicates
 the type of action performed.操作指令类型;

<CONNECTION_ID>An unsigned integerrepresenting
 the client connection identifier. This is the same as the
CONNECTION_ID()function value within the session;会话连接ID;

<STATUS>An unsigned integerrepresenting
 the command status: 0 for success, nonzero if an error occurred.This is the same as the value of the

mysql_errno()C API function.0代表成功,非0代表对应错误码;

<SQLTEXT>A string representing
 thetext of an SQL statement. The value can be empty. Long values may be truncated.This element appears only if the <NAME> value is Query or Execute.执行的SQL语句;

等等

理解上述参数含义后,便可从安全审计日志中grep对应的关键字“innodb_status_output”及其上下文日志内容,格式内容同上,再根据上述解析分析可以审计出做这个莫名操作的帐号,操作时间,操作来源IP地址等信息,实现异常问题的可追溯。

4.小结

(1)
 InnoDB的monitor只在需要的时候开启,观察结束后及时关闭,因为它会影响数据库性能和日志输出;

(2)类似异常操作可通过安全审计日志追溯,安全审计日志的记录时效需要在有效范围内,须平衡存储空间和记录时效;

(3)更重要的事,需要注意Audit
 Log Logging Control以及账户权限管理控制。

本文永久更新链接地址http://www.linuxidc.com/Linux/2017-02/141113.htm

linux
本文评论   查看全部评论 (0)
表情: 表情 姓名: 字数

       

评论声明
  • 尊重网上道德,遵守中华人民共和国的各项有关法律法规
  • 承担一切因您的行为而直接或间接导致的民事或刑事法律责任
  • 本站管理人员有权保留或删除其管辖留言中的任意内容
  • 本站有权在网站内转载或引用您的评论
  • 参与本评论即表明您已经阅读并接受上述条款