Logstash analyzing MySQL Slow query log

Recently in the use of Elkstack to the System log analysis, on the internet also saw the use of logstash cases, but found that can not be resolved properly, and then re-take the time to do regular calculations, the main code is as follows:

input {  file {    type =>  "Mysql-slow"      path =>  "/var/lib/mysql/slow.log"         start_ position => beginning                 sincedb_write_interval => 0    codec =>  multiline {      pattern =>  "^# [email protected]:"       negate => true      what = >  "Previous"     }  }}filter {if [message] =~  "^tcp"  {        drop {}}if [message] =~  "^time"  {        drop {}}if [message] =~  "^\/USR"  {        drop {}}  grok {    match =>  {  "message"  =>  "Select sleep"  }    add_tag => [   "Sleep_drop"  ]    tag_on_failure => []  }  if   "Sleep_drop"  in [tags] {    drop {}  }     grok {    match => {  "Message"  =>  "(? m) ^#  [email protected]: %{USER:User}\[[^\]]+\] @  (?:(? <clienthost>\s*)   \[(?:%{ip:client_ip})? \]\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 "&NBSP;]   }}output {            redis  {                host = >  "192.168.1.2:6379"                  data_type =>  "List"                  key =>  "Logstash:mysql_slow_log"              }}

Description

When used with codec/multiline, it is important to note that the grok and normal regular are not supported by matching carriage return line by default. Just like you need =~//m, you need to specify it individually, by adding (? m) markers at the beginning of the expression

At the beginning of the time has not added (? m), in the http://grokdebug.herokuapp.com/debugging normal pass, but to the logstash execution is not normal parsing.

This article is from "Maple Night" blog, please be sure to keep this source http://fengwan.blog.51cto.com/508652/1758920

Logstash analyzing MySQL Slow query log