.jpg)
1.存在問題
近日發(fā)現(xiàn)某庫錯誤日志里產(chǎn)生大量日志輸出，經(jīng)分析是數(shù)據(jù)庫的InnoDB監(jiān)控被莫名開啟后未及時關(guān)閉引起的，影響日志記錄和數(shù)據(jù)庫性能。查看innodb_status_output和innodb_status_output_locks狀態(tài)均為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監(jiān)控相關(guān)的兩個參數(shù)
?? ? ? ?InnoDB有四種監(jiān)控類型，包括StandardMonitor、LockMonitor、TablespaceMonitor、TableMonitor，其中后面兩類監(jiān)控在5.7版本中被移除，移除后通過information_schema的表獲取。Standard Monitor監(jiān)視活動事務(wù)持有的表鎖、行鎖，事務(wù)鎖等待，線程信號量等待，文件IO請求，buffer pool統(tǒng)計信息，InnoDB主線程purge和change buffer merge活動；Lock Monitor提供額外的鎖信息。???
InnoDB的monitor只在需要的時候開啟，它會造成性能開銷，觀察結(jié)束后切記關(guān)閉監(jiān)控。
??? ? ?StandardMonitor開啟關(guān)閉方法如下，innodb_status_output參數(shù)就是用來控制InnoDB的monitor開啟或關(guān)閉的。這種開啟方法會將監(jiān)控結(jié)果輸出到數(shù)據(jù)目錄下的MySQL錯誤日志中，每隔15秒產(chǎn)生一次輸出，這也就是發(fā)現(xiàn)錯誤日志下產(chǎn)生大量輸出的原因。
????????set?GLOBAL?innodb_status_output=ON/OFF;
??? ? ? Lock Monitor開啟關(guān)閉方法如下，注意開啟前必須先開啟innodb_status_output，而關(guān)閉時只需要直接關(guān)閉innodb_status_output_locks，如果關(guān)閉了innodb_status_output，那么Standard Monitor也會被一同關(guān)閉。
???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>
上述日志按官網(wǎng)文檔解析如下：
<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.語句執(zhí)行完成的時間； <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代表對應(yīng)錯誤碼； <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.執(zhí)行的SQL語句； 等等</name></sqltext></status></connection_id></command_class></timestamp></timestamp></record_id></name>
?? ??理解上述參數(shù)含義后，便可從安全審計日志中g(shù)rep對應(yīng)的關(guān)鍵字“innodb_status_output”及其上下文日志內(nèi)容，格式內(nèi)容同上，再根據(jù)上述解析分析可以審計出做這個莫名操作的帳號，操作時間，操作來源IP地址等信息，實(shí)現(xiàn)異常問題的可追溯。
4.小結(jié)
（1）?InnoDB的monitor只在需要的時候開啟，觀察結(jié)束后及時關(guān)閉，因為它會影響數(shù)據(jù)庫性能和日志輸出；
（2）類似異常操作可通過安全審計日志追溯，安全審計日志的記錄時效需要在有效范圍內(nèi)，須平衡存儲空間和記錄時效；
（3）更重要的事，需要注意Audit Log Logging Control以及賬戶權(quán)限管理控制。
以上就是InnoDB monitor被莫名開啟的問題分析的內(nèi)容，更多相關(guān)內(nèi)容請關(guān)注PHP中文網(wǎng)（www.php.cn）！
.png)
