Procedure in mysql to kill long queries executions.

mysql-kill-long-queries.sql

DELIMITER //
CREATE PROCEDURE killing_process (total_time int)
  BEGIN

  DECLARE done INT DEFAULT 0;
  DECLARE process_id INT;
  DECLARE result varchar(4000);
  DECLARE cur1 CURSOR FOR
		SELECT id
		FROM information_schema.processlist
		WHERE user IN('usuario') AND time > total_time AND UPPER(info) LIKE 'SELECT%';
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

  
  SELECT *
		FROM information_schema.processlist
		WHERE user IN('usuario') AND time > total_time AND UPPER(info) LIKE 'SELECT%';
  
  OPEN cur1;

  REPEAT
    FETCH cur1 INTO process_id;
    IF NOT done THEN
      kill process_id;
    END IF;
  UNTIL done END REPEAT;

  CLOSE cur1;
  
  END
  //

DELIMITER ;


/* To execute:
 * call killing_process (1800);
 *
 * ex command line execution:
 * mysql -u root -p -e 'call kill_all_long_process (1800);
 * or
 * mysql --defaults-extra-file=/path/credential-mysql.txt -e 'call kill_all_long_process (1800);'
 */

rds_kill_long_process.py

import argparse
import pymysql.cursors
import getpass


parser = argparse.ArgumentParser(description='Teste para Receber argumentos')
parser.add_argument('--dbuser', help='MySQL User', required=True)
parser.add_argument('--total_sec', help='Time running to kill (time = seconds)', required=True)
parser.add_argument('--server_ip', help='Ip from server')
args = parser.parse_args()

def getConnection(pw):
    connection = pymysql.connect(host="DB_endpoint",
                                 user=args.dbuser,
                                 password=pw,
                                 db="your_database",
                                 charset='latin1',
                                 cursorclass=pymysql.cursors.DictCursor)
    connection.autocommit(True)
    return connection

def DBexecute(conn, dbquery, arg_tuple):
    cur = conn.cursor()
    if len(arg_tuple) > 0:
        cur.execute(dbquery, arg_tuple)
    else:
        cur.execute(dbquery,)
    result = cur.fetchall()
    cur.close()
    return result


# password for user
print('Database user: ')
pw = getpass.getpass()

connection = getConnection(pw)

sql = ""
bdargs = ()
if args.server_ip is None:
	sql = "SELECT id FROM information_schema.processlist WHERE user IN('database1', 'database2', 'database3') AND time > %s AND UPPER(info) LIKE 'SELECT%%';"
	bdargs = (args.total_sec)
else:
	sql = "SELECT id FROM information_schema.processlist WHERE user IN('database1', 'database2', 'database3') AND HOST like concat(%s, ':%%') AND time > %s AND UPPER(info) LIKE 'SELECT%%';"
	bdargs = (args.server_ip, args.total_sec)

resultSelect = DBexecute(connection, sql, bdargs)

ids_list = []
for row in resultSelect:
	ids_list.append(row['id'])

print(ids_list)
sql = ""
for id in ids_list:
	sql  += " CALL mysql.rds_kill( {} ); ".format(id)
print(sql)
#DBexecute(connection, sql, ())

connection.close()

tes-mysql-kill-long-queries-from-server.sql

I didnt test it. try yourself

DELIMITER //
CREATE PROCEDURE kill_all_long_process_from_server (server_ip varchar(400), total_time int)
  BEGIN

  DECLARE done INT DEFAULT 0;
  DECLARE process_id INT;
  DECLARE result varchar(4000);
  DECLARE cur1 CURSOR FOR
		SELECT id
		FROM information_schema.processlist
		WHERE user IN('usuario') AND time > total_time AND HOST like concat(server_ip, ':%') AND UPPER(info) LIKE 'SELECT%';
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

  
  SELECT *
		FROM information_schema.processlist
		WHERE user IN('usuario') AND time > total_time AND HOST like concat(server_ip, ':%') AND UPPER(info) LIKE 'SELECT%';
  
  OPEN cur1;

  REPEAT
    FETCH cur1 INTO process_id;
    IF NOT done THEN
      kill process_id;
    END IF;
  UNTIL done END REPEAT;

  CLOSE cur1;
  
  END
  //

DELIMITER ;