systemadmin.es > DBA > Binary logs de MySQL

Binary logs de MySQL

Uno de los parámetros que están activados por defecto es el binary log de MySQL. Resulta curioso como se suele dejar activado, ya que afecta considerablemente al rendimiento y requiere de un cierto mantenimiento.

Para activar o desactivar el binary log es el parámetro log-bin:

log-bin=mysql-bin

Mediante FLUSH BINARY LOGS deberemos limpiar del binary log lo que ya no sea necesario, lo veremos próximamente.

En el binary log se escriben las sentencias que potencialmente hayan podido modificar algún dato. Por ejemplo, si ejecutamos un UPDATE aunque la condición del WHERE no coincida con ninguna fila esta va a parar al binary log.

En la versión 5.1 de MySQL se introdujo el concepto de “row based replication“: esto significa que se puede configurar el formato del log según nos interese. Los formatos, a especificar mediante el parámetro binlog-format, son:

  • Basado en sentencias (statement-based):
    binlog-format=STATEMENT
    

    Se trata del tipo que se usaba (porque era el único tipo) antes de la versión 5.1

  • Basado en filas (row-based):
    binlog-format=ROW
    

    En este caso en lugar de almacenar las sentencias que se han ejecutado, almacena las filas modificadas

  • Mezcla de los dos anteriores:
    binlog-format=MIXED
    

    Usa por defecto statement-based, pero puede usar row-based según crea el MySQL

Como todo, cada opción tiene sus ventajas e inconvenientes si se usa para la replicación:

En el caso de statement-based, si una query tarda mucho en ejecutar pero modifica pocas filas, el slave se va a retrasar el doble del tiempo de ejecución de la query: Primero estará el tiempo que tarde esperado que le lleguen datos cuando el master la este ejecutando más (suponiendo igual hardware) va a tardar lo mismo que el master en ejecutar la query. En el caso de usar row-based, el slave se retrasaría igual mientras se ejecuta la query en el master, pero si son pocas las filas replicadas podría salir más a cuenta enviar los datos que esperar que se vuelva a ejecutar la query.

Por el contrario, si una query muy simple modifica muchos datos en el caso de Binary logs de MySQL sólo se va a mandar la query, mientras que en row-based se van a mandar muchos datos.

Es por eso que para bases de datos de propósito general lo ideal es usar el modo “MIXED” y dejar que el MySQL elija.

Próximamente veremos como eliminar el binary log si lo tenemos activado y cómo obtener todas las querys que hayan modificado datos del binary log.

14 comments to “Binary logs de MySQL”

  1. Lo más importante de todo es que con esos logs puedes reconstruir los datos de la base de datos en un 99% desde el último backup completo que se hizo hasta el momento actual.

  2. Si, evidentemente puedes hacer un recovery a un determinado punto, ya que si tienes el punto inicial y todas las modificaciones con la fecha puedes recuperar hasta el día que quieras. La intención es hacer una pequeña serie con varios temas relacionados con el binary log.

    Lo que me quiero referir es que muchas veces veo que la gente lo tiene activado y no realizan ningún mantenimiento de él, lo van dejando crecer hasta que les llena el fs del datadir.

  3. mmm… Jordi, me es familiar esa problemática que comentas…

  4. Si, no se como se me ocurrió el tema 😛 Esto lo he visto en una empresa que se dedica a administrar sistemas de terceros, por lo que es más común de lo que parece

  5. Muy interesante el tema, a veces cambiamos de versión del software y no paramos suficiente atención a los cambios. Esto suele pasarnos si usamos una distro rolling release como Arch.

  6. Buenas,

    No sé si el tipo de formato del binary log me solucionaría mi problema.
    Tengo un master y un slave y quiero que todas las DDL’s que se ejecutan en mi master sobre una tabla, se ejecutan todas juntas en la misma sentencia para la replicación. No sé si el método ROW será el más apropiado para ésto. Pero el otro día se retrasó la replicación bastante por culpa de intentar ejecutar muchas DDL’s en el slave

    Gracias
    Saludos

  7. El formato ROW es para cuando una query muy compleja de ejecutar toca pocos registros, por lo que sale más a cuenta mandar los registros que se han modificado que volver a ejecutar la query en el slave.

    Depende exactamente de cuál sea tu caso te puede valer o no.

  8. Realmente lo que tengo que hacer, es que todas las sentencias DDL’s que se ejecutan sobre una tabla en el máster, se agrupen y se ejecute en una sola sentencia
    en el Slave.

    No sé si se puede hacer a nivel del config de la replicación o no, pero el otro día me encontré la replicación retrasada por esto.

    ¿Sabéis si hay alguna posibilidad de ejecutar todas esas sentencias en el máster como una sola dentro del slave?

    Gracias

    Saludos

  9. El MySQL no transforma queries para ejecutarlas en el slave de otra forma. Se pueden hacer cosas un poco feas en este sentido tocando el código del MySQL pero yo no lo haría si no hubiera más remedio.

    ¿Que inconveniente hay en simplemente cambiar las queries que ejecuta el master?

  10. el único incoveniente es que el programador es externo y es él el que realiza los scripts de bases de datos.

    Gracias por todo

    Saludos

  11. hola prodrían explicar un poco mas sobre como le hago el flush al binary log. Veo que tiene su importancia para el servidor. en que influiría si lo desactivo en mi servidor mysql?? ya a mi me tiene como 7 gb ocupado por ese concepto y solo llevo como 15 de instalado el servidor.

  12. Si no usas ningún slave y no necesitas poder guardar los cambios desde el último punto de backup no necesitas al binary log. Si lo quieres mantener puedes eliminar los antiguos mediante expire_logs_days

  13. Hola podria alguien explicarme como activar el binary log o como lo encuentro estoy algo confundido disculpen mis torpesa soy un novato apreniendo. gracias

  14. Teniendo en cuenta el tiempo que ha pasado desde que escribiste el post, ¿ahora lo recomendado es usar siempre RBR y no MIXED, verdad?

Deja un comentario:

XHTML - Tags permitidos:<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>