logstash处理MySQL慢日志，解决多行SQL匹配不完整问题 

 1 处理mysql慢日志
 2   input {  
 3       file {  
 4           type =>"mysql-slow"  
 5           path =>"/elk/mysql.log"  
 6           #start_position => "beginning"
 7           codec => multiline {  
 8               pattern =>"^# Time: "  
 9               negate => true  
10               what =>"previous"  
11               max_lines => 200000
12               max_bytes => "100MiB"
13           }  
14       }  
15   }  
16    
17   filter {  
18       # drop sleep events  
19       grok {  
20           match => { "message" =>"SELECT SLEEP" }  
21           add_tag => [ "sleep_drop" ]  
22           tag_on_failure => [] # prevent default _grokparsefailure tag on real records  
23       }  
24 
25       if "sleep_drop" in [tags] {  
26           drop {}  
27       }  
28 
29       grok {  #此正则为修改过的匹配方式，解决多行SQL匹配不完整问题：(?<sql>(?<action>\w+)([\w.*\W.*])*;)
30         match => { "message" => "(?m)^#\s+Time\s?.*\s+#\s+User@Host:\s+%{USER:user}\[[^\]]+\]\s+@\s+(?:(?<clienthost>\S*) )?\[(?:%{IPV4:clientip})?\]\s+Id:\s+%{NUMBER:row_id:int}\n#\s+Query_time:\s+%{NUMBER:query_time:float}\s+Lock_time:\s+%{NUMBER:lock_time:float}\s+Rows_sent:\s+%{NUMBER:rows_sent:int}\s+Rows_examined:\s+%{NUMBER:rows_examined:int}\n\s*(?:use %{DATA:database};\s*\n)?SET\s+timestamp=%{NUMBER:timestamp};\n\s*(?<sql>(?<action>\w+)([\w.*\W.*])*;)\s*$" }
31       }  
32 
33       date {  
34           match => [ "timestamp", "UNIX" ]  
35           remove_field => [ "timestamp" ]  
36       }  
37   } 
38 
39 
40   output {
41          elasticsearch {
42               hosts => ["10.x.x.1xx:9200"]
43          }
44            stdout {
45                   codec => rubydebug
46            }
47 
48   }
49 
50 样例数据：
51   # Time: 2019-04-25T05:53:29.251666Z
52   # User@Host: fof[fof] @  [172.1x.1x.1xx]  Id:  3419
53   # Query_time: 0.437667  Lock_time: 0.000122 Rows_sent: 1000  Rows_examined: 1000
54   SET timestamp=1556171609;
55   SELECT * FROM `tb_` LIMIT 0, 1000;
56 
57 
58 效果如下：
59   {
60           "lock_time" => 0.000122,
61              "action" => "SELECT",
62                "host" => "localhost.localdomain",
63            "@version" => "1",
64                "path" => "/elk/mysql.log",
65          "@timestamp" => 2019-04-25T05:53:29.000Z,
66            "clientip" => "172.1x.1x.1xx",
67                "type" => "mysql-slow",
68                "user" => "fof",
69             "message" => "# Time: 2019-04-25T05:53:29.251666Z\n# User@Host: fof[fof] @  [172.1x.1x.1xx]  Id:  3419\n# Query_time: 0.437667  Lock_time: 0.000122 Rows_sent: 1000  Rows_examined: 1000\nSET timestamp=1556171609;\nSELECT * FROM `tb_` LIMIT 0, 1000;",
70                "tags" => [
71           [0] "multiline"
72       ],
73                 "sql" => "SELECT * FROM `tb_` LIMIT 0, 1000;",
74           "rows_sent" => 1000,
75              "row_id" => 3419,
76       "rows_examined" => 1000,
77          "query_time" => 0.437667
78   }

 

posted @ 2019-04-26 14:27  tddh  阅读(859)  评论(0)    收藏  举报