MDL锁DDL查杀

    SELECT concat('kill ', i.trx_mysql_thread_id, ';') FROM information_schema.innodb_trx i,(SELECT MAX(time) AS max_time FROM information_schema.processlist WHERE state = 'Waiting for table metadata lock' AND (info LIKE 'alter%' OR info LIKE 'create%' OR info LIKE 'drop%' OR info LIKE 'truncate%' OR info LIKE 'rename%')) p WHERE timestampdiff(second, i.trx_started, now()) > p.max_time;

  一旦未提交事务执行完成,是无法通过innodb_trx查到具体的SQL， 此SQL生成的KILL SESSION 是查询到DDL之前的未提交事务,kill后就可以使DDL正常执行了,最好看看具体的操作,防止误杀

SQL执行查杀

 SELECT CONCAT('kill ',id,';') FROM `information_schema`.`PROCESSLIST` where Command='Query' and time > 60 INTO OUTFILE '/tmp/kill_id.sql'; #查询会话

  SELECT CONCAT('kill ',id,';') FROM `information_schema`.`PROCESSLIST` where Command='Sleep and time > 60 and user not in ('root') INTO OUTFILE '/tmp/kill_id.sql'; #休眠会话

 source /tmp/kill_id.sql

PT-KILL查杀

pt-kill --host=127.0.0.1 --port=3306 --user=root --password='root' --match-db='test' --match-info "select|SELECT" --busy-time 60 --victims all --interval 10 --daemonize --kill --log=pt_select_test.log

--print 是只打印，可以先打印后续再kill

 --kill  打印并kill

--match-user  " user1" 匹配某个用户

--match-info  "select|SELECT" 匹配查询语句

--match-host "1.1.1.1" 匹配具体host

--match-State "Create index " 匹配某种状态

--match-info "selec * from“ 匹配kill具体的sql语句

变量设置

 设置max_execution_time=60000 针对普通查询自动查杀,但是对于存储过程类的无效