InnoDBmonitor被莫名开启的问题分析

近日发现某库错误日志里产生大量日志输出，经分析是数据库的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 theCONNECTION_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以及账户权限管理控制。

以上就是InnoDB monitor被莫名开启的问题分析的内容，更多相关内容请关注PHP中文网（www.gxlcms.com）！