监控MySQL用户的查询
有什么方法可以监控/记录 MySQL 用户和他/她运行的查询吗?我必须使用查询关闭常规日志:
SET GLOBAL general_log = 'OFF';
因为否则文本编辑器将无法再处理日志文件。我一直在寻找一种方法来为我想要监视的某些用户打开此日志,但它似乎不起作用。
有没有其他方法可以了解某个特定用户在我的数据库上运行的所有内容?
提前致谢。
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.
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(3)
我可以建议几种方法。
SELECT * FROM information_schema.PROCESSLIST WHERE USER="someuser";
现在由您决定使用什么。编写一个 cronjob (linux) 将其存储到文件中,或编写一个 MySQL 事件将其输入到 mysql 表中。但是如果您不习惯编写命令行linux命令,您可以使用
mk-query-digest
甚至可以配置自定义监控工具,例如nagios
、cacti
等。但我个人更喜欢MONyog,它完美地完成了第 1 点和第 2 点,最重要的是它有一个 GUI。There are a few ways I can suggest.
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.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 likenagios
,cacti
etc. But I personally prefer MONyog, it does both point 1 and 2 perfectly and best of all it has a GUI.日志管理详细信息
details on logging administration
看一下 maatkit 中的 mk-query-digest 工具,它也许可以做与你想要的类似的事情。至少,如果您必须再次启用它,它应该能够帮助您解析一般查询日志:
http://www.maatkit.org/doc/mk-query-digest.html
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