Recuperación point-in-time

Imagina que a las 14:35 de un martes, alguien ejecuta accidentalmente un DELETE FROM pedidos sin cláusula WHERE, borrando todos los pedidos. Tu último backup completo es de las 2:00 de la madrugada. Si solo restauras ese backup, perderás todas las transacciones del día. La recuperación point-in-time (PITR) te permite restaurar el backup de las 2:00 y luego aplicar todos los cambios registrados en los binary logs hasta las 14:34, justo antes del error. Recuperas los datos hasta el momento exacto que necesitas.

Qué son los binary logs

Los binary logs (binlogs) registran cada cambio realizado en la base de datos: inserciones, actualizaciones, eliminaciones y modificaciones de esquema. MySQL escribe estos cambios de forma secuencial en archivos binlog numerados. Estos archivos son la base tanto de la replicación como de la recuperación point-in-time.

Para que PITR funcione, los binary logs deben estar habilitados en el servidor. Puedes verificar su estado:

SHOW VARIABLES LIKE 'log_bin';

Si el valor es OFF, necesitas activar los binary logs en el archivo de configuración my.cnf:

# /etc/mysql/my.cnf
[mysqld]
log_bin = /var/log/mysql/mysql-bin
server_id = 1
binlog_expire_logs_seconds = 604800   # 7 días

Después de modificar la configuración, reinicia MySQL:

sudo systemctl restart mysql

Prerequisitos para PITR

Antes de poder realizar una recuperación point-in-time, necesitas tres cosas: binary logs habilitados y disponibles desde el momento del último backup, un backup completo reciente creado con mysqldump, y la opción --master-data o --source-data en el backup para registrar la posición exacta del binlog.

Crea el backup registrando la posición del binlog:

mysqldump --single-transaction --routines --triggers \
    --source-data=2 \
    -u root -p tienda_mysql > /backups/tienda_mysql_20260214_0200.sql

La opción --source-data=2 (o --master-data=2 en versiones anteriores a 8.2) inserta un comentario con la posición del binlog al inicio del archivo:

-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000042', MASTER_LOG_POS=15463;

Esta línea indica el punto exacto del binlog donde el backup fue tomado. La recuperación PITR aplicará los cambios desde esa posición en adelante.

Listar los binary logs disponibles

Para ver qué binary logs tiene tu servidor:

SHOW BINARY LOGS;

La herramienta mysqlbinlog

mysqlbinlog lee los archivos binlog y los convierte en sentencias SQL legibles. Es la herramienta clave para la recuperación point-in-time.

Para ver el contenido de un binlog:

mysqlbinlog /var/log/mysql/mysql-bin.000042 | head -50

Para ver eventos en un rango de tiempo específico:

mysqlbinlog --start-datetime="2026-02-14 02:00:00" \
            --stop-datetime="2026-02-14 14:34:00" \
            /var/log/mysql/mysql-bin.000042

Flujo de trabajo de recuperación completo

Veamos el proceso completo paso a paso. Supongamos el escenario descrito al inicio: un DELETE desastroso a las 14:35, con un backup de las 2:00.

Paso 1: Identificar la posición del error

Primero, examina los binlogs para encontrar la sentencia exacta que causó el daño:

mysqlbinlog --start-datetime="2026-02-14 14:30:00" \
            --stop-datetime="2026-02-14 14:40:00" \
            /var/log/mysql/mysql-bin.000043

# at 52698
#260214 14:35:12 server id 1  end_log_pos 52845
DELETE FROM pedidos
# at 52845
#260214 14:35:15 server id 1  end_log_pos 52960
INSERT INTO log_actividad ...

Anota la posición: el DELETE está en la posición 52698 y termina en 52845. Necesitas aplicar todo hasta la posición 52698 (sin incluirla).

Paso 2: Restaurar el backup base

# Restaurar el backup completo de las 2:00 AM
mysql -u root -p tienda_mysql < /backups/tienda_mysql_20260214_0200.sql

Esto restaura la base de datos al estado de las 2:00 AM. Todas las transacciones posteriores se han perdido temporalmente.

Paso 3: Aplicar binlogs hasta antes del error

Ahora aplica los cambios registrados en los binlogs desde la posición del backup hasta justo antes del error:

# Aplicar desde la posición del backup hasta antes del DELETE
mysqlbinlog --start-position=15463 \
            --stop-position=52698 \
            /var/log/mysql/mysql-bin.000042 \
            /var/log/mysql/mysql-bin.000043 \
            | mysql -u root -p tienda_mysql

Si los binlogs abarcan múltiples archivos, pásalos todos como argumentos en orden.

Paso 4: Opcionalmente, aplicar cambios posteriores al error

Si hubo transacciones válidas después del DELETE desastroso que también quieres recuperar:

# Aplicar desde después del DELETE en adelante
mysqlbinlog --start-position=52845 \
            /var/log/mysql/mysql-bin.000043 \
            | mysql -u root -p tienda_mysql

Recuperación por fecha y hora

Si no conoces la posición exacta en el binlog, puedes usar marcas de tiempo:

# Aplicar todos los cambios desde las 2:00 hasta las 14:34
mysqlbinlog --start-datetime="2026-02-14 02:00:00" \
            --stop-datetime="2026-02-14 14:34:00" \
            /var/log/mysql/mysql-bin.000042 \
            /var/log/mysql/mysql-bin.000043 \
            | mysql -u root -p tienda_mysql

La recuperación por fecha es menos precisa que por posición. Si varias sentencias se ejecutaron en el mismo segundo, podrías incluir o excluir transacciones no deseadas. Siempre que sea posible, usa posiciones específicas.

Filtrar por base de datos

Si el servidor tiene múltiples bases de datos y solo quieres aplicar cambios a una:

mysqlbinlog --database=tienda_mysql \
            --start-position=15463 \
            --stop-position=52698 \
            /var/log/mysql/mysql-bin.000042 \
            | mysql -u root -p tienda_mysql

La opción --database filtra los eventos del binlog para incluir solo los de la base de datos especificada.

Consideraciones importantes

Los binary logs tienen un período de retención configurado por binlog_expire_logs_seconds. Si tus binlogs se purgan antes de que los necesites, la recuperación PITR será imposible. Configura la retención según tu RPO:

-- Verificar la retención actual
SHOW VARIABLES LIKE 'binlog_expire_logs_seconds';

Un valor de 604800 segundos equivale a 7 días. Si tu backup completo es semanal, necesitas al menos 7 días de retención.

Los binlogs ocupan espacio en disco. Monitorea regularmente el espacio utilizado:

du -sh /var/log/mysql/mysql-bin.*

Practicar la recuperación

La recuperación point-in-time es un procedimiento que debe practicarse antes de necesitarlo realmente. Crea un entorno de prueba y simula un desastre:

# 1. Crear backup con posición de binlog
mysqldump --single-transaction --source-data=2 -u root -p tienda_mysql > backup_prueba.sql
 
# 2. Hacer algunas operaciones
mysql -u root -p -e "INSERT INTO tienda_mysql.productos (nombre, precio) VALUES ('Test', 99.99)"
mysql -u root -p -e "UPDATE tienda_mysql.productos SET precio = 109.99 WHERE nombre = 'Test'"
 
# 3. Simular el desastre
mysql -u root -p -e "DELETE FROM tienda_mysql.productos WHERE nombre = 'Test'"
 
# 4. Practicar la recuperación
mysql -u root -p tienda_mysql < backup_prueba.sql
mysqlbinlog --start-datetime="..." --stop-datetime="..." /var/log/mysql/mysql-bin.* | mysql -u root -p tienda_mysql

La recuperación point-in-time es la técnica más poderosa de recuperación de datos en MySQL. Combinada con backups regulares y binary logs bien configurados, te permite recuperar tu base de datos hasta prácticamente cualquier momento en el tiempo. En el siguiente artículo veremos cómo copiar una base de datos completa, tanto en el mismo servidor como entre servidores diferentes.