Tienda Wifi

Tienda Wifi
CiudadWireless es la tienda Wifi recomendada por elhacker.NET

Entradas Mensuales

Síguenos en:

Canal Oficial Telegram de elhacker.NET Grupo Facebook elhacker.NET Twitter elhacker.NET Canal Youtube elhacker.NET Comunidad Steam: Grupo elhacker.NET Mastodon

Entradas populares

PostHeaderIcon Log Binario en MySQL


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 LOGSFLUSH-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

PostHeaderIcon 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

Primero hay que tener en cuenta que la replicación en MySQL por defecto es asynchronous (asincrónica) pero se puede hacer semi-sincrónica (Semisynchronous) a partir de MysQL 5.5 gracias a un parche de Google. Pero no es ni en tiempo real , ni automáticamente se recupera (fail over), se requiere intervención manual o programación.

Tan sólo Percona XtraDB ClusterMySQL Cluster permiten la replicación sincrónica (Synchronous Replication) para montar sistemas HA (ha high availability) de gran disponibildad. Esto es porque hay un servidor maestro (master) y todos los esclavos (slaves) que quieras, pero si el master se cae, el esclavo no pasa a  ser master automáticamente. O usar parches como Galera, o sistemas PaceMaker.o usando DRBD.



  • Copia de seguridad
  • Mejorar la escalabilidad
  • Alta disponibilidad



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


El proceso de replicación de una base de datos consiste en replicar las consultas de actualización (tanto DML como DDL) en una base de datos maestra (master) sobre una o varias bases de datos esclavas (slave), de manera que tengamos una copia de las mismas a lo largo del tiempo.

MySQL soporta replicación unidireccional asíncrona, es decir, las consultas de actualización ejecutadas en el maestro son replicadas en los servidores esclavos. Esta replicación se realiza de forma transparente. Además es instantánea si los servidores esclavos están levantandos y en estado de replicación. 

En el caso de la replicación es importante saber que cada servidor esclavo se conecta al servidor maestro y le solicita que le envie las sentencias registradas en los logs binarios a partir de una posición, para ello, cada esclavo mantiene un archivo a modo de índice en donde registra la posición actual de la replicación.

¿Cómo puedo desativar la replicación de MySQL?

En una configuración master-master setup, tienes que hacer lo siguiente en cada slave:
  1. STOP SLAVE;
  2. RESET SLAVE; (Use RESET SLAVE ALL; for MySQL 5.5.16 and later)
  3. Edita el fichero de configuración my.cnf  y elimina cualqueir informació (si es que está presente) que se refiera a las opciones de "master-..." o "replicate-...".
  4. Reinicia mysqld.

Posibles errores

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

  • Comprueba los permisos de escritura y lectura del usuario del mysql en el directorio de los ficheros binarios
  •  chown -R mysql:mysql /var/log/mysql
  • chown -R mysql:mysql /path/to/DB # Set your Owner/Group chmod -R 755 /path/to/DB # Set your permissions 

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

Solución:
my.cnf
  • binlog_format = MIXED
  • binlog_format = ROW
  • Eliminar LIMIT de las consultas SQL

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

Posibles soluciones:
  • Comprueba que las versión del ejecutable (binario) mysqlbinlog sea igual o superior en el esclavo que en el maestro y viceversa.
  • Comprueba el charset_set sea igual en el maestro (master) que en el esclave (slave), utf8, latin1, etc.

Copias de seguridad de MySQL 

Tenemos varias opciones:
  • mysqldump
  • mysqlhotcopy (deprecated)
  • mariabackup (fork de Percona XtraBackup)

 MySQLHotCopy

mysqlhotcopy está actualmente en desuso. Sólo sirve para tablas y DB en MyIsam (no funciona con InnoDB)

Actualmente el mejor sistema  es usar mysqldump o bien

 Mariabackup que es un fork de Percona XtraBackup  

mysqldump 


Lo ideal es primero el "dump" y luego comprimir para que se más rápido y no las dos cosas a la vez.
 
Básico

 mysqldump [OPTIONS] database [tables]

 Ejemplo básico

mysqldump -u -p base_datos > fichero.sql

 A tener en cuenta:

mysqldump hace un backup completo de la base de datos pero se pueden añadir parámetros:

  • sólo datos
  • estructura
  • eventos,
  • procedimientos
  • triggers
  • vistas

Opciones:

  • –all-databases (todas las BD)
  • charset
  • comprimir con (se puede comprimir el sql con gzip (sql.gz) o bzip2 (sql.bz2) 
  • --host -h (host o la ip (sql remoto)
  • usa  > para hacer la copia
  • < para restaurar la copia

Siempre pedirá usuario y contraseña:

  • -u  o la forma larga --user=USUARIO
  • -p o la forma larga   --password=pass

 Recuerda que al usar el parámetro -p, la contraseña se escribirá a continuación sin añadir ningún espacio en blanco entre -p y la contraseña.

Todas las base de datos

mysqldump [opciones] --all-databases > fichero.sql

Ejemplos comprimir con gzip

mysqldump -u dbUser -p DBName | gzip > db.sql.gz

Con bzip2

mysqldump -u dbUser -p DBName | bzip2 >db.sql.bz2