Build an End-to-End MySQL Slow Query Log Collection and Analysis System with ELK

1. Enable MySQL Slow Query Log

slow_query_log = 1
slow_query_log_file = /var/log/mysql/mysql-slow.log
long_query_time = 1   # log queries > 1 second
log_output = FILE

Set file ownership so MySQL can write and Logstash can read:

chown mysql:adm /var/log/mysql/mysql-slow.log
chmod 640 /var/log/mysql/mysql-slow.log
usermod -aG adm logstash

2. Logstash Configuration (config/mysql-slowlog.conf)

input {
  file {
    path => "/var/log/mysql/mysql-slow.log"
    start_position => "beginning"
    sincedb_path => "/var/lib/logstash/sincedb-slowlog"
    codec => multiline {
      pattern => "^# Time: "
      negate => true
      what => "previous"
      auto_flush_interval => 10
    }
  }
}
filter {
  grok {
    match => { "message" => ["# Time: %{TIMESTAMP_ISO8601:log_time}
# User@Host: %{USERNAME:user}(?:\[[^\]]+\])? @ %{HOSTNAME:host} \[%{IP:client_ip}\]
# Query_time: %{NUMBER:query_time:float}\s+Lock_time: %{NUMBER:lock_time:float}\s+Rows_sent: %{NUMBER:rows_sent:int}\s+Rows_examined: %{NUMBER:rows_examined:int}(?:\s+Rows_affected: %{NUMBER:rows_affected:int})?
SET timestamp=%{NUMBER:timestamp_unix:int};
%{GREEDYDATA:sql_query}"] }
    overwrite => ["message"]
  }
  date { match => ["log_time", "ISO8601"] target => "@timestamp" }
  fingerprint { source => "sql_query" target => "sql_fingerprint" method => "SHA1" }
  mutate {
    gsub => ["sql_query", "(?i)(password|token)=\\S+", "\1=***"]
    remove_field => ["log_time", "timestamp_unix"]
  }
}
output {
  elasticsearch {
    hosts => ["http://localhost:9200"]
    index => "mysql-slowlog-%{+YYYY.MM.dd}"
    user => "elastic"
    password => "${ES_PWD}"   # store securely in Logstash keystore
  }
  stdout { codec => rubydebug }
}

3. Run Logstash

bin/logstash -f config/mysql-slowlog.conf

4. Kibana Visualization

Create an index pattern mysql-slowlog-* and build common dashboards:

TOP N slow queries (aggregated by sql_fingerprint)

Query‑time distribution (average and maximum)

Lock‑wait analysis (average lock_time)

Source IP ranking ( client_ip)

Rows_examined vs Rows_sent to assess index efficiency

5. Alerts and Extensions

Kibana Alerting or ElastAlert – trigger when query_time > 5s AND rows_examined > 1e6 or a fingerprint exceeds 100 executions per minute.

Performance tuning – enable persisted queue ( queue.type: persisted) and separate pipelines for higher throughput.

Security – store passwords in Logstash keystore, mask sensitive literals in SQL, and restrict Elasticsearch index permissions to DBA/operations roles.

6. Architecture Overview

7. Summary

Input: file + multiline codec guarantees each complete SQL statement is a single event.

Filter: grok parses the log, date normalizes timestamps, and fingerprint creates a SHA1 hash of the query text for grouping.

Output: Elasticsearch stores data in daily indices ( mysql-slowlog-YYYY.MM.DD) for easy retention management.

Extension: Combine Kibana dashboards with alerting to achieve a real‑time slow‑SQL analysis platform.