shell监控mysql发邮件_shell脚本监控线上db数据并发送邮件。

看到同事写了shell脚本通过访问线上的db，监控业务数据并发送邮件。蛮有意思的，记录下。

#!/bin/bash

myHome=`cd $(dirname $0); pwd`

cd $myHome

mailfile="$myHome/quantity-lock-error.`date +'%Y-%m-%d'`"

echo "sc_item_id,store_code,quantity,lock_quantity,occupy_quantity,gmt_modified" > $mailfile

sh check_quantity_lock.sh | tr -s '\t' ',' >> $mailfile

TO="abc@abc.com"

if [ `wc -l $mailfile | awk '{print $1}'` -ne 1 ]; then

$myHome/email -V -smtp-server 121.0.29.128 -smtp-port 25 -from-addr abc@ddd.com -from-name "abc" -subject "bcd"

-no-encoding $TO < $mailfile

else

wc -l $mailfile

fi

执行db的脚本；

#!/bin/bash

for i in `seq 0 4095`

do

scitemid=`echo $i `

tbIndex=`./calkey.sh $scitemid | head -1`

dbIndex=`./calkey.sh $scitemid | sed -n 2p`

dbHost=`./calkey.sh $scitemid | sed -n 3p | cut -d : -f 1`

port=`./calkey.sh $scitemid | sed -n 3p | cut -d : -f 2`

echo "use tmall_inventory_`printf %04d $dbIndex`;" > sql

echo "select sc_item_id, store_code, quantity, lock_quantity, occupy_quantity, gmt_modified from ipc_inventory_`printf %04d $tbIndex` where quantity < occupy_quantity and inventory_type=1 and app=1 and is_deleted=0 and gmt_modified > \"2013-04-01 00:00:00\"; " >> sql

mysql -h $dbHost -P $port -u username -ppassword < sql | grep -v sc_item_id

done

其中calkey.sh是辅助脚本，用来取得配置信息；

email 是一个发送邮件的脚本。