--- title: "Recuperación PITR de MySQL y PostgreSQL en Ubuntu 24.04" description: "Configura los binlogs de MySQL 8 y el archivado WAL de PostgreSQL 16 en Ubuntu 24.04 para restaurar una base de datos al instante exacto anterior a un error." category: "Migración y Recuperación ante Desastres" language: es source: https://cubepath.com/es/docs/migración-y-recuperación-ante-desastres/recuperacion-en-un-momento-especifico-para-bases-de-datos updated: 2026-09-25 --- # Recuperación PITR de MySQL y PostgreSQL en Ubuntu 24.04 Una copia diaria te devuelve la base de datos tal como estaba anoche, pero si alguien ejecuta un `DELETE` sin `WHERE` a las 17:42, perderías todo lo escrito durante el día. La recuperación a un momento específico (PITR, *point-in-time recovery*) combina una copia base con el registro de transacciones que la base de datos ya genera, y reproduce ese registro hasta justo antes del error. En este tutorial configurarás PITR en Ubuntu 24.04 para MySQL 8.0, con los logs binarios, y para PostgreSQL 16, con el archivado de WAL, y harás una recuperación completa en cada uno. ## Requisitos previos Para seguir esta guía necesitas: - Un servidor con Ubuntu 24.04 LTS, por ejemplo un VPS de CubePath, con MySQL 8.0 (`mysql-server`) o PostgreSQL 16 (`postgresql`) instalados desde los repositorios de Ubuntu. - Un usuario no root con privilegios `sudo`. - Espacio en disco para la copia base y para los registros de transacciones de, al menos, el periodo entre dos copias base. - Idealmente, un servidor de pruebas donde ensayar la recuperación antes de necesitarla en producción. Las secciones de MySQL y PostgreSQL son independientes: sigue solo la del motor que uses. ## Cómo funciona PITR Cualquier recuperación a un momento concreto tiene tres piezas: - **Copia base**: una copia consistente de toda la base de datos en un instante conocido. - **Registro de transacciones**: los logs binarios en MySQL o los segmentos WAL en PostgreSQL, que contienen cada cambio posterior a la copia base. - **Objetivo de recuperación**: el punto (una hora o una posición en el log) hasta el que se reproducen los cambios. Si el error ocurrió a las 17:42 y la copia base es de las 03:00, restauras la copia de las 03:00 y reproduces los cambios de 03:00 a 17:41:59. Por eso es imprescindible conservar, sin huecos, todos los registros de transacciones desde la última copia base. ## MySQL 8.0 con logs binarios ### Paso 1: Configurar los logs binarios MySQL 8.0 activa los logs binarios por defecto, en formato `ROW`, con archivos `binlog.NNNNNN` en `/var/lib/mysql/` y una retención de 30 días. Comprueba el estado actual: ```bash sudo mysql -e "SHOW VARIABLES WHERE Variable_name IN ('log_bin','binlog_format','binlog_expire_logs_seconds','server_id','gtid_mode');" ``` ```output +----------------------------+---------+ | Variable_name | Value | +----------------------------+---------+ | binlog_expire_logs_seconds | 2592000 | | binlog_format | ROW | | gtid_mode | OFF | | log_bin | ON | | server_id | 1 | +----------------------------+---------+ ``` Si `log_bin` está en `OFF`, algún archivo de configuración lo desactiva con `skip-log-bin` o `disable-log-bin`. Busca esa línea en `/etc/mysql/mysql.conf.d/mysqld.cnf`, elimínala y reinicia MySQL. La retención debe cubrir con holgura el intervalo entre copias base. Para dejarla explícita en 14 días, edita el archivo de configuración: ```bash sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf ``` Añade al final de la sección `[mysqld]`: ```ini server-id = 1 binlog_expire_logs_seconds = 1209600 sync_binlog = 1 ``` `sync_binlog = 1` (el valor por defecto en 8.0) fuerza la escritura a disco en cada commit, así que no perderás transacciones confirmadas si el servidor se apaga de golpe. Reinicia y comprueba que MySQL arranca: ```bash sudo systemctl restart mysql sudo mysql -e "SHOW BINARY LOGS;" ``` ### Paso 2: Crear la copia base Crea un directorio para las copias: ```bash sudo install -d -m 0700 /var/backups/mysql ``` Haz un volcado consistente de todas las bases de datos. `--single-transaction` lo hace sin bloquear tablas InnoDB, `--flush-logs` rota el log binario para que el siguiente archivo empiece justo después de la copia, y `--source-data=2` anota como comentario el archivo y la posición del log en ese instante: ```bash sudo sh -c 'mysqldump --all-databases --single-transaction --flush-logs --source-data=2 --routines --events --triggers | gzip > /var/backups/mysql/base-$(date +%F-%H%M).sql.gz' ``` Consulta las coordenadas guardadas en el volcado: ```bash zcat /var/backups/mysql/base-*.sql.gz | grep -m1 'SOURCE_LOG_FILE' ``` ```output -- CHANGE REPLICATION SOURCE TO SOURCE_LOG_FILE='binlog.000004', SOURCE_LOG_POS=157; ``` Guarda estos dos valores: son el punto desde el que tendrás que reproducir los logs. ### Paso 3: Guardar los logs binarios fuera del servidor Los logs en `/var/lib/mysql/` no sirven de nada si pierdes el disco. Copia periódicamente la copia base y los logs a otra máquina. Primero cierra el log actual para que quede completo: ```bash sudo mysql -e "FLUSH BINARY LOGS;" ``` Después sincronízalos con tu servidor de backups (sustituye `backup_user` y `your_backup_server`): ```bash sudo rsync -a /var/lib/mysql/binlog.[0-9]* /var/backups/mysql/ backup_user@your_backup_server:/srv/backups/mysql-host/ ``` Programa estos dos comandos cada 15 minutos (con cron o un temporizador de systemd) y tu pérdida máxima de datos ante un fallo total del servidor será de unos 15 minutos. ### Paso 4: Recuperar MySQL a un momento concreto Supón que a las 17:42 se borró la tabla `orders` de la base de datos `appdb`. Primero, detén las escrituras de la aplicación para que no se sigan generando cambios. Copia los logs a un lugar seguro antes de tocar nada, porque la restauración generará actividad nueva en el servidor: ```bash sudo install -d -m 0700 /root/pitr sudo cp /var/lib/mysql/binlog.[0-9]* /root/pitr/ ``` Localiza la sentencia dañina en el log que estaba activo a esa hora (normalmente el más reciente; `ls -l /root/pitr` muestra la fecha de cada archivo). `--base64-output=DECODE-ROWS -v` muestra los cambios en un formato legible: ```bash sudo mysqlbinlog --base64-output=DECODE-ROWS -v --start-datetime="2026-09-25 17:30:00" /root/pitr/binlog.000005 | grep -B 12 'DROP TABLE' ``` ```output ... # at 48213 #260925 17:42:08 server id 1 end_log_pos 48361 CRC32 0x5f1c2a7e Query thread_id=812 exec_time=0 error_code=0 Xid = 23117 use `appdb`/*!*/; SET TIMESTAMP=1790350928/*!*/; DROP TABLE `orders` /* generated by server */ ``` La línea `# at 48213` es la posición de `binlog.000005` en la que empieza el evento dañino. Esa será la posición de parada: se aplica todo lo anterior y nada a partir de ella. Restaura la copia base sin escribir en el log binario, para no mezclar la restauración con el historial original: ```bash zcat /var/backups/mysql/base-2026-09-25-0300.sql.gz | sudo mysql --init-command="SET SESSION sql_log_bin=0" ``` Reproduce los logs desde la posición anotada en el paso 2 hasta justo antes del error. `--start-position` se aplica al primer archivo y `--stop-position` al último: ```bash sudo mysqlbinlog --start-position=157 --stop-position=48213 /root/pitr/binlog.000004 /root/pitr/binlog.000005 | sudo mysql --init-command="SET SESSION sql_log_bin=0" ``` Si prefieres parar por hora, usa `--stop-datetime="2026-09-25 17:42:00"` en lugar de `--stop-position`. La posición es más precisa: dentro del mismo segundo puede haber transacciones buenas y malas. Verifica el resultado: ```bash sudo mysql -e "SELECT COUNT(*), MAX(created_at) FROM appdb.orders;" ``` La tabla debe existir y la última fila debe ser de poco antes de las 17:42. Cuando lo confirmes, reactiva la aplicación y haz una copia base nueva. ## PostgreSQL 16 con archivado de WAL ### Paso 1: Activar el archivado de WAL PostgreSQL escribe cada cambio en el WAL (*write-ahead log*) y recicla los segmentos cuando ya no los necesita. Para PITR hay que copiar cada segmento completo a un archivo antes de que se recicle. Crea el directorio del archivo: ```bash sudo install -d -m 0700 -o postgres -g postgres /var/lib/postgresql/wal_archive ``` En producción, este directorio debería estar en otro disco o sincronizarse con otra máquina. Crea un archivo de configuración propio; Ubuntu carga automáticamente todo lo que hay en `conf.d`: ```bash sudo nano /etc/postgresql/16/main/conf.d/archiving.conf ``` ```ini wal_level = replica archive_mode = on archive_command = 'test ! -f /var/lib/postgresql/wal_archive/%f && cp %p /var/lib/postgresql/wal_archive/%f' archive_timeout = 300 ``` `archive_command` se niega a sobrescribir un segmento ya archivado, y `archive_timeout = 300` fuerza el cambio de segmento cada 5 minutos aunque haya poca actividad, lo que limita la pérdida de datos. `archive_mode` requiere reiniciar: ```bash sudo systemctl restart postgresql@16-main ``` Fuerza un cambio de segmento y comprueba que se archiva: ```bash sudo -u postgres psql -c "SELECT pg_switch_wal();" sudo -u postgres psql -c "SELECT archived_count, last_archived_wal, failed_count FROM pg_stat_archiver;" ``` ```output archived_count | last_archived_wal | failed_count ----------------+--------------------------+-------------- 1 | 000000010000000000000003 | 0 ``` Si `failed_count` sube, revisa `/var/log/postgresql/postgresql-16-main.log`: normalmente es un problema de permisos del directorio. ### Paso 2: Crear la copia base Crea el directorio de copias y lanza `pg_basebackup`. `-X stream` incluye en la copia el WAL necesario para que sea consistente por sí sola, y `-c fast` fuerza un checkpoint inmediato: ```bash sudo install -d -m 0700 -o postgres -g postgres /var/lib/postgresql/basebackups sudo -u postgres pg_basebackup -D /var/lib/postgresql/basebackups/base-$(date +%F-%H%M) -Fp -X stream -c fast -P ``` Verifica la copia con `pg_verifybackup`, que compara cada archivo con el manifiesto generado: ```bash sudo -u postgres /usr/lib/postgresql/16/bin/pg_verifybackup /var/lib/postgresql/basebackups/base-2026-09-25-0300 ``` ```output backup successfully verified ``` Haz una copia base nueva cada día o cada semana. Los segmentos WAL anteriores a la copia base más antigua que conserves ya no son necesarios y puedes borrarlos del archivo. ### Paso 3: Recuperar PostgreSQL a un momento concreto Supón de nuevo un borrado accidental a las 17:42. Detén la aplicación y después el clúster: ```bash sudo systemctl stop postgresql@16-main ``` Aparta el directorio de datos actual en lugar de borrarlo; si algo sale mal, podrás volver a él: ```bash sudo mv /var/lib/postgresql/16/main /var/lib/postgresql/16/main.broken ``` Copia la copia base en su lugar con los permisos que exige PostgreSQL: ```bash sudo cp -a /var/lib/postgresql/basebackups/base-2026-09-25-0300 /var/lib/postgresql/16/main sudo chown -R postgres:postgres /var/lib/postgresql/16/main sudo chmod 700 /var/lib/postgresql/16/main ``` Configura el objetivo de recuperación en un archivo aparte, para poder eliminarlo al terminar: ```bash sudo nano /etc/postgresql/16/main/conf.d/pitr.conf ``` ```ini restore_command = 'cp /var/lib/postgresql/wal_archive/%f %p' recovery_target_time = '2026-09-25 17:42:00+02' recovery_target_action = 'promote' ``` Indica siempre la zona horaria en `recovery_target_time`. `recovery_target_action = 'promote'` hace que, al llegar al objetivo, el servidor termine la recuperación y acepte escrituras. Si quieres revisar los datos antes de promocionar, usa `'pause'`. Crea el archivo `recovery.signal`, que indica a PostgreSQL que arranque en modo recuperación: ```bash sudo -u postgres touch /var/lib/postgresql/16/main/recovery.signal ``` Arranca el clúster y sigue el log: ```bash sudo systemctl start postgresql@16-main sudo tail -n 20 /var/log/postgresql/postgresql-16-main.log ``` ```output LOG: starting point-in-time recovery to 2026-09-25 17:42:00+02 LOG: restored log file "000000010000000000000005" from archive ... LOG: recovery stopping before commit of transaction 91822, time 2026-09-25 17:42:08.114+02 LOG: redo done at 0/7A1C3F8 LOG: selected new timeline ID: 2 LOG: database system is ready to accept connections ``` Comprueba que el servidor ya no está en recuperación y que los datos son los esperados: ```bash sudo -u postgres psql -c "SELECT pg_is_in_recovery();" sudo -u postgres psql -d appdb -c "SELECT COUNT(*), MAX(created_at) FROM orders;" ``` `pg_is_in_recovery` debe devolver `f`. Elimina la configuración de recuperación para que no se aplique en el próximo arranque, y haz una copia base nueva, ya que el servidor ha empezado una línea temporal nueva: ```bash sudo rm /etc/postgresql/16/main/conf.d/pitr.conf sudo -u postgres pg_basebackup -D /var/lib/postgresql/basebackups/base-$(date +%F-%H%M) -Fp -X stream -c fast -P ``` Cuando todo funcione, borra `/var/lib/postgresql/16/main.broken`. ## Solución de problemas **MySQL: `ERROR 1062 Duplicate entry` al reproducir los logs.** Has empezado en una posición anterior a la copia base, o has aplicado dos veces el mismo archivo. Usa exactamente el archivo y la posición de `SOURCE_LOG_FILE` y `SOURCE_LOG_POS` del volcado. **MySQL con GTID activado (`gtid_mode = ON`).** Los eventos reproducidos se saltan porque sus GTID ya constan como ejecutados. Antes de reproducir, ejecuta `RESET MASTER` en el servidor restaurado (solo si no tiene réplicas) o reproduce con `mysqlbinlog --skip-gtids`. **PostgreSQL: `recovery ended before configured recovery target was reached`.** Falta algún segmento WAL en el archivo o el objetivo es posterior al último WAL archivado. Comprueba que no hay huecos en `/var/lib/postgresql/wal_archive/` y que `failed_count` de `pg_stat_archiver` era 0. **PostgreSQL: el objetivo se ignora y recupera hasta el final.** Falta `recovery.signal` en el directorio de datos o `pitr.conf` no está en `conf.d`. Revisa el log al arrancar: debe aparecer `starting point-in-time recovery to ...`. ## Conclusión Con los logs binarios de MySQL o el archivado de WAL de PostgreSQL, tu pérdida de datos máxima pasa de "desde la última copia" a unos minutos, y puedes deshacer un error humano sin perder el resto del día. Como siguientes pasos, envía los logs y el WAL fuera del servidor de forma continua, automatiza las copias base con un temporizador de systemd y ensaya la recuperación en un servidor de pruebas cada cierto tiempo. Para PostgreSQL en producción, herramientas como pgBackRest o Barman gestionan la retención y la compresión por ti.