Example of ELK logstash processing MySQL slow query logs

In a production environment, logstash often processes logs in multiple formats. Different log formats have different parsing methods. The following is an example of how logstash processes multi-line logs and analyzes MySQL slow query logs. This is a frequent occurrence and many questions are raised on the Internet.
The slow query log format of MySQL is as follows:

# User @ Host: ttlsa [ttlsa] @ [10.4.10.12] Id: 69641319
# Query_time: 0.000148 Lock_time: 0.000023 Rows_sent: 0 Rows_examined: 202
SET timestamp = 1456717595;
Select 'id', 'URL' from 'file' where 'id' in ('20140901', '20140901 ');
# Time: 160229 11:46:37
Filebeat configuration

I am using filebeat 1.1.1. In the previous version, there is no multiline configuration item. For details, refer to the following.

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"]
Shipper:
Logging:
Files:

Logstash configuration

1. input segment configuration

# Vi/etc/logstash/conf. d/01-beats-input.conf
Input {
Beats {
Port = & gt; 5046
Host => "10.6.66.14"
 }
}

2. filter segment 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} \ n # \ 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} \ n \ s *(? : Use % {DATA: database}; \ s * \ n )? SET \ s + timestamp = % {NUMBER: timestamp}; \ n \ s *(? <SQL> (? <Action> \ w +) \ B. *;) \ s *(? : \ N # \ s + Time )?. * $ "}
  }
Date {
Match => ["timestamp", "UNIX", "YYYY-MM-dd HH: mm: ss"]
Remove_field => ["timestamp"]
    }
  }
}

The key is the configuration of the grok regular expression.

3. output segment 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]}"
    }
  }
}

 

If you are using a version earlier than filebeat1.1.1, the configuration is as follows:

1. filebeat configuration

Filebeat:
Prospectors:
    -
Paths:
-/Www.ttlsa.com/logs/mysql/slow.log
Document_type: mysqlslowlog
Input_type: log
Registry_file:/var/lib/filebeat/registry
Output:
Logstash:
Hosts: ["10.6.66.14: 5046"]
Shipper:
Logging:
Files:

2. logstash input segment configuration

Input {
Beats {
Port = & gt; 5046
Host => "10.6.66.14"
Codec => multiline {
Pattern => "^ # User @ Host :"
Negate => true
What => previous
    }
 }
}
Other configurations remain unchanged.