Working with MySQL Error Logs

[Rhys Campbell]

Hi All,

I've setup Fluentd/ElasticSearch/Kibana and pointed a lot of server at it (syslog only). I've been looking at getting the mysql error log into this but couldn't find a plugin for this so I've added it as a tail. This seems like a bit of a fudge as it doesn't include all data (hostname etc).

Does anyone have a good method of doing this? I might just tell MySQL to log to syslog if not.

Cheers,

Rhys

[Kiyoto Tamura]

Hy Rhys,

Thanks for using EFK =)

In general, there are a couple of scenarios for MySQL error logs:

1. in the data directory with <hostname>.err. I believe this is the default

2. with --log-error option.

3. with --syslog option.

You are talking about 3, right?

--
You received this message because you are subscribed to the Google Groups "Fluentd Google Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to fluentd+u...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

--

Check out Fluentd, the open source data collector for high-volume data streams

[Kiyoto Tamura]

Sorry, I misread your comment. I am guessing you have 2 right now (but with MySQL's rather nonstandard error log format) and wish to add hostname. And you are suggesting 3 as an alternative.

I think 3 + in_syslog should be perfectly fine, unless you do not want to collect non-MySQL related syslogs.

Also, you can "add" hostname as part of the filter pipeline after the data is . I am writing a tutorial to show how to do that right now.

Kiyoto

[Rhys Campbell]

Hi Kiyoto

I'm pretty much happy to use any mthod but I am using syslog for other stuff as well. Currently MySQL error log entries are added like this...

{
  "_index": "logstash-2014.06.17",
  "_type": "fluentd",
  "_id": "09pcufOPSY-IYxbhJ3XRCA",
  "_score": null,
  "_source": {
    "message": "Version: '5.5.36-MariaDB-log'  socket: '/var/lib/mysql/mysql.sock'  port: 3307  MariaDB Server",
    "@timestamp": "2014-06-17T19:05:59+01:00"
  },
  "sort": [
    1403028359000,
    1403028359000
  ]
}

If I could add a custom field to this then I think that would work for me. Is it possible to do this dynamically, i.e. determine the hostname as appropriate rather than hard coding and duplicating inputs?

Cheers,

Rhys

[Kiyoto Tamura]

> If I could add a custom field to this then I think that would work for me. Is it possible to do this dynamically, i.e. determine the hostname as appropriate rather than hard coding and duplicating inputs?

Yes. I am writing a solution guide on www.fluentd.org right now and will follow up on this thread with the link. I will point you to it. Essentially, you would either use the new v1 config (Socket.gethostname) or fluent-mixin-config-placeholder.

Kiyoto

[Kiyoto Tamura]

Hi Rhys,

Here is a generic guide that shows how to add a hostname to events in general: http://www.fluentd.org/guides/recipes/apache-add-hostname

It looks like you are running a MariaDB instance. Let me spin up one, make sure my config works against it, and share it with you.

Kiyoto

[Rhys Campbell]

Hi Kiyoto,

Perfect, sorted it from that.

Thanks again.

Rhys

[Kiyoto Tamura]

Hi Rhys,

I finally got my hands on MariaDB source code. It looks like there is really no structure to speak of for the error logs, and it can (often does) span multiple lines.

Try the following config:

<source>
  type tail
  path mysql_error/log/error.log
  format multiline
  format_firstline /^\d{6} \d\d:\d\d:\d\d/
  format1 /(?<time>\d{6} \d\d:\d\d:\d\d) (?<error_msg>.*)/
  time_format %y%m%d %H:%M:%S
  tag mariadb.error
</source>

<match mariadb.*>
  type elasticsearch

  logstash_format true

  # THE REST OF YOUR OUT_ELASTICSEARCH OPTIONS

</match>

When i output to stdout, the data looks like this after it goes through tail:

2014-06-20 02:13:28 +0000 [info]: adding source type="tail"
2014-06-20 02:13:28 +0000 [warn]: 'pos_file PATH' parameter is not set to a 'tail' source.
2014-06-20 02:13:28 +0000 [warn]: this parameter is highly recommended to save the position to resume tailing.
2014-06-20 02:13:28 +0000 [info]: adding match pattern="mariadb.*" type="stdout"
2014-06-20 02:13:28 +0000 [info]: following tail of mysql_error/log/error.log
2014-06-19 23:38:30 +0000 mariadb.error: {"error_msg":"mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql"}
2014-06-19 23:38:30 +0000 mariadb.error: {"error_msg":"InnoDB: The InnoDB memory heap is disabled"}
2014-06-19 23:38:30 +0000 mariadb.error: {"error_msg":"InnoDB: Mutexes and rw_locks use GCC atomic builtins"}
2014-06-19 23:38:30 +0000 mariadb.error: {"error_msg":"InnoDB: Compressed tables use zlib 1.2.8"}
2014-06-19 23:38:30 +0000 mariadb.error: {"error_msg":"InnoDB: Using Linux native AIO"}
2014-06-19 23:38:30 +0000 mariadb.error: {"error_msg":"InnoDB: Initializing buffer pool, size = 128.0M"}
2014-06-19 23:38:30 +0000 mariadb.error: {"error_msg":"InnoDB: Completed initialization of buffer pool\nInnoDB: The first specified data file ./ibdata1 did not exist:\nInnoDB: a new database to be created!"}

Let me know if you need more help.

Kiyoto

[Kiyoto Tamura]

Rhys-

I forgot to mention one thing: you need to point the "path" parameter to where you have your MariaDB logs. You probably have a better idea of where it is =)

Kiyoto