mysql 阻塞监控脚本

#!/bin/sh

while :
do

mysql -uroot -p'12wsxCDE#' -e "select now();show full processlist\g;select b.trx_mysql_thread_id AS '被阻塞线程',b.trx_query AS '被阻塞SQL',c.trx_mysql_thread_id AS '阻塞线程',(UNIX_TIMESTAMP()-UNIX_TIMESTAMP(c.trx_started)) AS '阻塞时间'
from information_schema.INNODB_LOCK_WAITS a
join information_schema.INNODB_TRX b on a.requesting_trx_id =b.trx_id
join information_schema.INNODB_TRX c on a.blocking_trx_id=c.trx_id
where (UNIX_TIMESTAMP()-UNIX_TIMESTAMP(c.trx_started))>60;"

ps aux --sort=-%cpu | head -20

echo "~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~"
sleep 15;
done