mysql数据库杀掉堵塞进程

mysql>SHOW PROCESSLIST;查看进程;

mysql>KILL ID;杀掉进程;

这是杀掉单一进程,一个一个解决起来太麻烦;

批量解决,导出到文件,在导入处理;

1,

mysql>select CONCAT('KILL ',id,';') FROM information_schema.`PROCESSLIST` where DB = 'finance_49db6118b5b24de792246550429dfb2b';

+------------------------+
| concat('KILL ',id,';')
+------------------------+
| KILL 3101;             
| KILL 2946;             

+------------------------+

2,

mysql>select CONCAT('KILL ',id,';') FROM information_schema.`PROCESSLIST` where DB = 'finance_49db6118b5b24de792246550429dfb2b' INTO OUTFILE'/var/lib/mysql-files/a.txt';

Query OK, 2 rows affected (0.00 sec)

注意:CONCAT('KILL ',id,';')里 KILL后面有个空格;

INTO OUTFILE 后面的路径是mysql配置文件里配置的路径,随意指定路径会引起权限问题;

会报这个错:The MySQL server is running with the --secure-file-priv option so it cannot execute this statement;

可以通过下面命令查看默认路径;

mysql>show variables like '%secure%';

如果值为Null就需要到mysql安装路径下的my.ini文件，设置一下路径：

然后重启mysql;

3,mysql> source /tmp/a.txt;

Query OK, 0 rows affected (0.00 sec);

我用navicat连接数据库,导入文件不成功,source命令不变色;

后来我发现 何必呢

mysql>select CONCAT('KILL ',id,';') from information_schema.`PROCESSLIST` where DB = 'finance_49db6118b5b24de792246550429dfb2b';

然后点击

全选 复制到运行窗口;

然后 运行;哈哈哈,全部解决;