How do I kill a running query in mysql ?
We can kill the processes with the help of the ‘kill’ command. However, you need to
kill those processes one by one, since MySQL does not have any massive kill command.
To check how many processes exist, use ‘show processlist’
The following is the output.
mysql> show processlist;
+------+--------+--------------------+----------+---------+------+-------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+------+--------+--------------------+----------+---------+------+-------+------------------+
| 41 | zabbix | localhost | zabbixdb | Sleep | 2 | | NULL |
| 48 | zabbix | localhost | zabbixdb | Sleep | 0 | | NULL |
| 50 | zabbix | localhost | zabbixdb | Sleep | 29 | | NULL |
| 55 | zabbix | localhost | zabbixdb | Sleep | 0 | | NULL |
| 58 | zabbix | localhost | zabbixdb | Sleep | 1 | | NULL |
| 62 | zabbix | localhost | zabbixdb | Sleep | 57 | | NULL |
| 5280 | root | 192.168.109.202:58608 | zabbixdb | Query | 0 | NULL | show processlist |
+------+--------+--------------------+----------+---------+------+-------+------------------+
7 rows in set (0.00 sec)
To kill a process which have been active for more than 10 seconds, the following is the query. Here, we are killing a process with Id “41”
mysql> select concat('kill ',41,';')
-> from information_schema.processlist
-> where TIME > 10;
The following is the output.
mysql> select concat('kill ',41,';') from information_schema.processlist where TIME > 10;
+------------------------+
| concat('kill ',41,';') |
+------------------------+
| kill 41; |
| kill 41; |
| kill 41; |
| kill 41; |
+------------------------+
4 rows in set (0.00 sec)
Method 2:
kill <thread_id>;
mysql> show processlist;
+------+--------+--------------------+----------+---------+------+-------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+------+--------+--------------------+----------+---------+------+-------+------------------+
| 48 | zabbix | localhost | zabbixdb | Sleep | 3 | | NULL |
| 50 | zabbix | localhost | zabbixdb | Sleep | 23 | | NULL |
| 55 | zabbix | localhost | zabbixdb | Sleep | 4 | | NULL |
| 58 | zabbix | localhost | zabbixdb | Sleep | 55 | | NULL |
| 62 | zabbix | localhost | zabbixdb | Sleep | 51 | | NULL |
| 5280 | root | 192.168.109.202:58608 | zabbixdb | Query | 0 | NULL | show processlist |
| 5355 | zabbix | localhost | zabbixdb | Sleep | 26 | | NULL |
+------+--------+--------------------+----------+---------+------+-------+------------------+
7 rows in set (0.00 sec)
mysql> kill 5355 ;
Query OK, 0 rows affected (0.00 sec)
you can still try the following MySQL query to kill all the processes.
mysql -e "show full processlist;" -ss | awk '{print "KILL "$1";"}'