自动检查Mysql是否存在锁表、三方回调状态，队列状态，微信推送状态

最近mysql总会出现锁表，具体原因涉及公司机密不详细说明
我习惯性的把重复的工作，都部署成脚本，这就保证对业务的实时监控，如果可以的话，多重的监控也是必不可少的，比如zabbix 它很全面，如果您对此不是很了解，请查看，Zabbib是如何做监控的，今天我们主要是利用shell，对Mysql的锁表、三方回调状态，队列状态及简单自愈，并添加微信消息推送，把业务掌控在手中，下面就来详细说明：

一、MySQL锁表简单处理过程：

1，登陆MySQL 查看进程状态

mysql> show processlist;

2，查看mysql innoDB表INNODB_TRX，是否存在锁定的事务线程

SELECT * FROM information_schema.INNODB_TRX G;

3，查询到导致锁表的事务进程ID

kill 事务进程ID

请跟进业务情况，自行决定是否需要kill，因为kill掉此事物将不在执行，可能导致数据无法更新。

二、脚本拟写

1，创建脚本文件

vim /server/scripts/check.sh chmod +x /server/scripts/check.sh

2，监控脚本

#!/bin/bash --login
#---------------------------------------------------------
# $Name:         check.sh
# $Version:      v1.0
# $Author:       qiuyuetao
# $organization: www.dgstack.cn
# $Create Date:  2017-12-12
# $Description:  Check Queue, Yepay Call, DB Lock
#---------------------------------------------------------
DATE=`date +%F-%H-%M`
#source ~/.bash_profile 
source /etc/profile
export PATH=/usr/local/bin/python2.7:/application/mysql/bin:/usr/lib64/qt-3.3/bin:/usr/local/sbin:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin:/root/bin:/root/bin
#WeiXin ENV-------------------------------------------------------------------------------------
CropID='wx3ac3f***8e63de' #企业ID
Secret='MnOkjVx9F6WY******ZsmwUZqNHQ2KrTgOo' #创建的应用SecretID
APIURL="https://qyapi.weixin.qq.com/cgi-bin/gettoken?corpid=$CropID&corpsecret=$Secret"
TOKEN=$(/usr/bin/curl -s -G $APIURL | awk -F" '{print $10}')
POSTURL="https://qyapi.weixin.qq.com/cgi-bin/message/send?access_token=$TOKEN"
##WeiXin body--------------------------------------------------------------------------------------
function body() {
local int AppID=1000005  #APPID
local UserID=guozhiheng0123   #用户
local PartyID=2  #部门ID
printf '{n'
printf 't"touser": "'"$UserID""",n"
printf 't"toparty": "'"$PartyID""",n"
printf 't"msgtype": "text",n'
printf 't"agentid": "'"$AppID""",n"
printf 't"text": {n'
printf 'tt"content": "'192.168.0.38--"$Msg"""n"
printf 't},n'
printf 't"safe":"0"n'
printf '}n'
}
##Check Queue--------------------------------------------------------------------------------------
function ckqueue() {
CKUS="队列管理用户"
CKPW="队列管理密码"
CKLIST="/root/shell/check/ckqueue/cklist.txt"
CKQULOG="/root/shell/check/ckqueue/ckqulog.txt"
#ARTCON=`ps -ef |grep artisan |grep -v grep |wc -l`
ARTCON=`ps -ef |grep artisan |egrep -v "grep|Entrance" |wc -l`
for line in `cat $CKLIST`
do
curl -s -u "$CKUS":"$CKPW" $line |grep 'status":"running' > /dev/null
if [ $? -eq 0 ];then
echo "---------------------------------------"
echo $DATE $line is running...
echo "---------------------------------------"
echo $DATE $line >> $CKQULOG
if [ $ARTCON -ne 16 ]; then
echo $ARTCON >> /tmp/1.log
echo "---------------------------------------"
echo "php artisan队列不等于16个，已自动处理，请检查正确性"
echo "---------------------------------------"
ps -elf |grep gosuv  |grep -v grep |awk '{print $4}' |xargs kill -9
ps -elf |grep artisan |grep -v grep |awk '{print $4}' |xargs kill -9
/usr/local/bin/gosuv start-server -c /root/.gosuv/config.yml
sleep 5
Msg="php artisan队列不等于16个，已自动处理，请检查正确性"
echo  "$(body guozhiheng0123 $2 )" $POSTURL
/usr/bin/curl --data-ascii "$(body guozhiheng0123 $2)" $POSTURL
break;
fi 
else 
ps -elf |grep gosuv  |grep -v grep |awk '{print $4}' |xargs kill -9
ps -elf |grep artisan |grep -v grep |awk '{print $4}' |xargs kill -9
/usr/local/bin/gosuv start-server -c /root/.gosuv/config.yml
sleep 5
echo "---------------------------------------"
echo  "gosuv队列Not running，已自动处理，请检查正确性"
echo "---------------------------------------"
echo $DATE $line is fail >> $CKQULOG
Msg="gosuv队列Not running，已自动处理，请检查正确性"
echo  "$(body guozhiheng0123 $2 )" $POSTURL
/usr/bin/curl --data-ascii "$(body guozhiheng0123 $2)" $POSTURL
break;
fi 
done
}
##Check yepay call--------------------------------------------------------------------------------------
function ckcall() {
ACCFILE="/home/logs/ghzz.yilonghc.com/access/www.yilongzc.com.log"
ALLLOG="/root/shell/check/ckcall/ckcallall.log"
FAILLOG="/root/shell/check/ckcall/ckcallfail.log"
LANG=en_US.UTF-8
Y=`date +%d/%b/%Y`
M=`date -d "10 minute ago" |awk '{print $4}' |awk -F ':' '{print $1":"$2}' |cut -c1-4`
grep ''"$Y":"$M"'' $ACCFILE |grep 'POST /Confluence/progress' > $ALLLOG
echo "---------------------------------------"
echo "前10分钟之内银联回调日志(如果为空，则无业务)"
grep ''"$Y":"$M"'' $ACCFILE |grep 'POST /Confluence/progress'
echo "---------------------------------------"
cat $ALLLOG | while read line
do 
STATUS=`echo $line |awk '{ print $8 }' |awk -F '"' '{ print $2 }'`
if [ "$STATUS" != "200" ] && [ "$STATUS" != "301" ] && [ "$STATUS" != "302" ] ;then
echo "---------------------------------------"
echo ""$Y":"$M" --银联回调有失败状态(日志状态不等于200,301,302) 请检查"
echo "---------------------------------------"
echo $LINE >> $FAILLOG
Msg=""$Y":"$M" --银联回调有失败状态(日志状态不等于200,301,302) 请检查"
echo  "$(body guozhiheng0123 $2 )" $POSTURL
/usr/bin/curl --data-ascii "$(body guozhiheng0123 $2)" $POSTURL
fi
done
}
##Check DB LOCK--------------------------------------------------------------------------------------
function ckdblock() {
CKHOST="数据库主机IP"
CKDB="information_schema"
CKUSER="数据库管理权限用户"
CKPWD="数据库管理权限用户密码"
CKMLLOG="/root/shell/check/cklock/cklock.txt"
LKCON=`/application/mysql/bin/mysql -h "$CKHOST" -u "$CKUSER" -p"$CKPWD" -e "SELECT count(*)  FROM "$CKDB".INNODB_LOCKS" |tr -d 'count(*) '`
LKTAB=`/application/mysql/bin/mysql -h "$CKHOST" -u "$CKUSER" -p"$CKPWD" -e "SELECT lock_table FROM "$CKDB".INNODB_LOCKS"`
/application/mysql/bin/mysql -h "$CKHOST" -u "$CKUSER" -p"$CKPWD" -e "SELECT lock_table FROM "$CKDB".INNODB_LOCKS" |grep 'lar_jobs'
JOBSTAT=$?
echo "---------------------------------------"
echo "目前锁表数量： $LKCON"
echo "目前锁表名字： $LKTAB"
echo "---------------------------------------"
if [ "$LKCON" -gt 0 ]; then
/application/mysql/bin/mysql -h "$CKHOST" -u "$CKUSER" -p"$CKPWD"  -e "SELECT * FROM "$CKDB".innodb_trx G"  >> $CKMLLOG
if [ "$JOBSTAT" -eq 0 ];then
echo "---------------------------------------"
echo "有锁定队列jobs表，脚本正在进行自动处理..."
echo "---------------------------------------"
ps -elf |grep gosuv   |grep -v grep |awk '{print $4}' |xargs kill -9
ps -elf |grep artisan |grep -v grep |awk '{print $4}' |xargs kill -9
MLP=$( /application/mysql/bin/mysql -h "$CKHOST" -u "$CKUSER" -p"$CKPWD"  -e "SELECT trx_mysql_thread_id FROM "$CKDB".innodb_trx  where trx_query like '%update `lar_jobs`%'" |tr -d 'trx_mysql_thread_id  ')
for k in $MLP
do
echo "---------------------------------------"
echo "$DATE" --------------- kill "$k"
echo "---------------------------------------"
echo "$DATE" --------------- kill "$k" >> $CKMLLOG
/application/mysql/bin/mysql -h "$CKHOST" -u "$CKUSER" -p"$CKPWD" -e "kill $k"
done
sleep 2
/usr/local/gosuv/gosuv start-server -c /root/.gosuv/config.yml
echo "---------------------------------------"
echo "$LKTAB --IS-LOCK, Kill MysqlProcess: $MLP,如果依然有报警，请手工介入处理"
echo "---------------------------------------"
Msg="$LKTAB --IS-LOCK, Kill MysqlProcess: $MLP,如果依然有报警，请手工介入处理"
echo  "$(body guozhiheng0123 $2 )" $POSTURL
/usr/bin/curl --data-ascii "$(body guozhiheng0123 $2)" $POSTURL
else
echo "---------------------------------------"
echo ""$LKTAB --IS-LOCK, 为保证数据完整性，此表请手工介入处理""
echo "---------------------------------------"
Msg="$LKTAB --IS-LOCK, 为保证数据完整性，此表请手工介入处理"
echo  "$(body guozhiheng0123 $2 )" $POSTURL
/usr/bin/curl --data-ascii "$(body guozhiheng0123 $2)" $POSTURL
fi
fi
}
##Main--------------------------------------------------------------------------------------
main(){
ckdblock;
ckqueue;
ckcall;
}
main
##END--------------------------------------------------------------------------------------
##END----------------------------

队列状态异常，会微信通知，并且自动处理故障

问题：MySQL5.6版本以后才出现的Using a password on the command line interface can be insecure.

[root@DB01 ~]# cat /etc/my.cnf |grep  -A 2 "mysqldump" [mysqldump] user=root password=你的mysql密码 [root@DB01 ~]# chmod 600 /etc/my.cnf    #配置文件权限最小化 [root@DB01 ~]# /etc/init.d/mysqld restart Shutting down MySQL.... SUCCESS!  Starting MySQL. SUCCESS!

至此关于mysql的锁表问题，三方回调状态监测，队列的状态，就全部完成了