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.

Índice

    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.

    La opción Editar evento en dbForge Studio para MySQL

    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.

    El resultado de la consulta MOSTRAR LISTA DE PROCESOS COMPLETA

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

    El resultado de ejecutar el script.

    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.

    Procedimiento con parámetros en 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.

    Ejecute el archivo .bat a través de la línea de comando

    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.

    Artículos de interés

    Subir