mysql慢查询lock_time_mysql慢查询与锁监控

1.vi /app/lockwait/querylockwait.sh

#!/bin/sh

/app/mariadb/bin/mysql -uroot -p'XXXXX' -P3306 -h10.81.44.176 -e"select concat(trx_query,'\n') from information_schema.innodb_trx where trx_state='LOCK WAIT' and now()-trx_started>=60" | grep -v "trx_query" > /app/lockwait/lockwaitsql`date +%Y%m%d`.txt

/app/mariadb/bin/mysql -uroot -p'XXXXX' -P3306 -h10.81.44.176 -e"select concat(USER,';',info,'\n') from information_schema.processlist where info is not null and time_ms>=10000" > /app/lockwait/slowquery`date +%Y%m%d`.txt

fi

find /app/lockwait/ -name "*.txt" -mtime +2 | xargs rm -rf

2.vi /app/mysql-lock/kill_innodb_lock.sh

#!/bin/sh

datetime=`date +%Y%m%d%H%M%S`

/app/mariadb/bin/mysql -uroot -p'XXXXX' -P3306 -h10.81.44.176 -e"select concat(info,'\n') from information_schema.processlist where info is not null and time_ms>=10000" > /app/lockwait/slowquery80-`date +%Y%m%d`.txt

/app/mariadb/bin/mysql -uroot -p'XXXXX' -P3306 -h10.81.44.176 test -e"call kill_slow_query;call kill_lock_wait_trx;"

if [ -s /app/lockwait/slowquery80-`date +%Y%m%d`.txt ]; then

cat /app/lockwait/slowquery80-`date +%Y%m%d`.txt | mutt -s "zxy9主业务库实时执行时间大于10秒sql被强制kill-`date +%Y%m%d`" ouyangluping@zhixueyun.com chengzhi@zhixueyun.com lihaichun@zhixueyun.com tangjie@zhixueyun.comweichuanbo@zhixueyun.com luomeiling@zhixueyun.com liuhaijie@zhixueyun.com

fi

/app/mariadb/bin/mysql -uroot -p'XXXXX' -P3306 -h10.81.44.176 test -e"select * from innodb_lock_monitor;" > /app/mysql-lock/$datetime.txt

cd /app/mysql-lock/

rm -rf $datetime.txt;

3.在test数据库中运行如下sql语句

use test;

drop table if exists innodb_lock_monitor;

CREATE TABLE `innodb_lock_monitor` (

`threadId` int(11) DEFAULT NULL,

`trxQuery` varchar(2000) DEFAULT NULL,

`trxStarted` datetime DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

DROP PROCEDURE if exists kill_lock_wait_trx;

DELIMITER ;;

CREATE PROCEDURE `kill_lock_wait_trx`()

Begin

DECLARE done INT DEFAULT 0;

declare threadId bigint(21);

declare trxQuery varchar(1024);

declare trxStarted datetime;

DECLARE cur CURSOR FOR select b.trx_mysql_thread_id,b.trx_query,b.trx_started

from information_schema.innodb_lock_waits a, information_schema.innodb_trx b

where a.blocking_trx_id=b.trx_id

and now()-b.trx_started>=80;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

OPEN cur;

REPEAT

FETCH cur INTO threadId,trxQuery,trxStarted;

IF NOT done THEN

kill threadId;

insert into innodb_lock_monitor values(threadId,trxQuery,trxStarted);

END IF;

UNTIL done END REPEAT;

CLOSE cur;

END;;

DELIMITER ;

DROP PROCEDURE if exists kill_slow_query;

DELIMITER ;;

CREATE PROCEDURE `kill_slow_query`()

Begin

DECLARE done INT DEFAULT 0;

declare threadId bigint(21);

DECLARE cur CURSOR FOR select id from information_schema.processlist where info is not null and time_ms>=10000 order by time_ms desc;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

OPEN cur;

REPEAT

FETCH cur INTO threadId;

IF NOT done THEN

kill threadId;

END IF;

UNTIL done END REPEAT;

CLOSE cur;

END;;

DELIMITER ;

4.安装msmtp

(1)tar xvf msmtp-1.4.30.tar.bz2

cd msmtp-1.4.30;

./configure --prefix=/usr/local/msmtp

(2)make

(3)make install

(4)mkdir /usr/local/msmtp/etc

(5)touch /usr/local/msmtp/etc/msmtprc

(6)在/usr/local/msmtp/etc/msmtprc中写入如下内容：

defaults

account lihaichun@netschina.com

host smtp.exmail.qq.com

domain netschina.com

from lihaichun@netschina.com

auth login

user lihaichun@netschina.com

password XXXXX

account default:lihaichun@netschina.com

logfile /var/log/maillog

(7)测试一下：/usr/local/msmtp/bin/msmtp lihaichun@dreamtech.com.cn，输入内容后按ctrl+D发出。

5.在实际测试中发现直接使用msmtp命令发出去的邮件会看不到发件人和主题，只能看到邮件内容，所以我使用mutt挂接在msmtp上，mutt默认会安装，

如果没有安装请yum install mutt* -y

(1)修改mutt的配置文件/etc/Muttrc, 不是/etc/muttrc ，M要大写

set sendmail="/usr/local/msmtp/bin/msmtp"

set use_from=yes

set realname=lihaichun@netschina.com

set editor="vi"

set copy=no

set from="lihaichun@netschina.com"

注意:set copy=no 这行必须加上不然会报错 "/root/sent: Permission denied (errno = 13)"

(2)测试一下：echo "邮件报警测试" | mutt -s "测试" lihaichun@dreamtech.com.cn #收件人地址

6.配置定时任务

crontab -e

*/2 7-18 * * 1-6 /app/mysql-lock/kill_innodb_lock.sh

*/2 7-18 * * 1-6 /app/lockwait/querylockwait.sh