Every so often I run into a situation when I need to terminate connections on MySQL server – for example, hundreds of instances of some bad query is running, making the server unusable. Many people have special scripts which can take the user, source host, or query as a parameter and perform the action. There is also a way to do it just using MySQL with a few commands:
|
1 |
mysql> select concat('KILL ',id,';') from information_schema.processlist where user='root';<br>+------------------------+<br>| concat('KILL ',id,';') |<br>+------------------------+<br>| KILL 3101; |<br>| KILL 2946; |<br>+------------------------+<br>2 rows in set (0.00 sec)<br><br>mysql> select concat('KILL ',id,';') from information_schema.processlist where user='root' into outfile '/tmp/a.txt';<br>Query OK, 2 rows affected (0.00 sec)<br><br>mysql> source /tmp/a.txt;<br>Query OK, 0 rows affected (0.00 sec)<br> |
In general, this is a very powerful approach which I use in a lot of cases to create a set of SQL statements by SQL query and then execute it.
It would be nice and clean if MySQL would have some way to “eval” – execute the result set of the query as SQL commands. This would avoid the requirement to use temporary file etc.
