Logstash is not processing MySQL logs correctly

Question

As you can see, the message is not just the message, but also contains the date and timestamp.

. .

This is the MySQL Log: /var/log/mysql/error.log

150630  9:01:29 [Warning] Access denied for user 'test1'@'localhost' (using password: YES)
150630  9:03:39 [Warning] Access denied for user 'test3'@'localhost' (using password: YES)
150630  9:07:48 [Warning] Access denied for user 'test5'@'localhost' (using password: YES)
150630  9:10:00 [Warning] Access denied for user 'test7'@'localhost' (using password: YES)
150630  9:12:21 [Warning] Access denied for user 'test9'@'localhost' (using password: YES)

This is the Logstash-shipper configuration: /etc/logstash/shipper/conf.d/20-filter.conf

else if [type] == "mysql" {
  grok {
    patterns_dir => "/etc/logstash/patterns"
    match => [ "message", "%{MYSQLLOG}" ]
    overwrite => [ "message" ]
  }
}

This is the pattern:

MYSQLLOG %{NUMBER:date} %{TIME:time} \[%{LOGLEVEL:loglevel}\] %{GREEDYDATA:message}

I've been trying to fix this for weeks and I actually had it working at one moment, but without intervention from my end it stopped working again. Any tips regarding debugging?

Answer 1

It looks like there's an extra space in your log entries between the date and time fields, so your grok isn't matching, as evidenced by the existance of the _grokparsefailure tag.

Try this pattern instead:

%{NUMBER:date}  %{TIME:time} \[%{LOGLEVEL:loglevel}] %{GREEDYDATA:message}

I've tested this in the Grok Constructor and it matched all the lines you provided.