Publi

Cómo expulsar todas las sesiones MySQL de un usuario en particular desde un script

502729552_aaa355b1ce_o

Muchas veces, puede que tengamos una aplicación no muy bien depurada en nuestro servidor web. A dicha aplicación le hemos dado su propio usuario MySQL y observamos que las conexiones no se cierran adecuadamente.

Temporalmente puede que la solución sea expulsar de vez en cuando todos los usuarios que siguen activos, con el fin de no saturar nuestro servidor MySQL. Puede que en otros servicios que tengamos activos hayamos visto el mensaje “Too many connections” impidiendo así el acceso a los demás servicios.

La primera detección del problema la podemos hacer gracias al comando “SHOW PROCESSLIST” de MySQL:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
> SHOW PROCESSLIST;
+---------+-------------+--------------------------------+-------------+---------+------+-------+------------------+
| Id      | User        | Host                           | db          | Command | Time | State | Info             |
+---------+-------------+--------------------------------+-------------+---------+------+-------+------------------+
| 2485425 | gooduser    | xxx.xxx.xxx.xxx:xxxxx          | db1         | Sleep   |    4 |       | NULL             |
| 2486333 | eviluser    | hostmalvado.com:57118          | devildb     | Sleep   |  243 |       | NULL             |
| 2486345 | eviluser    | hostmalvado.com:57119          | devildb     | Sleep   | 1739 |       | NULL             |
| 2486346 | eviluser    | hostmalvado.com:57120          | devildb     | Sleep   |  799 |       | NULL             |
| 2486359 | eviluser    | hostmalvado.com:57121          | devildb     | Sleep   |  739 |       | NULL             |
| 2486360 | eviluser    | hostmalvado.com:57122          | devildb     | Sleep   |  712 |       | NULL             |
| 2486364 | eviluser    | hostmalvado.com:57123          | devildb     | Sleep   | 9334 |       | NULL             |
| 2486365 | eviluser    | hostmalvado.com:57124          | devildb     | Sleep   | 9043 |       | NULL             |
| 2486376 | eviluser    | hostmalvado.com:57125          | devildb     | Sleep   | 5955 |       | NULL             |
| 2486382 | eviluser    | hostmalvado.com:57126          | devildb     | Sleep   | 5012 |       | NULL             |
| 2486383 | eviluser    | hostmalvado.com:57127          | devildb     | Sleep   | 5939 |       | NULL             |
| 2486384 | eviluser    | hostmalvado.com:57128          | devildb     | Sleep   | 5032 |       | NULL             |
| 2486387 | eviluser    | hostmalvado.com:57129          | devildb     | Sleep   | 1603 |       | NULL             |
| 2486388 | eviluser    | hostmalvado.com:57130          | devildb     | Sleep   |   16 |       | NULL             |
| 2486403 | eviluser    | hostmalvado.com:57131          | devildb     | Sleep   |    5 |       | NULL             |
| 2486405 | eviluser    | hostmalvado.com:57132          | devildb     | Sleep   |    1 |       | NULL             |
| 2486408 | root        | xxx.xxx.xxx.xxx:xxxxx          | NULL        | Query   |    0 | init  | SHOW PROCESSLIST |
| 2486412 | gooduser2   | xxx.xxx.xxx.xxx:xxxxx          | db2         | Sleep   |    0 |       | NULL             |
+---------+-------------+--------------------------------+-------------+---------+------+-------+------------------+
18 rows in set (0.00 sec)

Como vemos, tenemos los usuarios gooduser y gooduser2, que son buenos, usan la base de datos y no dan problemas. Luego tenemos root, que soy yo ahora mismo, pidiendo el listado de procesos y un montón de veces ha entrado eviluser, que además vemos que tiene conexiones abiertas desde hace mucho tiempo, la columna Time nos devuelve la cantidad de segundos que el hilo lleva en el mismo estado, lo que puede indicar una conexión no cerrada adecuadamente.

Diseño del script

Aunque podemos utilizar un bucle en MySQL, he preferido hacerlo con un script en bash que va matando uno a uno los procesos. Tal vez sea la forma más lenta de hacerlo, pero no tenemos que meter un procedure en el servidor MySQL para ejecutarlo, y copiando y pegando el código desde la página nos vale.

Script rápido

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
#!/bin/bash

HOST="localhost"
USER="root"
PASS=""
MINTIME=600

function printhelp()
{
    echo "Kill MySQL Sessions"
    echo "Arguments:"
    echo "   --user, -u : MySQL user"
    echo "   --pass, -p : MySQL password"
    echo "   --host, -h : MySQL host"
    echo "   --socket, -s : MySQL socket"
    echo "   --kick, -k : User to kick out"
    echo "   --time, -t : Min. time after last state change"
    exit 1
}

while [ "$#" -gt 0 ]
do
    case "$1" in
    --help)
        printhelp
        ;;
    -u|--user)
        USER="$2"
        shift
        ;;
    -p|--pass)
        PASS="$2"
        shift
        ;;
    -h|--host)
        HOST="$2"
        shift
        ;;
    -s|--socket)
        SOCKET="$2"
        shift
        ;;
    -k|--kick)
        KICKUSER="$2"
        shift
        ;;
    -t|--time)
        MINTIME="$2"
        shift
        ;;
    *)
        echo "Invalid option '$1'."
        printhelp
        exit 1
        ;;
    esac
    shift
done

CONDITION=""
if [ -n "$KICKUSER" ]
then
    CONDITION="User='$KICKUSER'"
fi

if [ -n "$MINTIME" ] && [ "$MINTIME" -gt 0 ]
then
    if [ -z "$CONDITION" ]
    then
    CONDITION="Time>$MINTIME"
    else
    CONDITION="$CONDITION AND Time>$MINTIME"
    fi
fi

CONNECTIONSTR=""

if [ -n "$SOCKET" ]
then
    CONNECTIONSTR="$CONNECTIONSTR --socket $SOCKET"
else
    if [ -z "$HOST" ]
    then
    echo "You must, at least, specify the host to connect to. -h"
    exit 1;
    else
    CONNECTIONSTR="$CONNECTIONSTR --host $HOST"
    fi
fi

if [ -n "$USER" ]
then
    CONNECTIONSTR="$CONNECTIONSTR -u $USER"
fi

if [ -n "$PASS" ]
then
    CONNECTIONSTR="$CONNECTIONSTR -p$PASS"
fi

if [ -z "$CONDITION" ]
then
    read -r -p "Are you sure you want to kick out all connections? [y/N] " response
    response=${response,,}
    if [[ $response =~ ^(yes|y|si|s)$ ]]
    then
    CONDITIONSTR=""
    else
    echo "Action cancelled"
    exit 2
    fi
else
    CONDITIONSTR="WHERE $CONDITION"
fi

THREADIDS=`echo "SELECT ID FROM information_schema.processlist $CONDITIONSTR" | mysql $CONNECTIONSTR --skip-column-names`
if [ -z "$THREADIDS" ]
then
    echo "No users to kick out"
    exit 3
fi
echo "Killing "$(echo "$THREADIDS" | wc -w)" connections..."
for tid in $THREADIDS
do
    echo "Killind ID "$tid"..."
    mysql $CONNECTIONSTR -e "KILL $tid"
done

De esta forma podremos expulsar los usuarios que hay actualmente conectados. Veamos detenidamente las opciones de línea de comandos para este script.

  • –user y –pass : Eso está claro, ¿no?
  • –socket : Especifica el socket unix con el que vamos a conectar, puede que tu base de datos no esté escuchando ningún puerto en nuestro host y tengamos que conectar de esta forma.
  • –host : En cualquier caso, si no conectamos por socket, debemos conectar por host, así que uno de los dos debe estar especificado.
  • –kick : Usuario que queremos expulsar, en nuestro caso eviluser.
  • –time : Tiempo mínimo sin dar señales de vida. Es decir, el tiempo que lleva la conexión sin cambiar de estado. El script, por defecto señala un tiempo mínimo de 600 segundos (10 minutos), aunque podemos variarlo, incluso poner 0 para no especificar tiempo.

Hay que tener en cuenta que si no especificamos usuario y pedimos que el tiempo sea 0, preguntará si de verdad queremos echar a todos los usuarios actuales del sistema.

Requerimientos

Como mínimo necesitamos MySQL 5.1.7 porque es cuando introdujeron la opción de consultar el listado de procesos desde la base de datos information_schema.
Y bash 4 o superior, por la pregunta al usuario cuando el tiempo es 0 y no hemos metido usuario. Si quitamos esa parte, podría funcionar sin problema con versiones muy antiguas de Bash.

Más referencias

Resultado de un SELECT de MySQL en un array de BASH

Foto principal: TheGlantVermin

También podría interesarte...

Leave a Reply

Current ye@r *