Logstash: parse mysql slow log
We send mysql slow log to logstash with rsyslog, as a whole multi-line message. So it should be parsed as single message, logstash multiline plugin is useless in this case.
I couldn’t google up apropriate grok pattern, so I spent some time creating it. Here it is, so you don’t have to lose that time again.
grok {
match => {
# !! REMOVE NEW LINES FROM PARSER STRING
# They were added for readability
"message" => "
(?m)^# Time: (?<time>%{NUMBER}%{SPACE}%{TIME})\n
# User@Host: %{USER:mysql_user}\[%{USER:mysql_current_user}\] @ (?<mysql_client_host>%{HOSTNAME}|%{IP}) \[%{IP:mysql_client_ip}\]\n
# Thread_id:%{SPACE}%{INT:thread_id} %{SPACE}Schema:%{SPACE}(%{WORD:schema})?%{SPACE}Last_errno:%{SPACE}%{INT:last_errno}%{SPACE}Killed:%{SPACE}%{INT:killed}\n
# Query_time:%{SPACE}%{NUMBER:query_time}%{SPACE}Lock_time:%{SPACE}%{NUMBER:lock_time}%{SPACE}Rows_sent:%{SPACE}%{INT:rows_sent}%{SPACE}Rows_examined:%{SPACE}%{INT:rows_examined}%{SPACE}Rows_affected:%{SPACE}%{INT:rows_affected}%{SPACE}Rows_read:%{SPACE}%{INT:rows_read}\n
# Bytes_sent:%{SPACE}%{INT:bytes_sent}\nSET timestamp=%{INT:mysql_timestamp};\n
%{GREEDYDATA:query}"
}
}
date {
match => [ "time", "YYMMdd HH:mm:ss", "YYMMdd H:mm:ss" ]
remove_field => [ "time" ]
}
Unfortunately, logstash config syntax, besides just being ugly(why not use yaml or other well-known format for structured data?) does not allow to write long strings in convenient multi-line form, like '''..''' in python(see this unanswered question on discuss.elastic.co). So don’t forget to remove new lines I added for readability.
License:
Share:
