監控MySQL長事務指令碼

標籤：amp   form   proc   pre   ssid   event   ati   監控   sql   

監控長事務的指令碼

#!/bin/bash
mysql -N -uroot -p‘密碼‘ -e "select now(),(UNIX_TIMESTAMP(now()) - UNIX_TIMESTAMP(a.trx_started)) diff_sec,b.id,b.user,b.host,b.db,d.SQL_TEXT from information_schema.innodb_trx a inner join
information_schema.PROCESSLIST b
on a.TRX_MYSQL_THREAD_ID=b.id and b.command = ‘Sleep‘
inner join performance_schema.threads c ON b.id = c.PROCESSLIST_ID
inner join performance_schema.events_statements_current d ON d.THREAD_ID = c.THREAD_ID;" | while read A B C D E F G H
do
#echo $C
if [ "$C" -gt 5 ]
then
echo date "+%Y-%m-%d %H:%M:%S"
echo "processid[$D] [email protected]$F in db[$G] hold transaction time $C SQL:$H"
fi
done >> /tmp/longtransaction.txt

    簡單說明一下，這裡的-gt 5是5秒鐘的意思，只要超過了5秒鐘就認定是長事務，可以根據實際需要自訂。用法就是定義個定時任務中每分鐘執行一次。

監控MySQL長事務指令碼