systemadmin.es > DBA > MySQL Point in Time Recovery mediante binlogs y mysqldump

MySQL Point in Time Recovery mediante binlogs y mysqldump

Vamos a ver cómo realizar una recuperación de datos de una base de datos MySQL en un determinado punto en el tiempo mediante backups con mysqldump y los binlogs activados

Primero vamos a preparar una base de datos de ejemplo:

mysql> create database jordidb;
Query OK, 1 row affected (0.00 sec)

mysql> use jordidb
Database changed
mysql> create table tbl(id int);
Query OK, 0 rows affected (0.03 sec)

mysql> insert into tbl values (1),(2),(3);
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from tbl;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
+------+
3 rows in set (0.00 sec)

A continuación generamos un mysqldump con los datos de posición de los binlogs (opción –master-data):

# mysqldump --master-data=2 --databases jordidb > jordidb.sql

A continuación insertaremos más datos:

mysql> insert into tbl values (4),(5),(6);
Query OK, 3 rows affected (0.02 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from tbl;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
|    5 |
|    6 |
+------+
6 rows in set (0.00 sec)

Este será el punto en el tiempo al que queremos volver a partir de los binlogs y el mysqldump anterior:

# date
Tue Aug 24 12:13:21 CEST 2014

Por ejemplo, porque a alguien se le ha olvidado el WHERE de un DELETE:

mysql> delete from tbl;
Query OK, 6 rows affected (0.02 sec)

Primero deberemos importar el backup:

# cat jordidb.sql | mysql

A continuación necesitaremos la posición del backup para empezar a aplicar binlogs. Cómo que hemos usado la opción –master-data en el mysqldump podemos buscar en el fichero .sql generado la cadena “CHANGE MASTER“:

# grep CHANGE jordidb.sql  
-- CHANGE MASTER TO MASTER_LOG_FILE='binlog.000001', MASTER_LOG_POS=473;

La posición inicial en mysqlbinlog la podemos indicar mediante la opción -j:

  -j, --start-position=# 
                      Start reading the binlog at position N. Applies to the
                      first binlog passed on the command line.

Para indicar la posición final podemos usar la opción –stop-datetime para pasar una fecha:

  --stop-datetime=name 
                      Stop reading the binlog at first event having a datetime
                      equal or posterior to the argument; the argument must be
                      a date and time in the local time zone, in any format
                      accepted by the MySQL server for DATETIME and TIMESTAMP
                      types, for example: 2004-12-25 11:25:56 (you should
                      probably use quotes for your shell to set it properly).

Por lo tanto el comando nos quedaría:

# mysqlbinlog -j 473 --stop-datetime="2014-08-24 12:13:21" /var/lib/mysql/binlog.000001 
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#140824 12:11:19 server id 1  end_log_pos 107 	Start: binlog v 4, server v 5.5.38-35.2-log created 140824 12:11:19 at startup
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
BINLOG '
R138Uw8BAAAAZwAAAGsAAAABAAQANS41LjM4LTM1LjItbG9nAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAABHXfxTEzgNAAgAEgAEBAQEEgAAVAAEGggAAAAICAgCAA==
'/*!*/;
# at 473
#140824 12:13:11 server id 1  end_log_pos 544 	Query	thread_id=9	exec_time=0	error_code=0
SET TIMESTAMP=1408875191/*!*/;
SET @@session.pseudo_thread_id=9/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=0/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=8/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
BEGIN
/*!*/;
# at 544
# at 589
#140824 12:13:11 server id 1  end_log_pos 589 	Table_map: `jordidb`.`tbl` mapped to number 37
#140824 12:13:11 server id 1  end_log_pos 633 	Write_rows: table id 37 flags: STMT_END_F

BINLOG '
t138UxMBAAAALQAAAE0CAAAAACUAAAAAAAEAB2pvcmRpZGIAA3RibAABAwAB
t138UxcBAAAALAAAAHkCAAAAACUAAAAAAAEAAf/+BAAAAP4FAAAA/gYAAAA=
'/*!*/;
# at 633
#140824 12:13:11 server id 1  end_log_pos 660 	Xid = 128
COMMIT/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
[root@my1 ~]# 

De esta forma obtenemos todos los comandos SQL que se han ejecutado desde el backup al punto que hemos indicado, por lo que únicamente nos quedará aplicarlos para completar la restauración:

# mysqlbinlog -j 473 --stop-datetime="2014-08-26 12:13:21" /var/lib/mysql/binlog.000001 | mysql

Si nos conectamos a la base de datos podremos ver que hemos recuperado no sólo el backup sino todos los datos antes del DELETE:

mysql> select * from tbl;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
|    5 |
|    6 |
+------+
6 rows in set (0.00 sec)

mysql> 

En este caso se ha aplicado todos los binlogs disponbiles para todas las bases de datos, pero si queremos aplicarlos para una base de datos en concreto, podemos filtrar los binlogs mediante la opción -d para indicar la que nos interesa aplicar (sin afectar al resto)

One comment to “MySQL Point in Time Recovery mediante binlogs y mysqldump”

  1. Hola qué tal, tengo un gran problema y por lo que leo lo de binlogs puede ser la solución, esto es lo que realicé:

    Hice un delete en wp_posts y wp_postmeta, lo hice desde el manager de MySQL, y no metí bien el código (Where) pero le di en ejecutar la consulta y al parecer borró todo eso, y ahora en mi sitio web aparece el error:

    Error establishing a database connection

    Quise hacer la restauración de archivos desde el hosting pero marca error en la base de datos, ¿hay modo de recuperar la información? No hice un respaldo, y desconozco si están activados los binlogs, aún así ¿podría recuperar mis posts?Espero puedas ayudarme.

    Saludos.

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>