logstash-mysql-slow.conf

#
# Mysql multiline slow log import config
#
#
# $ gunzip -c mysql.slow-20140708.log.gz | logstash -f logstash-mysql-slow.conf
#
# Bugs: May lose the final logline

input {
    stdin {
    }
}

filter {
  mutate { replace => { "type" => "mysql-slow" } }

  multiline {
    what => next
    pattern => "^# Time:"
  }
  multiline {
    what => previous
    negate => true
    pattern => "^# User@Host:"
  }

  grok {
    match => [ "message", "^# User@Host: %{USER:mysql_user}(?:\[[^\]]+\])?\s+@\s+%{HOST:mysql_client_host}?\s+\[%{IP:mysql_client_ip}?\]" ]
  }
  grok {
    match => [ "message", "^# Query_time: %{NUMBER:query_duration_s:float}\s+Lock_time: %{NUMBER:lock_wait_s:float} Rows_sent: %{NUMBER:query_sent_rows:int} \s*Rows_examined: %{NUMBER:query_examined_rows:int}"]
  }
  # Capture the time the query happened
  grok {
    match => [ "message", "^SET timestamp=%{NUMBER:timestamp};" ]
  }
  # Capture the tablename
  grok {
    match => {
      "message" => [
        "^FROM %{NOTSPACE:query_table}",
        "^UPDATE %{NOTSPACE:query_table}.*",
        "^INSERT INTO %{NOTSPACE:query_table}.*",
        "^DELETE FROM %{NOTSPACE:query_table}.*"
      ]
    }
  }
  # Extract the time based on the time of the query and
  # not the time the item got logged
  date {
    match => [ "timestamp", "UNIX" ]
  }
}

output {
    #elasticsearch { host => localhost }
    stdout { codec => rubydebug }
}