mysqldump y pg_dump generan copias de seguridad lógicas: un fichero con las sentencias SQL (o un archivo en formato propio, en el caso de PostgreSQL) necesarias para reconstruir una base de datos. Son portables entre versiones y servidores, permiten restaurar una sola base de datos o tabla y se pueden hacer sin detener el servicio.

En este tutorial harás copias de seguridad de MySQL y de PostgreSQL en Ubuntu 24.04, las restaurarás en una base de datos de prueba para comprobar que funcionan y automatizarás el proceso con un script y un timer de systemd que conserva las copias de los últimos 14 días.

Requisitos previos

Para seguir este tutorial necesitas:

  • Un servidor con Ubuntu 24.04 LTS, por ejemplo un VPS de CubePath, y un usuario no root con privilegios sudo.
  • MySQL 8.0 (paquete mysql-server) o MariaDB, y/o PostgreSQL 16 (paquete postgresql) instalados desde los repositorios de Ubuntu. Puedes seguir solo la parte que corresponda a tu motor.
  • Espacio libre en disco al menos igual al tamaño de tus bases de datos. Las copias comprimidas suelen ocupar entre un 10 % y un 30 % del tamaño original.

En los ejemplos se usa una base de datos llamada appdb. Sustitúyela por el nombre de la tuya.

Paso 1: Preparar el directorio de copias

Crea un directorio que solo pueda leer root. Los volcados contienen todos los datos de la aplicación, incluidos los hashes de contraseñas de tus usuarios:

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

Consulta el tamaño de tus bases de datos para estimar el espacio y el tiempo necesarios. En MySQL, root se autentica por socket en Ubuntu, así que no necesitas contraseña con sudo:

sudo mysql -e "SELECT table_schema AS db, ROUND(SUM(data_length + index_length) / 1024 / 1024) AS mb FROM information_schema.tables GROUP BY table_schema;"

+--------------------+------+
| db                 | mb   |
+--------------------+------+
| appdb              |  842 |
| mysql              |    3 |
...

En PostgreSQL, conéctate como el usuario del sistema postgres:

sudo -u postgres psql -c "SELECT datname, pg_size_pretty(pg_database_size(datname)) FROM pg_database WHERE NOT datistemplate;"

 datname  | pg_size_pretty
----------+----------------
 postgres | 7532 kB
 appdb    | 1204 MB

Paso 2: Hacer una copia de MySQL con mysqldump

Vuelca appdb y comprímela sobre la marcha:

sudo mysqldump --single-transaction --routines --triggers --events appdb | gzip | sudo tee /var/backups/db/appdb.sql.gz > /dev/null

Las opciones importantes son estas:

  • --single-transaction: hace el volcado dentro de una transacción con una vista consistente de los datos, sin bloquear las tablas InnoDB. La aplicación sigue funcionando mientras se hace la copia. No garantiza consistencia en tablas MyISAM.
  • --routines y --events: incluyen procedimientos almacenados, funciones y eventos programados, que no se exportan por defecto.
  • --triggers: incluye los triggers. Está activo por defecto, pero indicarlo deja clara la intención.

Para volcar todas las bases de datos, incluidos usuarios y permisos (almacenados en la base de datos mysql), usa --all-databases:

sudo mysqldump --all-databases --single-transaction --routines --events | gzip | sudo tee /var/backups/db/all-mysql.sql.gz > /dev/null

Comprueba que el fichero está completo. mysqldump escribe una línea Dump completed al terminar correctamente, así que si falta, el volcado se interrumpió:

sudo zcat /var/backups/db/appdb.sql.gz | tail -n 1

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

Comprueba también que el gzip no está dañado:

sudo gzip -t /var/backups/db/appdb.sql.gz && echo OK

Usar un usuario dedicado para las copias

Si las copias se hacen desde otro servidor o no quieres usar root, crea un usuario con los privilegios mínimos. Sustituye your_strong_password por una contraseña segura:

sudo mysql

CREATE USER 'backup'@'localhost' IDENTIFIED BY 'your_strong_password';
GRANT SELECT, SHOW VIEW, TRIGGER, LOCK TABLES, EVENT, PROCESS ON *.* TO 'backup'@'localhost';
EXIT;

Guarda las credenciales en un fichero de opciones en lugar de escribirlas en la línea de comandos, donde quedarían en el historial y en la lista de procesos:

sudo nano /root/.backup.my.cnf

[mysqldump]
user=backup
password=your_strong_password

sudo chmod 600 /root/.backup.my.cnf

Y úsalo con --defaults-extra-file, que debe ser la primera opción del comando:

sudo mysqldump --defaults-extra-file=/root/.backup.my.cnf --single-transaction --routines --events appdb | gzip | sudo tee /var/backups/db/appdb.sql.gz > /dev/null

Paso 3: Hacer una copia de PostgreSQL con pg_dump

pg_dump admite varios formatos. El formato personalizado (-Fc) es el más práctico: sale comprimido, se restaura con pg_restore y permite recuperar solo algunas tablas o restaurar en paralelo:

sudo -u postgres pg_dump -Fc appdb | sudo tee /var/backups/db/appdb.dump > /dev/null

pg_dump siempre obtiene una instantánea consistente de la base de datos sin bloquear las escrituras de la aplicación.

Verifica el fichero listando su contenido. Si el archivo está dañado o incompleto, pg_restore falla:

sudo pg_restore --list /var/backups/db/appdb.dump | head -n 12

;
; Archive created at 2026-09-25 10:40:02 UTC
;     dbname: appdb
;     TOC Entries: 214
;     Compression: gzip
;     Dump Version: 1.15-0
;     Format: CUSTOM
...

pg_dump no incluye los roles ni sus contraseñas, porque son globales al clúster y no pertenecen a ninguna base de datos. Guárdalos por separado con pg_dumpall --globals-only:

sudo -u postgres pg_dumpall --globals-only | sudo tee /var/backups/db/pg-globals.sql > /dev/null

Para bases de datos de muchos gigabytes, el formato de directorio (-Fd) permite volcar varias tablas en paralelo con la opción -j; consulta man pg_dump si el volcado en un solo proceso se queda corto.

Paso 4: Restaurar y comprobar las copias

Una copia que nunca has restaurado no es una copia fiable. Restaura en una base de datos nueva para no tocar la de producción.

Restaurar en MySQL

Crea una base de datos de prueba y carga el volcado de appdb en ella:

sudo mysql -e "CREATE DATABASE appdb_restore;"
sudo zcat /var/backups/db/appdb.sql.gz | sudo mysql appdb_restore

Este volcado se generó con el nombre de una base de datos, sin --databases, por lo que no contiene USE appdb y se puede cargar en cualquier base de datos. Los volcados hechos con --all-databases sí incluyen CREATE DATABASE y USE, y se restauran sin indicar base de datos: zcat all-mysql.sql.gz | sudo mysql.

Compara el número de tablas del original y de la copia:

sudo mysql -N -e "SELECT table_schema, COUNT(*) FROM information_schema.tables WHERE table_schema IN ('appdb','appdb_restore') GROUP BY table_schema;"

appdb	37
appdb_restore	37

Haz también una consulta sobre una tabla importante de tu aplicación (por ejemplo, SELECT COUNT(*) FROM appdb_restore.users;) y compara el resultado. Al terminar, elimina la base de datos de prueba:

sudo mysql -e "DROP DATABASE appdb_restore;"

Restaurar en PostgreSQL

Crea la base de datos de destino y restaura con pg_restore. El fichero solo lo puede leer root, así que se pasa por una tubería al proceso que se ejecuta como postgres. --no-owner asigna los objetos al usuario que restaura, útil si el rol original no existe en el servidor de destino:

sudo -u postgres createdb appdb_restore
sudo cat /var/backups/db/appdb.dump | sudo -u postgres pg_restore -d appdb_restore --no-owner

Comprueba el resultado contando las tablas:

sudo -u postgres psql -d appdb_restore -c "SELECT count(*) FROM information_schema.tables WHERE table_schema = 'public';"

Y elimina la base de datos de prueba:

sudo -u postgres dropdb appdb_restore

Para recuperar solo una tabla de un archivo -Fc, añade -t nombre_tabla al comando pg_restore.

Paso 5: Automatizar las copias con systemd

Un script único se encarga de volcar cada base de datos por separado, comprobar errores y borrar las copias antiguas. Créalo:

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

#!/usr/bin/env bash
# Vuelca cada base de datos de MySQL/MariaDB y PostgreSQL en /var/backups/db
# y borra las copias con más de RETENTION_DAYS días.
set -euo pipefail
umask 077

BACKUP_DIR=/var/backups/db
RETENTION_DAYS=14
STAMP=$(date +%F_%H%M)

if command -v mysql >/dev/null; then
  mysql -N -e 'SHOW DATABASES' \
    | grep -Ev '^(information_schema|performance_schema|sys|mysql)$' \
    | while read -r db; do
        mysqldump --single-transaction --routines --triggers --events "$db" \
          | gzip > "$BACKUP_DIR/mysql_${db}_${STAMP}.sql.gz"
      done
fi

if command -v psql >/dev/null; then
  runuser -u postgres -- pg_dumpall --globals-only > "$BACKUP_DIR/pg_globals_${STAMP}.sql"
  runuser -u postgres -- psql -At -c \
    "SELECT datname FROM pg_database WHERE NOT datistemplate AND datname <> 'postgres'" \
    | while read -r db; do
        runuser -u postgres -- pg_dump -Fc "$db" > "$BACKUP_DIR/pg_${db}_${STAMP}.dump"
      done
fi

find "$BACKUP_DIR" -maxdepth 1 -type f \
  \( -name 'mysql_*' -o -name 'pg_*' \) -mtime +"$RETENTION_DAYS" -delete

Algunos detalles del script:

  • set -euo pipefail detiene el script ante cualquier error, incluido un fallo de mysqldump dentro de una tubería, y systemd marca la ejecución como fallida.
  • umask 077 hace que los ficheros creados solo los pueda leer root.
  • Cada base de datos va en su propio fichero con fecha, para poder restaurar una sin tocar las demás.
  • La base de datos mysql se excluye del bucle; si necesitas conservar usuarios y permisos de MySQL, añade un volcado con --all-databases o exporta los usuarios aparte.

Hazlo ejecutable y pruébalo a mano:

sudo chmod 700 /usr/local/sbin/db-backup
sudo /usr/local/sbin/db-backup
sudo ls -lh /var/backups/db

-rw------- 1 root root 118M Sep 25 11:02 mysql_appdb_2026-09-25_1102.sql.gz
-rw------- 1 root root 164M Sep 25 11:03 pg_appdb_2026-09-25_1102.dump
-rw------- 1 root root 1.2K Sep 25 11:03 pg_globals_2026-09-25_1102.sql

Ahora crea un servicio de systemd que ejecute el script:

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

[Unit]
Description=Copia de seguridad de bases de datos
After=mysql.service postgresql.service

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

Y un timer que lo lance cada noche a las 02:30:

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

[Unit]
Description=Copia de seguridad diaria de bases de datos

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

[Install]
WantedBy=timers.target

Persistent=true ejecuta la copia al arrancar si el servidor estaba apagado a la hora programada. Activa el timer y comprueba la próxima ejecución:

sudo systemctl daemon-reload
sudo systemctl enable --now db-backup.timer
systemctl list-timers db-backup.timer

NEXT                        LEFT     LAST PASSED UNIT            ACTIVATES
Fri 2026-09-26 02:30:00 UTC 15h left -    -      db-backup.timer db-backup.service

Puedes lanzar una ejecución inmediata y revisar su resultado con:

sudo systemctl start db-backup.service
journalctl -u db-backup.service -n 20

Si el script falla, systemctl status db-backup.service muestra failed, lo que permite detectarlo con cualquier sistema de monitorización que vigile unidades de systemd.

Paso 6: Copiar las copias fuera del servidor

Guarda al menos una copia en otra máquina o en otro centro de datos. Una forma sencilla es sincronizar el directorio con rsync sobre SSH hacia un servidor de copias:

sudo rsync -a /var/backups/db/ backup_user@backup_server_ip:/srv/backups/$(hostname)/db/

También puedes usar herramientas como restic o rclone, que cifran los datos y los envían a almacenamiento de objetos compatible con S3. Sea cual sea el método, añádelo como un paso más del servicio o como un timer propio, y comprueba de vez en cuando que puedes restaurar desde esa copia externa.

Solución de problemas

mysqldump: Error: 'Access denied; you need (at least one of) the PROCESS privilege(s) for this operation' when trying to dump tablespaces. Desde MySQL 8.0.21, volcar la información de tablespaces requiere el privilegio PROCESS. Concédelo al usuario de copias o añade --no-tablespaces si no usas tablespaces propios.

pg_dump: error: aborting because of server version mismatch. La versión de pg_dump es anterior a la del servidor, algo habitual al hacer la copia desde otra máquina. Usa el pg_dump de la misma versión mayor que el servidor o una más reciente.

La restauración de MySQL falla con ERROR 1227 (42000): Access denied; you need (at least one of) the SUPER ... privilege(s). El volcado contiene cláusulas DEFINER de otro usuario en vistas, triggers o rutinas. Restaura con un usuario administrador o crea antes en el servidor de destino el usuario que aparece en DEFINER.

El volcado de MySQL tarda mucho y ralentiza la aplicación. --single-transaction no bloquea, pero el volcado consume disco y CPU. Programa las copias en horas de poco tráfico o hazlas desde una réplica.

Conclusión

Ya tienes copias de seguridad lógicas de MySQL y PostgreSQL hechas sin detener el servicio, comprobadas mediante una restauración de prueba y programadas cada noche con un timer de systemd que conserva los últimos 14 días. Como siguientes pasos, puedes enviar las copias a un almacenamiento externo cifrado, configurar una alerta cuando db-backup.service falle y, si necesitas recuperar datos a un momento concreto, complementar estos volcados con los binlogs de MySQL o el archivado WAL de PostgreSQL.