# Logstash6.3 grok mysql slow日志

**URL:** <https://discuss.elastic.co/t/logstash6-3-grok-mysql-slow/142667>\
**Category:** 中文提问与讨论\
**Created:** [August 2, 2018, 1:54am UTC](https://discuss.elastic.co/t/logstash6-3-grok-mysql-slow/142667 "2018-08-02T01:54:01Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![hjfeng1988](https://avatars.discourse-cdn.com/v4/letter/h/df788c/32.png) [@hjfeng1988](https://discuss.elastic.co/u/hjfeng1988)\
**Post date:** [August 2, 2018, 1:54am UTC](https://discuss.elastic.co/t/logstash6-3-grok-mysql-slow/142667/1 "2018-08-02T01:54:01Z")

</div>

官方参考页：[https://www.elastic.co/guide/en/logstash/current/logstash-config-for-filebeat-modules.html](https://www.elastic.co/guide/en/logstash/current/logstash-config-for-filebeat-modules.html)

```auto
      grok {
        match => { "message" => ["^# User@Host: %{USER:[mysql][slowlog][user]}(\[[^\]]+\])? @ %{HOSTNAME:[mysql][slowlog][host]} \[(IP:[mysql][slowlog][ip])?\](\s*Id:\s* %{NUMBER:[mysql][slowlog][id]})?\n# Query_time: %{NUMBER:[mysql][slowlog][query_time][sec]}\s* Lock_time: %{NUMBER:[mysql][slowlog][lock_time][sec]}\s* Rows_sent: %{NUMBER:[mysql][slowlog][rows_sent]}\s* Rows_examined: %{NUMBER:[mysql][slowlog][rows_examined]}\n(SET timestamp=%{NUMBER:[mysql][slowlog][timestamp]};\n)?%{GREEDYMULTILINE:[mysql][slowlog][query]}"] }
        pattern_definitions => {
          "GREEDYMULTILINE" => "(.|\n)*"
        }
        remove_field => "message"
      }

```

mysql5.6.30 slow日志格式如下：

```auto
/usr/local/mysql/bin/mysqld, Version: 5.6.30-log (MySQL Community Server (GPL)). started with:
Tcp port: 0 Unix socket: /tmp/mysql.sock
Time Id Command Argument
/usr/local/mysql/bin/mysqld, Version: 5.6.30-log (MySQL Community Server (GPL)). started with:
Tcp port: 0 Unix socket: /tmp/mysql.sock
Time Id Command Argument
# Time: 180801 17:40:45
# User@Host: root[root] @ [117.25.173.74] Id: 21
# Query_time: 7.924009 Lock_time: 0.000068 Rows_sent: 43788 Rows_examined: 43788
use jiadian;
SET timestamp=1533116445;
SELECT * FROM `jd_zone`;
# Time: 180801 17:43:18
# User@Host: root[root] @ [117.25.173.74] Id: 21
# Query_time: 7.993590 Lock_time: 0.000071 Rows_sent: 43788 Rows_examined: 43788
SET timestamp=1533116598;
SELECT * FROM `jd_zone`;
# Time: 180802 9:27:01
# User@Host: root[root] @ [117.25.173.74] Id: 192
# Query_time: 6.481909 Lock_time: 0.000076 Rows_sent: 43788 Rows_examined: 43788
SET timestamp=1533173221;
SELECT * FROM `jd_zone`;

```

1.官方文档存在错误，[mysql][slowlog][ip]没有用%{}，导致匹配失败。  
2.“# User@Host”那行，@之后只有两个空格（不知是不是只有我的日志这样，还是大家都这样，或者是系统发行版与mysql版本的问题），并没有 %{HOSTNAME:[mysql][slowlog][host]}，匹配失败，这里可能要改为%{HOSTNAME:[mysql][slowlog][host]}?  
3.为何要以"^# User@Host"为开头匹配，实际slow产生的日志首行为"# Time: 180802 9:27:01"，产生的影响是slow每产生一条记录，存入elastic为两条，一条时间，一条mysql.slowlog.query

---

<div class="post-metadata">

**Author:** ![hjfeng1988](https://avatars.discourse-cdn.com/v4/letter/h/df788c/32.png) [@hjfeng1988](https://discuss.elastic.co/u/hjfeng1988)\
**Post date:** [August 2, 2018, 2:11am UTC](https://discuss.elastic.co/t/logstash6-3-grok-mysql-slow/142667/2 "2018-08-02T02:11:39Z")

</div>

2.%{HOSTNAME:[mysql][slowlog][host]}问题，应该是mysql开启了skip-name-resolve

---

<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:** [August 30, 2018, 2:11am UTC](https://discuss.elastic.co/t/logstash6-3-grok-mysql-slow/142667/3 "2018-08-30T02:11:46Z")

</div>

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