自动检测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的锁表问题，三方回调状态监测，队列的状态，就全部完成了