暂无图片
暂无图片
暂无图片
暂无图片
暂无图片

MySQL锁等待SQL收集shell脚本-全网最全-独一份

*江西数库信息技术有限公司的原创内容未经授权不得随意使用,转载请联系小编并注明来源。

mysql死锁可以通过错误日志查看,但锁等待却没有对应的参数可以控制,下面是我生产环境使用的配置,可以收集到锁等待的SQL语句,方便分析排查问题

有开启:performance_schema=on参数的情况下,可以用以下脚本收集:
#!/bin/bash
user="root"
password="123.123."
logfile="/server/shell_scripts/mysql/innodb_lock_timeout_monitor/innodb_lock_timeout_monitor.log"
while true
do
num=`mysql -u${user} -p${password} -e "select count(*) from information_schema.innodb_lock_waits" |grep -v count`
if [[ $num -gt 0 ]];then
date >> ${logfile}
mysql -u${user} -p${password} -e  "SELECT r.trx_mysql_thread_id waiting_thread,r.trx_query waiting_query, \
concat(timestampdiff(SECOND,r.trx_wait_started,CURRENT_TIMESTAMP()),'s') AS duration,\
b.trx_mysql_thread_id blocking_thread,t.processlist_command state,b.trx_query blocking_query,e.sql_text \
FROM information_schema.innodb_lock_waits w \
JOIN information_schema.innodb_trx b ON b.trx_id = w.blocking_trx_id \
JOIN information_schema.innodb_trx r ON r.trx_id = w.requesting_trx_id \
JOIN performance_schema.threads t on t.processlist_id = b.trx_mysql_thread_id \
JOIN performance_schema.events_statements_current e USING(thread_id) \G " >> ${logfile}
blocking_thread=`tail $logfile | grep blocking_thread | awk '{print $2}'`
mysql -u${user} -p${password} -e "show processlist" | grep $blocking_thread >> ${logfile}


waiting_thread=`tail $logfile | grep waiting_thread | awk '{print $2}'`
mysql -u${user} -p${password} -e "show processlist" | grep $waiting_thread >> ${logfile}
fi
sleep 5
done




没有开启:performance_schema=on参数的情况下,可以用以下脚本收集:
[root@prod-db-monitor innodb_lock_timeout_monitor]# cat innodb_lock_wait_monitor.sh
#!/bin/bash
user="nsy_dba_rsc"
password="xFKx2U5wMweC"
host="rm-bp16t20k1ro17yn9t.mysql.rds.aliyuncs.com"
logfile="/server/shell_scripts/mysql/innodb_lock_timeout_monitor/innodb_lock_timeout_monitor.log"
while true
do
num=`mysql -u${user} -p${password} -h${host} -e "select count(*) from information_schema.innodb_lock_waits" |grep -v count`
if [[ $num -gt 0 ]];then
date >> ${logfile}
mysql -u${user} -p${password} -h${host} -e  "SELECT r.trx_mysql_thread_id waiting_thread,r.trx_query waiting_query, \
concat(timestampdiff(SECOND,r.trx_wait_started,CURRENT_TIMESTAMP()),'s') AS duration,\
b.trx_mysql_thread_id blocking_thread,b.trx_query blocking_query \
FROM information_schema.innodb_lock_waits w \
JOIN information_schema.innodb_trx b ON b.trx_id = w.blocking_trx_id \
JOIN information_schema.innodb_trx r ON r.trx_id = w.requesting_trx_id \G " >> ${logfile}
blocking_thread=`tail $logfile | grep blocking_thread | awk '{print $2}'`
mysql -u${user} -p${password} -h${host}  -e "show processlist" | grep $blocking_thread >> ${logfile}


waiting_thread=`tail $logfile | grep waiting_thread | awk '{print $2}'`
mysql -u${user} -p${password} -h${host} -e "show processlist" | grep $waiting_thread >> ${logfile}
fi
sleep 5
done


启动:
[root@node214-mysql-master innodb_lock_timeout_monitor]# cat start_nohup
nohup /bin/bash /server/shell_scripts/mysql/innodb_lock_timeout_monitor/innodb_lock_timeout_monitor.sh & &>> /dev/null


数据库首席架构师:阮胜昌 MySQL 8.0 OCP、Oracle 11G OCP,PostgreSQL PCA,Oceanbase OBCA、PingCAP PCTA、软考中级数据库系统工程师、RHCE7.0、cisco CCNA认证
擅长主流数据库MySQL、Oracle、PostgreSQL运维与开发、数据恢复、安装配置、数据迁移、集群容灾、性能优化、故障应急处理、其它问题等
欢迎关注我的博客: http://www.linuxmysql.com 第一时间一起学习新知识!


最后修改时间:2023-07-21 17:54:08
「喜欢这篇文章,您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容,转载时必须标注文章的来源(墨天轮),文章链接,文章作者等基本信息,否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容,欢迎发送邮件至:contact@modb.pro进行举报,并提供相关证据,一经查实,墨天轮将立刻删除相关内容。

文章被以下合辑收录

评论