ELK logstash 處理MySQL慢查詢日誌（初步）

標籤：row   sql   path   技術   logs   資料庫   檔案   set   beginning   

寫在前面：在做ELK logstash 處理MySQL慢查詢日誌的時候出現的問題：1、測試資料庫沒有慢日誌，所以沒有日誌資訊，導致 IP：9200/_plugin/head/介面異常（忽然出現日誌資料，刪除索引後就消失了）2、處理日誌指令碼問題3、目前單節點 配置指令檔/usr/local/logstash-2.3.0/config/slowlog.conf【詳細指令檔見最後】 output {  elasticsearch {    hosts => "115.28.3.150:9200"    index => "mysql-slowlog"    workers => 1    flush_size => 20000    idle_flush_time => 10    template_overwrite => true  }}在outpout定義Elasticsearch的IP與連接埠，以及索引名稱[[email protected] config]# ../bin/logstash agent -f slowlog.confSettings: Default pipeline workers: 1Pipeline main started在http://115.28.3.150:9200/_plugin/head/頁面上重新整理：

-------------------------------------------------------------------------------------------------------------------------------

cat /usr/local/logstash-2.3.0/config/slowlog.conf

input {

file {
type => "mysql-slow"
path => "/mnt/data/mysql/mysql-slow.log"
start_position => "beginning"
codec => multiline {
pattern => "^# Time:"
negate => true
what => "previous"
}
}
}
filter {
grok {
match => { "message" => "SELECT SLEEP" }
add_tag => [ "sleep_drop" ]
tag_on_failure => []
}
if "sleep_drop" in [tags] {
drop {}
}
grok {
match => [ "message", "(?m)^# Time:.*\s+# [email protected]: %{USER:user}\[[^\]]+\] @ (?:(?<clienthost>\S*) )?\[(?:%{IP:clientip})?\]\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+.*)$" ]
}
date {
match => [ "timestamp", "UNIX" ]
remove_field => [ "timestamp" ]
}
}
output {
elasticsearch {
hosts => "192.168.98.163:9200"
index => "mysql-slowlog"
workers => 1
flush_size => 20000
idle_flush_time => 10
template_overwrite => true
}
}

ELK logstash 處理MySQL慢查詢日誌（初步）