This is an analogous show processlist written as a stored proc, which also enables killing all processes belonging to a particular user. It may come handy should a particular user take up all your connections on the server due to a bug on the application thus crippling your db.

#######################################################################################
##                                                                                   ##
##  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 `securich`.`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 ;
 

Current Tags

You must be logged in to tag this tool