CODEWITHSUNDEEP
mysqlloggingmonitor
Maher
Maher

Reputation: 91

Monitoring queries of a MySQL user

is there any way to monitor/log a MySQL user and the queries he/she has ran? I had to turn off the general log using the query: 

SET GLOBAL general_log = 'OFF';

because otherwise the log file was no more processable by text editors. I was looking for a way to turn this log on for some users I want to monitor, but it does not seem to work that way.

Is there any other way to know everything a definite user does run on my database?

Thanks in advance.

Upvotes: 9

Views: 15387

Answers (3)

Rituparna Kashyap
Rituparna Kashyap

Reputation: 1507

There are a few ways I can suggest.

  1. SELECT * FROM information_schema.PROCESSLIST WHERE USER="someuser"; Now it is up to you what you use. Write a cronjob (linux) to store it into a file or write a MySQL event to enter it into a mysql table.
  2. Use General query log and use tools like linux awk, sed, grep etc to parse the log file to get desirable result

But if you are not accustom in writing command line linux command you can use mk-query-digest or even can configure custom monitoring tools like nagios, cacti etc. But I personally prefer MONyog, it does both point 1 and 2 perfectly and best of all it has a GUI.

enter image description here

Upvotes: 1

Ike Walker
Ike Walker

Reputation: 65547

Take a look at the mk-query-digest tool from maatkit, it may be able to do something similar to what you want. At the very least, it should be able to help you parse the general query log if you have to enable it again:

http://www.maatkit.org/doc/mk-query-digest.html

Upvotes: 0

ajreal
ajreal

Reputation: 47321

details on logging administration

MySQL Server can create a number of different log files to help you see what activity is taking place. See Section 5.2, “MySQL Server Logs”. However, you must clean up these files regularly to ensure that the logs do not take up too much disk space.

On other systems, you must install a short script yourself that you start from cron (or its equivalent) for handling log files.

You can force MySQL to start using new log files by flushing the logs. Log flushing occurs when you issue a FLUSH LOGS statement or execute a mysqladmin flush-logs, mysqladmin refresh, mysqldump --flush-logs, or mysqldump --master-data command.

Upvotes: 0

Related Questions