Using filebeat to push MySQL slow query log

This article introduces a slow query log that collects MySQL by using filebeat, Logstash parsing and pushes to Elasticsearch, and creates a custom index, which is ultimately displayed through Kibana Web.

Environment Introduction:
Operating system version: CentOS Linux release 7.3.1611 (Core) 64bit
MySQL version: 5.6.28
Logstash version: Logstash 5.3.0
Elasticsearch version: Elasticsearch 5.3.0
Kibana version: Kibana 5.3.0
Java version: 1.8.0_121

MySQL version: 5.6.28 and MySQL slow log

[bash]# time: 170420 1:41:04# [email protected]: root[root] @ [ 192.168.1.178] id: 2238895756# query_time: 3.887598 lock_time: 0.000099  Rows_sent: 19 rows_examined: 19set timestamp=1492623664;select * from users _test;# [email protected]: root[root] @ [192.168.1.178] id: 2238895828#  query_time: 3.161184 lock_time: 0.000150 rows_sent: 28 rows_examined:  28SET timestamp=1492623664;select * from users_test;# Time: 170420  1:41:12[/bash] 

Filebeat and Logstash Configuration

FILEBEAT.YML configuration file

[bash]filebeat:prospectors:-paths:-/data/mysql/xxx-slow.logdocument_type:mysqlslowmultiline:pattern: "^# [email] Protected]: "Negate:truematch:afterregistry_file:/var/lib/filebeat/registryoutput:logstash:hosts: [" 192.168.1.63:5044 "][/bash]

[bash]input {beats {port => 5044}}filter {grok {match => [  "Message",  "(? m) ^# [email protected]: %{user:query_user}\[[^\]]+\] @  (?:(?) <query_host>\s*)   \[(?:%{ip:query_ip})? \]\s*id: %{number:id:int}\s+# 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* (?: use %{data:database};\s*)? set timestamp=%{number:timestamp};\s* (?<query> (? <action>\w+) \s+.*) " ]}grok {match  => {  "message"  =>  "# time: "  }add_tag => [  " Drop " ]tag_on_failure =>[]}if  Drop"  in [tags] {drop {}}date {match  => [  "timestamp",  "UNIX",  "Yyyy-mm-dd hh:mm:ss"]remove_field =>  [  "Timestamp"  ]}}output {elasticsearch {hosts =>  "192.168.1.63:9200" manage_template => falseindex = >  "%{[@metadata][beat]}-%{[type]}-%{+yyyy. MM.DD} "document_type => "%{[@metadata][type]} "}}[/bash"

• 650) this.width=650; "class=" Size-large wp-image-1158 "src=" https://www.olinux.org.cn/wp-content/uploads/2017/04/ Qq%e6%88%aa%e5%9b%be20170420135345-1024x459.jpg "alt=" MySQL slow log kibana show "width=" 1024x768 "height=" 459 "style=" Height: auto;vertical-align:middle;border:0px;margin:0px; "/>

• MySQL Slow log kibana show

• 650) this.width=650; "class=" wp-image-1159 size-large "src=" https://www.olinux.org.cn/wp-content/uploads/2017/04/ Qq%e5%9b%be%e7%89%8720170420135617-1024x643.png "alt=" MySQL slow log kibana show "width=" 1024x768 "height=" 643 "style=" Height: auto;vertical-align:middle;border:0px;margin:0px; "/>

• MySQL Slow log kibana show

This article is from "Olinux Operations trivia" blog, please be sure to keep this source http://93489.blog.51cto.com/83489/1917982

Using filebeat to push MySQL slow query log