Analysis of the problem of InnoDB monitor being opened inexplicably

1. Existing problems

Recently, it was discovered that a large amount of log output was generated in the error log of a certain database. After analysis, it was caused by the InnoDB monitoring of the database being inexplicably turned on and then not being turned off in time, which affected log records and database performance. Check that the innodb_status_output and innodb_status_output_locks statuses are both 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. Two parameters related to InnoDB monitoring

InnoDB has four monitoring types, including StandardMonitor, LockMonitor, TablespaceMonitor, and TableMonitor. The latter two types of monitoring were removed in version 5.7 , obtained through the information_schema table after removal. Standard Monitor monitors table locks, row locks, transaction lock waits, thread semaphore waits, file IO requests, buffer pool statistics, InnoDB main thread purge and change buffer merge activities held by active transactions; Lock Monitor provides additional lock information.

InnoDB's monitor is only turned on when needed. It will cause performance overhead. Remember to turn off monitoring after the observation is completed.

The method for turning on and off StandardMonitor is as follows. The innodb_status_output parameter is used to control the turning on or off of the InnoDB monitor. This opening method will output the monitoring results to the MySQL error log in the data directory, and output will be generated every 15 seconds. This is why a large amount of output is found in the error log.

        set GLOBAL innodb_status_output=ON/OFF;

The method of opening and closing the Lock Monitor is as follows. Note that innodb_status_output must be enabled before opening it. When closing, you only need to close innodb_status_output_locks directly. If innodb_status_output is closed, the Standard Monitor will also be closed together.

   set GLOBALinnodb_status_output=ON;
        set GLOBAL innodb_status_output_locks=ON;

3. Security audit log traceability analysis

The above inexplicable opening operation should be traced through the security audit log. The sample field analysis of the security audit log is as follows:

<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>

The above logs are parsed according to the official website documents as follows:

<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语句；
等等

After understanding the meaning of the above parameters, you can grep the corresponding keyword "innodb_status_output" and its contextual log content from the security audit log. The format content is the same as above, and then according to the above Analysis and analysis can audit the account that performed this inexplicable operation, the operation time, the IP address of the operation source and other information, and realize the traceability of abnormal problems.

4. Summary

(1) InnoDB’s monitor is only turned on when needed, and closed in time after the observation is completed, because it will affect database performance and log output;

(2) Similar abnormal operations can be traced through security audit logs. The recording timeliness of security audit logs needs to be within the valid range, and storage space and recording timeliness must be balanced;

(3) More important Regarding matters, you need to pay attention to Audit Log Logging Control and account permission management control.

The above is the analysis of the problem of InnoDB monitor being opened inexplicably. For more related content, please pay attention to the PHP Chinese website (www.php.cn)!