Copiar los archivos de datos de una base de datos mientras está en marcha produce copias inconsistentes que muchas veces no arrancan. La forma segura y sencilla de proteger MySQL y PostgreSQL es hacer volcados lógicos con sus propias herramientas, mysqldump y pg_dump, que generan una copia coherente sin detener el servicio. En este tutorial harás volcados manuales de ambas bases de datos en Ubuntu 24.04, los automatizarás con un script y un timer de systemd, y restaurarás una copia para comprobar que funciona.

Requisitos previos

Para seguir esta guía necesitas:

  • Un servidor con Ubuntu 24.04 LTS, por ejemplo un VPS de CubePath.
  • Un usuario no root con privilegios sudo.
  • MySQL 8.0 (paquete mysql-server) o MariaDB, PostgreSQL 16 (paquete postgresql), o ambos, instalados desde los repositorios de Ubuntu. Si solo usas uno, sigue únicamente sus apartados.
  • Espacio libre en disco para al menos varias copias comprimidas de tus bases de datos.

En los ejemplos, la base de datos se llama app_db. Sustitúyela por la tuya.

Copias lógicas y físicas

Hay dos formas de copiar una base de datos:

Copia lógicaCopia física
Qué guardaSentencias SQL o un archivo con los datosLos archivos de datos del servidor
Herramientasmysqldump, pg_dumpPercona XtraBackup, pg_basebackup
RestauraciónMás lenta, se reconstruyen tablas e índicesRápida
PortabilidadEntre versiones y servidoresMisma versión principal
Adecuada paraBases de datos de hasta decenas de GBBases de datos grandes, recuperación a un punto en el tiempo

Esta guía usa copias lógicas, que cubren la mayoría de aplicaciones web y se restauran con herramientas estándar.

Paso 1: Crear el directorio de copias

Los volcados contienen todos los datos de la aplicación, incluidos hashes de contraseñas y datos personales, así que solo root debe poder leerlos:

sudo install -d -m 700 /var/backups/db

Los pasos manuales siguientes se ejecutan en una shell de root, para que las redirecciones puedan escribir en ese directorio:

sudo -i

Paso 2: Hacer un volcado de MySQL

En Ubuntu, el usuario root de MySQL se autentica por el socket del sistema (auth_socket), así que desde una shell de root puedes usar mysqldump sin contraseña ni archivos de credenciales.

Haz el volcado de una base de datos y comprímelo:

mysqldump --single-transaction --routines --triggers --events app_db | gzip > /var/backups/db/app_db.sql.gz

Qué hacen las opciones:

  • --single-transaction lee todas las tablas dentro de una transacción, de modo que la copia refleja un único instante sin bloquear las escrituras. Solo es consistente para tablas InnoDB, el motor por defecto. Si tienes tablas MyISAM, esas tablas pueden quedar inconsistentes.
  • --routines y --events incluyen procedimientos almacenados y eventos programados, que no se copian por defecto. --triggers está activo por defecto, pero indicarlo deja clara la intención.

Comprueba que el archivo comprimido está íntegro y que el volcado terminó. mysqldump escribe una línea final Dump completed solo cuando acaba correctamente:

gzip --test /var/backups/db/app_db.sql.gz && zcat /var/backups/db/app_db.sql.gz | tail -n 1

-- Dump completed on 2026-09-25 10:31:02

Si usas MariaDB, los comandos son iguales. Las herramientas se llaman también mariadb-dump y mariadb, y los nombres mysqldump y mysql siguen disponibles.

Paso 3: Hacer un volcado de PostgreSQL

En PostgreSQL, el superusuario es el usuario del sistema postgres, que se autentica por el socket con el método peer. Desde la shell de root, ejecuta pg_dump como ese usuario con runuser. La redirección la hace la shell de root, así que el archivo se escribe con los permisos del directorio de copias:

runuser -u postgres -- pg_dump --format=custom app_db > /var/backups/db/app_db.dump

El formato personalizado (--format=custom) va comprimido y permite restaurar tablas concretas o reordenar la restauración con pg_restore, cosa que no permite un volcado SQL plano.

pg_dump copia una base de datos, pero no los roles (usuarios) ni sus contraseñas, que son globales al servidor. Guárdalos con pg_dumpall:

runuser -u postgres -- pg_dumpall --globals-only > /var/backups/db/globals.sql

Comprueba que el volcado es válido listando su contenido. Si el archivo estuviera dañado o incompleto, pg_restore daría un error:

pg_restore --list /var/backups/db/app_db.dump | head -n 8

;
; Archive created at 2026-09-25 10:34:11 CEST
;     dbname: app_db
;     TOC Entries: 214
;     Compression: gzip
;     Dump Version: 1.15-0
;     Format: CUSTOM
;     Integer: 4 bytes

Sal de la shell de root con exit.

Paso 4: Crear el script de copia

Un script permite copiar cada base de datos en su propio archivo, detectar errores y borrar las copias antiguas. Cada ejecución crea un directorio con la fecha y la hora.

Crea el script:

sudo nano /usr/local/sbin/db-backup.sh

#!/usr/bin/env bash
# Volcados diarios de MySQL y PostgreSQL con rotación.
set -euo pipefail

BACKUP_ROOT="/var/backups/db"
RETENTION_DAYS=14
BACKUP_MYSQL=yes        # yes o no
BACKUP_POSTGRES=yes     # yes o no

umask 077
dest="${BACKUP_ROOT}/$(date +%F_%H%M)"
mkdir -p "$dest"

log() {
    printf '%s %s\n' "$(date '+%F %T')" "$*"
}

if [[ "$BACKUP_MYSQL" == yes ]]; then
    mapfile -t mysql_dbs < <(mysql -N -B -e 'SHOW DATABASES' \
        | grep -Ev '^(information_schema|performance_schema|mysql|sys)$')
    for db in "${mysql_dbs[@]}"; do
        log "MySQL: ${db}"
        mysqldump --single-transaction --routines --triggers --events "$db" \
            | gzip > "${dest}/mysql_${db}.sql.gz.part"
        mv "${dest}/mysql_${db}.sql.gz.part" "${dest}/mysql_${db}.sql.gz"
    done
fi

if [[ "$BACKUP_POSTGRES" == yes ]]; then
    runuser -u postgres -- pg_dumpall --globals-only > "${dest}/pg_globals.sql"
    mapfile -t pg_dbs < <(runuser -u postgres -- psql -At -c \
        "SELECT datname FROM pg_database WHERE datallowconn AND NOT datistemplate")
    for db in "${pg_dbs[@]}"; do
        log "PostgreSQL: ${db}"
        runuser -u postgres -- pg_dump --format=custom "$db" > "${dest}/pg_${db}.dump.part"
        mv "${dest}/pg_${db}.dump.part" "${dest}/pg_${db}.dump"
    done
fi

# Borrar los directorios de copias con más de RETENTION_DAYS días
find "$BACKUP_ROOT" -mindepth 1 -maxdepth 1 -type d -mtime +"$RETENTION_DAYS" -exec rm -rf -- {} +

log "Copia completada en ${dest}"

Puntos clave del script:

  • set -o pipefail es imprescindible: sin él, si mysqldump falla a mitad, gzip termina bien y la tubería se daría por correcta. Con él, el script se detiene con error.
  • Cada archivo se escribe con extensión .part y solo se renombra al terminar, así un archivo sin .part siempre es un volcado completo.
  • La lista de bases de datos se obtiene en cada ejecución: una base de datos nueva entra en la copia sin tocar el script.
  • La rotación solo borra directorios del primer nivel de /var/backups/db, con más de 14 días.

Si solo usas uno de los dos motores, cambia a no la variable del otro. Hazlo ejecutable y pruébalo:

sudo chmod 700 /usr/local/sbin/db-backup.sh
sudo /usr/local/sbin/db-backup.sh

2026-09-25 10:40:02 MySQL: app_db
2026-09-25 10:40:09 PostgreSQL: app_db
2026-09-25 10:40:12 PostgreSQL: postgres
2026-09-25 10:40:12 Copia completada en /var/backups/db/2026-09-25_1040

Comprueba los archivos generados:

sudo ls -lh /var/backups/db/2026-09-25_1040/

Paso 5: Programar la copia con systemd

Un timer de systemd registra cada ejecución en el journal y marca el servicio como fallido si el script termina con error. Crea la unidad de servicio:

sudo nano /etc/systemd/system/db-backup.service

[Unit]
Description=Volcado de bases de datos MySQL y PostgreSQL
After=mysql.service postgresql.service

[Service]
Type=oneshot
ExecStart=/usr/local/sbin/db-backup.sh
Nice=10
IOSchedulingClass=idle

Crea el timer para ejecutarla cada día a la 01:30:

sudo nano /etc/systemd/system/db-backup.timer

[Unit]
Description=Volcado diario de bases de datos

[Timer]
OnCalendar=*-*-* 01:30:00
Persistent=true

[Install]
WantedBy=timers.target

Actívalo y lanza el servicio una vez a mano para comprobar que funciona desde systemd:

sudo systemctl daemon-reload
sudo systemctl enable --now db-backup.timer
sudo systemctl start db-backup.service
sudo journalctl -u db-backup.service -n 10 --no-pager

El registro debe terminar con Copia completada en .... Comprueba la próxima ejecución:

systemctl list-timers db-backup.timer

Si además usas una herramienta de copia de archivos (rsnapshot, BorgBackup, Restic o un script con cron), prográmala después de la 01:30 e incluye /var/backups/db en ella. Así los volcados salen del servidor cada noche.

Paso 6: Restaurar y comprobar una copia

Una copia que nunca se ha restaurado no está comprobada. Restaura periódicamente en una base de datos temporal y verifica los datos. Abre una shell de root:

sudo -i

Restaurar MySQL

Crea una base de datos vacía y carga el volcado en ella. Como el volcado de una sola base de datos no incluye CREATE DATABASE, puedes restaurarlo con cualquier nombre:

mysql -e 'CREATE DATABASE app_db_restore'
zcat /var/backups/db/2026-09-25_1040/mysql_app_db.sql.gz | mysql app_db_restore

Compara el número de filas de una tabla importante entre la base de datos original y la restaurada (sustituye users por una de tus tablas):

mysql -N -e 'SELECT COUNT(*) FROM app_db.users; SELECT COUNT(*) FROM app_db_restore.users;'

1523
1523

Cuando termines, borra la base de datos de prueba:

mysql -e 'DROP DATABASE app_db_restore'

Para restaurar sobre la base de datos real tras una pérdida de datos, el procedimiento es el mismo usando app_db como destino. Detén antes la aplicación para que no escriba durante la restauración.

Restaurar PostgreSQL

Crea una base de datos vacía y restaura en ella el volcado. pg_restore lee el archivo desde la entrada estándar, que abre la shell de root:

runuser -u postgres -- createdb app_db_restore
runuser -u postgres -- pg_restore --dbname=app_db_restore < /var/backups/db/2026-09-25_1040/pg_app_db.dump

Si el servidor no tiene los roles propietarios de los objetos (por ejemplo, en un servidor nuevo), restaura antes los roles:

runuser -u postgres -- psql < /var/backups/db/2026-09-25_1040/pg_globals.sql

En un servidor que ya tiene esos roles, psql mostrará errores role ... already exists, que puedes ignorar.

Compara el número de filas:

runuser -u postgres -- psql -At -d app_db -c 'SELECT count(*) FROM users'
runuser -u postgres -- psql -At -d app_db_restore -c 'SELECT count(*) FROM users'

Y borra la base de datos de prueba:

runuser -u postgres -- dropdb app_db_restore
exit

Solución de problemas

mysqldump: Got error: 1045: Access denied for user 'root'@'localhost'. El usuario root de MySQL no usa auth_socket (por ejemplo, porque se le asignó una contraseña). Crea /root/.my.cnf con permisos 600 y una sección [client] con user y password, y mysqldump la leerá automáticamente.

pg_dump: error: connection to server ... failed: FATAL: Peer authentication failed. El comando no se ejecuta como el usuario del sistema postgres. Usa siempre runuser -u postgres -- o sudo -u postgres.

Permission denied al escribir el volcado de PostgreSQL. La redirección > se ejecuta con el usuario de tu shell. Si no eres root, no puedes escribir en /var/backups/db. Abre antes una shell de root con sudo -i.

El servicio falla con pipefail pero el archivo existe. Queda como .part: el volcado se cortó. Revisa en journalctl -u db-backup.service el error de mysqldump o pg_dump, normalmente falta de espacio en disco o una tabla dañada.

Conclusión

Tienes volcados diarios y consistentes de MySQL y PostgreSQL, uno por base de datos, con rotación automática, ejecutados por un timer de systemd y un procedimiento de restauración comprobado. Como siguientes pasos, envía /var/backups/db fuera del servidor con una herramienta como Restic o BorgBackup, programa una restauración de prueba mensual y, si necesitas recuperar datos a un instante concreto, estudia el archivado de binary logs en MySQL o de WAL en PostgreSQL.