"20180417" Elk Log Management filebeat collection analysis MySQL slow log

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.

1. 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.
2. In fact, the main problem is pipeline,

Problem solving

2. 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

3. 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