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

2017年12月12日17:28:28 发表评论 1,875 views
摘要

最近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 '\t\t"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----------------------------

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

GO语言的进程管理工具
自动检查Mysql是否存在锁表、三方回调状态,队列状态,微信推送状态

问题: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的锁表问题,三方回调状态监测,队列的状态,就全部完成了;
更多精彩:Mysql相关文章

  • QQ精品交流群
  • weinxin
  • 微信公众号
  • weinxin
广告也精彩
admin

发表评论

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen: