ElasticSearch 5.5.0之-06 mysql_slow日志

1.filebaeat.yml配置

[root@mysql-node01 ~]# vim /etc/filebeat/filebeat.yml
#=========================== Filebeat prospectors =============================

filebeat.prospectors:


# ===============================system secure log==============================

# system secure日志
- input_type: log
  paths:
    - /var/log/secure
  document_type: mysql01-secure-login
  include_lines: ["Accepted", "Failed"]
# ==============================================================================

# 多行日志处理:https://www.elastic.co/guide/en/beats/filebeat/current/multiline-examples.html
# mysql-slow日志
- input_type: log
  paths:
    - /usr/local/mysql/log/slow.log
  document_type: mysql01-slow.log
  multiline.pattern: "^[#][ ][User@Host:]i|[#][ ][Time:]"
  multiline.pattern: "^# User@Host:"
  multiline.pattern: '[#][ ][Time:]'
  multiline.negate: true
  multiline.match: after

#----------------------------- Logstash output --------------------------------
# 负载均衡模式:https://www.elastic.co/guide/en/beats/filebeat/current/load-balancing.html
output.logstash:
  # The Logstash hosts
  hosts: ["10.1.8.34:5044"]
  #hosts: ["10.1.8.33:5044","10.1.8.34:5044"]
  #loadbalance: true
  #worker: 2

#------------------------------drop fields-------------------------------------
# 参考文档：
# https://www.elastic.co/guide/en/beats/packetbeat/current/drop-fields.html
# 删除以下字段,@timestamp和type字段不能删除
processors:
    #target:
- drop_fields:
    fields: ["input_type","beat.hostname","beat.name","beat.version","offset"]
#================================ Logging =====================================

logging.level: info

# -------------------------------语法检查-------------------------------------
# /usr/share/filebeat/bin/filebeat -c /etc/filebeat/filebeat.yml -configtest

 2.logstash文件配置

[root@logstash02 ~]# vim /etc/logstash/conf.d/mysql01-slow-log.conf
# Date: 2017-07-25
# 各端应用服务器通过Filebeat采集发送日志过来 
# -------------------------------------------input---------------------------------------------------------
input {
        beats {
                port => "5044"
                #codec => "json"
        }
}

# -------------------------------------------filter---------------------------------------------------------
# 日志处理，分析
filter {
        # 以下4段用于日志不是jso格式时，用grok来格式化日志
        if [type] =~ "(mysql01-slow.log)" {
                grok {
                        match => { "message" => "SELECT SLEEP" }
                        add_tag => [ "sleep_drop" ]
                        tag_on_failure => [] # prevent default _grokparsefailure tag on real records
                }
                if "sleep_drop" in [tags] {
                        drop {}
                }
                grok {
                        patterns_dir => ["/etc/logstash/patterns/grok"]
                        match => { "message" => "%{MYSQLSLOW}" }
                }
                 date {
                        match => [ "timestamp", "UNIX" ]
                        remove_field => [ "timestamp" ]
                }
                mutate {
                        remove_field => "message"
                }
        }

}
# ------------------------------------------ouput-----------------------------------------------------------
# 日志输出到Elasticsearch
output {
        if [type] =~ "(mysql01-slow.log)" {
                elasticsearch {
                        hosts => ["10.18.45:9200","10.1.8.46:9200"]
                        #user => "elastic"
                        #password => "changeme"
                        index => "filebeat-mysql01-slow-log-%{+YYYY.MM.dd}"
                }
                # 标准输出，供调试
                #stdout { 
                #       codec => "rubydebug"
                #}      

        }
}
# -------------------------------------------------------------------------------------------------------

 3.grok配置文件

[root@logstash02 ~]# vim /etc/logstash/patterns/grok
# 登陆日志grok，示例分析
# Jul 31 16:34:05 zabbix sshd[22729]: Accepted password for root from 202.105.145.833 port 16597 ssh2
# Jul 31 16:34:05 zabbix sshd[22729]: Failed password for root from 202.105.145.82 port 16597 ssh2 
# 使用中1,
SECURELOG %{WORD:program}\[%{DATA:pid}\]: %{WORD:status} password for ?(invalid user)? %{WORD:USER} from %{DATA:IP} port
SYSLOGBASE2 (?:%{SYSLOGTIMESTAMP:timestamp}|%{TIMESTAMP_ISO8601:timestamp8601}) (?:%{SYSLOGFACILITY} )?%{SYSLOGHOST:logsource}+(?: %{SYSLOGPROG}:|)
# 示例分析
# Aug  2 12:15:04 zabbix sshd[3436]: pam_unix(sshd:session): session closed for user root
SYSLOGPAMSESSION %{SYSLOGBASE} (?=%{GREEDYDATA:message})%{WORD:pam_module}\(%{DATA:pam_caller}\): session %{WORD:pam_session_state} for user %{USERNAME:username}(?: by %{GREEDYDATA:pam_by})?

# MySQL-slow 日志
# https://www.elastic.co/guide/en/beats/filebeat/current/exported-fields-mysql.html#_mysql_error_thread_id
MYSQLSLOW (?m)^# User@Host: %{USER:user}\[[^\]]+\] @ (?:(?<clienthost>\S*) )?\[(?:%{IP:clientip})?\]\s*Id: %{NUMBER:id:long}\s+# Query_time: %{NUMBER:query_time:long}\s+Lock_time: %{NUMBER:lock_time:long}\s+Rows_sent: %{NUMBER:rows_sent:long}\s+Rows_examined: %{NUMBER:rows_examined:long}\s*(?:use %{DATA:database};\s*)?SET timestamp=%{NUMBER:timestamp};\s*(?<query>(?<action>\w+)\s+.*)

# nginx error日志