8

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?

keeg
  • 3,990
  • 8
  • 49
  • 97
  • 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. – ravnur Dec 13 '13 at 21:45
  • oh cool, if you put that as an answer I'll accept it – keeg Dec 13 '13 at 21:53
  • The easiest solution is to log into a mysql table instead of a logfile and select only rows where the `user_host` column matches your user or database. See https://stackoverflow.com/questions/650238/how-to-show-the-last-queries-executed-on-mysql on how to log into a table. I know this still logs everything, but it makes it very easy to filter. – Christopher K. Jun 12 '18 at 06:03

3 Answers3

11

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.

Brian Deterling
  • 13,556
  • 4
  • 55
  • 59
4

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.

ravnur
  • 2,772
  • 19
  • 28
0

You may use pt-query-digest where databasename is your database name

pt-query-digest --filter '($event->{db} || "") =~ m/databasename/' /var/lib/mysql/mysql-slow.log > /var/tmp/slow-result.log
sharulhafiz
  • 1
  • 1