MySQL8.0-监控数据库锁等待SHELL脚本#!/bin/bash
user="abccc"
password="123123"
host="192.168.1.100"
logfile="/server/shell_scripts/mysql/innodb_lock_timeout_monitor/innodb_lock_timeout_monitor_8.0.log"
while true
do
num=`mysql -u${user} -p${password} -h${host} -e "select count(*) from sys.innodb_lock_waits" |grep -v count`
if [[ $num -gt 0 ]];then
date >> ${logfile}
mysql -u${user} -p${password} -h${host} -e "SELECT r.trx_started waiting_start_time,r.trx_mysql_thread_id waiting_thread,r.trx_query waiting_query,w.waiting_lock_mode,w.locked_type,r.trx_operation_state waiting_operation_state, concat(timestampdiff(SECOND,r.trx_wait_started,CURRENT_TIMESTAMP()),'s') AS duration, b.trx_started blocking_start_time,b.trx_mysql_thread_id blocking_thread,b.trx_state blocking_status,b.trx_query blocking_query ,w.blocking_lock_mode ,w.sql_kill_blocking_query FROM sys.innodb_lock_waits w JOIN information_schema.innodb_trx b ON b.trx_id = w.blocking_trx_id JOIN information_schema.innodb_trx r ON r.trx_id = w.waiting_trx_id \G " >> ${logfile}
#blocking_thread=`tail $logfile | grep blocking_thread | awk '{print $2}'`
#mysql -u${user} -p${password} -h${host} -e "show full processlist" | grep $blocking_thread >> ${logfile}
#waiting_thread=`tail $logfile | grep waiting_thread | awk '{print $2}'`
#mysql -u${user} -p${password} -h${host} -e "show full processlist" | grep $waiting_thread >> ${logfile}
fi
sleep 5
done
