# Monitoring and analysis of mysql-slow.log

**URL:** <https://discuss.elastic.co/t/monitoring-and-analysis-of-mysql-slow-log/207071>\
**Category:** Beats\
**Tags:** filebeat\
**Created:** [November 8, 2019, 8:30am UTC](https://discuss.elastic.co/t/monitoring-and-analysis-of-mysql-slow-log/207071 "2019-11-08T08:30:07Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![Vladpov](https://avatars.discourse-cdn.com/v4/letter/v/8c91f0/32.png) [@Vladpov](https://discuss.elastic.co/u/Vladpov)\
**Post date:** [November 8, 2019, 8:30am UTC](https://discuss.elastic.co/t/monitoring-and-analysis-of-mysql-slow-log/207071/1 "2019-11-08T08:30:07Z")

</div>

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:

```auto
# 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:

```auto
%{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:

```auto
{
  "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:

```auto
  # 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:

```auto
# 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!!

---

<div class="post-metadata">

**Author:** ![exekias](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/exekias/32/28718_2.png) [@exekias](https://discuss.elastic.co/u/exekias)\
**Post date:** [November 15, 2019, 9:23am UTC](https://discuss.elastic.co/t/monitoring-and-analysis-of-mysql-slow-log/207071/2 "2019-11-15T09:23:54Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\
**Post date:** [December 13, 2019, 9:23am UTC](https://discuss.elastic.co/t/monitoring-and-analysis-of-mysql-slow-log/207071/3 "2019-12-13T09:23:57Z")

</div>

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