Una copia de seguridad solo sirve si se hace sola, se guarda fuera del servidor y se ha probado a restaurar. En este tutorial configurarás copias lógicas diarias de MySQL (o MariaDB) con mysqldump y de PostgreSQL con pg_dump en Ubuntu 24.04, programadas con timers de systemd, con rotación automática, copia a un servidor remoto y una restauración de prueba para confirmar que los archivos son válidos.

Las copias lógicas (volcados SQL) son adecuadas para bases de datos de hasta decenas de GB. Para volúmenes mayores o recuperación a un punto en el tiempo necesitarás copias físicas (Percona XtraBackup, pgBackRest), que quedan fuera de esta guía.

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, MariaDB 10.11 o PostgreSQL 16 instalado desde los repositorios de Ubuntu. Si solo usas uno de los dos motores, sigue únicamente su sección.
  • Un usuario no root con privilegios sudo.
  • Espacio libre en disco para al menos varias copias. Comprueba el tamaño de tus datos con sudo du -sh /var/lib/mysql o sudo du -sh /var/lib/postgresql.
  • Opcional: un segundo servidor accesible por SSH para guardar las copias fuera de la máquina.

Paso 1: Crear un usuario de MySQL para las copias

No uses root para las copias: crea un usuario que solo pueda leer. Abre la consola de MySQL (en Ubuntu, root se autentica por socket, así que no pide contraseña):

sudo mysql

Crea el usuario y concédele los privilegios que necesita mysqldump. Sustituye your_strong_password por una contraseña larga y aleatoria:

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

SELECT global permite volcar también procedimientos y funciones; SHOW VIEW, TRIGGER y EVENT cubren vistas, triggers y eventos programados.

Guarda las credenciales en un archivo de opciones legible solo por root, para no ponerlas en la línea de comandos (donde las vería cualquiera con ps):

sudo nano /etc/mysql/backup.cnf

[client]
user = backup
password = your_strong_password

sudo chmod 600 /etc/mysql/backup.cnf

Comprueba que el usuario funciona y ve tus bases de datos:

sudo mysql --defaults-extra-file=/etc/mysql/backup.cnf -e "SHOW DATABASES;"

+--------------------+
| Database           |
+--------------------+
| appdb              |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+

Paso 2: Crear el script de copia de MySQL

El script vuelca cada base de datos de usuario por separado a un archivo comprimido, dentro de un directorio con la fecha, y borra los directorios con más días que los indicados en RETENTION_DAYS. Cada volcado se escribe primero en un archivo .tmp y solo se renombra si termina bien, así nunca quedará un archivo incompleto con aspecto de copia válida.

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

#!/usr/bin/env bash
set -euo pipefail

BACKUP_DIR="/var/backups/mysql"
RETENTION_DAYS=14
DEFAULTS="/etc/mysql/backup.cnf"
DEST="${BACKUP_DIR}/$(date +%F_%H%M)"

umask 077
mkdir -p "$DEST"

mapfile -t databases < <(
  mysql --defaults-extra-file="$DEFAULTS" -N -e "SHOW DATABASES;" \
    | grep -Ev '^(information_schema|performance_schema|sys|mysql)$'
)

for db in "${databases[@]}"; do
  mysqldump --defaults-extra-file="$DEFAULTS" \
    --single-transaction --routines --triggers --events --no-tablespaces \
    "$db" | gzip > "${DEST}/${db}.sql.gz.tmp"
  mv "${DEST}/${db}.sql.gz.tmp" "${DEST}/${db}.sql.gz"
  echo "OK ${db}"
done

find "$BACKUP_DIR" -mindepth 1 -maxdepth 1 -type d -mtime +"$RETENTION_DAYS" -exec rm -rf {} +

Algunos detalles del script:

  • --single-transaction hace una copia consistente de las tablas InnoDB sin bloquear las escrituras.
  • --no-tablespaces evita que mysqldump necesite el privilegio PROCESS.
  • set -o pipefail hace que el script falle si mysqldump falla, aunque gzip termine bien.
  • Se excluye el esquema mysql, que contiene los usuarios. Si los quieres guardar, exporta sus definiciones con SHOW CREATE USER y SHOW GRANTS.

En MariaDB los binarios se llaman mariadb y mariadb-dump, aunque mysql y mysqldump siguen existiendo como enlaces, así que el script funciona igual.

Haz el script ejecutable:

sudo chmod 750 /usr/local/sbin/mysql-backup

Paso 3: Programar la copia de MySQL con un timer de systemd

Un timer de systemd tiene ventajas sobre cron: registra la salida en el journal, marca el servicio como fallido si el script falla y, con Persistent=true, ejecuta la copia pendiente si el servidor estaba apagado a la hora programada.

Crea la unidad de servicio:

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

[Unit]
Description=Copia de seguridad de MySQL
After=mysql.service

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

Si usas MariaDB, cambia mysql.service por mariadb.service.

Crea el timer, que lanzará el servicio todos los días a las 02:30:

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

[Unit]
Description=Copia diaria de MySQL

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

[Install]
WantedBy=timers.target

Recarga systemd, activa el timer y lanza una copia ahora para probarla:

sudo systemctl daemon-reload
sudo systemctl enable --now mysql-backup.timer
sudo systemctl start mysql-backup.service

Revisa el resultado en el journal:

sudo journalctl -u mysql-backup.service -n 20 --no-pager

Sep 25 10:12:03 db1 systemd[1]: Starting mysql-backup.service - Copia de seguridad de MySQL...
Sep 25 10:12:04 db1 mysql-backup[4121]: OK appdb
Sep 25 10:12:04 db1 systemd[1]: mysql-backup.service: Deactivated successfully.
Sep 25 10:12:04 db1 systemd[1]: Finished mysql-backup.service - Copia de seguridad de MySQL.

Comprueba también que el timer está programado:

systemctl list-timers mysql-backup.timer

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

Paso 4: Crear el script de copia de PostgreSQL

En PostgreSQL no hace falta crear un usuario ni guardar contraseñas: el script se ejecutará como el usuario del sistema postgres, que se autentica por socket local (método peer) y puede leer todas las bases de datos.

Crea el directorio de destino con postgres como propietario:

sudo install -d -o postgres -g postgres -m 700 /var/backups/postgresql

Crea el script:

sudo nano /usr/local/bin/pg-backup

#!/usr/bin/env bash
set -euo pipefail

BACKUP_DIR="/var/backups/postgresql"
RETENTION_DAYS=14
DEST="${BACKUP_DIR}/$(date +%F_%H%M)"

umask 077
mkdir -p "$DEST"

pg_dumpall --globals-only > "${DEST}/globals.sql"

mapfile -t databases < <(
  psql -At -c "SELECT datname FROM pg_database WHERE datallowconn AND NOT datistemplate;"
)

for db in "${databases[@]}"; do
  pg_dump --format=custom --file="${DEST}/${db}.dump.tmp" "$db"
  mv "${DEST}/${db}.dump.tmp" "${DEST}/${db}.dump"
  echo "OK ${db}"
done

find "$BACKUP_DIR" -mindepth 1 -maxdepth 1 -type d -mtime +"$RETENTION_DAYS" -exec rm -rf {} +

pg_dump --format=custom genera un archivo ya comprimido que se restaura con pg_restore y permite recuperar tablas sueltas. pg_dumpall --globals-only guarda los roles y tablespaces, que pg_dump no incluye.

sudo chmod 755 /usr/local/bin/pg-backup

Paso 5: Programar la copia de PostgreSQL

Crea el servicio. La directiva User=postgres hace que el script se ejecute con ese usuario:

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

[Unit]
Description=Copia de seguridad de PostgreSQL
After=postgresql.service

[Service]
Type=oneshot
User=postgres
ExecStart=/usr/local/bin/pg-backup
Nice=10
IOSchedulingClass=idle

Crea el timer a las 03:00, para no coincidir con la copia de MySQL:

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

[Unit]
Description=Copia diaria de PostgreSQL

[Timer]
OnCalendar=*-*-* 03:00:00
Persistent=true

[Install]
WantedBy=timers.target

Actívalo y prueba una ejecución:

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

Sep 25 10:20:11 db1 pg-backup[4388]: OK postgres
Sep 25 10:20:11 db1 pg-backup[4388]: OK appdb
Sep 25 10:20:11 db1 systemd[1]: pg-backup.service: Deactivated successfully.

Paso 6: Copiar las copias a otro servidor

Una copia que vive en el mismo disco que la base de datos no te protege si pierdes el servidor. Envía los archivos a una máquina distinta con rsync sobre SSH.

En el servidor de base de datos, genera una clave SSH sin frase de paso para root, dedicada solo a esta tarea:

sudo ssh-keygen -t ed25519 -N "" -f /root/.ssh/backup_ed25519

En el servidor remoto, crea un usuario backup y añade la clave pública (el contenido de /root/.ssh/backup_ed25519.pub) a su archivo ~/.ssh/authorized_keys. Después prueba la conexión desde el servidor de base de datos, sustituyendo your_backup_server por la IP o el nombre del servidor remoto. La primera vez te pedirá aceptar su huella:

sudo ssh -i /root/.ssh/backup_ed25519 backup@your_backup_server "mkdir -p backups/mysql backups/postgresql"

Añade la sincronización al final del servicio de MySQL. En un servicio Type=oneshot puedes encadenar varias líneas ExecStart, que se ejecutan en orden y se detienen si una falla:

sudo systemctl edit --full mysql-backup.service

[Service]
Type=oneshot
ExecStart=/usr/local/sbin/mysql-backup
ExecStart=/usr/bin/rsync -a -e "ssh -i /root/.ssh/backup_ed25519" /var/backups/mysql/ backup@your_backup_server:backups/mysql/
Nice=10
IOSchedulingClass=idle

No se usa --delete: así, si alguien borra las copias locales, las remotas se conservan. Gestiona la retención en el servidor remoto por separado, por ejemplo con un find -mtime propio.

Para PostgreSQL, el servicio se ejecuta como postgres, que no tiene acceso a la clave de root. Crea un servicio aparte que sincronice como root después de la copia:

sudo nano /etc/systemd/system/pg-backup-sync.service

[Unit]
Description=Sincronizar copias de PostgreSQL
After=pg-backup.service

[Service]
Type=oneshot
ExecStart=/usr/bin/rsync -a -e "ssh -i /root/.ssh/backup_ed25519" /var/backups/postgresql/ backup@your_backup_server:backups/postgresql/

Y enlázalo para que se lance cuando pg-backup.service termine con éxito, añadiendo esta línea a la sección [Unit] de pg-backup.service:

sudo systemctl edit --full pg-backup.service

[Unit]
Description=Copia de seguridad de PostgreSQL
After=postgresql.service
OnSuccess=pg-backup-sync.service

Recarga systemd y prueba toda la cadena:

sudo systemctl daemon-reload
sudo systemctl start mysql-backup.service pg-backup.service
sudo ssh -i /root/.ssh/backup_ed25519 backup@your_backup_server "ls backups/mysql backups/postgresql"

Deberías ver en el servidor remoto los mismos directorios con fecha que en /var/backups.

Paso 7: Verificar y restaurar una copia

Comprueba primero que los archivos no están corruptos. Para MySQL, gzip -t no muestra nada si el archivo es válido:

sudo ls -lh /var/backups/mysql/*/
sudo gzip -t /var/backups/mysql/2026-09-25_1012/appdb.sql.gz

Para PostgreSQL, pg_restore --list lee el índice del volcado:

sudo -u postgres pg_restore --list /var/backups/postgresql/2026-09-25_1020/appdb.dump | head

;
; Archive created at 2026-09-25 10:20:11 UTC
;     dbname: appdb
;     TOC Entries: 42
;     Compression: gzip
...

La única prueba real es restaurar. Restaura la copia de MySQL en una base de datos temporal y cuenta las tablas:

sudo mysql -e "CREATE DATABASE appdb_restore;"
sudo sh -c 'zcat /var/backups/mysql/2026-09-25_1012/appdb.sql.gz | mysql appdb_restore'
sudo mysql -e "SELECT COUNT(*) AS tablas FROM information_schema.tables WHERE table_schema = 'appdb_restore';"

+--------+
| tablas |
+--------+
|     12 |
+--------+

Haz lo mismo con PostgreSQL:

sudo -u postgres createdb appdb_restore
sudo -u postgres pg_restore --no-owner --dbname=appdb_restore /var/backups/postgresql/2026-09-25_1020/appdb.dump
sudo -u postgres psql -d appdb_restore -c "\dt"

Compara el número de tablas y algunos recuentos de filas con la base original. Cuando termines, elimina las bases de prueba:

sudo mysql -e "DROP DATABASE appdb_restore;"
sudo -u postgres dropdb appdb_restore

Repite esta prueba de restauración periódicamente, por ejemplo una vez al mes, y siempre después de cambiar los scripts.

Solución de problemas

ERROR 1045 (28000): Access denied for user 'backup'@'localhost': la contraseña de /etc/mysql/backup.cnf no coincide o el archivo no se está leyendo. Recuerda que --defaults-extra-file debe ser la primera opción del comando. Si hace falta, cambia la contraseña con ALTER USER 'backup'@'localhost' IDENTIFIED BY 'your_strong_password';.

Access denied; you need (at least one of) the PROCESS privilege(s): falta la opción --no-tablespaces en el comando mysqldump.

El servicio queda en estado failed: consulta el motivo con sudo systemctl status mysql-backup.service y sudo journalctl -u mysql-backup.service. Como el script usa set -e, se detiene en el primer error y lo registra.

rsync falla con Host key verification failed: la huella del servidor remoto no está en /root/.ssh/known_hosts. Conéctate una vez a mano con sudo ssh -i /root/.ssh/backup_ed25519 backup@your_backup_server y acéptala.

El disco se llena: reduce RETENTION_DAYS o mueve BACKUP_DIR a un volumen distinto. Comprueba el espacio ocupado con sudo du -sh /var/backups/*.

Conclusión

Tus bases de datos MySQL y PostgreSQL se copian ahora cada noche con timers de systemd, las copias antiguas se eliminan solas, se envían a otro servidor y has comprobado que se pueden restaurar. Como siguientes pasos, puedes cifrar las copias antes de enviarlas fuera del servidor (por ejemplo con age o gpg), configurar una alerta cuando un servicio de copia falle y, para bases de datos grandes, pasar a copias físicas con Percona XtraBackup o pgBackRest.