ELK收集mysql_slow.log

BigBao的部落格發表於2018-08-24
MySql

關於慢查詢的收集及處理也耗費了我們太多的時間和精力，如何在這一塊也能提升效率呢？且看本文講解如何利用ELK做慢日誌收集。

ELK 介紹

ELK 最早是 Elasticsearch（以下簡稱ES）、Logstash、Kibana 三款開源軟體的簡稱，三款軟體後來被同一公司收購，並加入了Xpark、Beats等元件，改名為Elastic Stack，成為現在最流行的開源日誌解決方案，雖然有了新名字但大家依然喜歡叫她ELK，現在所說的ELK就指的是基於這些開源軟體構建的日誌系統。

我們收集mysql慢日誌的方案如下：

 

  • mysql 伺服器安裝 Filebeat 作為 agent 收集 slowLog

  • Filebeat 讀取 mysql 慢日誌檔案做簡單過濾傳給 Kafka 叢集

  • Logstash 讀取 Kafka 叢集資料並按欄位拆分後轉成 JSON 格式存入 ES 叢集

  • Kibana讀取ES叢集資料展示到web頁面上

慢日誌分類

目前主要使用的mysql版本有5.5、5.6 和 5.7，經過仔細對比發現每個版本的慢查詢日誌都稍有不同，如下：

5.5 版本慢查詢日誌

 

5.6 版本慢查詢日誌

 

5.7 版本慢查詢日誌

 

慢查詢日誌異同點：

  1. 每個版本的Time欄位格式都不一樣

  2. 相較於5.6、5.7版本，5.5版本少了Id欄位

  3. use db語句不是每條慢日誌都有的

  4. 可能會出現像下邊這樣的情況，慢查詢塊# Time：下可能跟了多個慢查詢語句

     

  5.  

     

處理思路

上邊我們已經分析了各個版本慢查詢語句的構成，接下來我們就要開始收集這些資料了，究竟應該怎麼收集呢？

  1. 拼裝日誌行：mysql 的慢查詢日誌多行構成了一條完整的日誌，日誌收集時要把這些行拼裝成一條日誌傳輸與儲存。

  2. Time行處理：# Time: 開頭的行可能不存在，且我們可以通過SET timestamp這個值來確定SQL執行時間，所以選擇過濾丟棄Time行

  3. 一條完整的日誌：最終將以# User@Host: 開始的行，和以SQL語句結尾的行合併為一條完整的慢日誌語句

  4. 確定SQL對應的DB：use db這一行不是所有慢日誌SQL都存在的，所以不能通過這個來確定SQL對應的DB，慢日誌中也沒有欄位記錄DB，所以這裡建議為DB建立賬號時新增db name標識，例如我們的賬號命名方式為：projectName_dbName，這樣看到賬號名就知道是哪個DB了

  5. 確定SQL對應的主機：我想通過日誌知道這條SQL對應的是哪臺資料庫伺服器怎麼辦？

    慢日誌中同樣沒有欄位記錄主機，可以通過filebeat注入欄位來解決，例如我們給filebeat的name欄位設定為伺服器IP，這樣最終通過beat.name這個欄位就可以確定SQL對應的主機了。

Filebeat配置

filebeat 完整的配置檔案如下：

 

重要引數解釋：

  • input_type：指定輸入的型別是log或者是stdin

  • paths：慢日誌路徑，支援正則，比如/data/*.log

  • exclude_lines：過濾掉# Time開頭的行

  • multiline.pattern：匹配多行時指定正規表示式，這裡匹配以# Time或者# User開頭的行，Time行要先匹配再過濾

  • multiline.negate：定義上邊pattern匹配到的行是否用於多行合併，也就是定義是不是作為日誌的一部分

  • multiline.match：定義如何將皮排行組合成時間，在之前或者之後

  • tail_files：定義是從檔案開頭讀取日誌還是結尾，這裡定義為true，從現在開始收集，之前已存在的不管

  • name：設定filebeat的名字，如果為空則為伺服器的主機名，這裡我們定義為伺服器IP

  • output.kafka：配置要接收日誌的kafka叢集地址可topic名稱

Kafka 接收到的日誌格式：

{"@timestamp":"2018-08-07T09:36:00.140Z","beat":{"hostname":"db-7eb166d3","name":"10.63.144.71","version":"5.4.0"},"input_type":"log","message":"# User@Host: select[select] @  [10.63.144.16]  Id: 23460596\n# Query_time: 0.155956  Lock_time: 0.000079 Rows_sent: 112  Rows_examined: 366458\nSET timestamp=1533634557;\nSELECT DISTINCT(uid) FROM common_member WHERE hideforum=-1 AND uid != 0;","offset":1753219021,"source":"/data/slow/mysql_slow.log","type":"log"}

Logstash配置

logstash完整的配置檔案如下：

 

重要引數解釋：

  • input：配置 kafka 的叢集地址和 topic 名字

  • filter：過濾日誌檔案，主要是對 message 資訊（看前文 kafka 接收到的日誌格式）進行拆分，拆分成一個一個易讀的欄位，例如User、Host、Query_time、Lock_time、timestamp等。

    grok段根據我們前文對mysql慢日誌的分類分別寫不通的正規表示式去匹配，當有多條正規表示式存在時，logstash會從上到下依次匹配，匹配到一條後邊的則不再匹配。

    date欄位定義了讓SQL中的timestamp_mysql欄位作為這條日誌的時間欄位，kibana上看到的實踐排序的資料依賴的就是這個時間

  • output：配置ES伺服器叢集的地址和index，index自動按天分割

kibana查詢展示

開啟Kibana新增 mysql-slowlog-* 的Index，並選擇timestamp，建立Index Pattern

 

進入Discover頁面，可以很直觀的看到各個時間點慢日誌的數量變化，可以根據左側Field實現簡單過濾，搜尋框也方便搜尋慢日誌，例如我要找查詢時間大於2s的慢日誌，直接在搜尋框輸入 query_time: > 2 回車即可。

 

點選每一條日誌起邊的很色箭頭能檢視具體某一條日誌的詳情。

 

如果你想做個大盤統計慢日誌的整體情況，例如top 10 SQL等，也可以很方便的通過web介面配置。

 

 

文章轉自：https://mp.weixin.qq.com/s/NQuxPt0Gm_Z1FVNwLVA48g

相關文章