Monitoring and analysis of mysql-slow.log

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!!

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

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