MySQL Preacher

It”s not the most common task in the world, but you might want to view processes from a particular user and once in a while you might even need to kill processes from a single user, be it during an attack or because you simply got a bug in an application bombarding your db server with connections!

Here is a small stored procedure which does exactly that!

call process_list("show","username","hostname");
– shows all processes owned by username@hostname
call process_list("kill","username","hostname");
– kills all processes owned by username@hostname

The code for this stored procedure can be found below. If you have any comments / suggestions feel free to comment below.

######################################################################
## ##
## Stored Procedure: process_list & kill user ##
## call process_list("show","%") show processlist for all users ##
## call process_list("show","root") show processlist for root user ##
## call process_list("kill","user1") kill connections for user1 ##
## ##
## by Darren Cassar http://www.mysqlpreacher.com ##
## ##
######################################################################

DROP PROCEDURE IF EXISTS process_list;

DELIMITER $$

CREATE PROCEDURE "process_list"( choice char(4), usernamein varchar(16), hostnamein varchar(60))
BEGIN

DECLARE CURCONN int;

IF choice <> "show" AND choice <> "kill" then
select "wrong choice";
END IF;

IF usernamein = "" then
set usernamein = "%";
END IF;

IF hostnamein = "" then
set hostnamein = "%";
END IF;

SET CURCONN=(select connection_id());

IF choice = "show" then

select *
from information_schema.processlist
where ID <> CURCONN and
USER like usernamein and
( HOST like CONCAT(hostnamein ,":%") or
HOST like hostnamein );

ELSEIF choice = "kill" then

IF usernamein = "root" then
select "Illegal username when killing processes";
ELSE
SET @CNT = (
select count(*)
from information_schema.processlist
where ID <> CURCONN and
USER like usernamein and
( HOST like CONCAT(hostnamein ,":%") or
HOST like hostnamein )
);

SET @VAR=1;

WHILE ( @VAR <= @CNT) DO

SET @TID = (
select id
from information_schema.processlist
where ID <> CURCONN and
USER like usernamein and
( HOST like CONCAT(hostnamein ,":%") or
HOST like hostnamein ) limit 1
);

SET @k = CONCAT("kill " , @TID);
PREPARE killcom FROM @k;
EXECUTE killcom;
set @k=NULL;

SET @VAR=@VAR+1;

END WHILE;

END IF;

END IF;

END$$

DELIMITER ;


Beginner, Databases, MySQL kill, mysql stored procedure, processes, processlist, sp, stored proc, stored procedure