Mysql Ficheros de log

De MediaWiki
Ir a la navegación Ir a la búsqueda

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 encarga 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 son operaciones de consulta.


  • Para activar este tipo de registro tenemos que 'activar' la variable del sistema log-bin, la cual no es dinámica. Dicha variable puede llevar un valor o simplemente la incluimos en el fichero de configuración o por línea de comando.
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.
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.


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 log-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.

El 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 medio 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).

Políticas de seguridad Backup/recovery


  • Tener una política de copias de seguridad es fundamental para un administrador, ya que será el medio para recuperar las bases de datos a un estado correcto después de un problema que conllevara la pérdida de las mismas.
Aquí debemos de distinguir si necesitamos realizar un backup de todo el gestor (de todas las bases de datos del mismo, incluidas las del sistema) o solo un backup de las bases de datos del cliente.
Normalmente tendremos un backup de todo el gestor.
Posteriormente, si no se hacen cambios sobre la estructura de las tablas, usuarios o configuración del gestor, debería llegar con realizar un backup de cada base de datos del usuario.
  • También debemos de tener en cuenta si queremos realizar un backup 'completo' o un backup 'incremental', es decir, recoger las operaciones que se hayan realizado contra la base de datos desde un backup anterior (completo o diferencial).
EL diferencial es más rápido de realizar y consume menos recursos de la máquina, por lo que siempre se realizará en los periodos de tiempo en los que el gestor está más ocupado.
El backup completo debe de realizarse al menos una vez, pero teniendo en cuenta que no podemos utilizar un único backup completo y aplicar todos los incrementales, ya que el proceso se haría muy largo a medida que tuviéramos más y más incrementales.
Por lo tanto haremos un backup completo en los períodos de tiempo de carga baja en el servidor. El número de backups completos va a depender de la cantidad de operaciones diarias que se realicen sobre el gestor.
Si estas operaciones son muy elevadas, podría ser necesario realizar un backup completo cada día y incremental cada pocos minutos.


  • La orden de consola para realizar copias de seguridad es mysqldump.
Podéis consultar diferentes opciones de uso en este enlace



Backup Completo

  • Para realizar un backup completo de todo el gestor mysql y de todas sus bases de datos y tablas vamos a activar el registro binario como vimos en el punto anterior.
  • Después debemos de ejecutar esta orden:
Partimos de que el usuario root tiene establecido una contraseña. Dicha contraseña se podría indicar en la línea de comandos después de la opción -p. 
1 mysqldump -uroot -p --all-databases --master-data --single-transaction > backup_COMPLETO.sql
Esta orden crea una copia de seguridad de todas las bases de datos creadas en el Mysql hasta el momento.
Parámetros:
  • all-databases: Todas las bases de datos. Si en vez de este parámetro ponemos: --databases db1 db2 db3 haría un backup de las bases de datos db1, db2 y db3 (por ejemplo).
  • master-data: Indica que también haga uso de los ficheros binarios y que pase dichas operaciones al archivo de backup que se está creando. Si no tenemos el registro binario activo, esta opción no habría que ponerla.
  • single-transaction: Inicia el backup creando una transacción con un nivel de bloqueo REPETEABLE READ, para poder acceder a los datos de forma consistente y que no haya operaciones a media realizar. Si se realiza un backup de tablas con un motor MyISAM deberemos de asegurarnos que en este instante no estén realizando operaciones de modificación. Más información en este enlace.



Backup incremental

  • Como comentamos anteriormente, cada flush del archivo binario de log, provoca la creación de un nuevo fichero de log donde se recogen las nuevas operaciones realizadas desde el momento de realizar el flush.
Un Backup incremental consiste en realizar una copia de dichos archivos.
Cuando se realiza un backupcompleto, los ficheros binarios de log anteriores a ese backup completo ya se podrían eliminar.
Podríamos eliminarlos automáticamente cuando creamos la copia completa con la opción 'delete-master-logs': 
1 mysqldump -uroot -p --all-databases --master-data --single-transaction --delete-master-logs > backup_COMPLETO.sql
Esta opción no es recomendable ponerla en un entorno de replicación Maestro-Esclavo, ya que puede que dichos archivos aún no fueran replicados por todos los esclavos.



Restauración

  • El proceso contrario se realiza de la siguiente forma:
  • Si disponemos de un archivo .sql, debemos ejecutar la siguiente orden:
1 mysql -uroot -p < backup_COMPLETO.sql
  • Si disponemos de archivos binarios de log, el proceso ya lo vimos antes:
1 mysqlbinlog VMPROGRAMACION-bin.000001 VMPROGRAMACION-bin.000002 | mysql -uroot -p
Podremos poner uno o más archivos binarios de log a restaurar. Debemos de restaurarlos en el mismo orden en que fueron creados.





Caso práctico

  • Crea un Snapshot de tu máquina virtual para tener una copia en VirtualBox.
  • Partimos que está activo el fichero de log binario.


  • Crea una copia completa de tu gestor Mysql.
Crea una nueva tabla en la base de datos 'employees' de nombre tabla_instante_1.
Añade algún dato a dicha tabla.
Realiza un flush del fichero binario.
  • Realiza una copia de seguridad de los ficheros binarios creados hasta el momento (sería la copia incremental).
Crea una nueva tabla en la base de datos 'employees' de nombre tabla_instante_2.
Añade algún dato a dicha tabla.
Realiza un flush del fichero binario.
  • Realiza una copia de seguridad de los ficheros binarios creados hasta el momento (sería la copia incremental).
Borra las dos tablas creadas (drop table).
Realiza un flush del fichero binario.



Para el servicio Mysql
Borra la base de datos Mysql directamente desde el sistema de archivos así como la base de datos 'employees' (recuerda que están en /var/lib/mysql/).
  • Intenta iniciar el servicio (no podrás). Comprueba que error genera el Mysql.
Restaura el gestor Mysql a su estado inicial.
Como medida de seguridad hasta que no restaures todos los datos, impide que se pueda conectar al mysql desde la red.
Nota: No podrás iniciar el servicio y por tanto no podrás restaurar la base de datos.
¿ Cómo podrías restaurarla ?
Pista: El servicio Mysql no puede levantarse sin la base de datos Mysql. Busca en los apuntes de instalación el comando para crear las bases de datos del sistema. Tendrás que crearlas para poder iniciar el servicio. Recuerda que estás trabajando sobre la instancia predeterminada (recuerda donde se encuentra su archivo de configuración).
Comprueba que puedes iniciar el servicio.
Partimos de un escenario en el que consigues acceder al fichero binario, pero solo al último, por lo que tendrás que restaurar los anteriores desde la copia de seguridad previamente.


Restaura la base de datos hasta justo antes de crear la tabla 'tabla_instante_2'.
Comprueba que la tabla 'tabla_instante_1' está creada y con datos. No así la tabla 'tabla_instante_2'.
Restaura la base de datos hasta justo antes de borrar las tablas.
Comprueba que la tabla 'tabla_instante_1' y 'tabla_instante_2' está creada y con datos.
Restaura la base de datos hasta el último instante. Comprueba que las tablas no existen.


  • Una vez restaurada la base de datos, vuelve a dejar el gestor Mysql para que se pueda conectar desde la red.



Ejercicio propuesto

  • Haciendo uso del Crontab, crea una política de copias de seguridad del gestor Mysql partiendo de estos supuestos:
  • Las bases de datos del sistema no van a sufrir modificaciones.
  • El acceso y modificación sobre la base de datos employees es intenso.
  • El periodo de máxima intensidad se da desde las 9:00 hasta las 21:00 de forma ininterrumpida.Se producen cientos de modificaciones cada minuto (temas bancarios) que suponen 100KB cada minuto (se sabe mirando el tamaño de almacenamiento por fila). El resto del tiempo se producen decenas de modificaciones cada minuto del orden de 10KB.
  • El periodo de menor intensidad se da desde las 23:59 hasta las 02:00.
  • Deberás de realizar una estimación del tamaño de almacenamiento del disco duro para guardar los ficheros de log del gestor Mysql, además del tamaño que debería de tener el disco duro donde se guardan los backups.
Tu política de seguridad deberá incluir el borrado de los ficheros de log y copias de seguridad que ya no sean necesarias.
Crea los scripts necesarios y haz un volcado del crontab.




Enlace a la página principal de la UD1

Enlace a la página principal del curso




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

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