Logging of MySQL Queries and Forwarding to Syslog/Event log

Question

I was to able to enable Error log, General Query log, Slow Query Log and Binary Log and was able to forward the logs to Syslog. I have few questions about logging.

1) The error log is automatically forwarded to Event log in Windows. How do I forward the other logs to event log as well?

2) Is it possible to convert the multiline mysql logs to single line.

3) Is it possible to add some header to the log files or some tag at the beginning of each log line so that it is easy to identify which log file it is.

4) In the slow query logs, some information is commented. When forwarded using rsyslog, will these commented lines be forwarded in all the Unix systems?

# User@Host: debian-sys-maint[debian-sys-maint] @ localhost []
# Query_time: 0.000297  Lock_time: 0.000220 Rows_sent: 0  Rows_examined: 0
SET timestamp=1539924163;
select count(*) into @discard from `information_schema`.`EVENTS`;

5) mysqlbinlog can be used to convert the binary log to human readable format. Is there a way where I can convert the binary file and forward it to syslog without writing any custom script? Is there a way in mysql or rsyslog?

The `SET timestamp` is allided if it is the same as before. – Rick James Oct 26 '18 at 22:33 — Rick James, Oct 26 '18 at 22:33

Bill Karwin · Answer 1 · 2018-10-26T22:50:01.827

1) The error log is automatically forwarded to Event log in Windows. How do I forward the other logs to event log as well?

There is no feature to do this in MySQL.

2) Is it possible to convert the multiline mysql logs to single line.

You can't change the format of the slow-query log without modifying the MySQL source code. It's hard-coded:

  ...
  if (my_b_printf(&log_file,
                  "# Query_time: %s  Lock_time: %s"
                  " Rows_sent: %lu  Rows_examined: %lu\n",
                  query_time_buff, lock_time_buff,
                  (ulong)thd->get_sent_row_count(),
                  (ulong)thd->get_examined_row_count()) == (uint)-1)
    goto err;
  ...

And further, for the other lines for each query logged.

But you can get related query performance information from the PERFORMANCE_SCHEMA, and you can even get summaries by query type. There are advantages and disadvantages. See for example this blog: https://www.percona.com/blog/2014/02/11/performance_schema-vs-slow-query-log/

3) Is it possible to add some header to the log files or some tag at the beginning of each log line so that it is easy to identify which log file it is.

Not without changing the source code.

4) In the slow query logs, some information is commented. When forwarded using rsyslog, will these commented lines be forwarded in all the Unix systems?

You can't forward the slow query log to syslog.

5) mysqlbinlog can be used to convert the binary log to human readable format. Is there a way where I can convert the binary file and forward it to syslog without writing any custom script? Is there a way in mysql or rsyslog?

No.

But you can use the mysqlbinlog tool to download remote binary logs as a way of backing them up. See https://dev.mysql.com/doc/refman/8.0/en/mysqlbinlog-backup.html

Thank you Bill. I will check this. Any idea about the other questions? — Thunder Kay, Oct 26 '18 at 01:03
The timestamp in the logs seems to differ in 5.x and 8.0. Do you have any idea on what are all the possible timestamp formats in mysql logs? — Thunder Kay, Oct 30 '18 at 13:13
I don't know offhand. You can research the source code as easily as I can. I already gave you a link to the file that implements the slow query log output. — Bill Karwin, Oct 30 '18 at 22:54

Logging of MySQL Queries and Forwarding to Syslog/Event log

1 Answers1