#---------------------------------------------------
【use】 监控数据库是否存在锁等待 并记录下相关信息
【author】 zhangkh
#---------------------------------------------------
echo “开始监控MySQL的锁占用情况” > monitor.log
while true ; do
lock_trx=mysql -uroot -N -padminadmin123 -s -B -e" select concat(requesting_trx_id,'|',b.trx_mysql_thread_id,'|',blocking_trx_id,'|',c.trx_mysql_thread_id) from information_schema.innodb_lock_waits a left outer join information_schema.innodb_trx b on b.trx_id=a.requesting_trx_id left outer join information_schema.innodb_trx c on c.trx_id=a.blocking_trx_id "
for row in lock_trx
do
echo " 请求事务ID|请求processID|阻塞事务ID|阻塞processid --------"{row} >> monitor.log
echo " 请求事务ID|请求processID|阻塞事务ID|阻塞processid --------"${row}
reqtrxinfo=echo "${row}" | awk -F "|" '{print $1}'
blotrxinfo=echo "${row}" | awk -F "|" '{print $3}'
bloprocid=echo "${row}" | awk -F "|" '{print $4}'
echo " 可以执行kill ${bloprocid} 杀掉阻塞会话"
echo " 可以执行kill ${bloprocid} 杀掉阻塞会话" >>monitor.log
req_trx_info=mysql -uroot -N -padminadmin123 -s -B -e" select concat(trx_started,':',trx_query,':',trx_state) from information_schema.innodb_trx where trx_id=${reqtrxinfo} "
echo " 请求进程的事务信息如下："
echo " 请求进程的事务信息如下：" >>monitor.log
echo " ${req_trx_info}"
echo " ${req_trx_info}" >>monitor.log
blo_trx_info=mysql -uroot -N -padminadmin123 -s -B -e" select concat(trx_started,':',case when trx_query is null then ' ' else trx_query end ,':',trx_state) from information_schema.innodb_trx where trx_id=${blotrxinfo} "
echo " 阻塞进程的事务信息如下："
echo " 阻塞进程的事务信息如下：" >>monitor.log
echo " ${blo_trx_info}"
echo " ${blo_trx_info}" >>monitor.log
echo " 查询阻塞会话进程执行正在执行的内容 ${bloprocid}"
echo " 查询阻塞会话进程执行正在执行的内容 {bloprocid}) “`
echo " ${curr_info}”
echo " ${curr_info}" >> monitor.log
echo " 查询阻塞会话进程执行过的内容 ${bloprocid}"
echo " 查询阻塞会话进程执行过的内容 {bloprocid}) union all select concat(replace(replace(SQL_TEXT,char(10),’’),char(13),’’),’;’) from performance_schema.events_statements_history_long where thread_id in (select thread_id from performance_schema.threads where processlist_id=${bloprocid}) “`
for row_proc in $process_info
do
echo " ${row_proc}”
echo " ${row_proc}" >> monitor.log
done
done
if [[ -z $lock_trx ]]; then
echo “当前未发现锁等待,持续监控中。。。。。。。。。。”
sleep 5s
else
echo “已经捕获到一次锁信息，继续监控下一次 详细信息请查看日志。。。。。。。。。。”
#exit
fi
done
