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 }
