Tema 17: Restauración y Point-in-Time Recovery — reconstruir el altépetl
#PF-017 | XP: 280 | Insignia: Restauradora del Tiempo | Tier: Senior | Tiempo: 4 h
Objetivo
Aprenderás a restaurar respaldos lógicos en MySQL y PostgreSQL, ejecutar Point-in-Time Recovery (PITR) con binlogs y WAL, y simular incidentes reales (DROP TABLE accidental, corrupción de disco, fallo de replica) para validar tu procedimiento de recuperación. También vas a medir tu RPO y RPO objetivo de la empresa.
Mapa del tema
Visión mexica — Por Citlalli
El tlacuilo (escriba pintor) no solo copiaba los hechos: sabía cómo reconstruir un codex dañado por agua, fuego o guerra. Para eso, conservaba los borradores en el calmécac y conocía qué secciones eran del año actual, cuáles del anterior, cuáles eran tributarios. Esa memoria estructural es la diferencia entre borrar y volver a empezar y borrar y recuperar.
En bases de datos, la diferencia entre un DBA junior y uno senior no está en hacer respaldos —eso ya lo aprendiste en el Tema 16—, sino en poder decir con precisión: "puedo recuperar la base hasta las 14:32:17 del jueves, perdiendo a lo sumo 4 minutos de transacciones, en 25 minutos de tiempo de recuperación". Eso es Point-in-Time Recovery.
Bloque 1 — Restauración básica: de dump a base funcional
MySQL: del `.sql.gz` a la base restaurada
# Restauración simple: dump completo
gunzip -c tienda_2026-08-23_0200.sql.gz | mysql -u root -p tienda_tlalliSi necesitas crear la base primero
mysql -u root -p -e "CREATE DATABASE tienda_tlalli CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;"
gunzip -c tienda_2026-08-23_0200.sql.gz | mysql -u root -p tienda_tlalliVerificación post-restauración (no te saltes esto):
-- 1. Conteo de tablas
SELECT COUNT() AS total_tablas
FROM information_schema.tables
WHERE table_schema = 'tienda_tlalli';
-- Esperado: 6 (categorias, productos, clientes, pedidos, pedido_items, resenas)-- 2. Conteo de filas en tablas principales
SELECT
(SELECT COUNT(
) FROM categorias) AS categorias,
(SELECT COUNT() FROM productos) AS productos,
(SELECT COUNT() FROM clientes) AS clientes,
(SELECT COUNT() FROM pedidos) AS pedidos,
(SELECT COUNT() FROM pedido_items) AS items,
(SELECT COUNT() FROM resenas) AS resenas;
-- Esperado: 18, 46, 50, 110, 110, 50-- 3. Integridad referencial
SELECT COUNT(
) AS huerfanos
FROM pedido_items pi
LEFT JOIN pedidos p ON p.pedido_id = pi.pedido_id
WHERE p.pedido_id IS NULL;
-- Esperado: 0-- 4. Acentos correctos
SELECT nombre FROM clientes WHERE cliente_id = 1;
-- Esperado: 'Xochitl Hernández Ruiz', no 'Xochitl Hernández'
PostgreSQL: del `.dump` a la base restaurada
# Restaurar dump completo (formato custom)
pg_restore -U postgres -d tienda_tlalli -c backup_tienda.dump
-c = clean (DROP antes de CREATE)
O si es plain
psql -U postgres -d tienda_tlalli -f backup_tienda.sqlDetalle clave: para formato `custom`, la base de destino debe existir (pero estar vacía). `pg_restore` no la crea. Para `plain`, puedes crearla o no en el dump (revisar la cabecera del archivo).
Verificación:
-- Conteos esperados
SELECT
(SELECT COUNT() FROM categorias) AS categorias,
(SELECT COUNT() FROM productos) AS productos,
(SELECT COUNT() FROM clientes) AS clientes,
(SELECT COUNT() FROM pedidos) AS pedidos,
(SELECT COUNT() FROM pedido_items) AS items,
(SELECT COUNT() FROM resenas) AS resenas;
-- Esperado: idéntico a MySQLBloque 2 — Errores comunes al restaurar
Error 1: encoding/collation incompatible
Síntoma: los acentos llegan rotos en MySQL, o las comparaciones de texto fallan.
Causa: la base destino tiene `latin1` o `utf8` (no `utf8mb4`).
Fix:
ALTER DATABASE tienda_tlalli CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;Y en el dump, verifica que el `CREATE TABLE` use `CHARSET=utf8mb4`. Si no, el dump está mal generado (sin `--default-character-set=utf8mb4` en el `mysqldump` original).
Error 2: foreign key violation al restaurar
Síntoma: `ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails`.
Causa: el orden de las tablas en el dump no respeta dependencias, o estás restaurando en una base que ya tiene las tablas con datos viejos.
Fix: agregar `--force` a `mysql` para que continúe pese a errores, o restaurar en una base limpia:
mysql -u root -p -e "DROP DATABASE IF EXISTS tienda_tlalli; CREATE DATABASE tienda_tlalli CHARACTER SET utf8mb4;"
gunzip -c backup.sql.gz | mysql -u root -p tienda_tlalliError 3: timeout de conexión
Síntoma: `MySQL server has gone away` en restores de varios GB.
Fix en `my.cnf` o `my.ini`:
[mysqld]
max_allowed_packet = 256M
wait_timeout = 28800Y para clientes:
mysql --max-allowed-packet=256M -u root -p tienda_tlalli < backup.sqlError 4: permisos faltantes
Síntoma: `ERROR 1227 (42000): Access denied`.
Causa: el dump incluye `DEFINER` clauses de un usuario que no existe en el destino.
Fix temporal: `sed -i 's/DEFINER=[^ ]//g' backup.sql` antes de restaurar. Solución permanente: documentar el `DEFINER` original y replicar el usuario.
Bloque 3 — Point-in-Time Recovery (PITR) en MySQL con binlogs
¿Qué es PITR?
PITR = restaurar una base a un momento específico en el tiempo, no a un punto completo de respaldo. Útil cuando:
- Se hizo un `DROP TABLE` accidental a las 14:32.
- El último respaldo completo es de las 02:00.
- Quieres restaurar al estado de las 14:31:59.
Requisitos previos
[mysqld]
log_bin = mysql-bin
server_id = 1
binlog_format = ROW
expire_logs_days = 7
```Respaldo completo del cual partir (Tema 16).
Conocer la posición o el timestamp al que quieres恢复.
Procedimiento
Paso 1 — Detener la base y hacer un snapshot del estado actual (por seguridad):
Renombrar la base actual para no perder el estado "después del incidente"
mysqldump -u root -p --add-drop-table tienda_tlalli > tienda_tlalli_post_incidente.sql mysql -u root -p -e "CREATE DATABASE tienda_tlalli_post_incidente;" mysql -u root -p tienda_tlalli_post_incidente < tienda_tlalli_post_incidente.sql
Paso 2 — Restaurar el respaldo completo (de las 02:00):
Paso 3 — Aplicar binlogs hasta el momento del incidente (hasta las 14:31:59):Ver los binlogs disponibles
ls -la /var/lib/mysql/mysql-bin.Aplicar el binlog desde el inicio hasta el timestamp deseado
mysqlbinlog --stop-datetime="2026-08-23 14:31:59" \ /var/lib/mysql/mysql-bin.000123 \ | mysql -u root -p tienda_tlalli
Paso 4 — Verificar que el DROP TABLE no aparece:
Búsqueda del incidente en el binlog
Si no recuerdas el timestamp exacto, busca el `DROP TABLE` o `DELETE` problemático:
O usando posición (`--start-position`, `--stop-position`) si conoces el `Log_name` y `Pos` del evento.
Bloque 4 — Point-in-Time Recovery (PITR) en PostgreSQL con WAL
Concepto
PostgreSQL archiva cada cambio en un archivo WAL (Write-Ahead Log). Con WAL archiving configurado, puedes restaurar la base a un momento específico.
Configuración para PITR
`postgresql.conf`:
Reiniciar PostgreSQL y verificar:
Procedimiento de PITR
Paso 1 — Respaldo base (no hace falta detener la base para esto si usas `pg_basebackup`):
Paso 2 — Detener PostgreSQL:
Paso 3 — Crear `recovery.signal` y configurar `postgresql.conf`:Como root o postgres
touch /var/lib/postgresql/16/main/recovery.signal echo "restore_command = 'cp /var/lib/pg_archive/%f %p'" >> /var/lib/postgresql/16/main/postgresql.conf echo "recovery_target_time = '2026-08-23 14:31:59'" >> /var/lib/postgresql/16/main/postgresql.conf
Paso 4 — Arrancar PostgreSQL en modo recovery:PostgreSQL lee recovery.signal, aplica WALs hasta el timestamp, y arranca
Paso 5 — Verificar y desconectar del modo recovery:-- Desconectar el modo recovery SELECT pg_wal_replay_resume(); -- o reiniciar normalmente
Bloque 5 — RPO, RTO y la prueba de fuego
RPO (Recovery Point Objective): cuánto dato estás dispuesto a perder, medido en tiempo. Si tu RPO es 5 minutos, necesitas un respaldo o replicación que no tenga más de 5 minutos de desfase.
RTO (Recovery Time Objective): cuánto tiempo toleras que la base esté caída. Si tu RTO es 1 hora, tu procedimiento de restauración debe completarse en menos de 1 hora.
Ejemplo real con tienda_tlalli (comercio electrónico):
- RPO = 5 min (perder 5 min de pedidos es aceptable en horas de baja actividad)
- RTO = 30 min (puedo tolerar 30 min de caída durante el día, 5 min durante un CyberMonday)
Tabla de decisión:| Sistema | RPO objetivo | RTO objetivo | Estrategia mínima |
|---|---|---|---|
| E-commerce | 5 min | 15 min | PITR + binlog shipping a standby |
| Blog personal | 24 h | 24 h | Dump diario |
| Sistema de nóminas | 0 (cero pérdida) | 4 h | Replicación síncrona + PITR |
| App móvil de notas | 1 h | 1 h | Replicación asíncrona |
| Datos analíticos | 24 h | 48 h | Dump diario |
La prueba de fuego: una vez al trimestre, simula un desastre en un entorno de prueba y mide:
- Tiempo desde el "incidente" hasta tener la base funcional.
- Datos perdidos (comparar con la última transacción que recuerdas).
- Personas involucradas, herramientas usadas, pasos manuales.
Documenta el resultado. Si no llegas al RTO, ajusta el procedimiento o la infraestructura.
AI Mission
Pídele a tu IA que genere un runbook de recuperación. Prompt sugerido:
- Tiempo estimado de recuperación.
- Datos que se perderían.
- Comandos exactos.
- Lista de verificación post-restauración.
Compara con lo que aprendiste. La IA puede ayudarte a redactar, pero la ejecución depende de ti.
Errores típicos
Confundir PITR con restauración completa → PITR es hasta un momento, restauración completa es hasta el respaldo más reciente.
Binlogs no habilitados → no se puede hacer PITR. Verificar antes del incidente.
No probar el recovery.signal de PostgreSQL → la primera vez que lo necesitas, descubres que el archive_command falla.
Olvidar el `DEFINER` en rutinas/triggers → al restaurar, los objetos no se crean por permisos.
Restaurar en producción sin probar antes en test_restore → el día del incidente, descubres que tu procedimiento no funciona.
Confundir PITR con replicación → PITR recupera del pasado. La replicación protege del futuro. Son complementarios, no intercambiables.
LAB práctico — Simular un DROP TABLE y recuperarlo con PITR
Objetivo: ejecutar un `DROP TABLE` deliberado, detectarlo, y recuperar la base con PITR en menos de 30 minutos.
Paso 1 — Preparar el terreno
1. Hacer un respaldo completo fresco
mysqldump -u root -p --single-transaction --routines --triggers \ --default-character-set=utf8mb4 tienda_tlalli \ | gzip > /tmp/lab_pre.sql.gz2. Verificar que hay binlogs
ls -la /var/lib/mysql/mysql-bin.
Paso 2 — El incidente
Paso 3 — Detectar el momento del incidente
Anota el timestamp exacto
Paso 4 — Restaurar el respaldo base
Paso 5 — Aplicar binlogs hasta 5 segundos antes del DROP
mysqlbinlog --stop-datetime="$T_STOP" \ /var/lib/mysql/mysql-bin.000001 \ | mysql -u root -p tienda_tlalli
Paso 6 — Verificar
Entregables del LAB
- [ ] Captura del momento del incidente.
- [ ] Tiempo total de recuperación medido.
- [ ] Hash SHA-256 del binlog aplicado.
- [ ] Verificación de integridad referencial.
- [ ] Conclusión: ¿se cumplió el RTO? ¿qué se podría mejorar?
Quest
Preguntas de opción múltiple (10)
1. ¿Qué es PITR?
A. Un protocolo de Internet para bases de datos. B. Point-in-Time Recovery: restaurar la base a un momento específico. C. Un tipo de índice. D. Una librería de PHP.
2. ¿Qué tecnología habilita PITR en MySQL?
A. Los archivos InnoDB redo log. B. Los binlogs (binary logs). C. El archivo `my.cnf`. D. El log de errores.
3. ¿Qué archivo marca el inicio del modo recovery en PostgreSQL?
A. `recovery.conf` B. `recovery.signal` C. `postgresql.conf` D. `pg_hba.conf`
4. ¿Qué significa RPO?
A. Recovery Point Objective: cuánto dato estás dispuesto a perder, medido en tiempo. B. Recovery Process Order: el orden de los pasos de recuperación. C. Real Performance Optimizer. D. Read Parallel Operations.
5. ¿Qué significa RTO?
A. Recovery Time Objective: cuánto tiempo toleras que la base esté caída. B. Real-Time Operations. C. Relational Table Optimization. D. Restore To Original.
6. ¿Por qué no basta con tener respaldos sin haberlos restaurado de prueba?
A. Porque ocupan espacio. B. Porque un respaldo nunca probado puede estar corrupto o ser inconsistente. C. Porque el motor de la base se queja. D. Porque la ley lo prohíbe.
7. ¿Qué comando se usa para aplicar binlogs en MySQL?
A. `mysqlapply` B. `mysqlbinlog` C. `mysqlrestore` D. `mysqllog`
8. ¿Qué vista de `information_schema` permite verificar la integridad referencial en MySQL?
A. `KEY_COLUMN_USAGE` B. `PROCESSLIST` C. `INNODB_TRX` D. `GLOBAL_STATUS`
9. En PostgreSQL, ¿qué parámetro de `postgresql.conf` activa el archivado de WAL?
A. `wal_level = replica` B. `archive_mode = on` C. `archive_command = '...'` D. Todas las anteriores.
10. ¿Qué herramienta de PostgreSQL hace un respaldo base consistente mientras la base sigue corriendo?
A. `pg_dump` B. `pg_basebackup` C. `pg_dumpall` D. `psql`
Preguntas abiertas (3)
A. Una tienda online tiene RPO de 5 minutos y RTO de 15 minutos. Describe la arquitectura mínima (respaldo, replicación, monitoreo) que permitiría cumplir ambos objetivos. ¿Qué pasa si solo tenemos un respaldo diario?
B. Explica la diferencia entre restauración lógica (de un dump) y Point-in-Time Recovery (PITR). Da un caso real en el que PITR sea imprescindible y otro en el que un dump basta.
C. Tu `recovery.signal` de PostgreSQL no se activó tras un reinicio. Lista las 4 causas más probables y cómo las investigarías.
Respuestas modelo
1. B — PITR = Point-in-Time Recovery, restaurar al estado de un momento específico. La A es inventada. La C es un tipo de objeto. La D no es librería de PHP.
2. B — Los binlogs (`mysql-bin.NNNNNN`) registran cada cambio. Sin ellos no hay PITR. Los redo logs son internos de InnoDB, no sirven para PITR de base completa. `my.cnf` es configuración. El log de errores no almacena cambios.
3. B — Desde PostgreSQL 12, el archivo `recovery.signal` (un archivo vacío) marca el modo recovery. En versiones anteriores era `recovery.conf`. La C es la configuración general, no el marcador. La D es la configuración de autenticación.
4. A — RPO = Recovery Point Objective, mide la pérdida de datos tolerable en tiempo. La B es un invento. La C y D son ficticias.
5. A — RTO = Recovery Time Objective, mide el tiempo de inactividad tolerable. La B, C, D son ficticias.
6. B — Un respaldo puede estar corrupto, incompleto, generado con charset incorrecto, con FK rotas, o ser de una versión incompatible. Solo la restauración real lo demuestra. La A es práctica pero no la razón principal. La C es falsa. La D es exagerada.
7. B — `mysqlbinlog` lee y aplica binlogs. `mysqlapply` no existe. `mysqlrestore` no existe (existe `mysqlbackup` de Mysql Enterprise). `mysqllog` no existe.
8. A — `KEY_COLUMN_USAGE` muestra las foreign keys y permite cruzarlas para detectar huérfanos. `PROCESSLIST` muestra conexiones. `INNODB_TRX` muestra transacciones activas. `GLOBAL_STATUS` muestra métricas.
9. D — Las tres son necesarias: `wal_level` define el nivel de información, `archive_mode` activa el archivado, `archive_command` indica el comando a ejecutar. Sin una de las tres, el archivado no funciona.
10. B — `pg_basebackup` hace un respaldo base consistente en línea, ideal como punto de partida para PITR o para configurar una replica. `pg_dump` también puede hacerlo pero es más lento y no es físico. `pg_dumpall` es para todo el cluster. `psql` es el cliente.
Tras las huellas — Por Citlalli
En el Templo Mayor de Tenochtitlan, los sacerdotes no solo conservaban los codex: también hacían
copias de seguridad en cuevas cercanas y en el templo de Tlatelolco. Sabían que el fuego, el agua y la guerra podían llevarse los registros. Y practicaban, cada año, la ceremonia del Fuego Nuevo: encendían una llama nueva, y si la antigua se apagaba antes de tiempo, se vaciaba el templo y se reiniciaba el ciclo.En bases de datos modernas, esa ceremonia del Fuego Nuevo es tu prueba de restauración trimestral. La pregunta no es
si vas a necesitar restaurar, sino cuándo*. Y cuando pase, no quieres descubrir que tu procedimiento no funciona.Reflexión mexica: los mexicas no tenían un SLA de 15 minutos, pero entendían que la continuidad del registro era la diferencia entre un pueblo con historia y un pueblo sin memoria. Tu base de datos es el codex de tu organización. Trátala como tal.