Diferencia entre revisiones de «Mysql Ficheros de log»

De MediaWiki
Ir a la navegación Ir a la búsqueda
Línea 146: Línea 146:
  
  
−
=Registro binario=
+
=El registro binario=
  
  
Línea 239: Línea 239:
  
 
</gallery>
 
</gallery>
−
 
−
 
  
 
=Registro slow query=
 
=Registro slow query=

Revisión del 14:42 17 oct 2017

Introducción

  • Los ficheros de log, son ficheros que registran eventos que suceden durante la utilización del gestor Mysql.
  • Algunos de dichos ficheros ya los hemos visto durante la instalación.
  • Tenemos que tener cuidado, a la hora de hacer uso de dichos registros, de vigilar el tamaño ya que crecerán indefinidamente hasta acabar con el espacio de disco.
  • Una forma de evitarlo es utilizar (en Linux) crontab, una tarea programada que ejecuta cada cierto tiempo un script en el que se borren o muevan los logs antiguos.
1 * * * * *  find  /path/to/*.log -mtime +7 -exec rm -f {} \;
En este ejemplo se borraría los ficheros de log de más de 7 días de antigüedad.


Más información en este enlace.



Tipos de ficheros de log

  • Existen 4 tipos de ficheros de log que registran diferentes informaciones.
  • El registro de error: Registra los problemas encontrados al iniciar, ejecutar o parar el servicio mysqld.
  • El registro de consultas: Registra las conexiones de clientes establecidas y las sentencias ejecutadas.
  • El registro binario: Registra todas las sentencias que cambian datos. Es utilizado en la replicación.
  • El registro slow: Registra todas las sentencias que han tardado más segundos que los especificados en la variable del sistema long_query_time.


  • Por defecto, todos los archivos de log son creados en el directorio de datos, el cual viene indicado por la variable del sistema data_dir la cual tiene el valor por defecto de /var/lib/mysql.


  • Se puede 'forzar' al gestor Mysql a que cierre y reabra los archivos de registros mediante:
  • La orden SQL: FLUSH LOGS
  • El comando: mysqladmin -uroot -p flush-logs
En el caso de los ficheros binarios, se cerrará el existente y se creará uno nuevo. El en resto, no se creará un nuevo fichero. Para que suceda eso, tendremos que 'mover' los ficheros antiguos antes de hacer el flush de la forma: 
1 shell> cd mysql-data-directory
2 shell> mv mysql.log mysql.old
3 shell> mv mysql-slow.log mysql-slow.old
4 shell> mysqladmin flush-logs


  • La orden SQL Flush puede afectar sólo a un tipo de fichero de registro, al igual que la herramienta cliente mysqladmin.

El registro de errores (error.log)

Recuerda en en distribuciones UBUNTU, debemos otorgar permisos de acceso (por el sistema AppArmor) si utilizamos un directorio diferente al que trae por defecto, como vimos en este punto de la instalación, el 'caso especial: los directorios'.


  • En el caso de que el servidor Mysql no se inicie, debemos acudir a este fichero para ver lo que ha ocurrido.
Una forma rápida de ver los posibles errores es mediante la orden: 
1 tail -n200 /var/log/mysql/error.log | grep ERROR
Esta orden buscará en las últimas 200 líneas del archivo de log, aquellas que contengan la palabra ERROR.


  • Otra posibilidad en el caso de que no muestre información es ejecutar la orden:
1 sudo service mysql status



  • Si queremos realizar un flush de este fichero solamente tendremos que:
  • Ejecutar la orden SQL: flush error logs
  • Desde consola con la orden: mysqladmin -uroot -p flush-logs error
Podemos consultar la sintaxis completa de la orden SQL FLUSH en este enlace.

Ejercicio

  • Busca el valor de la variable del sistema en la que se define la ruta y el nombre del fichero de log. ¿ Se puede cambiar su valor con la orden SQL set ?
  • Cambia alguna entrada del archivo de configuración del servidor para provocar un error y que el servicio no pueda iniciarse.
Busca el error en el fichero de log.
Deja el fichero como estaba e inicia el servicio.
  • Cambia el archivo del archivo de log a error.log.old (desde el sistema de archivos, por consola) y haz un flush de este log para que cree uno nuevo.


Solución Ejercicio

  • Fichero de log del servicio

  • Ejecutamos la orden SQL show variables like '%error%' y podemos comprobar como la ruta y nombre del fichero de log es: /var/log/mysql/error.log. No podemos cambiar su valor ya que no es dinámica.

  • Cambiamos la variable del sistema user por user2. Guardamos y reiniciamos el servicio (service mysql restart), provocando un error del servicio que no puede iniciarse (service mysql status). 

  • grep ERROR. Dejaremos el fichero de configuración como estaba e iniciamos el servicio.

  • Renombramos el archivo de registro de errores, hacemos un flush del fichero de registro de errores y comprobamos que se crea el nuevo fichero de log.

El registro general de consulta

  • Este registro sirve para registrar todas las sentencias que los clientes envían al servidor.
Debemos de tener en cuenta que el orden en que se registran no tiene por qué coincidir con el orden en que se van a ejecutar en el servidor Mysql.
Los valores posibles son: 0 | 1 o ON | OFF.
  • El nombre del fichero, por defecto, es: nombre_del_host-log (en linux podemos saber el nombre del host en el archivo de configuración /etc/hostname/) y la ruta es la de datos (/var/lib/mysql). Podemos cambiar tanto la ruta como el nombre con la variable del sistema general_log_file.


  • Hacer un flush de este fichero no lo reinicia, sólo lo cierra y abre.
  • Si queremos realizar un flush de este fichero solamente tendremos que:
  • Ejecutar la orden SQL: flush general logs
  • Desde consola con la orden: mysqladmin -uroot -p flush-logs general


Ejercicio

  • Busca el valor de la variable del sistema en la que se define la ruta y el nombre del fichero de log general. ¿ Se puede cambiar su valor con la orden SQL set ?
Cambia el valor de la variable para que el nombre sea: MiMYSQL.log
Activa el registro de este fichero.
  • Ejecuta una orden SQL y comprueba que queda registrada en el registro de log.
  • Deja todo a sus valores por defecto.


Solución Ejercicio

  • Fichero de log general

  • Ejecutamos la orden: show variables like 'general_log_file'. Se puede cambiar ya que es dinámica.

  • Cambiamos el valor de la variable y activamos el registro con la orden: set general_log=ON

  • Podemos comprobar como el fichero existe físicamente y en su interior queda registrada la sentencia SQL ejecutada (en mi caso un select sobre la tabla departments).

  • Dejamos todo a sus valores por defecto con la palabra clave 'DEFAULT'.



El registro binario

  • El registro binario se encargar de registrar todas las sentencias que producen o pueden producir modificaciones sobre los datos.
Ejemplos SQL de este tipo de sentencias son: DELETE, INSERT, UPDATE, CREATE,..
  • Las sentencias se almacenan en forma de eventos que describen las modificaciones.
  • También registra información sobre cuanto ha tardado en ejecutarse una sentencia.


  • El propósito principal del registro binario es el de recuperar la base de datos desde la última copia de seguridad hasta el momento del fallo.
  • Si está activado el rendimiento del servidor baja sobre un 1%, pero los beneficios de tenerlo activado son mayores que los de no tenerlo.
Únicamente no deberías tenerlo activado si las operaciones de actualización sobre la base de datos son casi nulas y la mayor parte de son operaciones de consulta.


Importante: Es necesario asignar un valor a la variable del sistema server-id para que el servidor pueda iniciar el servicio. Esta variable es utilizada por Mysql durante la replicación de servidores, pero por un bug, es necesario que tenga un valor para activar el registro binario de log.


La modificación no implica darle un valor. Se puede iniciar el servicio con dicha opción de la forma: mysqld --log-bin y creará un fichero por defecto como se indica a continuación.
Una vez activada, se crea un fichero de registro que tiene por nombre por defecto de: nombre_host-bin, escribiéndose en el directorio de datos (/var/lib/mysql/).
Al nombre anterior, el servicio mysql añade un número que se va incrementando en los siguientes casos:
  • Para hacer un flush podemos realizar una de estas operaciones:
  • SQL: flush binary logs
  • Herramienta cliente: mysqladmin -uroot -p flush-logs binary


  • Para averiguar que registros binarios se han utilizado, se crea un archivo de índice de los registros binarios que contiene los nombres de todos los archivos de registro binario utilizados. Por defecto tiene el mismo nombre que el registro binario pero con la extensión '.index'.
Para cambiar el nombre de este archivo debemos hacer uso de la variable del sistema lob-bin-index.
  • Un usuario con permisos SUPER, puede desactivar el registro de sus sentencias en el fichero de registro binario, con la orden SQL: set SQL_LOG_BIN=0.
Esto puede ser muy útil si no queremos que nuestras órdenes SQL queden registradas en el fichero de log.
  • Para borrar los archivos de registro binario podemos:
  • Orden SQL: RESET MASTER: Borra todos los registros binarios.
  • Orden SQL: PURGE MASTER LOGS TO 'fichero de log': Borra todos los ficheros hasta el fichero indicado.
  • Orden SQL: PURGE MASTER LOGS BEFORE 'fecha-hora': Borra todos los ficheros de logs hasta la fecha-hora indicada. Por ejemplo: PURGE BINARY LOGS BEFORE '2008-04-02 22:46:26';
  • Las órdenes son guardadas en el mismo orden en que son ejecutadas para que se pueda volver a reconstruir la base de datos a su estado original.


  • Podemos indicar el formato cómo se guardarán las sentencias en el fichero de log, con la variable del sistema binlog-format.
Más información en este enlace.


  • Para poder consultar la información guardada en el fichero de log debemos hacer uso de la herramienta cliente: mysqlbinlog.
Por ejemplo, para que el servidor Mysql vuelva a ejecutar las sentencias guardadas en el fichero de log: 
1 mysqlbinlog VMPROGRAMACION-bin.000001 | mysql -uroot -p
Siendo VMPROGRAMACION-bin.000001, el fichero binario de log creado.



Ejercicio

  • Activa el registro de los ficheros binarios de log.
Comprueba que la ejecución de una operación de manipulación de datos queda reflejada en dicho registro.
  • Establece el tamaño máximo de cada archivo a 4096 bytes.
  • Provoca que se genere un nuevo fichero binario de log.
  • Ejecuta una orden SQL que provoque una manipulación de datos o de cambios en la estructura, por ejemplo un 'create table'.
Haz un nuevo flush del fichero binario y anota cual es el último creado (al anterior al último creado, es el fichero de log donde está la orden create).
Borra la tabla creada y vuelve a aplicar el fichero binario donde está guardada la creación de la tabla.


Solución Ejercicio

  • Registros binarios de log

  • Activamos el registro binario de log cambiando el fichero de configuración de Mysql. Reiniciamos el servicio.

  • Comprobamos como están creados los ficheros binarios de log.

  • Cambiamos la variable del sistema max-binglog-size para darle el tamaño de 4096 bytes.

  • Ejecutamos la orden SQL: flush binary logs y comprobamos como se genera un nuevo fichero de log.

  • Creamos una nueva tabla de ejemplo en la base de datos Proba22 y después ejecutamos la orden SQL: flush binary logs

  • Podemos comprobar como se crea un nuevo fichero de registro binario de log (el 000002). Por tanto el fichero donde está la orden de creación de la tabla es el 000001. Borramos ahora la tabla creada. 

  • mysql -uroot -p. Al actualizar la base de datos podemos comprobar como la tabla vuelve a estar creada.

Registro slow query

  • Este tipo de registro puede ser utilizado para estudiar aquellas sentencias SQL que se ejecutan de forma lenta.
No siempre conlleva la conclusión que dichas consultas están mal hechas ya que puede darse el caso de que se ejecutaran en un momento en el que el servidor se encontraba cargado de trabajo.
Si, de todas formas, aparecen las mismas consultas en días diferentes y a horas distintas, puede que sean candidatas a algún tipo de optimización.


  • Para activarlo debemos modificar el valor de la variable del sistema slow_query_log, la cual es dinámica y global.
Al activarla, empezará a registrar aquellas consultas que tarden más del tiempo en segundos indicado por la variable del sistema long_query_time (por defecto 10 segundos) y que dicha orden de consulta obtenga un mínimo de filas indicado por la variable del sistema min_examined_row_limit (por defecto 0 filas, es decir, cualquier consulta que tarde más del tiempo especificado).


  • Por defecto, guardará las consultas en el directorio de datos bajo el nombre /var/lib/mysql/host_name-slow.log.
Si queremos cambiar el nombre debemos modificar la variable del sistema slow_query_log_file.


  • Para ver un resumen de las sentencias guardadas en el fichero de registro, se puede hacer uso de la herramienta cliente: mysqldumpslow.
Con esta herramienta podemos ver un resumen del registro 'slow query', ver las sentencias en orden inverso al que fueron ejecutadas,....diversas opciones que podéis consultar en el enlace.


  • Si queremos realizar un flush de este fichero solamente tendremos que:
  • Ejecutar la orden SQL: flush slow logs
  • Desde consola con la orden: mysqladmin -uroot -p flush-logs slow
Hacer un flush en este tipo de fichero supone cerrarlo y abrirlo.




Ejercicio

  • Modifica las variables del sistema necesarias para que se empiecen a registrar las consultas que tarden más de 1 segundo en ejecutarse.
  • Ejecuta la orden SQL siguiente:
1 select *
2 from employees.salaries
3 where employees.salaries.from_date='1995-12-02';
Debería tardar sobre un segundo y media en ejecutarse.
Comprueba que queda registrada en el fichero de slow query.
  • Comprueba su contenido con la herramienta cliente: mysqldumpslow
  • Vuelve a dejar todo a sus valores originales.


Solución

  • Ejercicio Slow query log

  • Cambiamos la variable del sistema que inicia el registro de las consultas con la orden SQL set global slow_query_log=1;

  • La variable del sistema que cambia el tiempo a partir del cual se empiezan a registrar las consultas es 'global' y de 'sesión'. Por lo tanto, si la cambiamos globalmente tendríamos que volver a conectarnos para que coja el nuevo valor, mientras que si la cambiamos a nivel de sesión, la conexión actual tendría el nuevo valor. Optamos por esta segunda forma.

  • En la misma conexión, realizamos la consulta SQL indicada.

  • Podemos comprobar como el fichero de log está creado y tiene datos.

  • Ejecutamos la herramienta cliente: mysqldumpslow

  • Dejamos todo a sus valores por defecto (cambiar el valor de la variable de sesión en la misma conexión donde se cambió previamente).





-- Ángel D. Fernández González -- (2017).

Obtenido de «https://wiki.cifprodolfoucha.es/index.php?title=Mysql_Ficheros_de_log&oldid=4274»