mysql general query log for a specific database or user?

Question

Is there a way to just log queries for a certain user or database? I know you can set the general log, but I don't want to log all queries across the entire MySQL instance. Rather I would like to only log queries against a specific database.

SET GLOBAL general_log = 'ON';

Is there a non GLOBAL parameter that can be set?

Answer 1

I accomplished this by using table logging and a cron job to copy entries from the general_log table to my database-specific log table and then truncate the general_log table. So it does have to log every query but you can isolate your database and the table only grows as big as needed to log the queries for your database (plus however many queries run in between executions of the cron job).

This is the procedure I used (for a database named db):

set global general_log = 'OFF';
alter table mysql.general_log ENGINE = MyISAM;
show create table mysql.general_log;

-- create a new table mysql.db_log using the above schema
-- i.e. just replace the table name.

set global log_output = 'TABLE';
set global general_log = 'ON';

The cron job looks like:

mysql -e "insert into mysql.db_log select * from mysql.general_log where user_host like '%[db]%"
mysql -e "truncate mysql.general_log"

Adjust the where clause to match whatever logs you want to save: database, user, ip, table name, etc.

Answer 2

There is no way to do it in MySQL, but you can grep general log file for specific database. If you are going to use pt-query-digest you can filter queries for selected database as well.