Logstash collect MySQL slow query log

#此处以收集mysql慢查询日志为准 to add different field values depending on the file name
Input {file{Path="/data/order-slave-slow.log"type="Mysql-slow-log"start_position="beginning"codec=Multiline {pattern="^# [email protected]:"negate=true What=Previous}} file{Path="/data/other-slave-slow.log"type="Mysql-slow-log"start_position="beginning"codec=Multiline {pattern="^# [email protected]:"negate=true What=Previous}} file{Path="/data/order-master-slow.log"type="Mysql-slow-log"start_position="beginning"codec=Multiline {pattern="^# [email protected]:"negate=true What=Previous}} file{Path="/data/other-master-slow.log"type="Mysql-slow-log"start_position="beginning"codec=Multiline {pattern="^# [email protected]:"negate=true What=Previous}}} Filter {if[Path] =~"Order-slave-slow"{//Add a different field depending on the content of the fileGrok {Match= = {"message"="(? m) ^#\[email Protected]:\s+%{user:user}\[[^\]]+\]\[email protected]\s+ (?:(? <clientip>\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:row_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> (? <action>\w+) \b.*;) \s* (?: \ n#\s+time)?. *$"} Remove_field= ["message"]} mutate {replace= ["Host","%{host}"] Add_field= ["Nscode","%{nscode}"] Add_field= ["Envcode","%{envcode}"] Add_field= ["Mysqltype","%{mysqltype}"] Gsub= ["SQL","\n# Time: \d+\s+\d+:\d+:\d+","" ]        }    }    if[Path] =~"Other-slave-slow"{grok {match= = {"message"="(? m) ^#\[email Protected]:\s+%{user:user}\[[^\]]+\]\[email protected]\s+ (?:(? <clientip>\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:row_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> (? <action>\w+) \b.*;) \s* (?: \ n#\s+time)?. *$"} Remove_field= ["message"]} mutate {replace= ["Host","%{host}"] Add_field= ["Nscode","%{nscode}"] Add_field= ["Envcode","%{envcode}"] Add_field= ["Mysqltype","%{mysqltype}"] Gsub= ["SQL","\n# Time: \d+\s+\d+:\d+:\d+","" ]        }    }    if[Path] =~"Order-master-slow"{grok {match= = {"message"="(? m) ^#\[email Protected]:\s+%{user:user}\[[^\]]+\]\[email protected]\s+ (?:(? <clientip>\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:row_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> (? <action>\w+) \b.*;) \s* (?: \ n#\s+time)?. *$"} Remove_field= ["message"]} mutate {replace= ["Host","%{host}"] Add_field= ["Nscode","%{nscode}"] Add_field= ["Envcode","%{envcode}"] Add_field= ["Mysqltype","%{mysqltype}"] Gsub= ["SQL","\n# Time: \d+\s+\d+:\d+:\d+","" ]        }    }    if[Path] =~"Other-master-slow"{grok {match= = {"message"="(? m) ^#\[email Protected]:\s+%{user:user}\[[^\]]+\]\[email protected]\s+ (?:(? <clientip>\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:row_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> (? <action>\w+) \b.*;) \s* (?: \ n#\s+time)?. *$"} Remove_field= ["message"]} mutate {
#替换原有host字段的值 Replace= ["Host","%{host}" ]
#新增三个字段 Add_field= ["Nscode","%{nscode}"] Add_field= ["Envcode","%{envcode}"] Add_field= ["Mysqltype","%{mysqltype}" ]
The value of the SQL field is sliced, "\n# time: \d+\s+\d+:\d+:\d+ "match to the content to be replaced by empty.
gsub= ["SQL","\n# Time: \d+\s+\d+:\d+:\d+","" ]
}
}
}

//Output here to Redis server Output {    if'mysql-slow-log'  {        Redis {            '  %{es_sever}"//point to Redis server address              "list"       "Mysql-slow-log"}}}             

Logstash collect MySQL slow query log