Netdata logs everything to mysql logs

Question

Since I enabled mariadb logs for all queries on my server, using Netdata shows me each second these queries:

SHOW GLOBAL VARIABLES LIKE 'max_connections'
11 Query     SHOW GLOBAL STATUS
11 Query     commit

I have to say it's not helping me to invest what's going on on the server because it's polluting my logs.

Is there a way to avoid logging this kind of query each sec?

Answer 1

While it's probably wiser to specify the exact problem with your database instead of trying to clean up the log (I'd suggset using grep or sed for that). But here's an over-engineered solution, which will solve your logging problem once and for all.

We'll be redirecting mysql logs to a fifo pipe, which will be read & filtered by syslog-ng.

To do that go to the folder, where general-log-file is stored, delete/rename the original log & create a fifo pipe on its place with the command:

mkfifo mysql-general.log

check your my.cnf which should point to that file:

[mysqld]
general-log-file = /path/to/your/log/mysql-general.log

Then, configure your /etc/syslog-ng/syslog-ng.conf (centos 7 would actually require installing it first) to read the FIFO pipe and filter it as needed while writing result into the final file..

Here's an example configuration, that suits your needs:

source s_mysql_general_log { pipe("/path/to/your/log/mysql-general.log" program_override("mysql-general-log")); };
filter f_mysql_general_log { not match("^SHOW GLOBAL VARIABLES LIKE 'max_connections'") and not match("^11 Query     SHOW GLOBAL STATUS") and not match("^11 Query     commit"); };
destination d_mysql_general_log { file("/var/log/mysql-general.log"); }
log { source(s_mysql_general_log); filter(f_mysql_general_log); destination(d_mysql_general_log); flags(final);   };

As a result, you'll get /var/log/mysql-general.log file without queries you dislike... I'll try to post a systemd-only version of this solution a bit later