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

Source: Internet
Author: User
Tags kibana filebeat

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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.