Environment version
filebeat:6.2.3
mysql:5.6.38
Error message
{"_index": "mysql-slow-2018.04.17", "_type": "Doc", "_id": "Awlridqyhjfmcbqrk5ez", "_version": 1, "_score": null, " _source ": {" @timestamp ":" 2018-04-17t02:56:22.823z "," offset ": 100619865," beat ": {" hostname ":" TEST-DB1 ", "Name": "TEST-DB1", "Version": "6.2.3"}, "Prospector": {"type": "Log"}, "source": "/var/log /mysql_3306/mysql-slow.log "," Fileset ": {" module ":" MySQL "," name ":" Slowlog "}," message ":" # [email& Nbsp;protected]: test_db[test_table] @ [10.10.10.10] id:1874266\n# query_time:2.088465 lock_time:0.000086 Rows_sent: Rows_examined:18862\nset timestamp=1523933781;\nselect ID, DCT, MH, Topcolor, Bit_count (dct^1144174128272565460) as Dist from image_feature where topcolor=\ "278522176103c518c774fe2a73b20569\" and created_at<\ "2018-04-17 10:54:16\" and ID not in ( 120251270,120251181,120251202,120251209,120251221,120251229,120251240,120251252,120251259,120251270,120251278) Having dist<=20 order by Dist; "," error ": {" message ":" Provided Grok expressions does not match field value: [# [email protected]: Test_db[test_table] @ [10.10.10.10] id:1874266\\n# query_time:2.088465 lock_time:0.000086 rows_sent:67 Rows_examin Ed:18862\\nset timestamp=1523933781;\\nselect ID, DCT, MH, Topcolor, Bit_count (dct^1144174128272565460) as Dist from IMA Ge_feature where topcolor=\\\ "278522176103c518c774fe2a73b20569\\\" and created_at<\\\ "2018-04-17 10:54:16\\\" and ID not in ( 120251270,120251181,120251202,120251209,120251221,120251229,120251240,120251252,120251259,120251270,120251278) have dist<=20 order by Dist;] "}}," Fields ": {" @timestamp ": [1523933782823]}," highlight ": { "Beat.name": ["@[email protected]@/[email protected]"]}, "Sort": [1523933782823]}
{ "_index": "mysql-slow-2018.04.17", "_type": "doc", "_id": "AWLRb2nl6-SuKroP98i-", "_version": 1, "_score": null, "_source": { "@timestamp": "2018-04-17T02:29:21.535Z", "offset": 100614853, "beat": { "hostname": "test-db1", "name": "test-db1", "version": "6.2.3" }, "prospector": { "type": "log" }, "source": "/var/log/mysql_3306/mysql-slow.log", "message": "# Time: 180417 10:29:18", "fileset": { "module": "mysql", "name": "slowlog" }, "error": { "message": "Provided Grok expressions do not match field value: [# Time: 180417 10:29:18]" } }, "fields": { "@timestamp": [ 1523932161535 ] }, "highlight": { "error.message": [ "Provided Grok expressions do not match field value: [# @[email protected]@/[email protected]: 180417 10:29:18]" ] }, "sort": [ 1523932161535 ]}
The above information can be queried in the discover of Kibana.
From the above JSON information we can be very clear to get two information:
- One is that Kibana cannot parse the slow log of the MySQL instance.
- Another is similar to "# time:180417 10:26:11" Such a time information is also treated as MySQL SQL information sent to Kibana.
- In fact, the main problem is pipeline,
Problem solving
- Modify Module/mysql/slowlog/config/slowlog.yml
修改之前:exclude_lines: [‘^[\/\w\.]+, Version: .* started with:.*‘] # Exclude the header修改之后:exclude_lines: [‘^[\/\w\.]+, Version: .* started with:.*‘,‘^# Time.*‘] # Exclude the header
- Modify Module/mysql/slowlog/ingest/pipeline.json
Before you modify: "Patterns": ["^# [email protected]:%{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} "] After modification:" Patterns ": [" ^# [email& Nbsp;protected]:%{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} "],
"20180417" Elk Log Management filebeat collection analysis MySQL slow log