Cómo activar el slow query log de MySQL

MySQL puede generar automáticamente un log con las consultas más lentas. De este modo podrás saber cuáles son las consultas más lentas que se ejecutan en tu sistema o en tu servidor, de modo que puedas optimizarlas. Este log se puede almacenar tanto en un archivo de texto plano como en una tabla de una base de datos.

En este tutorial vamos a ver cómo activar el log de slow queries en MySQL. Además, en caso de que uses una tabla como método del almacenamiento del log de consultas, también veremos cómo exportarlas a un archivo de texto plano.

Contenidos

1 Cómo activar el Slow Query Log de MySQL
- 1.1 Método A: Utiliza MySQL
- 1.2 Método B: Edita el archivo my.cnf
2 Verifica que el slow query log está activado
3 Desactiva el Slow Query Log de MySQL
- 3.1 Método A: Utiliza una consulta de MySQL
- 3.2 Método B: Edita el archivo my.cnf
4 Crea un archivo a partir de la tabla de Slow Queries

Cómo activar el Slow Query Log de MySQL

Vamos a ver cómo activar el Slow Query Log. Sin embargo, una vez hayas hecho las comprobaciones pertinentes, es recomendable que desactives el log de nuevo para no sufrir el impacto en el rendimiento de MySQL.

Lo primero que debes hacer es abrir la terminal de comandos o, si vas a activar el log en un servidor remoto, conectarte mediante SSH.

Existen dos métodos mediante los cuales puedes activar el Slow Query Log. Vamos a ver ambos.

Método A: Utiliza MySQL

Para activar el log de slow queries mediante la aplicación de terminal MySQL, sigue estos pasos:

Conéctate a MySQL, reemplazando usuario por tu nombre de usuario e introduciendo la contraseña cuando se te pida:
```
mysql -u usuario -p
```
Par activar el slow query log, ejecuta esta sentencia MySQL:
```
SET GLOBAL slow_query_log = 'ON';
```
Además de activar el log, también es posible que quieras configurar algunas opciones.
OPCIONAL: El slow query log almacenará por defecto aquellas consultas que tarden más de 10 segundos en ejecutarse. Sin embargo, puedes cambiar este intervalo estableciendo el tiempo máximo de ejecución con la siguiente consulta, reemplazando TIEMPO por el tiempo en segundos que quieras establecer:
```
SET GLOBAL long_query_time = TIEMPO;
```
OPCIONAL: Los resultados del log se almacenan por defecto en el archivo /var/lib/mysql/hostname-slow.log, siendo hostname el nombre del host sobre el que se ejecutan las consultas. Pero puedes cambiar la ruta y el nombre del archivo con la siguiente consulta, reemplazando ruta por la ruta absoluta hasta el archivo y archivo por el nombre que quieres que tenga el archivo de log:
```
SET GLOBAL slow_query_log_file = '/ruta/archivo';
```
OPCIONAL: Puedes establecer también si deseas almacenar el log en un archivo o en una base de datos. Para ello debes usar la opción log_output, cuyos posibles valores son FILE (valor por defecto), para almacenar el log en un archivo, o TABLE, para almacenar el log en una tabla. Si por ejemplo prefieres almacenar el log en una tabla, tendrás que ejecutar esta consulta:
```
SET GLOBAL log_output = TABLE;
```
Una vez hechos los cambios anteriores, sal de la aplicación MySQL.

Con esto ya debería estar activado el log de consultas lentas.

Método B: Edita el archivo my.cnf

Para activar el log de consultas lentas en MySQL bastará con que sigas los pasos que se describen a continuación:

Edita el archivo de configuración my.cnf de MySQL con permisos de administrador mediante algún editor de texto como nano o mcedit. Dependiendo de tu distribución de Linux, el archivo puede estar localizado en diferentes directorios:
- Si tu distribución es Ubuntu o Debian el archivo estará en el directorio /etc/mysql:
```
nano /etc/mysql/my.cnf
```
- Si tu distribución es Fedora o CentOS el archivo estará en el directorio /etc:
```
nano /etc/my.cnf
```
Localiza la sección [mysqld] del archivo my.cnf y agrega los siguientes valores del configuración al final de dicha sección, reemplazando /var/lib/mysql/slow.log por la ruta y nombre del archivo en donde quieres que se almacena el log de consultas lentas, aunque por lo general querrás usar el mismo archivo de este ejemplo:
```
slow_query_log=1
long_query_time=1
log_output=FILE
slow_query_log_file=/var/lib/mysql/slow.log
```
Si no indicamos un valor para la opción long_query_time, el slow query log almacenará solamente las consultas que tarden más de 10 segundos en ejecutarse. Puedes establecer este tiempo agregando la siguiente línea, reemplazando TIEMPO por el tiempo en segundos que quieras establecer:
```
long_query_time = TIEMPO
```
Entrando algo más en detalle, puedes configurar la ubicación del log modificando la línea slow_query_log_file, reemplazando ruta por la ruta absoluta del archivo y archivo por el nombre que quieras darle:
```
slow_query_log_file = /ruta/archivo
```
Puedes configurar si prefieres que el log se almacene en un archivo o en una tabla. Para ello debes usar la opción log_output, que puede tener el valor FILE (valor por defecto), que permite almacenar el log en un archivo, o TABLE, para permite almacenar el log en una tabla. En caso de que prefieras almacenar el log en una tabla, tendrías que darle el valor TABLE a la opción:
```
log_output=TABLE
```
Guarda el archivo my.cnf. Si usas nano, pulsa CTRL + X y confirma los cambios con y.
Reinicia el servicio de MySQL de tu sistema, que o bien se llamará mysql o mysqld, así que si el servicio mysql no existe, bastará con que pruebes con mysqld. Reinicia k MySQL de tu sistema mediante uno de los siguientes comandos:
- En Ubuntu o Debian:
```
service mysql restart
```
- En Fedora o CentOS:
```
service mysqld restart
```
En caso de que tu sistema use systemctl, tendrás que usar el siguiente comando en su lugar:
```
systemctl restart mysql
```
Una vez reiniciado el servicio y aplicada la configuración, ya debería estar activado el Slow Query Log de MySQL.

Con esto ya debería estar activado el log de consultas lentas.

Verifica que el slow query log está activado

Para verificar que has activado el log y que funciona correctamente, primero ejecuta el siguiente comando para conectarte a MySQL:

mysql -u usuario -p

Luego ejecuta la siguiente consulta, reemplezando TIEMPO por un valor superior al tiempo que has establecido en la opción long_query_time:

SELECT SLEEP(TIEMPO);

Esta consulta debería aparecer en el log, así que ábrelo y compruébalo.

Desactiva el Slow Query Log de MySQL

Una vez hayas realizado todos los cambios o comprobaciones deseadas en los logs de MySQL, puedes desactivarlo si así lo deseas. Puedes hacerlo mediante uno de estos dos métodos:

Método A: Utiliza una consulta de MySQL

Para desactivar el log mediante mysql, sigue estos pasos:

Conéctate primero a MySQL mediante el siguiente comando.
```
mysql -u usuario -p
```
Ejecuta la siguiente consulta para desactivar la opción slow_query_log:
```
SET GLOBAL slow_query_log = 'OFF';
```
Reinicia el servicio de MySQL de tu sistema mediante uno de los siguientes comandos:
- En Ubuntu o Debian:
```
service mysql restart
```
- En Fedora o CentOS:
```
service mysqld restart
```
En caso de que tu sistema use systemctl, tendrás que usar el siguiente comando en su lugar:
```
systemctl restart mysql
```
Con esto ya deberías haber desactivado el log de consultas lentas.

Si quieres volver a activar el log, basta con que vuelvas a seguir uno de los métodos de activación anteriores.

Método B: Edita el archivo my.cnf

Al igual que antes, también puedes desactivar el log editando el archivo my.cnf. Para ello sigue estos pasos:

Edita el archivo my.cnf. Si usas el editor nano puedes usar este comando:
```
nano /etc/my.cnf
```
Luego establece a 0 el valor de la opción slow_query_log:
```
slow_query_log=0
```
Cierra el editor guardando los cambios. Si usas nano, pulsa CTRL + X y confirma los cambios con y.
Reinicia el servicio de MySQL de tu sistema mediante uno de los siguientes comandos:
- En Ubuntu o Debian:
```
service mysql restart
```
- En Fedora o CentOS:
```
service mysqld restart
```
En caso de que tu sistema use systemctl, tendrás que usar el siguiente comando en su lugar:
```
systemctl restart mysql
```
Con esto ya deberías haber desactivado el log de consultas lentas.

Para volver a activar el log basta con que sigas los pasos para activarlo que ya hemos visto.

Crea un archivo a partir de la tabla de Slow Queries

Si almacenas el log de consultas lentas en una tabla de la base de datos, quizás quieras exportarlo a un archivo de texto para analizarlo con ciertas herramientas.

Para exportar el log a un archivo de texto, ejecuta el siguiente comando desde la terminal de comandos, reemplanzando USUARIO por el nombre de usuario con el que te quieras conectar y nombre_archivo.log por el nombre que el quieras dar al archivo resultante:

mysql -u USUARIO -p --raw --skip-column-names --quick --silent --no-auto-rehash --compress -e "SELECT CONCAT('# Time: ', DATE_FORMAT(start_time, '%y%m%d %H:%i:%s'), CHAR(10), CHAR(13), '# User@Host: ', user_host, CHAR(10), CHAR(13), '# Query_time: ', TIME_TO_SEC(query_time), ' Rows_sent: ', rows_sent, ' Rows_examined: ', rows_examined, CHAR(10), CHAR(13), 'SET timestamp=', UNIX_TIMESTAMP(start_time), ';', CHAR(10), CHAR(13), sql_text, ';') FROM mysql.slow_log;" > nombre_archivo.log

Al ejecutar el comando anterior se te pedirá primero la contraseña del usuario indicado.

Esto ha sido todo. Espero que os haya sido útil.

Cómo activar el slow query log de MySQL

📚 Tutoriales

💘 Seguir

🏷️ Etiquetas

Cómo activar el Slow Query Log de MySQL

Método A: Utiliza MySQL

Método B: Edita el archivo my.cnf

Verifica que el slow query log está activado

Desactiva el Slow Query Log de MySQL

Método A: Utiliza una consulta de MySQL

Método B: Edita el archivo my.cnf

Crea un archivo a partir de la tabla de Slow Queries