Logstash recipe – MySQL slow log

I’ll describe here how to use logstash and logstash-forwarder to harvest the mysql slow log on a database server so you can centralize it in elasticsearch and kibana.

It is recommended to check my previous post for the software setup : Centralized logging with an ELK stack (Elasticsearch-Logback-Kibana) on Ubuntu

Setup logstash-forwarder

You can follow the steps described in my previous post to install logstash-forwarder on your web server.

Once installed, you’ll need to create the configuration file /etc/logstash-forwarder with the following content:

{
        "network": {
                "servers": [ "logstash-node:5001" ],
                "ssl ca": "/etc/ssl/logstash.pub",
                "timeout": 15
        },
        "files": [
                {
                        "paths": [
                        "/var/log/mysql/slow_query.log",
                        ],
                        "fields": { "type": "mysql" }
                }
        ]
}

Process the logs in the logstash indexer

Add the following input definition to the configuration file:

/etc/logstash/conf.d/00_input.conf

lumberjack {
           port => 5001
           tags => ["mysql_slow_log"]
           ssl_certificate => "/etc/ssl/logstash.pub"
           ssl_key => "/etc/ssl/logstash.key"
}

This configuration will told logstash to listen and receive events via the lumberjack protocol. It will listen on port 5001 to reiceve events and automatically add a tag mysql_slow_log to each.

See http://logstash.net/docs/1.4.1/inputs/lumberjack for more information on this input.

/etc/logstash/conf.d/02_filter_mysql.conf

filter {
    if "MYSQL_SLOW_LOG" in [tags] {

        if [message] =~ "^# Time:.*$" {
                  drop {}
        }


        multiline {
                  pattern => "^# User@Host:.*$"
                  negate => true
                  what => "previous"
        }

        grok {
             match => [
                      "message", "(?m)^# User@Host: %{GREEDYDATA:user}\[%{GREEDYDATA}\] @ \[%{IP:client_ip}\]\s*# Query_time: %{NUMBER:query_time:float} Lock_time: %{NUMBER:query_lock_time:float} Rows_sent: %{NUMBER:query_rows_sent:int} Rows_examined: %{NUMBER:query_rows_examined:int}\s*SET timestamp=%{NUMBER:log_timestamp};\s*%{GREEDYDATA:query}$",
                      "message", "(?m)^# User@Host: %{GREEDYDATA:user}\[%{GREEDYDATA}\] @ \[%{IP:client_ip}\]\s*# Query_time: %{NUMBER:query_time:float} Lock_time: %{NUMBER:query_lock_time:float} Rows_sent: %{NUMBER:query_rows_sent:int} Rows_examined: %{NUMBER:query_rows_examined:int}\s*use %{GREEDYDATA:database};\s*SET timestamp=%{NUMBER:log_timestamp};\s*%{GREEDYDATA:query}$",
                      "message", "(?m)^# User@Host: %{GREEDYDATA:user}\[%{GREEDYDATA}\] @ %{GREEDYDATA:client_ip} \[\]\s*# Query_time: %{NUMBER:query_time:float} Lock_time: %{NUMBER:query_lock_time:float} Rows_sent: %{NUMBER:query_rows_sent:int} Rows_examined: %{NUMBER:query_rows_examined:int}\s*SET timestamp=%{NUMBER:log_timestamp};\s*%{GREEDYDATA:query}$"
                      ]
             }

        date {
             match => ["log_timestamp", "UNIX"]
        }

        mutate {
               remove_field => "log_timestamp"
        }
    }
}

This configuration file will apply some filters on events tagged as mysql_slow_log.

Now, you need to restart logstash to apply the changes:

$ sudo service logstash restart

					
Advertisements

5 thoughts on “Logstash recipe – MySQL slow log

  1. this below is throwing an error –

    grep {
    match => [ “message”, “^# Time:.*$” ]
    negate => true
    }

    ERROR –

    The error reported is:
    Couldn’t find any filter plugin named ‘grep’. Are you sure this is correct? Trying to load the grep filter plugin resulted in this error: no such file to load — logstash/filters/grep

    Please help.

    Thanks
    Vikas

  2. Quick Question:

    What elasticsearch index will data from teh mysql server be written to? Can I send data from multiple DB servers to teh same port?

    Thanks

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s