Monitor MySQL long transaction scripts

Scripts to monitor long transactions

#!/bin/bash
Mysql-n-uroot-p ' password '-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秒钟就认定是长事务，可以根据实际需要自定义。用法就是定义个定时任务中每分钟执行一次。

Monitor MySQL long transaction scripts