How to Kill Many MySQL Processes at Once

We recently had an issue on our database server which meant many slow MySQL queries had built up (up to 50 of them!) These slow queries were preventing other queries from running properly and were essentially causing a DoS.

Killing 50 queries would have been a very manual process. These slow SELECT queries were not particularly essential, and a quick solution was to kill these queries all at once.

I took the following steps to kill many MySQL queries at once:

1. Identify the process IDs of the slow-running queries.
   The SHOW PROCESSLIST; command will display details on the currently running queries along with the truncated query itself. Killing each of these processes individually would take a long time so we need a quick way to get all of these IDs at once and kill them as quickly as we can.
2. A better way of getting the process IDs
   By running the following query, we can retrieve the process IDs in one column along with a ‘KILL ‘ keyword which we will need for step 3.

 SELECT CONCAT('KILL ',id,';') AS kill_list FROM information_schema.processlist;

3. Remove pipe symbols and spaces
   Using a text editor (e.g. VS Code), we can find & replace (or multi-line edit) the pipe & space symbols to give us a number of kill queries which are ready for pasting directly into a MySQL terminal.
4. Paste the queries
   Paste the queries into a MySQL terminal and the processes will be killed.