Hi, all!
I want use Filebeat MySQL Module for collecting slowlogs into Elasticsearch to view in Kibana Dashboard.
I would like to resolve this issue only without using Logstash, only filebeat module.
Anyone can help me to create grok pattern for this case?
My versions:
Elasticsearch version: 6.3.0
MariaDB Versions: 5.5.56-MariaDB and 10.1.21-MariaDB
Filebeat version: 6.3.0
Kibana version: 6.3.0
This is my log pattern:
# Time: 180613 11:04:36
# User@Host: root[root] @ localhost [ ]
# Thread_id: 5 Schema: QC_hit: No
# Query_time: 2.000652 Lock_time: 0.000000 Rows_sent: 1 Rows_examined: 0
SET timestamp=1528898676;
select sleep(2);
Error Message:
error.message Provided Grok expressions do not match field value: [# User@Host: root[root] @ localhost []\n# Thread_id: 313237 Schema: QC_hit: No\n# Query_time: 35.000261 Lock_time: 0.000000 Rows_sent: 1 Rows_examined: 0\n# Rows_affected: 0\nSET timestamp=1528825570;\nSELECT SLEEP(35);]
Default "pipeline.json" file
cat /usr/share/filebeat/module/mysql/slowlog/ingest/pipeline.json
{
"description": "Pipeline for parsing MySQL slow logs.",
"processors": [{
"grok": {
"field": "message",
"patterns":[
"^# User@Host: %{USER:mysql.slowlog.user}(\\[[^\\]]+\\])? @ (%{HOSTNAME:mysql.slowlog.host})? \\[(%{IP:mysql.slowlog.ip})?\\](\\s*Id:\\s* %{NUMBER:mysql.slowlog.id})?\n# Query_time: %{NUMBER:mysql.slowlog.query_time.sec}\\s* Lock_time: %{NUMBER:mysql.slowlog.lock_time.sec}\\s* Rows_sent: %{NUMBER:mysql.slowlog.rows_sent}\\s* Rows_examined: %{NUMBER:mysql.slowlog.rows_examined}\n(SET timestamp=%{NUMBER:mysql.slowlog.timestamp};\n)?%{GREEDYMULTILINE:mysql.slowlog.query}"
],
"pattern_definitions" : {
"GREEDYMULTILINE" : "(.|\n)*"
},
"ignore_missing": true
}
}, {
"remove":{
"field": "message"
}
}, {
"date": {
"field": "mysql.slowlog.timestamp",
"target_field": "@timestamp",
"formats": ["UNIX"],
"ignore_failure": true
}
}, {
"gsub": {
"field": "mysql.slowlog.query",
"pattern": "\n# Time: [0-9]+ [0-9][0-9]:[0-9][0-9]:[0-9][0-9](\\.[0-9]+)?$",
"replacement": "",
"ignore_failure": true
}
}],
"on_failure" : [{
"set" : {
"field" : "error.message",
"value" : "{{ _ingest.on_failure_message }}"
}
}]
}
Default "slowlog.yml" file
cat /usr/share/filebeat/module/mysql/slowlog/config/slowlog.yml
type: log
paths:
{{ range $i, $path := .paths }}
- {{$path}}
{{ end }}
exclude_files: ['.gz$']
multiline:
pattern: '^# User@Host: '
negate: true
match: after
exclude_lines: ['^[\/\w\.]+, Version: .* started with:.*'] # Exclude the header
Thanks!
.
