ELK 構建 MySQL 慢日志收集平臺詳解

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：下可能跟了多個慢查詢語句

處理思路

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

  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 完整的配置文件如下：

# mysql_slow.log
-input_type: logpaths:- /home/logs/mysql/mysqld_slow.logdocument_type: mysqld-slowexclude_lines: ['^\# Time']multiline.pattern: '^\# Time|^\# User'multiline.negate: truemultiline.match: aftertail_files: true

重要參數解釋：

  • 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完整的配置文件如下：

僅顯示filter信息

  if [type] =~ "mysqld-slow" {mutate {add_field => {"line_message" => "%{message} %{offset}"}}ruby {code => "require 'digest/md5';event.set('computed_id', Digest::MD5.hexdigest(event.get('line_message')))"}#有ID有usegrok {match => { "message" => "(?m)^# User@Host: %{USER:user}\[[^\]]+\] @\s+(?:(?<clienthost>\S*))?\s+\[(?:%{IP:clientip})?\]\s+Id:\s+%{NUMBER:id:
int}\n\#\s+Query_time: %{NUMBER:query_time:float}\s+Lock_time: %{NUMBER:lock_time:float}\s+Rows_sent:\s+%{NUMBER:rows_sent:int}\s+Rows_examined: 
%{NUMBER:rows_examined:int}\nuse\s(?<dbname>\w+)\;\s+SET\s+timestamp=%{NUMBER:timestamp_mysql:int}\;\s+(?<query>.*)" }}#有ID無usegrok {match => {"message" => "(?m)^# User@Host: %{USER:user}\[[^\]]+\] @\s+(?:(?<clienthost>\S*))?\s+\[(?:%{IP:clientip})?\]\s+Id:\s+%{NUMBER:id
:int}\n\#\s+Query_time: %{NUMBER:query_time:float}\s+Lock_time: %{NUMBER:lock_time:float}\s+Rows_sent:\s+%{NUMBER:rows_sent:int}\s+Rows_examined:%{NUMBER:rows_examined:int}\nSET\s+timestamp=%{NUMBER:timestamp_mysql:int}\;\s+(?<query>.*)"}}#無ID有usegrok {    				                                match => {"message" => "(?m)^# User@Host: %{USER:user}\[[^\]]+\] @\s+(?:(?<clienthost>\S*))?\s+\[(?:%{IP:clientip})?\]\n\#\s+Query_time: %
{NUMBER:query_time:float}\s+Lock_time: %{NUMBER:lock_time:float}\s+Rows_sent:\s+%{NUMBER:rows_sent:int}\s+Rows_examined: %{NUMBER:rows_examined:i
nt}\nuse\s(?<dbname>\w+)\;\nSET\s+timestamp=%{NUMBER:timestamp_mysql:int}\;\s+(?<query>.*)"}}#無ID無usegrok {match => { "message" => "(?m)^# User@Host: %{USER:user}\[[^\]]+\] @\s+(?:(?<clienthost>\S*))?\s+\[(?:%{IP:clientip})?\]\n\#\s+Query_time: %{
NUMBER:query_time:float}\s+Lock_time: %{NUMBER:lock_time:float}\s+Rows_sent:\s+%{NUMBER:rows_sent:int}\s+Rows_examined: %{NUMBER:rows_examined:in
t}\nSET\s+timestamp=%{NUMBER:timestamp_mysql:int}\;\s+(?<query>.*)" }}date {match => ["timestamp_mysql", "UNIX"]target => "@timestamp"}mutate {remove_field => ["line_message","message","kafka","tags"]}}

重要參數解釋：

  • 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自動按天分割

ES 中mysqld-slow-*索引模板

{"order": 0,"template": "mysqld-slow-*","settings": {"index": {"refresh_interval": "5s"}},"mappings": {"mysqld-slow": {"numeric_detection": true,"properties": {"@timestamp": {"type": "date","format": "strict_date_optional_time||epoch_millis"},"@version": {"type": "string"},"query_time": {"type": "double"},"row_sent": {"type": "string"},"rows_examined": {"type": "string"},"clientip": {"type": "string"},"clienthost": {"type": "string"},"id": {"type": "integer"},"lock_time": {"type": "string"},"dbname": {"type": "keyword"},"user": {"type": "keyword"},"query": {"type": "string","index": "not_analyzed"},"tags": {"type": "string"},"timestamp": {"type": "string"},"type": {"type": "string"}}}},"aliases": {}
}

kibana查詢展示

  • 打開Kibana添加?mysql-slowlog-*?的Index，并選擇timestamp，創建Index Pattern

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

  • 點擊每一條日志起邊的很色箭頭能查看具體某一條日志的詳情。

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

  • 總結

    1. 不要望而卻步，當你開始去做已經成功一半了

    2. 本篇文章詳細介紹了關于mysql慢日志的收集，收集之后的處理呢？我們目前是DBA每天花時間去Kibana上查看分析，有優化的空間就跟開發一起溝通優化，后邊達成默契之后考慮做成自動報警或處理

    3. 關于報警ELK生態的xpark已經提供，且最新版本也開源了，感興趣的可以先研究起來，歡迎一起交流

?

轉載于:https://www.cnblogs.com/xzlive/p/10762998.html

本文來自互聯網用戶投稿，該文觀點僅代表作者本人，不代表本站立場。本站僅提供信息存儲空間服務，不擁有所有權，不承擔相關法律責任。
如若轉載，請注明出處：http://www.pswp.cn/news/449136.shtml
繁體地址，請注明出處：http://hk.pswp.cn/news/449136.shtml
英文地址，請注明出處：http://en.pswp.cn/news/449136.shtml

如若內容造成侵權/違法違規/事實不符，請聯系多彩編程網進行投訴反饋email:809451989@qq.com，一經查實，立即刪除！

相關文章

數據類型轉換規則C語言

數據類型轉換規則C語言

一、數據類型 為什么對數據進行分類&#xff1a;節約存儲空間&#xff0c;提高運行速度。整型&#xff1a;signedsigned char 1 -128~127signed short 2 -32678~32767signed int 4 正負20億signed long 4/8 正負20億/9…
閱讀更多...
Visual Studio 的碼云擴展 V1.0.85 發布

Visual Studio 的碼云擴展 V1.0.85 發布

開發四年只會寫業務代碼&#xff0c;分布式高并發都不會還做程序員&#xff1f; >>> Visual Studio 的碼云擴展 V1.0.85 已發布&#xff1a; 針對擴展進行了SDK部分的重寫修正克隆窗體和快捷方式的圖標顏色隨主題變化修正快捷方式 優化用戶登錄和token處理機制調整了…
閱讀更多...
用 FastJSON 將 JSON 字符串轉換為 Map

用 FastJSON 將 JSON 字符串轉換為 Map

前些天發現了一個巨牛的人工智能學習網站&#xff0c;通俗易懂&#xff0c;風趣幽默&#xff0c;忍不住分享一下給大家。點擊跳轉到教程。 本文是利用阿里巴巴封裝的FastJSON來轉換json字符串的。例子如下&#xff1a; import com.alibaba.fastjson.JSON; import com.aliba…
閱讀更多...
shark恒破解筆記4-API斷點GetPrivateProfileStringA

shark恒破解筆記4-API斷點GetPrivateProfileStringA

這小節是通過斷在GetPrivateProfileStringA&#xff0c;然后找到注冊碼的。 1.運行程序輸入假碼111111&#xff0c;提示重啟。通過這判斷這是一個重啟來驗證的&#xff0c;那么它是如何來驗證的呢&#xff1f;觀察程序目錄下會發現有一個名為“config.ini"的文件 那么判斷…
閱讀更多...
開關語句、循環語句、goto

開關語句、循環語句、goto

復習&#xff1a; 1、數據類型&#xff1a;signed char 1 %hhd -128~127signed short 2 %hd -32768~32767signed int 4 %d 正負20億signed long 4 %ldsigned long long 8 %lld 9天開頭的19位數unsigned char …
閱讀更多...
成功男士的健康心理特征

成功男士的健康心理特征

自信&#xff1a;他們普遍都有很強的自信心&#xff0c;有時有咄咄逼人的感覺。 急迫感&#xff1a;他們通常很急地想見到事物的成果&#xff0c;因此會給別人帶來許多的壓力。他們信仰“時間就是金錢”&#xff0c;不喜歡也不會把寶貴的時間浪費在瑣碎的無聊事情上。 腳踏實…
閱讀更多...
DBlink的創建與刪除

DBlink的創建與刪除

創建方式一&#xff1a; create [public] database link link名稱 connect to 對方數據庫用戶identified by 對方數據庫用戶密碼 using 對方數據庫ip:端口/服務名; 創建方式二&#xff1a; create [public] database link link名稱 connect to 對方數據庫用戶identified by 對…
閱讀更多...
kong 啟動報錯 could not retrieve server_version: timeout

kong 啟動報錯 could not retrieve server_version: timeout

前些天發現了一個巨牛的人工智能學習網站&#xff0c;通俗易懂&#xff0c;風趣幽默&#xff0c;忍不住分享一下給大家。點擊跳轉到教程。 報錯&#xff1a; nginx: [error] init_by_lua error: /usr/local/share/lua/5.1/kong/init.lua:169: [postgres error] could not ret…
閱讀更多...
2-STM32物聯網開發WIFI(ESP8266)+GPRS(Air202)系統方案安全篇(監聽Wi-Fi和APP的數據)

2-STM32物聯網開發WIFI(ESP8266)+GPRS(Air202)系統方案安全篇(監聽Wi-Fi和APP的數據)

1-STM32物聯網開發WIFI(ESP8266)GPRS(Air202)系統方案安全篇(來看一下怎么樣監聽網絡數據,監聽電腦上位機軟件的數據) 因為那個軟件只能監聽咱自己電腦上的數據,所以咱就用電腦開個熱點,然后讓Wi-Fi和APP連接咱電腦的熱點,這樣就能監聽數據了 電腦開個熱點 手機APP連接這個熱點…
閱讀更多...
數組越界的后果

數組越界的后果

數組&#xff1a; 什么是數組&#xff1a;變量的組合&#xff0c;是一種批量定義變量的方式。 定義&#xff1a;類型 數組名[數量];int arr[5]; 使用&#xff1a;數組名[下標];下標&#xff1a;從零開始&#xff0c;范圍&#xff1a;0~數量-1。 遍歷&#xff1a;與for循環配合…
閱讀更多...
職場心理：12個建議或許能改變你的一生(圖)

職場心理：12個建議或許能改變你的一生(圖)

心理引言&#xff1a;如何更好實現自我的職業目標&#xff1f;聽起來很簡單&#xff0c;回答也很簡短&#xff0c;就是你必須清楚你最終想要實現什么&#xff1f;只要你這樣做&#xff0c;你就已經開始從看的見和看不見的兩條路徑來實現你的目標。職場是一種特殊的環境&#xf…
閱讀更多...
python—包

python—包

1.模塊 可以增加代碼的重復利用的方式 避免命名沖突 如何定義模塊 模塊就是一個普通文件&#xff0c;所以任何代碼可以直接書寫規范 函數&#xff08;單一的功能&#xff09;類&#xff08;相似功能的組合&#xff0c;或者類似業務模塊&#xff09;測試代碼 如何使用模塊 模塊…
閱讀更多...
Docker Installation : Docker 中安裝并啟動 Kong

Docker Installation : Docker 中安裝并啟動 Kong

前些天發現了一個巨牛的人工智能學習網站&#xff0c;通俗易懂&#xff0c;風趣幽默&#xff0c;忍不住分享一下給大家。點擊跳轉到教程。 Details about how to use Kong in Docker can be found on the DockerHub repository hosting the image: kong. We also have a Docke…
閱讀更多...
tomcat是否有必要配置環境變量（摘）

tomcat是否有必要配置環境變量（摘）

之前發表了一篇關于如何安裝和配置Tomcat的文章&#xff0c;而最近在開發項目的時候總是報錯。后來被公司的大神問了一句&#xff1a;是誰告訴你Tomcat是需要配置環境變量的&#xff1f; 作為新手的我瞬間整個人都不好了&#xff01;于是偷偷百度了一下&#xff0c;終于找到了關…
閱讀更多...
走迷宮+推箱子

走迷宮+推箱子

兩個小游戲的大致邏輯 走迷宮: 1、定義二維字符數組作為迷宮 2、定義變量記錄老鼠的位置 3、獲取游戲開始時間 3、進入循環 1、清理屏幕&#xff0c;使用system調用系統命令。 2、顯示迷宮(遍歷二維字符數組) 3、檢查是否到達出口 獲取游戲結束時間&#xff0c;計算出走出迷宮用…
閱讀更多...
Facebook開源ptr：在Python環境中并行運行單元測試

Facebook開源ptr：在Python環境中并行運行單元測試

Facebook開源了一個名為Python Test Runner&#xff08;ptr&#xff09;的新項目&#xff0c;允許開發人員運行Python單元測試套件。ptr和現有測試運行器之間的主要區別在于ptr通過爬取存儲庫查找一定的Python項目&#xff0c;這些項目帶有的單元測試在安裝文件中已經定義過。 …
閱讀更多...
小竅門解決大問題(組圖)

小竅門解決大問題(組圖)

1.洗頭時&#xff0c;在水中放少許鹽&#xff0c;也可以預防脫發。 2.用醋洗頭&#xff0c;可以令頭發飄順&#xff0c;容易打理而且兼有去頭皮屑的功效。特別適合燙染后的頭發。 3.刷牙時在牙膏上加上一點小蘇打&#xff0c;刷三次后牙齒潔白如玉&#xff0c;牙銹自然脫落…
閱讀更多...
解決報錯： Connecting to Kong on http ... Could not reach Kong on http://xxx.xxx.xxx.xxx:8001

解決報錯： Connecting to Kong on http ... Could not reach Kong on http://xxx.xxx.xxx.xxx:8001

前些天發現了一個巨牛的人工智能學習網站&#xff0c;通俗易懂&#xff0c;風趣幽默&#xff0c;忍不住分享一下給大家。點擊跳轉到教程。 1. Docker 中安裝并啟動好網關 kong 后&#xff0c;想要安裝 kong 的圖形化界面 kong-dashboard &#xff0c;運行命令&#xff1a; do…
閱讀更多...
生成迷宮/C++

生成迷宮/C++

小時候&#xff0c;都喜歡玩迷宮&#xff0c;現在的電腦上還可以玩3D類迷宮。 那么每次開始游戲時&#xff0c;迷宮里密密麻麻的道路是怎么生成的呢&#xff1f; 在代碼里面&#xff0c;我們把它們想象成一堆像素小格子&#xff0c;當兩個格子連在一起&#xff0c;就像一堵墻&a…
閱讀更多...
Spring boot 配置tomcat后 控制臺不打印SQL日志

Spring boot 配置tomcat后 控制臺不打印SQL日志

在pom.xml中配置tomcat啟動處加上&#xff1a; <dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-web</artifactId><exclusions><exclusion><groupId>org.springframework.boot</…
閱讀更多...
最新文章

Copyright @ 2022~2023