Mysql慢日志数据收集和展示查询

超、凢脫俗 2022-09-12 11:45 120阅读 0赞

> ELK收集和展示Mysql慢日志数据

##### 1、收集需求`Mysql`慢日志 #####

要求`Mysql`的慢日志可以有地方查询，便于开发人员分析问题；  
使用的版本是filebeat-7.9.1  
对应的慢日志记录如下：

# Time: 2021-08-19T05:28:13.275255+08:00
    # User@Host: gtmed_wm[gtmed_wm] @ [10.152.160.184] Id: 50278
    # Schema: gtmed_wm Last_errno: 0 Killed: 0
    # Query_time: 4.434879 Lock_time: 0.000053 Rows_sent: 0 Rows_examined: 1 Rows_affected: 1
    # Bytes_sent: 52
    SET timestamp=1629322093;
    UPDATE QRTZ_SCHEDULER_STATE SET LAST_CHECKIN_TIME = 1629322088839 WHERE SCHED_NAME = 'RuoyiScheduler' AND INSTANCE_NAME = 'gt-med-hosp-test-oss-b7c7-u -dev-6db86c86bb-6zvzl1627890243700';
    # Time: 2021-08-19T05:28:13.275265+08:00
    # User@Host: gt_gmc_itg_im[gt_gmc_itg_im] @ [10.152.160.47] Id: 43743
    # Schema: gt_gmc_itg_im Last_errno: 0 Killed: 0
    # Query_time: 4.503094 Lock_time: 0.000099 Rows_sent: 0 Rows_examined: 1 Rows_affected: 1
    # Bytes_sent: 52
    use gt_gmc_itg_im;
    SET timestamp=1629322093;
    UPDATE QRTZ_SCHEDULER_STATE SET LAST_CHECKIN_TIME = 1629322088771 WHERE SCHED_NAME = 'quartzScheduler' AND INSTANCE_NAME = '9c6d2f383e2d40cabea6db030a deaad2-devops-users-6f478d5fb4-p6w2n1627442350407';
    # Time: 2021-08-19T05:28:13.275265+08:00
    # User@Host: gt_gmc_itg_im[gt_gmc_itg_im] @ [10.152.160.47] Id: 43743
    # Schema: gt_gmc_itg_im Last_errno: 0 Killed: 0
    # Query_time: 4.503094 Lock_time: 0.000099 Rows_sent: 0 Rows_examined: 1 Rows_affected: 1
    # Bytes_sent: 52
    SET timestamp=1632462482;
    UPDATE QRTZ_SCHEDULER_STATE SET LAST_CHECKIN_TIME = 1629322088771 WHERE SCHED_NAME = 'quartzScheduler' AND INSTANCE_NAME = '9c6d2f383e2d40cabea6db030a deaad2-devops-users-6f478d5fb4-p6w2n1627442350407';

##### 2、分析需求如何处理 #####

通过上面的记录我们不难发现`mysql`慢日志是有规律的，我们可以使用`filebeat`的多行合并进行收集，此时需要注意的是使用`# Time、# User`进行合并，然后合并后再丢弃开头包括`# Time`；在使用`logstash`的`Grok`进行主要的正则匹配分析，最后对分析的结果入到`ES`集群,用户使用`kibana`可视化的工具进行查询和展示.  
设计架构：  
![cb03785e697f927e63ff34e9d48e2ebd.png][]  
说明：在使用`filebeat`的`multiline`那么在用`exclude_lines|include_lines`过滤之前会将每个多行消息合并成一个单行。

##### 3、`filebeat`收集数据 #####

开发`filebeat`配置文件如下：

filebeat.inputs:
    - type: log
      enabled: true
      backoff: 6s
      paths:
        - /app/logs/slowquery.*.log  #通配mysql慢查询日志
      exclude_lines: ['^\# Time','\+08:00$'] #filebeat丢弃以# Time开头或者以+08:00结尾
      multiline.pattern: '^\# User|^\# Time'  #合并以# Time或者# User 开头
      multiline.negate: true
      multiline.match: after
      multiline.max_lines: 10000
      tags: ["mysql_slowquery"]
      fields:
        log_type: mysql_slow_sql
    output.file:
      path: "/tmp"
      filename: "filebeat_mysql_slow_sql.log"

此时`filebeat`收集的数据会输入本机的临时目录`/tmp`下面，可以实时验证，便于分析与开发。  
启动`filebeat`收集上面的慢日志记录如下：  
![14eed91c8c56801eb2fbd749e7471058.png][]  
取出字段`message`的记录：

"message":"# User@Host: gtmed_wm[gtmed_wm] @  [10.152.160.184]  Id: 50278\n# Schema: gtmed_wm  Last_errno: 0  Killed: 0\n# Query_time: 4.434879  Lock_time: 0.000053  Rows_sent: 0  Rows_examined: 1  Rows_affected: 1\n# Bytes_sent: 52\nSET timestamp=1629322093;\nUPDATE QRTZ_SCHEDULER_STATE SET LAST_CHECKIN_TIME = 1629322088839 WHERE SCHED_NAME = 'RuoyiScheduler' AND INSTANCE_NAME = 'gt-med-hosp-test-oss-b7c7-u\n-dev-6db86c86bb-6zvzl1627890243700';"

此时需要分析此记录，按照我们的架构`message`是交给`logstash`处理，`logstash`处理这样非结构化的数据使用`Grok`组件；`Grok`开发离不开测试调试工具，我推荐 https://www.5axxw.com/tools/ 的`Grok Debug`  
分析如下：  
![a62b6f47d77d6e0332d2b15bd6ff9d76.png][]  
`(?m)^#\s*User@Host:\s*%{USER:User}\[%{USER-2:User}\]\s*@\s*(?:(?<Clienthost>\S*) )?\[(?:%{IP:Client_IP})?\]\s*Id:\s*%{NUMBER:id}\s*`  
说明：`\s*` 配置空格、`%{USER:User}`使用内置的匹配模式去配置使用User接收，结构里面有`\n`建议使用空格替换`gsub => ["message","\n"," "]`  
匹配完成如下：  
![66b7436a5ae85449c86d1a1e15bed047.png][]  
![b8fbfb06247572e1df8198616e6c08a1.png][]  
解析语句如下：  
`(?m)^#\s*User@Host:\s*%{USER:User}\[%{USER-2:User}\]\s*@\s*(?:(?<Clienthost>\S*) )?\[(?:%{IP:Client_IP})?\]\s*Id:\s*%{NUMBER:id}\s*#\s*Schema:\s*%{WORD:Schema}\s*Last_errno:\s*%{NUMBER:Last_errno}\s*Killed:\s*%{NUMBER:Killed}\s*#\s*+Query_time:\s*%{NUMBER:Query_time}\s*+Lock_time:\s*%{NUMBER:Lock_time}\s*+Rows_sent:\s*%{NUMBER:Rows_sent}\s*+Rows_examined:\s*%{NUMBER:Rows_examined}\s*+Rows_affected:\s*%{NUMBER:Rows_affected}\s*#\s*Bytes_sent:\s*%{NUMBER:Bytes_sent}\s*SET\s*timestamp=%{NUMBER:timestamp};\s*(?<Query>(?<Action>\w+)\s+.*)`  
注意：此处是一行的记录  
细心的朋友会发现，上面的慢`SQL`中有一行中多`use gt_gmc_itg_im;` 我们解析`message`也要多一个，解析记录如下：  
`(?m)^#\s*User@Host:\s*%{USER:User}\[%{USER-2:User}\]\s*@\s*(?:(?<Clienthost>\S*) )?\[(?:%{IP:Client_IP})?\]\s*Id:\s*%{NUMBER:id}\s*#\s*Schema:\s*%{WORD:Schema}\s*Last_errno:\s*%{NUMBER:Last_errno}\s*Killed:\s*%{NUMBER:Killed}\s*#\s*+Query_time:\s*%{NUMBER:Query_time}\s*+Lock_time:\s*%{NUMBER:Lock_time}\s*+Rows_sent:\s*%{NUMBER:Rows_sent}\s*+Rows_examined:\s*%{NUMBER:Rows_examined}\s*+Rows_affected:\s*%{NUMBER:Rows_affected}\s*#\s*Bytes_sent:\s*%{NUMBER:Bytes_sent}\s*use\s*%{WORD:use};\s*SET\s*timestamp=%{NUMBER:timestamp};\s*(?<Query>(?<Action>\w+)\s+.*)`  
注意：此处是一行的记录

##### 4、`logstash`过滤 #####

开发`logstash`配置文件如下：

[root@gt-resource-09 logs]# more /usr/local/logstash-7.9.1/config/conf.d/input_filebeat_output_es.conf.20210924 
    input { 
            beats { 
                    port => 5044
                  }
    }
    
    
    #filter { 
    # json { 
    # source => "message"
    # }
    #}
    filter { 
    
      mutate { 
                    gsub => ["message","\n"," "]
            }
      grok { 
                    match => { 
            
                     "message" => "(?m)^#\s*User@Host:\s*%{USER:User}\[%{USER-2:User}\]\s*@\s*(?:(?<Clienthost>\S*) )?\[(?:%{IP:Client_IP})?\]\s*Id:\s*%{NUMBER:id}\s*#\s*Schema:\s*%{WORD:Schema}\s*Last_errno:\s*%{NUMBER:Last_errno}\s*Killed: \s*%{NUMBER:Killed}\s*#\s*+Query_time:\s*%{NUMBER:Query_time}\s*+Lock_time:\s*%{NUMBER:Lock_time}\s*+Rows_sent:\s*%{NUMBER:Rows_sent}\s*+Rows_examined:\s*%{NUMBER:Rows_examined}\s*+Rows_affected:\s*%{NUMBER:Rows_affected}\s*#\s*Bytes_sen t:\s*%{NUMBER:Bytes_sent}\s*SET timestamp=%{NUMBER:timestamp};\s*(?<Query>(?<Action>\w+)\s+.*)"
                    }
                    match => { 
                     "message" => "(?m)^#\s*User@Host:\s*%{USER:User}\[%{USER-2:User}\]\s*@\s*(?:(?<Clienthost>\S*) )?\[(?:%{IP:Client_IP})?\]\s*Id:\s*%{NUMBER:id}\s*#\s*Schema:\s*%{WORD:Schema}\s*Last_errno:\s*%{NUMBER:Last_errno}\s*Killed: \s*%{NUMBER:Killed}\s*#\s*+Query_time:\s*%{NUMBER:Query_time}\s*+Lock_time:\s*%{NUMBER:Lock_time}\s*+Rows_sent:\s*%{NUMBER:Rows_sent}\s*+Rows_examined:\s*%{NUMBER:Rows_examined}\s*+Rows_affected:\s*%{NUMBER:Rows_affected}\s*#\s*Bytes_sen t:\s*%{NUMBER:Bytes_sent}\s*use\s*%{WORD:use};\s*SET\s*timestamp=%{NUMBER:timestamp};\s*(?<Query>(?<Action>\w+)\s+.*)"
                   }
    
           }
    
    date {       
                match => ["timestamp","UNIX", "YYYY-MM-dd HH:mm:ss"]
                target => "@timestamp"
                timezone => "Asia/Shanghai"    
      }
    mutate { 
    # add_field => {"host_name" => "%{[host][name]}"}
            rename => { "[log][file][path]" => "log_path"}
            rename => { "[host][name]" => "hostname"}
            split => ["log_path", "."]
            remove_field => ["input","agent","ecs","tags","log","host"]
            convert => ["Lock_Time","float"]
            convert => ["Query_Time","float"]
            add_field => {  "target_index" => "logstash-mysql-slow-%{+YYYY.MM.dd}" }
            add_field => { "mysql_port" => "%{[log_path][1]}"}
         }
    
    }
    
    output { 
    # stdout{ 
    # codec=> rubydebug
    # }
            elasticsearch { 
                    hosts => ["10.152.160.41:60982","10.152.160.40:60982"]
                    index => "%{[hostname]}_%{[mysql_port]}"
            }
            stdout { 
                    codec => "rubydebug"
            }
    
    }
    
    [root@gt-resource-09 logs]#

效果如下：  
![880758bd049696e8d1c55d185b36e3ee.png][]  
![f1a236a3224db74ba390a4e4ed7f72ec.png][]  
![51d83e0a0b1d3ac45917e13994aba636.png][]

##### 5、大屏报表展示 #####

![在这里插入图片描述][watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBA6L-Q57u05omT5oCq5pmL57qn5LmL6Lev_size_20_color_FFFFFF_t_70_g_se_x_16]  
![在这里插入图片描述][watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBA6L-Q57u05omT5oCq5pmL57qn5LmL6Lev_size_20_color_FFFFFF_t_70_g_se_x_16 1]

[cb03785e697f927e63ff34e9d48e2ebd.png]: /images/20220828/7e01b5c28bda41de84e8ea5164cc3aee.png
[14eed91c8c56801eb2fbd749e7471058.png]: /images/20220828/d5b949e78b7e4a84978cc2cfab4bd83e.png
[a62b6f47d77d6e0332d2b15bd6ff9d76.png]: /images/20220828/0e3bd8dfdc354036bae675374f817146.png
[66b7436a5ae85449c86d1a1e15bed047.png]: /images/20220828/692ebbc552a54c49ae5454fec5939c7b.png
[b8fbfb06247572e1df8198616e6c08a1.png]: /images/20220828/a5124cffd41c4636b14a45fcec109b4e.png
[880758bd049696e8d1c55d185b36e3ee.png]: /images/20220828/1065d0ba94fc48ab8a639b5f4cebac61.png
[f1a236a3224db74ba390a4e4ed7f72ec.png]: /images/20220828/f4f7e311d34443148fa4e91c5c7693a9.png
[51d83e0a0b1d3ac45917e13994aba636.png]: /images/20220828/0b6eae1295cc404ba69f336592f000c2.png
[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBA6L-Q57u05omT5oCq5pmL57qn5LmL6Lev_size_20_color_FFFFFF_t_70_g_se_x_16]: /images/20220828/86b84ea1b0a9487089212165efe00bd0.png
[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBA6L-Q57u05omT5oCq5pmL57qn5LmL6Lev_size_20_color_FFFFFF_t_70_g_se_x_16 1]: /images/20220828/6805b5ed92874d349871a944021608bb.png