mysql 执行sql脚本_Mysql—如何通过脚本自动处理sql执行时间长而造成的线程阻塞...

脚本截图

check_mysql.sh

#!/bin/shmysql_socket=/tmp/3306.sockwhile true;doecho "-----------------------------------------------------------------"mysql -S $mysql_socket -e "select now(), (UNIX_TIMESTAMP(now()) - UNIX_TIMESTAMP(a.trx_started)) as '0', 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<>'Binlog Dump' 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;" |grep 2019- | while read A B C D E F G H;do if [ "$C" -gt 60 ];then echo $(date +"%Y-%m-%d %H:%M:%S") echo "processid[$D] $E@$F in db[$G] hold transaction time $C SQL:$H" for id in $D;do mysql -S $mysql_socket -e "kill $id" done fidonesleep 5done >> /data/dbak/check_mysql.log

以上脚本内容大概意思是：

1、进行一个死循环，5s进行一次

2、获取执行时长超过60s的sql

3、分别打印now()、sql执行时长、线程id、user、host、库名、sql语句字段

4、如果有sql执行超过60s的，并循环通过kill id进程处理

5、通过重定向输出，将日志写入到文件，这样就算kill了，也可以记录超过60s的sql