--- title: "Copias de seguridad de MySQL y PostgreSQL en Ubuntu 24.04" description: "Haz copias consistentes de MySQL con mysqldump y de PostgreSQL con pg_dump en Ubuntu 24.04, automatízalas con un timer de systemd y comprueba que se restauran." category: "Recuperación de copias" language: es source: https://cubepath.com/es/docs/recuperación-de-copias/respaldo-de-base-de-datos-mysql-postgresql updated: 2026-09-25 --- # Copias de seguridad de MySQL y PostgreSQL en Ubuntu 24.04 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ógica | Copia física | |---|---|---| | Qué guarda | Sentencias SQL o un archivo con los datos | Los archivos de datos del servidor | | Herramientas | `mysqldump`, `pg_dump` | Percona XtraBackup, `pg_basebackup` | | Restauración | Más lenta, se reconstruyen tablas e índices | Rápida | | Portabilidad | Entre versiones y servidores | Misma versión principal | | Adecuada para | Bases de datos de hasta decenas de GB | Bases 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: ```bash 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: ```bash 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: ```bash 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: ```bash gzip --test /var/backups/db/app_db.sql.gz && zcat /var/backups/db/app_db.sql.gz | tail -n 1 ``` ```output -- 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. > Nota: los usuarios y permisos de MySQL se guardan en la base de datos del sistema `mysql`, que esta guía no copia porque restaurarla entre versiones da problemas. Guarda aparte las sentencias para recrear los usuarios de tu aplicación, por ejemplo con `SHOW GRANTS FOR 'app_user'@'localhost';`. ## 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: ```bash 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`: ```bash 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: ```bash pg_restore --list /var/backups/db/app_db.dump | head -n 8 ``` ```output ; ; 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: ```bash sudo nano /usr/local/sbin/db-backup.sh ``` ```bash #!/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: ```bash sudo chmod 700 /usr/local/sbin/db-backup.sh sudo /usr/local/sbin/db-backup.sh ``` ```output 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: ```bash 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: ```bash sudo nano /etc/systemd/system/db-backup.service ``` ```ini [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: ```bash sudo nano /etc/systemd/system/db-backup.timer ``` ```ini [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: ```bash 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: ```bash 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: ```bash 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: ```bash 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): ```bash mysql -N -e 'SELECT COUNT(*) FROM app_db.users; SELECT COUNT(*) FROM app_db_restore.users;' ``` ```output 1523 1523 ``` Cuando termines, borra la base de datos de prueba: ```bash 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: ```bash 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: ```bash 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: ```bash 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: ```bash 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.