mysql监控脚本

共分两个脚本，脚本alert.sh是用于监控报警（邮件通知），脚本check.sh只用于监控，不报警。

1:alert1.sh

监控mysql实例状态，当mysql停止服务了，自动报警。

#Mysql


host='192.168.1.21'
port='3306'
user='root'
password='……'


#Mail


source='<span style="font-family:Arial, Helvetica, sans-serif;">……</span>'
target='……'
title='mysql has stopped'
MailUser='……'
MailPassword='……'
content='ERROR! MySQL is not running,please start it and find the reason,then deal with it'


##Check mysql status 
mysql --host=$host --port=$port --user=$user --password=$password  -e "show databases;" > /dev/null 2>&1
if [ $? == 0 ]
then
    echo " $host mysql login successfully "
else
    echo " $host mysq login faild"
#设置邮件报警
    /usr/local/bin/sendEmail -f $source -t $target  -s smtp.163.com -u $title -xu $MailUser -xp $MailPassword -m $content
fi

设置crontab，每5分钟监控一次：

crontab -e

*/5 * * * *  /download/dandan/alert1.sh >>/download/dandan/alert1_.log 2>&1

2.alert2.sh

#Mysql

host='192.168.1.21'
port='3306'
user='root'
password='……'

#Mail

source='……'
target='……'
title1='Threads_connected is close to max_connections'
MailUser='……'
MailPassword='……'
value=500
content1='Threads_connected is close to max_connections,please deal with it as soom as possible'


v1=$(mysql --host=$host --port=$port --user=$user --password=$password  -e "show variables like 'max_connections';" | grep max | cut -f 2)
v2=$(echo $v1-$value | bc)
v3=$(mysql --host=$host --port=$port --user=$user --password=$password -e "show status like 'Threads_connected';"  | grep Thr | cut -f 2)

if [ $v3 -gt $v2 ]
then
    echo '当前连接数为:'$v3',快接近最大连接数，请尽快处理'
    /usr/local/bin/sendEmail -f $source -t $target  -s smtp.163.com -u $title1 -xu $MailUser -xp $MailPassword -m $content1
else
    echo '当前连接数尚在可控范围内'
fi

设置crontab，每5分钟监控一次：

crontab -e

*/5 * * * *  /download/dandan/alert2.sh >>/download/dandan/alert2_.log 2>&1

3:check.sh

FilePath=/download/dandan
TXT=$FilePath/mysql_system_check_$(date +%F-%H:%M).txt 
UserName='root'
Password='……'

cd $FilePath
rm mysql*.txt
#查看内存
echo ' 
(1) memory:
 ' >> $TXT 

free -m >> $TXT 

#查看硬盘容量(注意：还要根据需要查看mysql所在目录磁盘容量，容量超过某个阀值，要报警)
echo ' 

(2) disks information:
                        ' >> $TXT 
df -h >> $TXT 

echo '

(3) mysql status:
' >> $TXT

service mysql status >> $TXT

echo '

(4) mysql uptime:
' >> $TXT

mysql -u $UserName -p$Password -e"SHOW STATUS LIKE '%uptime%'"|awk '/ptime/{ calc = $NF / 3600 / 24;print $(NF-1), calc" days" }' >> $TXT

echo '

(5) databases size
' >> $TXT

mysql -u $UserName -p$Password -e 'select table_schema,round(sum(data_length+index_length)/1024/1024,4) as "value(MB)" from information_schema.tables group by table_schema;' >> $TXT

echo '

(6) connection information: 

当前连接数:' >> $TXT  

v1=$(mysql -u$UserName -p$Password -e "show status like 'Threads_connected';"  | grep Thr | cut -f 2)

sed -i /当前连接数:/s/$/$v1/ $TXT

echo '
服务器启动后已经同时使用的最大连接数:' >> $TXT

v2=$(mysql -u$UserName -p$Password -e "show status like 'Max_used_connections'" | grep Max | cut -f 2)

sed -i /服务器启动后已经同时使用的最大连接数:/s/$/$v2/ $TXT

echo '
试图连接服务器的连接数(不管成功与否):' >> $TXT

v3=$(mysql -u$UserName -p$Password -e "show status like 'connections'" | grep Con | cut -f 2)

sed -i /试图连接服务器的连接数/s/$/$v3/ $TXT


#(7) InnoDB Buffer命中率:

#Innodb_buffer_read_hits = (1 - innodb_buffer_pool_reads / innodb_buffer_pool_read_requests) * 100%

echo '
' >> $TXT

v4=$(mysql -u$UserName -p$Password -e "show status like 'Innodb_buffer_pool_reads'" | grep Inn | cut -f 2)

v5=$(mysql -u$UserName -p$Password -e "show status like 'Innodb_buffer_pool_read_requests'" | grep Inn | cut -f 2)

printf "(7) InnoDB Buffer命中率为: %.2f%%\n" `echo "scale=4;(($v5-$v4)/$v5)*100"|bc` >> $TXT

#(6) key Buffer 命中率
#(7) Query Cache命中率

#
cat $TXT

有机会待继续补充。

注：用户名，密码，邮箱等信息请根据自己实际情况进行修改。