Logstash6.3 grok mysql slow日志

Elastic In Your Native Tongue 中文提问与讨论
1

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

      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日志格式如下：

/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

2

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

3

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