---
title: "Automatizar copias de seguridad de MySQL y PostgreSQL"
description: "Programa copias diarias de MySQL y PostgreSQL en Ubuntu 24.04 con mysqldump, pg_dump y timers de systemd, con rotación, copia externa y prueba de restauración."
category: "Gestión de Bases de Datos"
language: es
source: https://cubepath.com/es/docs/gestión-de-bases-de-datos/automatizacion-de-respaldos-de-bases-de-datos
updated: 2026-09-25
---

# Automatizar copias de seguridad de MySQL y PostgreSQL

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):

```bash
sudo mysql
```

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

```sql
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`):

```bash
sudo nano /etc/mysql/backup.cnf
```

```ini
[client]
user = backup
password = your_strong_password
```

```bash
sudo chmod 600 /etc/mysql/backup.cnf
```

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

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

```output
+--------------------+
| 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.

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

```bash
#!/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:

```bash
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:

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

```ini
[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:

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

```ini
[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:

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

Revisa el resultado en el journal:

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

```output
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:

```bash
systemctl list-timers mysql-backup.timer
```

```output
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:

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

Crea el script:

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

```bash
#!/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.

```bash
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:

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

```ini
[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:

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

```ini
[Unit]
Description=Copia diaria de PostgreSQL

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

[Install]
WantedBy=timers.target
```

Actívalo y prueba una ejecución:

```bash
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
```

```output
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:

```bash
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:

```bash
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:

```bash
sudo systemctl edit --full mysql-backup.service
```

```ini
[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:

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

```ini
[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`:

```bash
sudo systemctl edit --full pg-backup.service
```

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

Recarga systemd y prueba toda la cadena:

```bash
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:

```bash
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:

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

```output
;
; 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:

```bash
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';"
```

```output
+--------+
| tablas |
+--------+
|     12 |
+--------+
```

Haz lo mismo con PostgreSQL:

```bash
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:

```bash
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.