ELK之收集Mysql慢查询日志

1.　　logstash的配置

　　　　1.　　mysql5.7的慢日志收集

input {
  beats {
    port => 5047
  }
}

filter {

   #这一步格式化messages为json格式
    grok {
       match => [ "message", "(?m)^# User@Host: %{USER:query_user}\[[^\]]+\] @ (?:(?<query_host>\S*) )?\[(?:%{IP:query_ip})?\]\s+Id:\s+%{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+.*)" ]
       }
#这一步是将日志中的时间那一行（如：# Time: 181218  9:17:42）加上一个“drop”的tag
    grok {
        match => { "message" => "# Time: " }
        add_tag => [ "drop" ]
        tag_on_failure => []
    }
#删除标签中含有drop的行。也就是要删除慢日志里面的“# Time: 181218  9:17:42”这样的内容
    if "drop" in [tags] {
        drop {}
    }

#   时间转换
    date {
        match => ["mysql.slowlog.timestamp", "UNIX", "YYYY-MM-dd HH:mm:ss"]
        target => "@timestamp"
        timezone => "Asia/Shanghai"
    }

    ruby {
        code => "event.set('[@metadata][today]', Time.at(event.get('@timestamp').to_i).localtime.strftime('%Y.%m.%d'))"
    }

#删除字段message
    mutate {
        remove_field => [ "message" ]
    }
#修改字段query_time类型
    mutate {
        convert => ["query_time", "float"]
    }
}

output {
  elasticsearch {
    hosts => ["192.168.1.100:9200"]
    #index =>  "%{tags[0]}" 
    index => "mysql-slow%{[@metadata][beat]}-%{+YYYY.MM.dd}"
    #user => "elastic"
    #password => "changeme"
  }

}

　　　　2.　　mysql5.5的收集日志

input {
  beats {
    port => 5048
  }
}

filter {

   #这一步格式化messages为json格式
    grok {
      match => [ "message", "(?m)^# User@Host: %{USER:query_user}\[[^\]]+\] @ (?:(?<query_host>\S*) )?\[(?:%{IP:query_ip})?\]\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+.*)" ]
       }
#这一步是将日志中的时间那一行（如：# Time: 181218  9:17:42）加上一个“drop”的tag
    grok {
        match => { "message" => "# Time: " }
        add_tag => [ "drop" ]
        tag_on_failure => []
    }
#删除标签中含有drop的行。也就是要删除慢日志里面的“# Time: 181218  9:17:42”这样的内容
    if "drop" in [tags] {
        drop {}
    }

#   时间转换
    date {
        match => ["mysql.slowlog.timestamp", "UNIX", "YYYY-MM-dd HH:mm:ss"]
        target => "@timestamp"
        timezone => "Asia/Shanghai"
    }

    ruby {
        code => "event.set('[@metadata][today]', Time.at(event.get('@timestamp').to_i).localtime.strftime('%Y.%m.%d'))"
    }

#删除字段message
    mutate {
        remove_field => [ "message" ]
    }
}

output {
  elasticsearch {
    hosts => ["192.168.1.100:9200"]
    #index =>  "%{tags[0]}" 
    index => "mysql-slow%{[@metadata][beat]}-%{+YYYY.MM.dd}"
    #user => "elastic"
    #password => "changeme"
  }

}

2.　　mysql服务器的filebeat的配置

#==============/bint prospectors =============================

filebeat.prospectors:
#filebeat.inputs:
- type: log
  enabled: true
  paths:
    - /server/mysql/log/slow.log

  multiline.pattern: "^# User@Host:"

  multiline.negate: true

  multiline.match: after


#================================ General =====================================
filebeat.config.modules:
  path: ${path.config}/modules.d/*.yml
  reload.enabled: false
setup.template.settings:
  index.number_of_shards: 3
#================================ Outputs =====================================


#----------------------------- Logstash output --------------------------------
output.logstash:
  hosts: ["192.168.1.100:5047"]
#================================ Logging =====================================
#processors:
#  - add_host_metadata: ~
#  - add_cloud_metadata: ~