MySQL的監控原理
MySQL的監控通過日志文件來實現。
那么MySQL的日志文件有哪些類型呢? MySQL使用以下這幾種類型的日志來記錄服務器活動信息。
- Error log錯誤日志:關于啟動、關閉和異常情況的診斷消息(排查錯誤)
- General query log通用查詢日志:服務器從客戶端接收到的所有語句(全局監控)
- Slow query log慢速查詢日志:執行時間較長的查詢(性能優化)
- Audit log審計日志:針對企業版的策略審計(主從搭配)
- Binary log二進制日志:用于修改數據(備份用的到)
這5個類型的日志文件涵蓋了所有MySQL的運行記錄，是將來排查問題，優化性能的重要依據。很多經驗豐富的DBA遇到問題就是先從這些日志文件下手分析的。重點之重，要考的。
日志文件的特點：占用大量的磁盤空間，存儲在文件中，也可以存儲在表中，但只有Slow query log慢速查詢日志和General query log通用查詢日志是可以存儲在表中。可以用文本格式書寫，但Binary log二進制日志除外。(因為Binary log中都是二進制格式啊!)
Error log錯誤日志:設置文件名set- -log- Error =filename來記錄錯誤到給定的文件。或者在 UNIX或基于UNIX的系統中使用- -log-syslog服務器選項將錯誤消息發送到syslog系統日志上。
General query log通用查詢日志:設置文件名set- -general_log_file=filename為日志查詢。全局general_log和general_log_file服務器變量提供了對通用查詢日志的運行時控制。將general_log設置為0(或OFF)以禁用日志，或設置為1(或ON)以啟用日志。
Slow query log慢速查詢日志:設置文件名set- -slow_query_log_file=filename以提供對慢速查詢日志的運行時控制。設置slow_query_log為0禁用日志，設置為1啟用日志，如果日志文件已經打開，則關閉該日志文件并打開新文件。
Binary log二進制日志:設置Set --log-bin以啟用二進制日志記錄。
服務器使用該選項值作為基本名稱，在創建新日志文件時向基本名稱添加一個遞增的連續數字后綴。這些日志文件以二進制格式而不是文本格式存儲。
Audit log審計日志:審計日志作為Enterprise Edition插件提供，當加載時，您可以使用該插件將事件記錄到audit_log_file選項指定的文件中。審計不斷寫入審計日志，直到您刪除插件，或者使用audit_log_policy=NONE選項設置關閉審計。通過在服務器啟動時使用audit_log=FORCE_PLUS_PERMANENT作為選項來防止刪除插件。
接下來我們逐個講解
General query log通用查詢日志可以使用general_log服務器選項啟用，它是用來記錄所執行語句的連接信息和詳細信息。包括記錄每個連接的時間和類型以及所有操作的進程ID，記錄對所有表執行的所有語句，但不包括存儲在二進制日志的更新操作行變化，因為這些不是更新操作以語句形式被存儲的。而且General query log通用查詢日志擴張的很快，但是如果以一個短時間內啟用它，可以收集這些時間內所有活動的完整記錄。
具體記錄了些什么，我們在此舉例說明，請看下面：
- 1424 Query /* mem dbpool.default */ update
- `mem__events`.events set lastUpdateTime=1397633565708 where id=7 1429 Query /* mem dbpool.default */ update `mem__events`.events set lastUpdateTime=1397633565708 where id=4970 1424 Query /* mem dbpool.default */ commit 1429 Query /* mem dbpool.default */ commit 150526 7:32:46 1434 Connect root@localhost on mysql 1434 Query SET NAMES latin1 1434 Query SET character_set_results = NULL
里面包含事件類型和查詢內容，示例類型:查詢、連接。
上面代碼顯示每個連接和查詢的詳細信息，記錄每次連接的時間和所有操作的進程號。
Slow query log慢速查詢日志可以通過使用slow_query_log服務器選項啟用。用于記錄執行時間超過指定閾值的語句。一般設定10秒(默認)，可以使用long_query_time server選項更改此持續時間。可以指定微秒精度的秒數。可以記錄不使用索引的語句，甚至是long_query_time下面的語句。比如用log_queries_not_using_indexes來實現。可以使用mysqldumpslow命令行程序查看Slow query log慢速查詢日志。
Slow query log慢速查詢日志是記錄管理和復制語句。但默認情況下，慢速查詢日志不記錄管理語句。可以通過啟用log_slow_admin_statements服務器選項來記錄這些語句。默認情況下，從復制主機復制的語句不會出現在慢速查詢日志中，即使它們超過了long_query_time服務器選項指定的時間。此時可以要記錄這些語句，請啟用log_slow_slave_statements服務器選項來實現。
如何過濾慢速查詢日志事件?這有兩個額外的服務器選項，您可以使用來過濾緩慢的查詢日志輸出:
第一個min_examined_row_limit：指定語句必須檢查的最低行數，以便慢速查詢日志記錄該語句。
第二個log_throttle_queries_not_using_indexes：它指定慢速查詢在60秒內記錄的查詢數量，因為它們不使用索引。慢速查詢日志記錄了這些查詢的數量后，它匯總了在該時間段內花費在其余語句上的數量和總時間。默認情況下，該服務器選項的值為0，表示它記錄所有此類查詢。
舉例說明：
- # Time: date-and-time # User@Host: root[root] @ localhost [127.0.0.1] Id: 7694 # Query_time: 1.010099 Lock_time: 0.000023 Rows_sent: 0 Rows_examined: 1 SET timestamp=timestamp; /* mem dbpool.default */ update `mem__inventory`.`MysqlServer` set `timestamp`=timestamp where hid=x'08310BA6528CBF5783BBD95CE5B4561F';
例子包括服務器日期和時間，連接和查詢信息，運行查詢和持有鎖的時間。
我們使用mysqldumpslow查看慢速查詢日志，mysqldumpslow命令行程序總結了慢速查詢日志的內容。它將類似的查詢分組在一起，例如將數值參數更改為N，將字符串參數更改為'S'，顯示此類查詢的數量以及運行查詢所需的平均時間和總時間。使用-g選項提供一個搜索詞，僅顯示匹配搜索項的語句的摘要信息。也可以通過設定log_output服務器選項來指定表或文件日志輸出。。
log_output服務器選項是用來配置慢速查詢日志和通用查詢日志。它包含一個或多個FILE、TABLE或NONE值(以逗號分隔)。默認情況下，它的值為FILE。NONE代表日志既不寫文件，也不寫表。NONE的存在會導致忽略FILE或TABLE。FILE表示日志分別寫入由slow_query_log_file和general_log_file MySQL服務器選項指定的文件。在MySQL數據目錄文件中的缺省值為hostname-slow.log和hostname.log。TABLE表示在mysql數據庫中，慢速查詢日志寫到slow_log表，常規日志寫到general_log表。
隨著時間的推移，日志文件會占用更大的空間。定期備份和刪除舊的日志文件，并重新記錄到新的日志文件是DBA的日常工作之一。注意，如果使用二進制日志進行復制，請謹慎使用。備份后，刷新日志。刷新日志需要注意
1. 創建新的二進制日志文件
2. 關閉并重新打開常規和慢速查詢日志文件。在創建新日志時，必須在刷新之前重命名當前日志文件。
可以創建自己的腳本或使用提供的mysql-log-rotate腳本來實現定期安排日志文件的循環。備份日志文件后，刷新日志以強制MySQL服務器開始寫入新的日志文件。可以通過執行FLUSH LOGS SQL語句或執行mysqladmin flush-logs來實現。在刷新二進制日志會導致二進制日志記錄順序中的下一個文件重新開始。刷新普通和慢速查詢日志時，這將關閉日志文件，重新打開它們，然后在相同的文件名下重新進行日志記錄，要啟動新的日志，請在刷新之前重命名現有的日志文件。
