Terminate MySQL Connections

I prefer the following as it will kill them in a multi threaded manner… (sometimes killing a single query can take a while)

for i in $(mysql -uroot -pPASS -e ‘show processlist’ | grep ‘search_term’ | awk ‘{print $1}’); do
mysql -uroot -pPASS -e “kill $i” &
done

could also be a modifier to the client instead of a special sql construct (much like \G)

Hi Peter,

With INFORMATION_SCHEMA.PROCESSLIST one can create a stored procedure using server cursor to achieve this. The following procedure accepts a user name and kills all queries for that user.
server cursors are a MySQL solution for ‘eval’.

Shlomi

DELIMITER $$

DROP PROCEDURE IF EXISTS test.kill_user_queries$$
CREATE PROCEDURE test.kill_user_queries (kill_user_name VARCHAR(16) CHARSET utf8)
SQL SECURITY INVOKER
BEGIN
DECLARE query_id INT;
DECLARE iteration_complete INT DEFAULT 0;
DECLARE select_cursor CURSOR FOR SELECT id FROM INFORMATION_SCHEMA.PROCESSLIST WHERE user=kill_user_name;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET iteration_complete=1;

OPEN select_cursor;
cursor_loop: LOOP
FETCH select_cursor INTO query_id;
IF iteration_complete THEN
LEAVE cursor_loop;
END IF;
KILL QUERY query_id;
END LOOP;
CLOSE select_cursor;

END$$

DELIMITER ;

A few years ago I wrote an article about how to use Vim with mysql to generate and execute SQL statements. It’s here: http://peterstuifzand.nl/external-programs-in-vim.html

If information_schema.processlist doesn’t exist on your version of MySQL, this works in a linux script:

#!/bin/bash
for each in mysqladmin -u root -prootpwd processlist | awk '{print $2, $4, $8}' | grep $dbname | grep $dbuser | awk '{print $1}';
do mysqladmin -u root -prootpwd kill $each;
done

No Percona patches yet, unfortunately. We’re running the standard MySQL 5.0.77 on Centos.

How about a technique like this:

. File optimizeworld.sfs:
—
# Optimize tables in the World database.
SELECT ‘OPTIMIZE TABLE ‘, CONCAT(TABLE_SCHEMA, ‘.’, TABLE_NAME, ‘;’)
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = ‘World’;
—

. To execute:

shell> mysql –skip-column-names < optimizeworld.sfs | mysql

I am wondering if there isn’t a symptom that is missing? I am running a high volume ecommerce site and we switched to Magento from custom app written in vb.net (.net 1.1). Shouldn’t MySql handle all of this? What I am wondering is why my old windows box running doesn’t crash with the same load as my high powered linux box?

I used the command and the query being run in mysql processlist still running for 28226 and l cant kill it with everything l did. what might else l should try ?
thanks.

Thanks Peter. Query is really very helpful to me.

This may not directly related to this chain, but I landed here to kill massive connection due to the hung drop trigger – I have a dilemma with dropping a trigger on a percona 5.5.27 on a very large table ~ 68 Gig. I get the metadata lock and it hangs.

I have tried everything. Is it safe to remove the trigger from DB.TRG file? Thanks a lot

@Alon,
You have a typo. Should be information_schema.processlist

Very informative as all your articles.. keep it up.

Great tip, thanks!