批量kill掉无用的sql语句，避免影响拖垮数据库。
MariaDB [(none)]> show processlist; +--------+------+-----------------+----------+---------+------+-------+------------------+----------+ | Id | User | Host | db | Command | Time | State | Info | Progress | +--------+------+-----------------+----------+---------+------+-------+------------------+----------+ | 103258 | root | localhost:57884 | adminset | Sleep | 2 | | NULL | 0.000 | | 103259 | root | localhost | NULL | Query | 0 | NULL | show processlist | 0.000 | +--------+------+-----------------+----------+---------+------+-------+------------------+----------+ 2 rows in set (0.00 sec) MariaDB [(none)]> show full processlist; +--------+------+-----------------+----------+---------+------+-------+-----------------------+----------+ | Id | User | Host | db | Command | Time | State | Info | Progress | +--------+------+-----------------+----------+---------+------+-------+-----------------------+----------+ | 103258 | root | localhost:57884 | adminset | Sleep | 3 | | NULL | 0.000 | | 103259 | root | localhost | NULL | Query | 0 | NULL | show full processlist | 0.000 | +--------+------+-----------------+----------+---------+------+-------+-----------------------+----------+ 2 rows in set (0.00 sec)
找到你符合你条件的线程id就可以kill了
mysql -uroot -S /var/lib/mysql/mysql.sock -sNe "select id from information_schema.processlist where COMMAND='Sleep' and TIME>60" | xargs -n 1 mysqladmin -uroot -S /var/lib/mysql/mysql.sock kill
或者生成kill 命令，再自己手动执行：
select concat('KILL ',id,';') from information_schema.processlist where COMMAND='Sleep' and TIME>60;
或者用工具mt-kill pt-kill
pt-kill --host=192.168.0.1 --user=root --password=mypwd --port=3306 --busy-time 60 --match-command="query|Execute" --victim all --interval 1 --kill --daemonize --pid=/tmp/ptkill.pid --print --log=/home/pt-kill.log
pt-kill --host=192.168.0.1 --user=root --password=mypwd --port=3306 --busy-time 60 --match-state="Locked|Sending data" --victim all --interval 1 --kill --daemonize --pid=/tmp/ptkill.pid --print --log=/home/pt-kill.log
pt-kill --host=192.168.0.1 --user=root --password=mypwd --port=3306 --busy-time 60 --match-info="SELECT|DELETE" --victim all --interval 1 --kill --daemonize --pid=/tmp/ptkill.pid --print --log=/home/pt-kill.log
还可以用如下脚本
#!/bin/bash #批量kill mysql进程的时候条件要尽可能的严格，最好是过滤掉包括update和insert的进程 #kill掉test用户的mysql进程 #for i in `mysql -uroot -pmypwd -se "show processlist" | grep -v "show processlist" | awk '{if($2=="test") print $1}'` #kill掉来自172.16.13.177的主机的mysql进程 #for i in `mysql -uroot -pmypwd -se "show processlist" | grep -v "show processlist" | awk '{if($3 ~/^172.16.13.177:*/) print $1}'` #kill掉执行时间大于15秒的mysql进程 #for i in `mysql -uroot -pmypwd -se "show processlist" | grep -v "show processlist" | awk '{if($6 > 15) print $1}'` #kill掉执行所有的不包括update和insert关键词的mysql进程 #for i in `mysql -uroot -pmypwd -se "show full processlist" | grep -v "show full processlist"|grep -vi -E "update|insert"|awk '{print $1}'` do #mysql -uroot -pmypwd -e "kill $i" echo $i done