Geek Answers Handbook

Monitoring MySQL user queries

Is there a way to track / log in the MySQL user and the queries that he / she was doing? I had to disable the general log using the request:

SET GLOBAL general_log = 'OFF';

because otherwise the log file was no longer processed by text editors. I was looking for a way to enable this log for some users that I want to control, but it seems like this is not the case.

Is there any other way to find out that a particular user is working in my database?

Thanks in advance.

+7
source share
3 answers

There are several ways to suggest.

  • SELECT * FROM information_schema.PROCESSLIST WHERE USER="someuser"; Now you decide what you use. Write cronjob (linux) to save it to a file or write a MySQL event to inject it into the mysql table.
  • Use a common query log and use tools like linux awk, sed, grep, etc. to parse the log file to get the desired result.

But if you don't know how to write the linux command line, you can use mk-query-digest or even set up custom monitoring tools like nagios , cacti , etc. But I personally prefer MONyog , it works great for both point 1 and 2, and best of all has a graphical interface.

enter image description here

+1
source

logging administration information

MySQL Server can create several different log files to help you understand what is going on. See Section 5.2 "MySQL Server Logs". However, you should regularly clean these files to ensure that the logs do not take up too much disk space .

On other systems, you must install a short script yourself, which you start with cron (or its equivalent) to process the log files.

You can get MySQL to start using new log files by clearing the logs. Logs are reset when you issue the FLUSH LOGS statement or execute the mysqladmin flush-logs, mysqladmin refresh, mysqldump-flush-logs, or mysqldump -master-data commands.

0
source

Take a look at maatkit's mk-query-digest tool, it can do something similar to what you want. At the very least, it should help you parse the general query log if you need to enable it again:

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

0
source

More articles:


All Articles