监视MySQL用户的查询 [英] Monitoring queries of a MySQL user

问题描述

有什么方法可以监视/记录MySQL用户以及他/她已经运行的查询吗?我必须使用查询关闭常规日志:

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?

谢谢.

推荐答案

我可以建议几种方法.

There are a few ways I can suggest.

1. SELECT * FROM information_schema.PROCESSLIST WHERE USER="someuser";现在由您决定使用什么.编写一个cronjob(linux)以将其存储到文件中，或者编写一个MySQL事件以将其输入到mysql表中.
2. 使用常规查询日志并使用linux awk，sed，grep等工具来解析日志文件以获得理想的结果

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

但是，如果您不习惯编写命令行linux命令，则可以使用mk-query-digest甚至可以配置自定义监视工具，例如nagios，cacti等.但是我个人更喜欢

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.

这篇关于监视MySQL用户的查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！