SELECT CONCAT('KILL ', id, ';') AS kill_command FROM information_schema.processlist WHERE command != 'Sleep' AND time > 300; -- 5+ minutes Copy the output and run the commands. DELIMITER $$ CREATE PROCEDURE kill_long_running_queries(IN max_time_seconds INT) BEGIN DECLARE done INT DEFAULT FALSE; DECLARE pid INT; DECLARE cur CURSOR FOR SELECT id FROM information_schema.processlist WHERE command != 'Sleep' AND time > max_time_seconds; DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE; OPEN cur; read_loop: LOOP FETCH cur INTO pid; IF done THEN LEAVE read_loop; END IF; SET @kill_stmt = CONCAT('KILL ', pid); PREPARE stmt FROM @kill_stmt; EXECUTE stmt; DEALLOCATE PREPARE stmt; END LOOP; CLOSE cur; END$$ DELIMITER ;
KILL QUERY <id>; Example:
SELECT id, user, host, db, command, time, state, SUBSTRING(info, 1, 100) AS query_preview FROM information_schema.processlist WHERE command != 'Sleep' AND time > 60 -- running for more than 60 seconds ORDER BY time DESC; Once you have the connection id : mysql kill long running queries
KILL [CONNECTION] <id>; Or if you only want to terminate the current query but keep the connection: SELECT CONCAT('KILL ', id, ';') AS kill_command FROM