阮胜昌的技术记录站-LinuxMySQL数据库运维

有开启：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


启动：
[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






没有开启：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