# 适用于腾讯云慢sql
# multiline 插件安装
# ./logstash-plugin install logstash-filter-multiline
input {
    file {
        type =>"mysql-slow"
        path =>"/data/mysql-slow/*.log"
        #path => "/tmp/test.log"
        start_position => "beginning"
    }
}

filter {
    grok {
        match => { "message" =>"SELECT SLEEP" }
        add_tag => [ "sleep_drop" ]
        tag_on_failure => [] # prevent default _grokparsefailure tag on real records
    }
    grok {
        match => { "message" =>"Tcp port" }
        add_tag => [ "other_drop" ]
        tag_on_failure => [] # prevent default _grokparsefailure tag on real records
    }

    grok {
        match => { "message" =>"mysqld, Version:" }
        add_tag => [ "other_drop" ]
        tag_on_failure => [] # prevent default _grokparsefailure tag on real records
    }

    grok {
        match => { "message" =>"Time                 Id Command    Argument" }
        add_tag => [ "other_drop" ]
        tag_on_failure => [] # prevent default _grokparsefailure tag on real records
    }

    if "sleep_drop" in [tags] {
        drop {}
    }

    if "other_drop" in [tags] {
        drop {}
    }
    multiline {
        pattern =>"^# Time: "
        negate => true
        what =>"previous"
    }

    grok {
      match => { "message" => "(?m)^#\s+Time\s?.*\s+#\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>(.|\n)*;)\s*$" }
    }

    date {
        match => [ "timestamp", "UNIX" ]
        remove_field => [ "message" ]
    }
}
output {
    elasticsearch {
        #action   => "index"
        hosts => ["elk:9200","elk2:9200","elk3:9200"]
        index => "mysql-slowlog-%{+YYYY.MM.dd}"
        user     => "elastic"
        template_overwrite => true
        password => "123456"
    }
}