mysql自动跑sql发邮件_mysql主从监控脚本含自动配置发Gmail邮件客户端

常用脚本备份

自动配置msmtp，使用mail发邮件

#!/bin/bash

SRC=/usr/local/src

cd $SRC

#install msmtp client

# if your want support TLS/SSL,install openssl-devel

# yum install -y openssl-devel  //

wget http://nchc.dl.sourceforge.net/project/msmtp/msmtp/1.4.28/msmtp-1.4.28.tar.bz2

tar xjf msmtp-1.4.28.tar.bz2

cd msmtp-1.4.28

./configure --prefix=/usr/local/msmtp && make && make install

cd /usr/local/msmtp/

mkdir etc

echo 'account gmail

host smtp.gmail.com

from reportonline@gmail.com

auth on

tls on

tls_starttls on

tls_force_sslv3 on

tls_trust_file /usr/local/msmtp/etc/gmail.crt

user reportonline@gmail.com

password 1234567890

port 587

syslog off

logfile /tmp/msmtp.log

account default: gmail'  >  /usr/local/msmtp/etc/msmtprc

wget -O /usr/local/msmtp/etc/gmail.crt http://www.geotrust.com/resources/extended-validation-ssl/certs/Equifax%20Secure%20Certificate%20Authority.crt

ln -s /usr/local/msmtp/bin/msmtp /usr/bin/

echo "set sendmail=/usr/bin/msmtp" >> /etc/mail.rc

脚本2

#!/bin/bash

#check MySQL_Slave Status

#crontab time every 10 min

test -e /data0/mysql/check_mysql_slave.log || touch /data0/mysql/check_mysql_slave.log

chown mysql.mysql /data0/mysql/check_mysql_slave.log

MYSQLPORT=`netstat -na|grep "LISTEN"|grep "3306"|awk -F[:" "]+ '{print $5}'`

MYSQLIP=`ifconfig eth0|grep "inet addr" | awk -F[:" "]+ '{print $4}'`

STATUS=$(/usr/local/mysql/bin/mysql -uadmin -ppassword -S /tmp/mysql.sock -e "show slave status\G" | grep -i "running")

IO_env=`echo $STATUS | grep IO | awk  ' {print $2}'`

SQL_env=`echo $STATUS | grep SQL | awk  '{print $2}'`

DATA=`date +"%y-%m-%d %H:%M:%S"`

if [ "$MYSQLPORT" == "3306" ]

then

echo "mysql is running"

else

mail -s "ERROR!server: $MYSQLIP mysql is down" start@gmail.com  -c user1@gmail.com -c user2@gmail.com -c user3@gmail.com

fi

if [ "$IO_env" = "Yes" -a "$SQL_env" = "Yes" ]

then

echo "Slave is running!"

else

echo "####### $DATA #########">> /data0/mysql/check_mysql_slave.log

echo "Slave is not running!" >>  /data0/mysql/check_mysql_slave.log

echo "Slave is not running!" | mail -s "ERROR! $MYSQLIP MySQL Slave is not running" start@gmail.com -c user1@gmail.com -c user2@gmail.com -c user3@gmail.com

fi

最近因实际需求，对脚本做了以下更改，添加了DNS的监控。与上一个有些区别的就是，经一发邮件出来。而不是有一个错就发会邮件出来。

#!/bin/bash  -x

#check MySQL_Slave Status and check dns status

#crontab time 00:10

test -e /data0/mysql/check_mysql_slave.log || touch /data0/mysql/check_mysql_slave.log

chown mysql.mysql /data0/mysql/check_mysql_slave.log

MYSQLPORT=`netstat -na|grep "LISTEN"|grep "3306"|awk -F[:" "]+ '{print $5}'`

MYSQLIP=`ifconfig eth0|grep "inet addr" | awk -F[:" "]+ '{print $4}'`

STATUS=$(/usr/local/mysql/bin/mysql -uadmin -ppassword -S /tmp/mysql.sock -e "show slave status\G" | grep -i "running")

IO_env=`echo $STATUS | grep IO | awk  ' {print $2}'`

SQL_env=`echo $STATUS | grep SQL | awk  '{print $2}'`

DATA=`date +"%y-%m-%d %H:%M:%S"`

> /data0/mysql/check_mysql_slave.log

if [ "$MYSQLPORT" == "3306" ]

then

echo "mysql is running"

else

echo "Mysql Server is not running!" >>  /data0/mysql/check_mysql_slave.log

fi

if [ "$IO_env" = "Yes" -a "$SQL_env" = "Yes" ];then

echo "Slave is running"

ps aux | grep bind | grep -v grep > /dev/null

if [ "$?" == "0" ];then

echo "Bind9 is running"

else

echo "####### $DATA #########">> /data0/mysql/check_mysql_slave.log

echo "Bind9 is not running!" >>  /data0/mysql/check_mysql_slave.log

fi

else

echo "####### $DATA #########">> /data0/mysql/check_mysql_slave.log

echo "Slave is not running!" >>  /data0/mysql/check_mysql_slave.log

fi

LINE=`wc -l /data0/mysql/check_mysql_slave.log | awk '{print $1}' `

if [ "$LINE" -gt "0" ];then

cat /data0/mysql/check_mysql_slave.log | mail -s "PLOBLEAM: DNS SERVER $MYSQLIP ERROR INFORMATION" baoch8@163.com

fi

线上机器运行

+ test -e /data0/mysql/check_mysql_slave.log

+ chown mysql.mysql /data0/mysql/check_mysql_slave.log

++ netstat -na

++ grep LISTEN

++ grep 3306

++ awk '-F[: ]+' '{print $5}'

+ MYSQLPORT=3306

++ ifconfig eth0

++ grep 'inet addr'

++ awk '-F[: ]+' '{print $4}'

+ MYSQLIP=118.X.X.X

++ /usr/local/mysql/bin/mysql -uadmin -ppassword -S /tmp/mysql.sock -e 'show slave status\G'

++ grep -i running

+ STATUS='             Slave_IO_Running: Yes

Slave_SQL_Running: Yes'

++ echo Slave_IO_Running: Yes Slave_SQL_Running: Yes

++ grep IO

++ awk ' {print $2}'

+ IO_env=Yes

++ echo Slave_IO_Running: Yes Slave_SQL_Running: Yes

++ grep SQL

++ awk '{print $2}'

+ SQL_env=Yes

++ date '+%y-%m-%d %H:%M:%S'

+ DATA='12-12-28 18:44:14'

+ '[' 3306 == 3306 ']'

+ echo 'mysql is running'

mysql is running

+ '[' YesYes = Yes -a YesYes = Yes ']'

+ echo 'Slave is running'

Slave is running

+ ps aux

+ grep bind

+ grep -v grep

+ '[' 0 == 0 ']'

+ echo 'Bind9 is running'

Bind9 is running

++ wc -l /data0/mysql/check_mysql_slave.log

++ awk '{print $1}'

+ LINE=5

+ '[' 5 -ne 0 ']'

+ cat /data0/mysql/check_mysql_slave.log

+ mail -s 'PLOBLEAM: DNS SERVER 118.X.X.X ERROR INFORMATION' baoch8@163.com

©著作权归作者所有：来自51CTO博客作者Bnsen的原创作品，如需转载，请注明出处，否则将追究法律责任

shellmsmtpmysql主从Shell大汇总