Named pipe for general log

MySQL & MariaDB Percona Server for MySQL 8.0
1

I’d like to use a named pipe for the general log to track the frequency of queries with specific columns in the where clause (don’t you love technical debt?)
I’ve used…

mkfifo [general.log]
chmod mysql:mysql [general.log]
grep -E ‘[string1]|[string2]|[stringN]’ [general.log] > [output]

to create the pipe and parse for the relevant strings, but attempting to enable the general log fails with the error : ERROR 1210 (HY000): Incorrect arguments to SET

Am I missing something or is it simply not possible to do this? I’m sure it used to work with earlier versions.

2

You can enable the general log to be stored in the mysql.general_log table instead of a log file. This allows you to query the general_log table using SELECT statements and apply a WHERE clause to retrieve specific details.

https://dev.mysql.com/doc/refman/8.0/en/log-destinations.html

To enable it :

SET GLOBAL general_log = 'ON';
SET GLOBAL log_output = 'TABLE';

and to persist it, add these inside my.cnf as well :

[mysqld]
general_log = 1
log_output = TABLE
3

Hello @Andrew2,
The general log is typically not used for ongoing query analysis due to its impact on MySQL, especially when used in table style. Your two best options for query monitoring with minimal impact would be the slow log + long_query_time=0, or using the audit log plugin. With the slow log, you can set rate sampling to lower any impact (though you shouldn’t see impact unless you are > 20,000 qps), and the audit log lets you filter on specific users.