Shulou(Shulou.com)06/01 Report--

MYSQL automatically sends slow SQL scripts to developers

Recently, I wrote a script that automatically analyzes slow sql in the last 5 minutes when the average 5-minute load of mysql database cpu is higher than 18, and sends slow sql to the corresponding developers for optimization.

At the same time, use pt-kill command to run slow sql for more than 5 minutes to make mysql database failure self-healing

[root@MySQL02 ~]# cat /usr/local/script/send_slow_sql.sh

Click here to fold or open

#!/ bin/bash

top5=`uptime |awk '{print $12}' |awk -F',' '{print $1}' |awk -F'. ' '{print $1}'`

if [ $top5 -gt 18 ];then

echo -e "Hello everyone: \nIn the last 5 minutes, the average CPU load of mysql database (172.16.2.4) is: `uptime| awk -F'users,''{print $2}'`\n is caused by the following slow sql, please find your own responsible slow sql to optimize, thank you. " > /log/slow/report.txt

#echo -e "\n Description: The following content is obtained by pt-query-digest analysis of slow logs. If you have questions about the following content, you can visit http://blog.csdn.net/seteor/article/details/24017913 for reference. " >> /log/slow/report.txt

echo -e "\n Description: The following content is obtained by pt-query-digest tool analysis slow log, if you have questions about the following content, you can Baidu pt-query-digest report interpretation. " >> /log/slow/report.txt

echo -e "\n In addition, this email is automatically triggered by monitoring script, please do not reply to this email, thank you! " >> /log/slow/report.txt

pt-query-digest --since=5m /log/slow/slow-query.log >> /log/slow/report.txt

cat /log/slow/report.txt |mail -s "Slow sql occurred in mysql database in the last 5 minutes" chenzhixin@abc.com zhangqiang@abc.com #Use pt-kill command to kill slow sql running for more than 5 minutes, pt-kill name automatically exits after 3 minutes in the background, the purpose of doing this is to make mysql fault self-healing

pt-kill --no-version-check --host=localhost --user=root --password='hy_QWSA_root' --port=3306 --daemonize --log=/log/killed_query.log --run-time=180s --busy-time=300s --idle-time=5s --match-command=Query --victims all --kill

Then put the above send_slow_sql.sh into crontab and call it automatically once a minute.

[root@MySQL02 ~]# crontab -l

#Send slow sql to the appropriate person

* * * * * /usr/local/script/send_slow_sql.sh

Finally, I got the following email:

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.