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 是一个发送邮件的脚本。