Mysql慢日志采集

Mysql的慢日志也是日常我们比较关注的一点，用logstash 可以很好的帮助我们完成这一项任务：1、 下面是我从线上简单复制出来的一段代码：# User@Host: mall[mall] @ [192.168.10.9]# Query_time: 1536.572069 Lock_time: 0.000203 Rows_sent: 0 Rows_examined: 0SET timestamp=1467686975;SELECT `shs_user`.`uid`, `shs_user`.`uname`, `shs_user_stat`.`rebate_money`, `shs_user_stat`.`order_num`FROM (`shs_user_stat`)JOIN `shs_user` ON `shs_user`.`uid`=`shs_user_stat`.`uid`WHERE `shs_user`.`is_lock` = 0ORDER BY `shs_user_stat`.`rebate_money` DESC, `shs_user_stat`.`order_num` DESCLIMIT 6;# User@Host: mall[mall] @ [192.168.10.6]# Query_time: 937.995136 Lock_time: 0.000147 Rows_sent: 0 Rows_examined: 0SET timestamp=1467686975;SELECT `shs_user`.`uid`, `shs_user`.`uname`, `shs_user_stat`.`rebate_money`, `shs_user_stat`.`order_num`FROM (`shs_user_stat`)JOIN `shs_user` ON `shs_user`.`uid`=`shs_user_stat`.`uid`WHERE `shs_user`.`is_lock` = 0ORDER BY `shs_user_stat`.`rebate_money` DESC, `shs_user_stat`.`order_num` DESCLIMIT 6;Mysql 慢日志说明：第一行：# User@Host: mall[mall] @ [192.168.10.6]#表示登录的用户和登录主机第二行：查询时间、锁的时间、查询返回的行数、查询匹配的行数、查询语句。2、 可以匹配如下格式：在使用codec/multiline搭配使用的时候，需要注意，grok和普通正则一样默认是不支持匹配回车换行的。就像你需要=～//m一样也需要单独指定，具体写法是在表达式开始位置加(?m)标记看一下匹配文件：[root@monitor logstash-5.0.0]# cat etc/logstash_mysql.slowinput{stdin{codec => multiline {pattern => "^# User@Host:"negate => truewhat => "previous"}}}filter{grok {match => { "message" => "(?m)^#\s+User@Host:\s+%{USER:user}\［^\］+\]\s+@ (?:(?\S*) )?\[(?:%{IPV4: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*(?(?\w+)\s+.*).*$" }}date {match => [ "timestamp", "UNIX", "YYYY-MM-dd HH:mm:ss"]remove_field => [ "timestamp" ]}}output{stdout{codec=>rubydebug}elasticsearch {action => "index"hosts => "192.168.10.100:9200"index => "mysql-slow-%{+yyyy.MM.dd}"}}测试返回结果：{"rows_examined" => 0,"query" => "SELECT `shs_user`.`uid`, `shs_user`.`uname`, `shs_user_stat`.`rebate_money`, `shs_user_stat`.`order_num`\nFROM (`shs_user_stat`)\nJOIN `shs_user` ON `shs_user`.`uid`=`shs_user_stat`.`uid`\nWHERE `shs_user`.`is_lock` = 0\nORDER BY `shs_user_stat`.`rebate_money` DESC, `shs_user_stat`.`order_num` DESC\nLIMIT 6;","message" => "# User@Host: mall[mall] @ [192.168.10.9]\n# Query_time: 1536.572069 Lock_time: 0.000203 Rows_sent: 0 Rows_examined: 0\nSET timestamp=1467686975;\nSELECT `shs_user`.`uid`, `shs_user`.`uname`, `shs_user_stat`.`rebate_money`, `shs_user_stat`.`order_num`\nFROM (`shs_user_stat`)\nJOIN `shs_user` ON `shs_user`.`uid`=`shs_user_stat`.`uid`\nWHERE `shs_user`.`is_lock` = 0\nORDER BY `shs_user_stat`.`rebate_money` DESC, `shs_user_stat`.`order_num` DESC\nLIMIT 6;","tags" => [[0] "multiline"],"lock_time" => 0.000203,"rows_sent" => 0,"@timestamp" => 2016-07-05T02:49:35.000Z,"clientip" => "192.168.10.9","@version" => "1","host" => "localhost","action" => "SELECT","user" => "mall","query_time" => 1536.572069}测试：# User@Host: mall[mall] @ [192.168.10.9]\n# Query_time: 1536.572069 Lock_time: 0.000203 Rows_sent: 0 Rows_examined: 0\nSET timestamp=1467686975;\nSELECT `shs_user`.`uid`, `shs_user`.`uname`, `shs_user_stat`.`rebate_money`, `shs_user_stat`.`order_num`\nFROM (`shs_user_stat`)\nJOIN `shs_user` ON `shs_user`.`uid`=`shs_user_stat`.`uid`\nWHERE `shs_user`.`is_lock` = 0\nORDER BY `shs_user_stat`.`rebate_money` DESC, `shs_user_stat`.`order_num` DESC\nLIMIT 6;匹配数据：^#\s+User@Host:\s+(?\w+)\［^\］+\]\s+@\s+\[(?\d+.\d+.\d+.\d+)\].*#\sQuery_time:\s(?\d+.\d+)\s+Lock_time:\s+(?\d+.\d+)\s

{

"user": [

[

"mall"

]

],

"ip": [

[

"192.168.10.9"

]

],

"Query_time": [

[

"1536.572069"

]

],

"lock_time": [

[

"0.000203"

]

]

}

日志分析资源整理：http://www.roncoo.com/course/view/3c0710458fe347c2a0b31135bbbcb57b