mysql监控脚本

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

1:alert1.sh

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

#Mysqlhost='192.168.1.21'port='3306'user='root'password='……'#Mailsource='<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>&1if [ $? == 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 $contentfi

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

crontab -e

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

2.alert2.sh

#Mysqlhost='192.168.1.21'port='3306'user='root'password='……'#Mailsource='……'target='……'title1='Threads_connected is close to max_connections'MailUser='……'MailPassword='……'value=500content1='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 $content1else    echo '当前连接数尚在可控范围内'fi

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

crontab -e

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

3:check.sh

FilePath=/download/dandanTXT=$FilePath/mysql_system_check_$(date +%F-%H:%M).txt UserName='root'Password='……'cd $FilePathrm mysql*.txt#查看内存echo ' (1) memory: ' >> $TXT free -m >> $TXT #查看硬盘容量(注意：还要根据需要查看mysql所在目录磁盘容量，容量超过某个阀值，要报警)echo ' (2) disks information:                        ' >> $TXT df -h >> $TXT echo '(3) mysql status:' >> $TXTservice mysql status >> $TXTecho '(4) mysql uptime:' >> $TXTmysql -u $UserName -p$Password -e"SHOW STATUS LIKE '%uptime%'"|awk '/ptime/{ calc = $NF / 3600 / 24;print $(NF-1), calc" days" }' >> $TXTecho '(5) databases size' >> $TXTmysql -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;' >> $TXTecho '(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/ $TXTecho '服务器启动后已经同时使用的最大连接数:' >> $TXTv2=$(mysql -u$UserName -p$Password -e "show status like 'Max_used_connections'" | grep Max | cut -f 2)sed -i /服务器启动后已经同时使用的最大连接数:/s/$/$v2/ $TXTecho '试图连接服务器的连接数(不管成功与否):' >> $TXTv3=$(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 '' >> $TXTv4=$(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

有机会待继续补充。

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