El binlog sirve principalmente para la replicación maestro-esclavo y maestro-maestro pero también sirve para guardar un registro de las consultas que afectan a los datos de la bd, ya que todas las consultas select (lectura) no se guardan en el log binario.




Activando el log binario puedes reconstruir los datos de la base de datos en un 99% desde el último backup. Es por lo tanto fundamental para mantener sana la integridad de la base de datos.

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.  

¿Qué ocurre cuando no tienes una copia de seguridad reciente (con mysqldump, mysqlhotcopy o similares) ? Sirve para hacer copias de seguridad incrementales.

El registro binario contiene toda la información que está disponible en el registro de actualizaciones, en un formato más eficiente y de una manera que es segura para las transacciones. El registro binario contiene todas las sentencias que han actualizado datos

El propósito principal del registro binario es el de actualizar la base de datos durante una operación de recuperación tan completamente como sea posible, porque el registro binario contiene todas las actualizaciones hechas tras la copia de seguridad.    

Ejecutar el servidor con el registro binario activado hace que el rendimiento baje un 1%. Aún así, los beneficios del registro binario para las operaciones de restauración y el hecho de permitirnos poder establecer replicación generalmente son superiores a este decremento de rendimiento.

El registro binario de MySQL es importante para la restauración, porque son copias incrementales de datos. Si se asegura de volcar los registros cuando hace su copia de seguridad completa, entonces cualquier registro binario creado tras esa copia contiene todos los cambios hechos desde entonces.


A tener en cuenta:

• Uso % CPU y escritura en disco
• Tamaño LOGS

Configuración

Fichero configuración MySQL my.cnf

# El binlog sirve principalmente para replicación maestro-esclavo maestro-maestro
# también sirve para guardar un registro de las consultas que afectan a la bd
# las consultas select no se guardan
# Ajusta sync_binlog a un valor extraordinariamente alto (256 en mi caso)
# Reduce significativamente la carga del binlog en el servidor.
#server-id = 1
 log_bin = /var/log/mysql/mysql-bin.log
 binlog_cache_size = 256K
 sync_binlog = 256
 expire_logs_days = 14
 max_binlog_size = 1G
#binlog_do_db = include_database_name
#binlog_ignore_db = include_database_name
#replicate-ignore-table=db_name.tbl_name

El log binario por lo tanto guarda todas las consultas INSERT, DELETE, UPDATE, y REPLACE 

Mediante PURGE BINARY LOGS o FLUSH-LOGS (mysqladmin flush-logs) podemos limpiar/vacíar/purgar los ficheros binary log. O con la variable expire_logs_days del my.cnf.

Los registros binarios de MySQL ocupan espacio de disco. Para ligerar espacio, púrguelos de vez en cuando. Una manera de hacerlo es borrar los registros binarios que no se necesiten, como cuando hacemos una copia de seguridad completa: 

PURGE BINARY LOGS TO 'mysql-bin.010';
PURGE BINARY LOGS BEFORE '2013-11-25 21:23:14';

 shell> mysqldump --single-transaction --flush-logs --master-data=2 --all-databases --delete-master-logs > backup_sunday_1_PM.sql

Tenga en cuenta que: Borrar los registros binarios de MySQL con mysqldump --delete-master-logs puede ser peligroso si su servidor es un servidor maestro de replicación, porque los servidores esclavos pueden no haber procesado completamente los contenidos del registro binario. 

Existen tres tipos de formato del log binario

binlog_format     = MIXED

Basado en filas (row-based):

binlog_format      = ROW

 En MySQL 5.5 el formato del bin log por defecto es statement-based replication (SBR)

Basado en sentencias (statement-based):

  binlog_format = STATEMENT

 Ventajas:

 Fichero log más pequeño, menos uso escritura en disco.

 Menos datos escritos en archivos de registro. Cuando las actualizaciones o eliminaciones afectar muchas filas, esto se traduce en el espacio de almacenamiento mucho menos necesario para los archivos de registro. Esto también significa que la toma y la restauración de las copias de seguridad se puede lograr con mayor rapidez.

Recordemos que podemos hacer una copia de seguridad completa full con  mysqlhotcopy para MyISAM y InnoDB Hot Backup o Xtrabackup para tablas InnoDB.

 Es una buena idea guardar los logs binarios en entornos SAN, o rsync de manera periódica a otro lugar, así  si algo sale mal, primero puede restaurar los datos desde la copia de seguridad incremental anterior, a continuación, restaurar los datos adicionales de los nuevos registros, por lo que el mínimo cantidad de datos se pierde. También puede consultar sync_binlog parámetro mysql, que controla los registros binarios cuando se sincronizan con el disco.

140528  4:21:01 [ERROR] /var/local/mysql/bin/mysqld: Table '/wcp_log_floodcontrol' 
is marked as crashed and should be repaired
140528  4:21:01 [Warning] Checking table:   './whk/wcp_log_floodcontrol'

Una opción muy interesante para añadir en el fichero de configuración de my.cnf para tablas en formato MyISAM que están "crashed" después un reinicio inesperado es (se reparan auomáticamente)

# Auto-creates a backup when running the recover operation.
# http://dev.mysql.com/doc/refman/5.1/en/server-options.html#option_mysqld_myisam-recover
# myisam-recover           = BACKUP
myisam-recover-options = BACKUP,FORCE

MySQL a fondo (Optimización my.cnf, MyISAM vs InnoDB)

Deshabilitar el registro binario (temporalmente) cuando restauramos con MySQLdump

 Hay una opción perfecta para mysqlbinlog para desactivar el registro binario al hacer uso de los registros binarios de recuperación, es decir, usando -disable-log-bin. ¿Cómo, uno podría pensar que esta también disponible para algo como mysqldump o incluso la CLI mysql? Pues no.
Pero hay varias maneras de hacer esto, he aquí una:

  shell> (echo "SET SESSION SQL_LOG_BIN = 0;" cat dump.sql)> dump_nobinlog.sql

SQL Query:

SET sql_log_bin = 0;

Recuperación registros usando el log binario

Los ficheros de log binario que el servidor genera se escriben en formato binario. Para examinar estos ficheros en formato de texto, se utiliza la utilidad mysqlbinlog .

 Para verlos:

 shell> mysqlbinlog mysql-bin.000001

 Para guardarlos en un fichero de texto:

 shell> mysqlbinlog mysql-bin.000001 > mysql.sql
 shell> mysqlbinlog mysql-bin.000002 >> mysql2.sql

 Para reducir la cantidad de datos recuperados de los registros binarios, hay varias opciones que se pueden utilizar para limitar los datos que se han devuelto. Entre los útiles se enumeran a continuación:

 -Start-datetime = datetime 

 Comienza a leer el log binario en el primer evento que tenga una fecha igual o posterior a la del argumento datetime. El valor datetime es relativo a la zona horaria local en la máquina donde se ejecuta mysqlbinlog. El valor debe estar en un formato aceptado por los tipos de datos DATETIME o TIMESTAMP. Por ejemplo:

 mysqlbinlog - start-datetime = "25/12/2012 11:25:56" mysql-bin.000001 

 -Stop-datetime = datetime

 Deja de leer el log binario en el primer evento que tenga una fecha igual o posterior a la del argumento datetime. Esta opción es útil para la recuperación de punto en el tiempo. Véase la descripción de la-start-datetime opción para obtener información sobre el valor de fecha y hora.

 -Start-position = N 

 Comienza a leer el log binario en el primer evento que tenga una posición igual al argumento N. Esta opción se aplica al archivo de registro citado en primer lugar en la línea de comandos.

 -Stop-position = N 

 Deja de leer el log binario en el primer evento que tenga una posición igual o superior al argumento N. Esta opción se aplica al último archivo de registro especificado en la línea de comandos.

Replicación en MySQL

• El servidor Master debe tener activado el log binario (log-bin).
• Ambos deben tener un identificador único (server-id).
• El servidor maestro deberá tener un usuario con privilegio REPLICATION SLAVE.

Posibles errores

mysql-bin.index' not found (Errcode: 13)

[Warning] Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement is unsafe because it uses a LIMIT clause. This is unsafe because the set of rows included cannot be predicted.

ERROR: Error in Log_event::read_log_event(): 'Found invalid event in binary log',