MySQL processlist – 一个辅助的存储过程

原帖地址：http://mysqlpreacher.com/wordpress/2009/07/mysql-processlist-showkill-processes/

 

使用方法：

    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

 

 

具体定义：

 

###########################################
##
##  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), user_name_in varchar(16), host_name_in varchar(60))
  BEGIN
    DECLARE cur_conn int;

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

    IF user_name_in = '' then
       SET user_name_in = '%';
    END IF;

    IF host_name_in = '' then
       SET host_name_in = '%';
    END IF;

    SET cur_conn = (SELECT connection_id());

    IF choice = 'show' then
       SELECT *
         FROM `information_schema`.`processlist`
        WHERE ID <> cur_conn
          AND USER LIKE user_name_in
          AND (HOST LIKE CONCAT(host_name_in ,':%') OR HOST LIKE host_name_in);
    ELSEIF choice = 'kill' then
       IF user_name_in = 'root' then
          SELECT "Illegal username when killing processes";
       ELSE
          SET @CNT = (
             SELECT count(*)
               FROM `information_schema`.`processlist`
              WHERE ID <> cur_conn
                AND USER LIKE user_name_in
                AND (HOST LIKE CONCAT(host_name_in ,':%') OR HOST LIKE host_name_in));

          SET @VAR=1;
          WHILE (@VAR <= @CNT) DO
              SET @TID = (
                 SELECT id
                   FROM `information_schema`.`processlist`
                  WHERE ID <> cur_conn
                    AND USER LIKE user_name_in
                    AND (HOST LIKE CONCAT(host_name_in ,':%') OR HOST LIKE host_name_in) 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;