3.MySQL5.7锁自动化检查

vi mysql_lock_check_for_5.7.sh 

#!/bin/bash
mysql -uroot -prootroot -t -e "select trx_id,trx_state,trx_started,trx_requested_lock_id,trx_wait_started,trx_weight,trx_query,trx_isolation_level, trx_mysql_thread_id from information_schema.innodb_trx;" > LOCK.txt
LOCK_NUM1=`cat LOCK.txt|wc -l` 
cat LOCK.txt |grep -v "+" > LOCK2.txt
sed -i '1d' LOCK2.txt 
LOCK_NUM=`cat LOCK2.txt |wc -l`
echo "There "$LOCK_NUM" transaction is Running about LOCKS"
#正在执行的SQL是
echo ""
echo ""
echo "输出正处于RUNNING状态的事务信息............."

echo "事务:" `cat LOCK2.txt |grep "RUNNING" |cut -d "|" -f 2` " is RUNNING"
echo "    它的开始时间是:"`cat LOCK2.txt |grep RUNNING |cut -d "|" -f 4`
echo "    它所对应的SQL是:"`cat LOCK2.txt |grep RUNNING |cut -d "|" -f 8`
echo "    它所对应的线程ID是:"`cat LOCK2.txt |grep RUNNING |cut -d "|" -f 10`

#锁等待信息输出。
echo ""
echo "" 
echo "输出正处于LOCK WAIT的事务信息.............."
while read rows
do
    result=$(echo $rows |grep "LOCK WAIT")
    if [[ "$result" != "" ]]
    then
        echo "事务: "`echo $rows |grep "LOCK WAIT" |cut -d "|" -f 2`"IS LOCK WAIT"
        echo "    该事务开始时间是: "`echo $rows |grep "LOCK WAIT" |cut -d "|" -f 4`
        echo "    该事务对应的SQL语句是: "`echo $rows |grep "LOCK WAIT" |cut -d "|" -f 8`
        echo "    该事务对应的线程ID是: "`echo $rows |grep "LOCK WAIT" |cut -d "|" -f 10`
        echo "    如果你想杀掉正在等待的这个事务,请在Mysql里面执行：kill "`echo $rows |grep "LOCK WAIT" |cut -d "|" -f 10`
        echo ""
        echo ""
    fi
done < LOCK2.txt

#"阻塞的源头事务信息输出........"
echo ""
echo ""
echo "阻塞的源头事务信息输出........"
mysql -uroot -prootroot -t -e "
select lc.requesting_trx_id , lc.requested_lock_id , lc.blocking_trx_id , lc.blocking_lock_id 
from information_schema.innodb_lock_waits lc where lc.blocking_trx_id in(
select trx_id from information_schema.innodb_trx where trx_state='RUNNING');" > LOCK3.txt
cat LOCK3.txt |grep -v "+" > LOCK4.txt
sed -i '1d' LOCK4.txt 
while read rows
do 
    result=$(cat LOCK2.txt |grep "RUNNING")
    if [[ "$result" != "" ]]
    then
        kill_id=`echo $rows |cut -d "|" -f 4`
        echo "事务: "`echo $rows |cut -d "|" -f 4` " 正在阻塞事务:"`echo $rows |cut -d "|" -f 2`
        echo "    如果你想杀掉引起阻塞的源头,你可以执行: kill "`cat LOCK2.txt|grep $kill_id |cut -d "|" -f 10`"; 但是你必须确认这样做是安全的。"
        echo ""
    fi
done < LOCK4.txt