How to collect multiline mysql-slow-log to single line format in fluentd?

Question

I want to pass the following log

# Time: 210226 12:28:55
# User@Host: root[root] @ localhost []
# Query_time: 0.025196  Lock_time: 0.000074 Rows_sent: 10000  Rows_examined: 10000
SET timestamp=1614310135;
select * from posts;

My fluent.conf file configuration is as follows:

<source>
  type tail_asis_alternative
  path /var/log/mysql/slow.log
  pos_file /var/log/td-agent/pos/slow.log.pos
  tag raw.mysql-slowlog
</source>
<match raw.mysql-slowlog.**>
  type parser
  remove_prefix raw
  format /^(?<message>.*)/
  time_format %d-%b-%Y %H:%M:%S %Z
  key_name message
</match>

And the result I get is multiple lines not in a single line

20210226T122159+0900    mysql-slowlog.db0001    {"message":"# Time: 210226 12:21:59"}
20210226T122159+0900    mysql-slowlog.db0001    {"message":"# User@Host: root[root] @ localhost []"}
20210226T122159+0900    mysql-slowlog.db0001    {"message":"# Query_time: 0.028777  Lock_time: 0.000146 Rows_sent: 10000  Rows_examined: 10000"}
20210226T122159+0900    mysql-slowlog.db0001    {"message":"SET timestamp=1614309719;"}
20210226T122159+0900    mysql-slowlog.db0001    {"message":"select * from posts;"}

Answer 1

By the way, I can collect multiline MySQL-slow-log to a single line format in fluentd by using fluent-plugin-mysqlslowquerylog.

My fluent.conf file to forward log from database server to fluentdserver:

<source>
  @type tail
  path /var/log/mysql/slow.log
  format /^(?<message>.+)$/
  tag "slowlog.#{Socket.gethostname}" 
</source>

<match slowlog.*.*.*>
  type mysqlslowquerylog
  add_tag_prefix mysql.
</match>

<match mysql.slowlog.*.*.*>
  @type keep_forward
  heartbeat_type tcp
  phi_threshold 100
  buffer_type file
  buffer_path /var/log/td-agent/buffer/out_forward_mysqlslowlog
  flush_interval 30s
  retry_limit 20000
  max_retry_wait 2m
  flush_at_shutdown true

  <server>
    host fluentd001.xxx.com
    port 24220
    weight 50
  </server>

</match>

My configuration file from fluentd server to elasticsearch server.

Noted: we also need fluentd-plugin-forest to output plugin dynamically per tagpart,

<match mysql.slowlog.*.*.*>
  type forest
  subtype copy
  <template>
  <store>
    type elasticsearch
    hosts eslastic001.xxx.com
    port 9200
    buffer_type file
    buffer_path /var/log/td-agent/buffer/out_elasticsearch_mysqlslowlog
    logstash_format true
    logstash_prefix mysql.slowlog-${tag_parts[2]}.${tag_parts[3]}.${tag_parts[4]}
    flush_interval 30s
    retry_limit 20000
    flush_at_shutdown true
  </store>
  </template>
</match>

And here is the result from elasticsearch server

user:root[root] host:localhost query_time:0.04 lock_time:0 rows_sent:20,000 rows_examined:20,000 sql:SET timestamp=1614658245; select * from posts; @timestamp:Mar 2, 2021 @ 11:10:45.000 _id:bmgi8XcBSRAHV-rAiGNR _type:fluentd _index:mysql.slowlog-app001.xxx.com-2021.03.02 _score: -