MySQL, MariaDB y PostgreSQL: usuarios con permisos mínimos, copia lógica programada, restauración probada, retraso de réplica y copias de 0 bytes

Qué vas a conseguir

Las bases de datos de las aplicaciones internas (el CRM en MariaDB, el ERP en PostgreSQL) con cuentas separadas y permisos mínimos, una copia lógica cada noche que falla de forma visible si algo va mal, una restauración probada en otra base de datos con su tiempo apuntado, el retraso de la réplica a la vista y la rutina para el ticket que nadie quiere: la copia lleva una semana generando ficheros de 0 bytes.

Antes de empezar

• MySQL 8.0 u 8.4, o MariaDB 10.11 u 11.4, y PostgreSQL 15 o posterior, en Linux, con sudo en el servidor.
• Un destino para las copias que no sea el disco de datos (/backup, montado desde otra cabina o un NAS) y que luego recoja el sistema de copias (Veeam, cinta).
• Un servidor de pruebas (srv-db-test) con las mismas versiones de los motores para restaurar.
• La guía de PostgreSQL, MySQL y SQL Server cubre las consultas básicas, el espacio y la consulta lenta; esta va de cuentas, copias automáticas y su verificación.

1. Tres cuentas por aplicación

Una aplicación no necesita ser dueña de su base de datos para funcionar. Separa:

En MySQL / MariaDB:

›sudo mysql›CREATE USER 'crm_app'@'10.20.30.%' IDENTIFIED BY '...';›GRANT SELECT, INSERT, UPDATE, DELETE ON crm.* TO 'crm_app'@'10.20.30.%';›CREATE USER 'backup'@'localhost' IDENTIFIED BY '...';›GRANT SELECT, SHOW VIEW, TRIGGER, EVENT, LOCK TABLES, PROCESS, RELOAD ON *.* TO 'backup'@'localhost';›SHOW GRANTS FOR 'crm_app'@'10.20.30.%';+-------------------------------------------------------------------------------+| Grants for crm_app@10.20.30.%                                                 |+-------------------------------------------------------------------------------+| GRANT USAGE ON *.* TO `crm_app`@`10.20.30.%`                                  || GRANT SELECT, INSERT, UPDATE, DELETE ON `crm`.* TO `crm_app`@`10.20.30.%`     |+-------------------------------------------------------------------------------+

• El host forma parte del usuario: crm_app solo entra desde la red de los servidores de aplicación, y backup solo desde localhost.
• PROCESS evita el aviso de tablespaces de mysqldump en MySQL 8; RELOAD lo pide mysqldump con --single-transaction desde MySQL 8.0.32 cuando la base de datos usa GTID.
• Nada de GRANT ALL ni de % como host para la aplicación.

En PostgreSQL, permisos a un rol de grupo y el usuario de la aplicación dentro de él:

›sudo -u postgres psql -d erp›CREATE ROLE erp_rw NOLOGIN;›GRANT CONNECT ON DATABASE erp TO erp_rw;›GRANT USAGE ON SCHEMA erp TO erp_rw;›GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA erp TO erp_rw;›GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA erp TO erp_rw;›ALTER DEFAULT PRIVILEGES FOR ROLE erp_owner IN SCHEMA erp GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO erp_rw;›ALTER DEFAULT PRIVILEGES FOR ROLE erp_owner IN SCHEMA erp GRANT USAGE, SELECT ON SEQUENCES TO erp_rw;›CREATE ROLE erp_app LOGIN PASSWORD '...' IN ROLE erp_rw;›CREATE ROLE backup LOGIN PASSWORD '...';›GRANT pg_read_all_data TO backup;

• ALTER DEFAULT PRIVILEGES FOR ROLE erp_owner: las tablas que cree el propietario en el próximo despliegue llegan ya con permisos para la aplicación. Sin esto, la tabla nueva da permission denied el día del despliegue.
• pg_read_all_data (PostgreSQL 14 y posteriores) da lectura sobre todas las tablas y esquemas: justo lo que necesita pg_dump.
• En pg_hba.conf, cada cuenta desde donde corresponde: hostssl erp erp_app 10.20.30.0/24 scram-sha-256 y host all backup 127.0.0.1/32 scram-sha-256. Recarga con SELECT pg_reload_conf();.

2. Las contraseñas de la copia, en ficheros 0600

El trabajo de copia corre con una cuenta del sistema propia, dbbackup, y lee las contraseñas de ficheros en su carpeta personal. Nunca en el script ni en la línea de comandos (se verían en ps).

›sudo useradd --system --create-home --home-dir /var/lib/dbbackup --shell /usr/sbin/nologin dbbackup›sudo -u dbbackup install -m 600 /dev/null /var/lib/dbbackup/.my.cnf›sudo -u dbbackup install -m 600 /dev/null /var/lib/dbbackup/.pgpass›sudoedit -u dbbackup /var/lib/dbbackup/.my.cnf /var/lib/dbbackup/.pgpass›sudo ls -l /var/lib/dbbackup/.my.cnf /var/lib/dbbackup/.pgpass-rw------- 1 dbbackup dbbackup  42 nov  3 10:12 /var/lib/dbbackup/.my.cnf-rw------- 1 dbbackup dbbackup  38 nov  3 10:12 /var/lib/dbbackup/.pgpass

# .my.cnf[client]user=backuppassword=...# .pgpass (host:puerto:base de datos:usuario:contraseña)localhost:5432:*:backup:...

• Los dos ficheros se buscan en el HOME del usuario que ejecuta el comando. Si el trabajo corre con otra cuenta o sin HOME, no los encuentra.
• .pgpass con permisos para el grupo o para otros se ignora: libpq avisa con has group or world access y sigue sin contraseña.
• MySQL ignora un .my.cnf que cualquiera pueda escribir. En MySQL (no en MariaDB) existe además mysql_config_editor, que guarda las credenciales ofuscadas en ~/.mylogin.cnf.

3. El script y el temporizador

#!/usr/bin/env bashset -euo pipefailumask 077DEST=/backup/dbSTAMP=$(date +%F_%H%M) # MariaDB / MySQL: CRMmysqldump --single-transaction --routines --triggers --events crm | gzip > "$DEST/crm-$STAMP.sql.gz.part"zcat "$DEST/crm-$STAMP.sql.gz.part" | tail -n 1 | grep -q "Dump completed"mv "$DEST/crm-$STAMP.sql.gz.part" "$DEST/crm-$STAMP.sql.gz" # PostgreSQL: ERPpg_dump -h localhost -U backup -Fc -f "$DEST/erp-$STAMP.dump.part" erppg_restore -l "$DEST/erp-$STAMP.dump.part" > /dev/nullmv "$DEST/erp-$STAMP.dump.part" "$DEST/erp-$STAMP.dump" # Retención local: 14 díasfind "$DEST" -type f \( -name '*.sql.gz' -o -name '*.dump' \) -mtime +14 -delete

• set -euo pipefail: si mysqldump falla dentro de la tubería, el script falla. Sin pipefail, el resultado sería el de gzip, que siempre termina bien aunque reciba cero bytes.
• .part y mv: solo tiene nombre definitivo una copia que ha terminado y se ha comprobado. Un .part en la carpeta es una copia rota.
• Las comprobaciones: mysqldump termina el fichero con -- Dump completed on ...; pg_restore -l lee el índice del fichero -Fc y falla si está truncado.
• --single-transaction da una copia coherente sin bloquear las tablas InnoDB. Las tablas MyISAM no entran en esa garantía.
• La base de datos va sin --databases: así el fichero no lleva USE crm y se puede restaurar con otro nombre (paso 4).

Un servicio y un temporizador de systemd:

[Unit]Description=Copia lógica de CRM y ERPOnFailure=notify-email@%n.service [Service]Type=oneshotUser=dbbackupExecStart=/usr/local/sbin/db-backup.sh # --- db-backup.timer ---[Unit]Description=Copia lógica diaria [Timer]OnCalendar=*-*-* 02:30:00Persistent=true [Install]WantedBy=timers.target

›sudo systemctl daemon-reload && sudo systemctl enable --now db-backup.timer›sudo systemctl start db-backup.service && systemctl status db-backup.service --no-pager | head -3○ db-backup.service - Copia lógica de CRM y ERP     Loaded: loaded (/etc/systemd/system/db-backup.service; static)     Active: inactive (dead) since Tue 2026-11-03 10:31:52 CET; 4s ago›ls -lh /backup/db | tail -2-rw------- 1 dbbackup dbbackup 412M nov  3 10:31 crm-2026-11-03_1030.sql.gz-rw------- 1 dbbackup dbbackup 1,9G nov  3 10:31 erp-2026-11-03_1030.dump›systemctl list-timers db-backup.timer

• Con User=dbbackup, systemd pone el HOME de esa cuenta, y con él se encuentran .my.cnf y .pgpass.
• OnFailure= lanza otra unidad cuando la copia falla (aquí una plantilla notify-email@ que envía un correo; vale cualquier aviso que use el equipo). Una copia que falla en silencio es el ticket del paso 6.
• Persistent=true lanza la copia al arrancar si el servidor estaba apagado a las 2:30.

4. La restauración, probada en otra base de datos

Una vez al mes, en srv-db-test, con la copia de anoche y el cronómetro en marcha:

›sudo -u postgres createdb erp_restore›time sudo -u postgres pg_restore -d erp_restore --no-owner --no-privileges -j 4 /restore/erp-2026-11-03_0230.dumpreal    6m41s›sudo -u postgres psql -d erp_restore -tAc "SELECT count(*), max(fecha) FROM erp.facturas;"918244|2026-11-02 23:58:11›sudo mysql -e "CREATE DATABASE crm_restore"›time (zcat /restore/crm-2026-11-03_0230.sql.gz | sudo mysql crm_restore)real    3m12s›sudo mysql -N -e "SELECT COUNT(*), MAX(creado) FROM crm_restore.clientes;"41877   2026-11-02 21:14:03

• Se restaura en una base de datos con otro nombre (erp_restore), nunca encima de la original. Con --no-owner y --no-privileges los objetos quedan a nombre de quien restaura, sin necesitar que existan los roles de producción; -j 4 restaura en paralelo.
• Compara el recuento y la última fecha con producción a la hora de la copia: si la última factura es de hace una semana, la copia es vieja aunque restaure bien.
• Apunta el tiempo (6 minutos y 41 segundos): es tu RTO real para esa base de datos, el que se compara con lo que el negocio espera.
• Borra erp_restore y crm_restore al terminar y registra la prueba: fecha, fichero, tiempo, recuentos y quién la hizo.

5. El retraso de la réplica, en un vistazo

Si hay una réplica (para lectura o para conmutar en caso de fallo), su retraso es lo primero que se mira antes de hacer copias desde ella o de conmutar.

›sudo mysql -e "SHOW REPLICA STATUS\G" | grep -E "Replica_IO_Running|Replica_SQL_Running:|Seconds_Behind_Source|Last_SQL_Error:"             Replica_IO_Running: Yes            Replica_SQL_Running: Yes          Seconds_Behind_Source: 0                 Last_SQL_Error:

En MariaDB los campos siguen llamándose Slave_IO_Running, Slave_SQL_Running y Seconds_Behind_Master (también con SHOW REPLICA STATUS\G, que existe desde la 10.5; antes, SHOW SLAVE STATUS\G), así que ajusta el grep. Los dos hilos tienen que estar en Yes; uno en No con Last_SQL_Error relleno es una réplica parada, aunque el retraso diga NULL y no un número alto.

›sudo -u postgres psql -c "SELECT application_name, client_addr, state, replay_lag FROM pg_stat_replication;" application_name | client_addr  |   state   |   replay_lag------------------+--------------+-----------+----------------- srv-db-02b       | 10.20.40.12  | streaming | 00:00:00.004112# en la réplica:›sudo -u postgres psql -tAc "SELECT now() - pg_last_xact_replay_timestamp();"00:00:01.83

Una fila vacía en pg_stat_replication es que la réplica no está conectada. En la réplica, el segundo valor crece aunque todo vaya bien si el primario no tiene escrituras: compáralo con la actividad real antes de alarmarte.

6. El ticket: "la copia de la base de datos lleva una semana dejando ficheros de 0 bytes"

Martes, 9:30. Al preparar la restauración mensual, el fichero de anoche pesa 0 bytes. Y el de la noche anterior. El servidor srv-db-03 aún usa una línea de cron antigua, no el script del paso 3:

30 2 * * * dbbackup pg_dump -h localhost -U backup -Fc erp > /backup/db/erp-$(date +\%F).dump 2>/dev/null

Así se explica el tamaño: la redirección > la hace el shell y crea el fichero antes de que pg_dump arranque. Si pg_dump falla, queda un fichero vacío, y 2>/dev/null tira el mensaje de error. (El \% es obligatorio: en un crontab, % sin escapar es un salto de línea.)

1. Desde cuándo: ls -l --time-style=long-iso /backup/db | tail muestra 0 bytes desde el 27 de octubre. ¿Qué cambió ese día? El registro de cambios dice que se aplicó un rol de Ansible que reescribió las carpetas personales de las cuentas de servicio.
2. Ejecutar lo mismo, como el mismo usuario y con el error a la vista:

›sudo -u dbbackup -H pg_dump -h localhost -U backup -Fc erp -f /tmp/prueba.dumpWARNING: password file "/var/lib/dbbackup/.pgpass" has group or world access; permissions should be u=rw (0600) or lesspg_dump: error: connection to server at "localhost" (127.0.0.1), port 5432 failed: fe_sendauth: no password supplied›sudo ls -l /var/lib/dbbackup/.pgpass-rw-r--r-- 1 dbbackup dbbackup 38 oct 27 11:02 /var/lib/dbbackup/.pgpass

1. La causa: el rol de Ansible dejó .pgpass en 0644. libpq lo ignora por seguridad, pg_dump se queda sin contraseña y, sin terminal, no puede pedirla. Solución: sudo chmod 600 /var/lib/dbbackup/.pgpass y corregir el rol de Ansible para que use mode: "0600".
2. Descarta lo demás antes de cerrar, porque los mismos 0 bytes salen de otras causas:

• Disco lleno: df -h /backup y df -i /backup. Con el destino lleno, el fichero se crea y no se escribe.
• Permisos del destino: sudo -u dbbackup touch /backup/db/prueba. Si falla, el cron ni siquiera crearía el fichero.
• En MySQL, el equivalente es mysqldump: Got error: 1045: Access denied for user 'backup'@'localhost' (using password: NO): no ha leído .my.cnf (otro HOME u otro usuario), o lo ha descartado con World-writable config file ... is ignored.

1. Copia ya, sin esperar a esta noche: el comando del punto 2 con el fichero en /backup/db/, comprobado con pg_restore -l, y una restauración rápida en srv-db-test como en el paso 4.
2. Que no vuelva a pasar en silencio: sustituir la línea de cron por el script y el temporizador del paso 3 (falla de verdad, deja .part y avisa con OnFailure) y añadir a la monitorización una comprobación del tamaño y la fecha del último fichero de /backup/db.

En el ticket: desde qué fecha no hay copia válida (una semana sin copia lógica del ERP: avisa a su responsable), la causa, el cambio, la copia nueva con su prueba de restauración y la mejora para detectarlo.

Siguientes pasos

• La guía de PostgreSQL, MySQL y SQL Server cubre la consulta que bloquea, el espacio y el crecimiento.
• La de Veeam explica cómo el sistema de copias recoge /backup y lo guarda fuera del servidor.
• Con bases de datos grandes o un RPO de minutos, la copia lógica se queda corta: copias físicas con archivado de WAL (pgBackRest) o binlogs (MariaDB Backup, Percona XtraBackup).

¿Falta algo o en tu entorno es distinto? Escríbeme y lo corrijo.