Cómo matar ciertas conexiones a una base de datos MySQL

El mantenimiento de las bases de datos o de los servidores a menudo lo realizan los administradores de bases de datos durante la noche. Pero estos procedimientos a veces están bloqueados por solicitudes largas o aplicaciones que permanecen bloqueadas mucho más tiempo de lo esperado.

Por lo general, se da prioridad a la aplicación y los procedimientos de mantenimiento a menudo se cancelan para no interferir con la aplicación. Pero a veces sucede que necesita eliminar una consulta de MySQL que lleva mucho tiempo y no se ha completado en un tiempo determinado. En este caso, es posible que necesite una forma rápida de desconectarse.

En este artículo, tratamos de considerar dos formas de resolver el problema anterior. Para ello utilizaremos dbForge Studio para MySQL.

Método 1: destruir las conexiones de inicio de sesión de usuario basadas en MySQL mediante un evento

El primer método es crear un evento MySQL especial. Cuando crea un evento, crea un objeto de base de datos específico que contiene una o más declaraciones SQL que se ejecutarán en uno o más intervalos regulares, comenzando y terminando en una fecha y hora específicas.

Utilice el siguiente script para crear un evento en la base de datos:
[code lang=”SQL”]DELIMITADOR $
CREA un evento kill_process_event
EN EL HORARIO DE CADA "1" DÍA
INICIO '2019-03-20 08:45:30'
HACER
EMPEZAR
DECLARAR kill_done int;
EXPLIQUE el controlador de SALIDA PARA EL CONJUNTO NO ENCONTRADO kill_done = 1;
EMPEZAR
DECLARAR cursor_ID int;
EXPLICAR cursor_i CURSOR PARA
ESCOGER
identificador
Con information_schema.PROCESSLIST
DONDE AL USUARIO LE GUSTA 'test%'
Y ID! = ID_CONEXIÓN ();
ABRIR cursor_i;
leer_ciclo:
CÍRCULO
FETCH cursor_i EN cursor_ID;
OLVIDAR CONEXIÓN cursor_ID;
SI KILL_DONE ENTONCES
Deja el ciclo de lectura;
TERMINARA SI;
FIN DEL BUCLE;
CERRAR cursor_i;
FIN;
FIN
PS

ALTERAR EVENTO kill_process_event
INCLUIR
PS

DIVISIÓN;[/code]
Los siguientes pasos

• Asignar valores propios a los parámetros requeridos. A saber, el horario, la hora y el inicio de sesión del usuario.
• Programar un evento
• Establecer la variable de servidor event_scheduler = ON

Cómo planificar un evento
Para programar un evento, haga clic derecho en el Explorador de objetos y seleccione Editar evento de la ventana emergente. En el asistente que se abre, puede configurar las fechas de inicio y finalización, las recurrencias y otras configuraciones relevantes para el evento.

En nuestro ejemplo, queremos eliminar la conexión para el usuario test% en la base de datos sakila. La siguiente figura muestra el resultado de la consulta SHOW FULL PROCESSLIST de MySQL antes de realizar los pasos descritos en el Método 1.

Después de ejecutar el script anterior, se destruirán todas las conexiones al inicio de sesión del usuario ('test%').

La desventaja de este método es que cuando cambia las condiciones para seleccionar una ID de sesión, necesita cambiar el objeto (evento).

Como alternativa, puede crear un procedimiento y un evento guardados para llamar a ese procedimiento.

Finalice las conexiones de MySQL en función del inicio de sesión del usuario mediante un evento que desencadena un procedimiento almacenado

En esencia, este método es una extensión a largo plazo. Esto implica crear un procedimiento MySQL y luego programar un evento para llamar a ese procedimiento.

1. Primero necesita crear un procedimiento almacenado en la base de datos:

[code lang=”SQL”]DELIMITADOR $
CREAR PROCEDIMIENTO kill_process_proc ()
EMPEZAR
DECLARAR kill_done int;
EXPLIQUE el controlador de SALIDA PARA EL CONJUNTO NO ENCONTRADO kill_done = 1;
EMPEZAR
DECLARAR cursor_ID int;
EXPLICAR cursor_i CURSOR PARA
ESCOGER
identificador
Con information_schema.PROCESSLIST
DONDE AL USUARIO LE GUSTA 'test%'
Y ID! = ID_CONEXIÓN ();
ABRIR cursor_i;
leer_ciclo:
CÍRCULO
FETCH cursor_i EN cursor_ID;
OLVIDAR CONEXIÓN cursor_ID;
SI KILL_DONE ENTONCES
Deja el ciclo de lectura;
TERMINARA SI;
FIN DEL BUCLE;
CERRAR cursor_i;
FIN;
FIN $
DIVISIÓN;[/code]

Debe asignar el valor requerido al parámetro USUARIO. En nuestro ejemplo, esto es 'test%'.

2. Luego, después de haber preparado el procedimiento, puede crear un evento para llamarlo y ejecutarlo según lo programado. Considere el siguiente escenario:
[code lang=”SQL”]CREAR DEFINITOR = 'raíz' @ 'localhost'
EVENTO sakila.kill_process_event
EN EL HORARIO DE CADA "1" DÍA
INICIO '2019-03-19 14:53:00'
HACER
EMPEZAR
LLAME a kill_process_proc ();
FIN;

ALTERAR EVENTO sakila.kill_process_event
INCLUIR;[/code]

3. Seguimiento

• Asignar valores propios a los parámetros requeridos. Es decir, el horario, la hora, la base de datos y el inicio de sesión del usuario.
• Programar un evento
• Establecer la variable de servidor event_scheduler = ON

Después de seguir los pasos anteriores, se destruirán todas las conexiones de inicio de sesión de usuario ('test%').

Método 2: cree un archivo bat para ejecutar el cliente MySQL y el procedimiento con la configuración

También puede crear un archivo BAT ejecutable para ejecutar el cliente MySQL y el procedimiento con parámetros a través de la interfaz de línea de comandos.
Primero necesita crear un procedimiento con parámetros. Para hacer esto, use el siguiente script:

[code lang=”SQL”]DELIMITADOR $
CREAR PROCEDIMIENTO kill_process_param (Usuario VARCHAR (255))
EMPEZAR
DECLARAR kill_done int;
EXPLIQUE el controlador de SALIDA PARA EL CONJUNTO NO ENCONTRADO kill_done = 1;
EMPEZAR
DECLARAR cursor_ID int;
EXPLICAR cursor_i CURSOR PARA
ESCOGER
identificador
Con information_schema.PROCESSLIST
DONDE USUARIO COMO usuario
Y ID! = ID_CONEXIÓN ();
ABRIR cursor_i;
leer_ciclo:
CÍRCULO
FETCH cursor_i EN cursor_ID;
OLVIDAR CONEXIÓN cursor_ID;
SI KILL_DONE ENTONCES
Deja el ciclo de lectura;
TERMINARA SI;
FIN DEL BUCLE;
CERRAR cursor_i;
FIN;
FIN $
DIVISIÓN;[/code]
El procedimiento correspondiente aparecerá en la base de datos. Veamos esto usando dbForge Studio para MySQL.

A continuación, debe crear un archivo BAT.

Para crear un archivo BAT:
1. Abra un editor de texto simple, como el Bloc de notas.
2. Introduce el siguiente código:

"C:Program FilesMySQLMySQL Server 8.0binmysql.exe" --user=<user> --password=<password> --host=<host> --port=<port> --Base de datos=<database_name> --execute="CALL kill_process_param('%1')"

3. Asigne sus propios valores a los parámetros de nombre de usuario, contraseña, host, puerto y nombre de la base de datos.

4. Guarde el archivo con la extensión .bat.

Ahora necesita ejecutar el archivo bat con el parámetro.

Todas las conexiones de inicio de sesión de usuario ('test%') se destruirán como resultado.

Conclusión

Saber cómo eliminar un proceso en MySQL Server puede ser una herramienta útil en su kit de herramientas. Sin embargo, recomendamos usar estas técnicas con moderación.