Monitoring and analysis of mysql-slow.log

Elastic Stack Beats
1

Hi guys,

I'm trying to monitor logs from mysql database so I configured Filebeat to push logs from mysql-slow.log file to logstash and to elasticsearch afterwards.

I wanted to use multiline codec in input part of logstash config file but it's not possible with filebeat.

So I used GROK Debugger and wrote a filter that could parse the following one line message:

# Time: 2019-11-07T10:33:28.299458Z # User@Host: vlad[vlad] @ localhost []  Id:     8 # Query_time: 0.000448  Lock_time: 0.000113 Rows_sent: 2  Rows_examined: 2 SET timestamp=1573115750; select * from Persons;

The code:

%{NOTSPACE:MySQL_User} @ %{HOSTNAME:MySQL_Hostname} \[]%{SPACE} Id: %{SPACE} %{BASE10NUM:Id} # Query_time:%{SPACE}%{BASE16FLOAT:Query_time}%{SPACE}Lock_time: %{BASE16FLOAT:Lock_time}%{SPACE}Rows_sent:%{SPACE}%{BASE10NUM:Rows_sent}%{SPACE}Rows_examined: %{BASE10NUM:Rows_examined}%{SPACE}%{GREEDYDATA:Query}

Result:

{
  "Lock_time": "0.000113",
  "Rows_examined": "2",
  "Query": "SET timestamp=1573115750; select * from Persons;",
  "MySQL_User": "vlad[vlad]",
  "MySQL_Hostname": "localhost",
  "Query_time": "0.000448",
  "Id": "8",
  "Rows_sent": "2"
}

I thought that when I change multiline input in filebeat.yml file it sends me the myslq-slow.log in one line format so logstash could parse it like one line.

My filebeat.yml:

  # The regexp Pattern that has to be matched. The example pattern matches all lines starting with [
  multiline.pattern: '^#'

  # Defines if the pattern set under pattern should be negated or not. Default is false.
  multiline.negate: true

  # Match can be set to "after" or "before". It is used to define if lines should be append to a pattern
  # that was (not) matched before or after or as long as a pattern is not matched based on negate.
  # Note: After is the equivalent to previous and before is the equivalent to to next in Logstash
  multiline.match: after

Unfortunately I still get grokparsefailure message that I can see in Kibana tags.

How to achieve that logstash recieves the following message:

# Time: 2019-11-07T10:33:28.299458Z
# User@Host: vlad[vlad] @ localhost []  Id:     2
# Query_time: 0.000282  Lock_time: 0.000154 Rows_sent: 2  Rows_examined: 2
SET timestamp=1573122808;
select * from Persons;

In one line format and parse it? Is it even possible?

Any help would be really aprreciated!!

2

Hi @Vladpov,

I'm wondering, do you know about Filebeat modules? There is one for MySQL. It should also be a good reference for FIlebeat config and grok patterns if you want to go with your own solution.

Best regards

3

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.