本文将将介绍通过logstash用来收集mysql的慢查询日志,然后推送给elasticsearch,并创建自定义的索引,最终通过kibana进行web展示。
环境介绍:
操作系统版本:centos6.6 64bit
Mysql版本: mysql5.6.17与mysql5.1.36
Logstash版本: logstash-2.0.0.tar.gz
Elasticsearch版本:elasticsearch-2.1.0.tar.gz
Kibana版本:Kibana 4.2.1
Java版本:1.8.0_45
一:mysql5.1.36版本
1:配置mysql5.1.36版本慢查询日志,这里为了测试,将查询时间超过0.1s的均记录到慢查询日志中
1
2
|
mysql> show variables like '%slow%'; mysql> show variables like '%long%'; |
2:配置logstash
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
|
# cat /usr/local/logstash/etc/logstach.conf input { file {
type => "mysql-slow"
path => "/mydata/slow-query.log"
codec => multiline {
pattern => "^# User@Host:"
negate => true
what => "previous"
}
}
} #input节的配置定义了输入的日志类型为mysql慢查询日志类型以及日志路径,采用合并多行数据。negate字段是一个选择开关,可以正向匹配和反向匹配 filter { # drop sleep events
grok {
match => { "message" => "SELECT SLEEP" }
add_tag => [ "sleep_drop" ]
tag_on_failure => [] # prevent default _grokparsefailure tag on real records
}
if "sleep_drop" in [tags] {
drop {}
}
#filter节的配置定义了过滤mysql查询为sleep状态SQL语句 grok { match => [ "message", "(?m)^# User@Host: %{USER:user}\[[^\]]+\] @ (?:(?< clienthost >\S*) )?\[(?:%{IP:clientip})?\]\s*# Query_time: %{NUMBER:query_time:float}\s+Lock_time: %{NUMBER:lock_time:float}\s+Rows_sent: %{NUMBER:rows_sent:int}\s+Rows_examined: %{NUMBER:rows_examined:int}\s*(?:use %{DATA:database};\s*)?SET timestamp=%{NUMBER:timestamp};\s*(?< query >(?< action >\w+)\s+.*)\n# Time:.*$" ]
} date {
match => [ "timestamp", "UNIX" ]
remove_field => [ "timestamp" ]
}
} #grok节定义了慢查询日志输出的正则切割,这个容易头晕眼花! output { stdout {
codec => rubydebug {}
} elasticsearch {
hosts => "192.168.1.226:9200"
index => "mysql-server81-%{+YYYY.MM.dd}"
} } #output节定义了输出,这里除了打印到屏幕上之外,还输入到elasticsearch,同时起一个自定义的索引名称 |
3:启动测试
# /usr/local/logstash/bin/logstash -f /usr/local/logstash/etc/logstach.conf
# tail -f /mydata/slow-query.log
二:mysql5.6.17版本
由于mysql5.6.17版本的的slowlog多了一个id字段,所以需要调整grok节的正则配置。
Mysql5.1.36的slowlog:
1
2
3
4
5
6
|
# tail -f /mydata/slow-query.log # Time: 151202 17:29:24 # User@Host: root[root] @ [192.168.1.156] # Query_time: 6.578696 Lock_time: 0.000039 Rows_sent: 999424 Rows_examined: 999424 SET timestamp=1449048564; select * from users_test; |
Mysql5.6.17的slowlog:对比mysql5.1.36版本的慢查询日志输出,多了Id: 84589。
1
2
3
4
5
6
|
# tail -f /mydata/slow-query.log # Time: 151202 16:09:54 # User@Host: root[root] @ [192.168.1.156] Id: 84589 # Query_time: 7.089324 Lock_time: 0.000112 Rows_sent: 1 Rows_examined: 33554432 SET timestamp=1449043794; select count(*) from t1; |
这里顺便说一下,之前还测试了Percona Server 5.5.34版本,发现慢查询日志多了Thread_id,Schema,Last_errno,Killed 4个字段。
1
2
3
4
5
6
7
|
# tail -f /mydata5.5/slow-query.log # User@Host: root[root] @ [192.168.1.228] # Thread_id: 1164217 Schema: mgr Last_errno: 0 Killed: 0 # Query_time: 0.371185 Lock_time: 0.000056 Rows_sent: 0 Rows_examined: 0 Rows_affected: 2 Rows_read: 0 # Bytes_sent: 11 SET timestamp=1449105655; REPLACE INTO edgemgr_dbcache(id, type, data, expire_time) VALUES(UNHEX('ec124ee5766c4a31819719c645dab895'), 'sermap', '{\"storages\":{\"sg1-s1\":[{\"download_port\":9083,\"p2p_port\":9035,\"rtmp_port\":9035,\"addr\":\"{\\\"l\\\":{\\\"https://192.168.1.227:9184/storage\\\":\\\"\\\"},\\\"m\\\":{},\\\"i\\\":{\\\"https://192.168.1.227:9184/storage\\\":\\\"\\\"}}\",\"cpu\":6,\"mem\":100,\"bandwidth\":0,\"disk\":0,\"dead\":0}]},\"lives\":{}}', '2016-01-02 09:20:55'); |
因而5.6.17版本只需要修改logstash.conf配置文件中的grok节内容如下后重启logstash进程即可。
1
2
3
|
grok { match => [ "message", "(?m)^# User@Host: %{USER:user}\[[^\]]+\] @ (?:(?< clienthost >\S*) )?\[(?:%{IP:clientip})?\]\s*Id: %{NUMBER:id:int}\s+# Query_time: %{NUMBER:query_time:float}\s+Lock_time: %{NUMBER:lock_time:float}\s+Rows_sent: %{NUMBER:rows_sent:int}\s+Rows_examined: %{NUMBER:rows_examined:int}\s*(?:use %{DATA:database};\s*)?SET timestamp=%{NUMBER:timestamp};\s*(?< query >(?< action >\w+)\s+.*)\n# Time:.*$" ]
} |
Kibana日志输出
本文转自斩月博客51CTO博客,原文链接http://blog.51cto.com/ylw6006/1719214如需转载请自行联系原作者
ylw6006