How to Parse MySQL Slow Query Logs with Logstash and Filebeat

In production environments, Logstash often needs to handle multiple log formats, each requiring different parsing methods. This article demonstrates processing multi‑line MySQL slow query logs using Filebeat and Logstash, covering configuration of Filebeat, Logstash input, filter (grok), and output to Elasticsearch.

1. Filebeat configuration

filebeat:
  prospectors:
    - paths:
        - /www.ttlsa.com/logs/mysql/slow.log
      document_type: mysqlslowlog
      input_type: log
      multiline:
        negate: true
        match: after
  registry_file: /var/lib/filebeat/registry
output:
  logstash:
    hosts: ["10.6.66.14:5046"]

2. Logstash input configuration

# vi /etc/logstash/conf.d/01-beats-input.conf
input {
  beats {
    port => 5046
    host => "10.6.66.14"
  }
}

3. Logstash filter configuration

# vi /etc/logstash/conf.d/16-mysqlslowlog.log
filter {
  if [type] == "mysqlslowlog" {
    grok {
      match => { "message" => "(?m)^#\s+User@Host:\s+%{USER:user}\[[^\]]+\]\s+@\s+(?:(?<clienthost>\S*) )?\[(?:%{IPV4:clientip})?\]\s+Id:\s+%{NUMBER:row_id:int}
#\s+Query_time:\s+%{NUMBER:query_time:float}\s+Lock_time:\s+%{NUMBER:lock_time:float}\s+Rows_sent:\s+%{NUMBER:rows_sent:int}\s+Rows_examined:\s+%{NUMBER:rows_examined:int}
\s*(?:use %{DATA:database};\s*
)?SET\s+timestamp=%{NUMBER:timestamp};
\s*(?<sql>(?<action>\w+)\b.*;)\s*(?:
#\s+Time)?.*$" }
    }
    date {
      match => [ "timestamp", "UNIX", "YYYY-MM-dd HH:mm:ss"]
      remove_field => [ "timestamp" ]
    }
  }
}

4. Logstash output configuration

# vi /etc/logstash/conf.d/30-beats-output.conf
output {
  if "_grokparsefailure" in [tags] {
    file { path => "/var/log/logstash/grokparsefailure-%{[type]}-%{+YYYY.MM.dd}.log" }
  }
  if [@metadata][type] in [ "mysqlslowlog" ] {
    elasticsearch {
      hosts => ["10.6.66.14:9200"]
      sniffing => true
      manage_template => false
      template_overwrite => true
      index => "%{[@metadata][beat]}-%{[type]}-%{+YYYY.MM.dd}"
      document_type => "%{[@metadata][type]}"
    }
  }
}

For Filebeat versions prior to 1.1.1, the multiline option is unavailable; the article provides an alternative configuration using Logstash’s multiline codec.

Key point: correct Grok regex configuration is essential.