![检查mysql慢查询](https://linux22.com/image/599535/%E6%A3%80%E6%9F%A5mysql%E6%85%A2%E6%9F%A5%E8%AF%A2.png)
是否可以像这样监视 mysql:
- 如果某些查询运行超过 300 秒 - 将会发出警告
- 如果某些查询运行超过 500 秒 - 这将是至关重要的
我试过:
/usr/lib/nagios/plugins/check_mysql_health --hostname localhost --username icinga --password XXX --mode slow-queries --warning 300
但这仅显示每秒的 slow_queries 速率。如何才能按照我描述的方式监控 mysql?
感谢您的帮助。
Br,
答案1
检查 percona 的 mysql 脚本。有一些非常好的实用程序: http://www.percona.com/software/percona-toolkit http://www.percona.com/software/percona-monitoring-plugins
答案2
使用 Nagios 监控 MySQL 服务可用性是可以的,但肯定不是慢速查询。
我使用一个简单的 bash 脚本,该脚本在 crontab 中每 x 秒运行一次并扫描进程列表以捕获运行时间超过 180 秒的查询。希望它也适合您的情况:
#!/bin/bash
[email protected]
count=0
# capture all running queries
echo "<TABLE BORDER=1><TR><TH>Queries running more than 180 seconds</TH></TR></TABLE>" > /tmp/long_running_queries.htm
mysql -uroot -ppassword -s -e "SELECT now(), ID, USER, HOST, DB, COMMAND, TIME, STATE, INFO FROM information_schema.PROCESSLIST WHERE HOST not in ('%','localhost') AND COMMAND not in ('Sleep','Binlog Dump') AND TIME > 120;" > /tmp/all_running_queries.txt
echo "<TABLE BORDER=1>" >> /tmp/long_running_queries.htm
# store the output
while IFS= read -r ROW
do
count=$(($count + 1))
echo "<TR>" >> /tmp/long_running_queries.htm
echo "$ROW" >> /tmp/long_running_queries.htm
echo "</TR>" >> /tmp/long_running_queries.htm
done < /tmp/all_running_queries.txt
# if there are more than 2 long running queries then send the output from while loop above into mail
# else pruge the output
if (("$count" > "2"));
then
echo "</TABLE>" >> /tmp/long_running_queries.htm
Subject="$count SQL queries running for more than 180 Seconds"
Body="Some text"
echo $Body | mutt -a /tmp/long_running_queries.htm -s "$Subject" $Notify
else
echo "" > /tmp/long_running_queries.htm
fi