# Filebeat / slow mysql log **URL:** \ **Category:** Beats\ **Tags:** filebeat\ **Created:** [February 15, 2018, 12:04pm UTC](https://discuss.elastic.co/t/filebeat-slow-mysql-log/119973 "2018-02-15T12:04:40Z")\ **Posts on this page:** 7\ **Page:** 1
**Author:** ![rosselg](https://avatars.discourse-cdn.com/v4/letter/r/f14d63/32.png) [@rosselg](https://discuss.elastic.co/u/rosselg)\ **Post date:** [February 15, 2018, 12:04pm UTC](https://discuss.elastic.co/t/filebeat-slow-mysql-log/119973/1 "2018-02-15T12:04:41Z")
Hi there, I'm trying to ingest the "mysql slow log" into my elasticsearch. As my mysql version doesn't match the PATTERN defined in _/usr/share/filebeat/module/mysql/slowlog/ingest/pipeline.json_, I've modified the pattern directly in this file When I try to test it ```auto rm -f /tmp/testreg.json; filebeat -e -v -c /etc/filebeat/filebeat.yml -E filebeat.registry_file=/tmp/testreg.json -E output.elasticsearch.enabled=false -E output.console.pretty=true ``` I still get ```auto "# User@Host: admin[admin] @ [my.ip.addr.ess]\n# Query_time: 4.564669 Lock_time: 0.000038 Rows_sent: 0 Rows_examined: 1\nSET timestamp=1518674946;\nUPDATE blabla SET blabla=blabla+1 '\nWHERE ( blabla = '444') );\n# Time: 180215 7:09:09" ``` As you can see, the _#Time 180215_ is at the end of my message instead of the beginning. My slowlog file has the following format: ```auto # Time: 180215 7:09:09 # User@Host: admin[admin] @ [my.ip.addr.ess] # Query_time: 4.564669 Lock_time: 0.000038 Rows_sent: 0 Rows_examined: 1 SET timestamp=1518674946; UPDATE blabla SET blabla=blabla+1 WHERE ( blabla = '444') ); ``` I've test the modified pattern in the DEV Tools and it work like a charm. Do I need to re-build the mysql module somehow in filebeat? Many thanks in advance G. ---
**Author:** ![kvch](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/kvch/32/72058_2.png) [@kvch](https://discuss.elastic.co/u/kvch)\ **Post date:** [February 16, 2018, 10:01am UTC](https://discuss.elastic.co/t/filebeat-slow-mysql-log/119973/2 "2018-02-16T10:01:24Z")
If a pipeline is loaded once, it's not updated if it changes. You can delete the previous pipeline using the Dev Tools: ```auto DELETE _ingest/pipeline/my-pipeline-id ``` Then FB will upload the new, modified pipeline you created. ---
**Author:** ![rosselg](https://avatars.discourse-cdn.com/v4/letter/r/f14d63/32.png) [@rosselg](https://discuss.elastic.co/u/rosselg)\ **Post date:** [February 19, 2018, 9:11am UTC](https://discuss.elastic.co/t/filebeat-slow-mysql-log/119973/3 "2018-02-19T09:11:36Z")
Hi @kvch, Thanks for your reply. I've tried to delete the pipeline but I'm still facing the same issue. Filebeat is still sending the "User@Host: xxx" at the beginning, instead of the "# Time: xxx" in the message field: ```auto "# User@Host: admin[admin] @ [my.ip.addr.ess]\n# Query_time: 4.564669 Lock_time: 0.000038 Rows_sent: 0 Rows_examined: 1\nSET timestamp=1518674946;\nUPDATE blabla SET blabla=blabla+1 '\nWHERE ( blabla = '444') );\n# Time: 180219 7:09:09" ``` thanks G. ---
**Author:** ![kvch](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/kvch/32/72058_2.png) [@kvch](https://discuss.elastic.co/u/kvch)\ **Post date:** [February 19, 2018, 10:19am UTC](https://discuss.elastic.co/t/filebeat-slow-mysql-log/119973/4 "2018-02-19T10:19:52Z")
Could you share the modified pipeline? ---
**Author:** ![rosselg](https://avatars.discourse-cdn.com/v4/letter/r/f14d63/32.png) [@rosselg](https://discuss.elastic.co/u/rosselg)\ **Post date:** [February 19, 2018, 10:22am UTC](https://discuss.elastic.co/t/filebeat-slow-mysql-log/119973/5 "2018-02-19T10:22:16Z")
Sure! ```auto { "filebeat-6.2.1-mysql-slowlog-pipeline" : { "description" : "Pipeline for parsing MySQL slow logs.", "processors" : [ { "grok" : { "field" : "message", "patterns" : [ "^# Time:%{INT:mysql.slowlog.date} %{SPACE} %{TIME:mysql.slowlog.time}\n# User@Host: %{USER:mysql.slowlog.user}(\\[[^\\]]+\\])? @ %{SPACE} \\[(%{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" : { "formats" : [ "UNIX" ], "ignore_failure" : true, "field" : "mysql.slowlog.timestamp", "target_field" : "@timestamp" } }, { "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 }}" } } ] } } ``` Thanks for your help! ---
**Author:** ![rosselg](https://avatars.discourse-cdn.com/v4/letter/r/f14d63/32.png) [@rosselg](https://discuss.elastic.co/u/rosselg)\ **Post date:** [February 20, 2018, 10:53am UTC](https://discuss.elastic.co/t/filebeat-slow-mysql-log/119973/6 "2018-02-20T10:53:32Z")
Hi, The problem was in the file _slowlog.yml_ I had to adapt the multiline pattern in this file Thanks for your help! G. ---
**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\ **Post date:** [March 20, 2018, 10:53am UTC](https://discuss.elastic.co/t/filebeat-slow-mysql-log/119973/7 "2018-03-20T10:53:51Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.