返回顶部

收藏

shell脚本 监控mysql占cpu负载并发送日志记录

更多

对mysql的cpu进行监控,并做日志记录和发送功能,分为两个脚本,分别为mysqlstat.sh和send_mysqlstat_log.sh , 前面是负载监控负载和记录日志,后面的进行日志的发送,将两个脚本部署到服务器后,在服务器的crontab中分别设置计划任 务,mysqlstat.sh脚本每3分钟执行一次,send_mysqlstat_log.sh脚本每天早上9点和下午17点执行,这样可得到当天白天 和前天晚上的日志记录,便于及时处理,以下是相关脚本内容:

1、监控mysql cup资源脚本 mysqlstat.sh

#!/bin/sh
##mysql负载监控脚本
##主要功能是对mysql的cpu资源率进行监控,如果超过一定限制则进行重启,并到记录日志中
#written by cuiyang 20081118

#定义日志文件的位置
log=/home/admin/bin/mysqlstat.log
#mysql的cpu资源率的限制值
load=80

#对mysql的端口进行监控
#PORT=`netstat -na | grep "LISTEN" | grep "3306" | awk '{print $4}' | awk -F. '{print $2}'`
#if [ "$PORT" -eq "3306" ]

#对mysql服务器的健康状态进行监控,比监控端口更有效
/usr/local/mysql/bin/mysqladmin -uroot -p12345 ping > /dev/null 2>&1
if [ $? = 0 ]
        then
        mysql_cpu=`top -U root -b -n 2 | grep mysql | awk '{print $10}'|awk -F. '{print $1}'`
        if [ "$mysql_cpu" -ge "$load" ]
                then
                echo `date` | awk '{print $6"-"$2"-"$3" "$4"\<br\>"}' >> $log
                ps xww |grep 'bin/mysqld_safe' |grep -v grep | awk '{print $1}' | xargs kill -9
                ps xww |grep 'libexec/mysqld' |grep -v grep | awk '{print $1}' | xargs kill -9
                sleep 10
                /usr/local/mysql/bin/mysqld_safe --user=root > /dev/null &
        else
                exit 0
        fi
else
        ps xww |grep 'bin/mysqld_safe' |grep -v grep | awk '{print $1}' | xargs kill -9
        ps xww |grep 'libexec/mysqld' |grep -v grep | awk '{print $1}' | xargs kill -9
        sleep 5
        /usr/local/mysql/bin/mysqld_safe --user=root > /dev/null &
fi

2、发送日志邮件脚本 send_mysqlstat_log.sh

#!/bin/sh
##### 发送mysql负载监控日志脚本,当mysql监控负载日志中的记录条数超过一定数量时即将日志发送到指定邮箱
# 定义日志文件和邮件临时文件的位置
log=/home/admin/bin/mysqlstat.log
mtxt=/home/admin/bin/mtxt.txt

# 定义发送记录的阀值
Threshold=10

#定义邮件地址
To=cuiyang@xxxxx.com
From=mysqlstat@xxxxx.com

#定义邮件主题
Subject="`hostname -s` mysqlstat "

#建立发送邮件的函数
send_mail()
{
        echo "To: $To" > $mtxt
        echo "From: $From" >> $mtxt
        echo "Subject: $Subject " >> $mtxt
        echo "Content-Type: text/html; charset=gb2312" >> $mtxt
        echo >> $mtxt
        echo "<HTML>" >> $mtxt
        echo "<HEAD>" >> $mtxt
        echo "<TITLE>$Subject]</TITLE>" >> $mtxt
        echo "<META HTTP-EQUIV='Content-Type' CONTENT='text/html; charset=gb2312'>" >> $mtxt
        echo "<STYLE TYPE='TEXT/CSS'>" >> $mtxt
        echo "</STYLE>" >> $mtxt
        echo "</HEAD>" >> $mtxt
        echo "<BODY>" >> $mtxt
        cat $log >> $mtxt
        echo "</BODY>" >> $mtxt
        echo "</HTML>" >> $mtxt
        /usr/sbin/sendmail -fadmin -t < $mtxt
        echo > $log
        echo > $mtxt
}

Number=`cat $log |wc -l`

if [ "$Number" -ge "$Threshold" ]
        then
        send_mail
else
        echo > $log
fi

3、再贴一下服务器上/etc/crontab里的任务设置

####monitor Mysql load
*/3 * * * * root /home/admin/bin/mysqlstat.sh
0 9,17 * * * root /home/admin/bin/send_mysqlstat_log.sh

标签:shell,监控,mysql,cpu

收藏

0人收藏

支持

0

反对

0