Script per a backup diari de MySQL a Linux

No hi ha res especialment glamurós en els backups de bases de dades, però són un d’aquests elements que, quan fallen i el necessites, deixen una empremta duradora. A hores d’ara, tots hem tingut aquesta conversa incòmoda quan es trenca alguna cosa i l’últim backup útil té diversos dies. I encara que sembli que el backup d’una base MySQL és cosa resolta, a la pràctica continuen apareixent els mateixos errors: dumps corruptes, cron jobs que no notifiquen en fallar, backups buits, compressions truncades, permisos mal assignats, i la llista segueix.

1. El més bàsic funciona, fins que deixa de fer-ho

La majoria comença amb alguna cosa com això:

mysqldump -u root -p'password' nombre_bd > backup.sql

I només amb això creuen que tenen un backup. El problema és que aquest comandament funciona fins que no. No hi ha rotació, no hi ha compressió, el password en text pla es queda en l’historial del shell, i ningú s’assabenta si un dia deixa de funcionar.

La solució òbvia és embolicar-lo en un script, programar-lo per cron, i enviar-se un correu en cas de fallada. Tot i així, el que sembla trivial comença a mostrar matisos: encoding trencat, dúmps incomplets, credencials mal protegides, mides que creixen sense control, etc.

2. Script funcional

Aquest és un exemple de script que he acabat fent servir amb mínimes modificacions durant anys. No és l’únic enfocament vàlid, però ha demostrat ser predictible i mantenible. L’acompanyo amb explicacions que solen passar-se per alt quan un copia un script de Stack Overflow.

#!/bin/bash

set -euo pipefail

# Configuració
BACKUP_DIR="/var/backups/mysql"
MYSQL_USER="backup_user"
MYSQL_PASSWORD="********"
MYSQL_HOST="localhost"
DATE=$(date +%F_%H-%M)
RETENTION_DAYS=7
LOG_FILE="/var/log/mysql_backup.log"
DATABASES=$(mysql -u $MYSQL_USER -p"$MYSQL_PASSWORD" -h "$MYSQL_HOST" -e "SHOW DATABASES;" | grep -Ev "(Database|information_schema|performance_schema|mysql|sys)")

mkdir -p "$BACKUP_DIR"

for DB in $DATABASES; do
    FILE="${BACKUP_DIR}/${DB}_${DATE}.sql.gz"
    echo "$(date +%F_%T) - Backing up $DB to $FILE" >> "$LOG_FILE"

    mysqldump --single-transaction --routines --triggers --events \
        -u "$MYSQL_USER" -p"$MYSQL_PASSWORD" -h "$MYSQL_HOST" "$DB" \

        | gzip > "$FILE"

    if [ $? -ne 0 ]; then
        echo "$(date +%F_%T) - ERROR backing up $DB" >> "$LOG_FILE"
        # Aquí pots afegir notificació per correu o alertes
    fi
done

# Neteja de backups antics
find "$BACKUP_DIR" -type f -name "*.sql.gz" -mtime +$RETENTION_DAYS -delete

echo "$(date +%F_%T) - Backup completo" >> "$LOG_FILE"

3. Eleccions que no convé fer per inèrcia

No fer servir –single-transaction en bases amb activitat

Sense aquesta opció, mysqldump fa un lock complet de les taules InnoDB. Si hi ha connexions concurrents, pots acabar amb un dúmp inconsistent o bloquejar l’aplicació sense adonar-te’n. L’opció –single-transaction és segura per a InnoDB, però no funciona en taules MyISAM. Avui dia no hi ha gaires raons per continuar fent servir MyISAM, però si ho fas, això és un punt crític.

Incloure –routins, –events i –triggers sempre

Molts scripts els ometen per omissió o desconeixement. Després restauren en un altre entorn i es pregunten per què alguna cosa va deixar d’executar-se. Si tens procediments emmagatzemats, esdeveniments o disparadors, assegura’t d’incloure’ls.

No deixar .sql.gz sense comprovar-ne la mida

Un arxiu que pesa 0 bytes però té el timestamp correcte és una trampa clàssica. Un gzip que falla silenciosament, un mysqldump que no genera cap sortida. Pots afegir validació posterior (mida mínima, xequeig de gzip vàlid) si vols assegurar que no estàs guardant fum.

4. Seguretat: credencials, permisos i errors comuns

Una de les fallades més comunes que veig és deixar l’script accessible per altres usuaris del sistema o incloure la contrasenya en cru sense control. Algunes pràctiques útils:

1. Crear un usuari de MySQL dedicat al backup, amb permisos de només lectura (SELECT, SHOW VIEW, LOCK TABLES, etc.) sobre les bases necessàries.
2. Guardar credencials en un arxiu .my.cnf en /root amb permisos 600, i fer servir –defaults-extra-file perquè mysqldump el llegeixi.
3. Evitar escriure passwords directament en scripts o passar-los per línia de comandaments. El comandament queda registrat en l’ historial o accessible via pg.

Exemple de .my.cnf segur:

[client]
user=backup_user
password=supersecreta
host=localhost

I en el script:

mysqldump --defaults-extra-file=/root/.my.cnf ...

Important: No facis servir aquest arxiu com a .my.cnf de l’usuari backup_user si el backup es llença des de cron com a root. Assegura’t que l’arxiu estigui on el procés l’espera.

5. Logs que serveixen

Omplir /var/log de línies inútils és tan dolent com no tenir logs. Registra l’inici, la fi, els errors i el que pot fallar. Un detall útil és incloure la mida final del dump o fer servir gzip -t al final de l’script per validar que l’arxiu és correcte.

Exemple:

if gzip -t "$FILE"; then
    SIZE=$(du -h "$FILE" | cut -f1)
    echo "$(date +%F_%T) - Backup de $DB completat ($SIZE)" >> "$LOG_FILE"
else
    echo "$(date +%F_%T) - ERROR: $FILE corrupte" >> "$LOG_FILE"
    # Opció: esborrar arxiu i enviar alerta
fi

6. Què fer quan el backup comença a pesar més del que hauria

Els dumps no s’han d’inflar sense motiu. Si una base de 800 MB passa a ocupar 6 GB en una setmana, alguna cosa està malament.

Possibles causes:

1. Taules amb blobs o dades binaris mal gestionades (logs en una taula, arxius pujats a la base, etc.).
2. Purga automàtica que deixa d’executar-se.
3. Truncate de taules grans que després es tornen a omplir (cas comú en processos ETL diaris).

Revisar la mida per taula:

SELECT table_schema, table_name, round(data_length/1024/1024) AS data_mb
FROM information_schema.tables
ORDER BY data_mb DESC
LIMIT 10;

O mirar quin arxiu creix més en els backups comprimits:

zgrep -a 'INSERT INTO' nom_dump.sql.gz | cut -d'`' -f2 | sort | uniq -c | sort -nr | head

No és exacte, però dona una idea ràpida.

7. Cron no és infal·lible: assegura’t que t’avisi

Configurar el cron sense redireccionar stdout i stderr a un log o a un mail és el camí més curt a l’oblit. Si fas servir cron sense MTA configurat, de les fallades no se n’assabentarà ningú.

Un enfocament pràctic és que l’script escrigui en un log sempre, i que un altre servei (com logrotate, monit, systemd, cronic, etc.) supervisi aquest log o aquest script.

Exemple amb cronic:

0 2 * * * cronic /usr/local/bin/mysql_backup.sh

cronic només notifica si hi ha errors (està en moreutils), la qual cosa redueix soroll i ajuda a prestar atenció quan toca.

8. Restaurar: provar-ho almenys una vegada abans que sigui massa tard

Fer backups sense provar la restauració és el mateix que no fer-los. No cal aixecar un servidor complet cada setmana, però sí que val la pena tenir un script que aixequi un contenidor o un entorn temporal i carregui el dúmp automàticament.

Per exemple, per a tests locals:

docker run --rm -v $PWD:/backup -e MYSQL_ROOT_PASSWORD=pass -d --name mysql-test mysql:5.7
sleep 10
docker exec -i mysql-test mysql -uroot -ppass < /backup/backup.sql

No és producció, però almenys sabràs si l’arxiu està sencer i es pot importar.

9. Rotació i neteja: més enllà del find -mtime

El típic find … -mtime +N -delet fa la seva feina, però té limitacions. Si fas backups múltiples al dia o necessites conservar-ne un d’específic (per exemple, el primer de cada mes), no et serveix. Aquí és on una política de retenció més afinada cobra sentit.

Una alternativa pràctica: combinar noms d’ arxiu amb data i incloure lògica condicional a l’ script.

Exemple: conservar els primers backups de cada mes per 6 mesos, i els diaris per 7 dies.

# Aquest s'executa diari, al final de l'script de backup
find "$BACKUP_DIR" -type f -name "*.sql.gz" -mtime +7 -not -name "*-01_*.sql.gz" -delete

# Després un altre script setmanal o mensual podria fer neteja més fina:
find "$BACKUP_DIR" -type f -name "*-01_*.sql.gz" -mtime +180 -delete

És simple i no requereix eines externes, però dona més control que simplement esborrar tot per antiguitat. Assegura’t que els noms d’arxiu incloguin la data en un format predictible (per això fes servir F_%H-%M).

10. Backups paral·lels: ¿val la pena?

Quan el nombre de bases creix, el temps del backup total comença a importar. Fer el backup de cada base en sèrie pot trigar molt, tot i que moltes vegades el coll d’ampolla és el disc, no la CPU. Executar backups en paral·lel pot ajudar, però no sempre és bona idea.

Un enfocament raonable és limitar la concurrència:

MAX_JOBS=3
for DB in $DATABASES; do
    (
        FILE="${BACKUP_DIR}/${DB}_${DATE}.sql.gz"
        echo "$(date +%F_%T) - Backing up $DB" >> "$LOG_FILE"
        mysqldump --single-transaction --routines --triggers --events \
            -u "$MYSQL_USER" -p"$MYSQL_PASSWORD" -h "$MYSQL_HOST" "$DB" \
            | gzip > "$FILE"

        gzip -t "$FILE" || echo "$(date +%F_%T) - ERROR en $FILE" >> "$LOG_FILE"
    ) &

    # Límite de concurrencia
    while [ "$(jobs -r | wc -l)" -ge "$MAX_JOBS" ]; do
        sleep 1
    done
done

wait

Aquesta tècnica és suficient per a reduir els temps en entorns mitjans, sense necessitat d’ eines addicionals. Però compte: si el teu servidor està justet d’I/O, fer això pot afectar el rendiment de MySQL en producció.

11. Alternatives a mysqldump que no tothom considera

En bases de més de 20–30 GB, mysqldump es torna una opció cada vegada més limitada. És lineal, lent i produeix un sol arxiu monolític. Algunes alternatives reals que val la pena avaluar:

mydumper / myloader

Especialment útil en bases grans. És més ràpid que mysqldump i permet backups paral·lels.

mydumper -u backup_user -p pass --outputdir /var/backups/mysql_dumps --threads 4

La restauració també és més ràpida:

myloader -u backup_user -p pass --directory /var/backups/mysql_dumps --threads 4

Una limitació: no gestiona bé dades que canvien ràpid entre taules relacionades, perquè no hi ha consistència transaccional creuada.

xtrabackup (de Percona)

Ideal per a backups físics en calent, especialment en entorns amb gran volum de dades. Requereix més preparació, però permet restaurar directament els arxius de dades sense reimportar. És el que es fa servir en producció quan hi ha SLAs estrictes sobre RTO/RPO.

El seu ús dona per a un post a part, però val la pena esmentar-lo com a alternativa de pes.

12. Detalls menors que eviten problemes reals

1. Evitar noms d’arxiu amb espais o caràcters estranys: No és per estètica, sinó perquè els scripts de restauració posteriors, rsync, o scp sovint fallen en situacions límit. Stick a guions i subratllats.
2. Evitar –password= sense cometes: Especialment si el password té caràcters especials ($, !, &). L’ script pot fallar de forma subtil. Fes servir sempre -p”$MYSQL_PASSWORD”.
3. Evita muntar backups en carpetes compartides de xarxa sense proves d’escriptura: Hi ha casos on un NFS o un muntatge SMB sembla escriure bé, però el gzip acaba truncat. Ho he vist en servidors virtuals amb backups a NAS. Sempre valida amb gzip -t.
4. No depenguis de crontab -e sense versionat: Fes servir un arxiu de cron en /etc/cron.d/ amb comentaris clars i guàrda’l a Git. Evita sorpreses després d’un canvi d’equip.
5. Fes que l’script sigui idempotent: Si falla a meitat de camí, que no deixi arxius corruptes o parcials. Si el tornes a executar, que no reescrigui res sense validació. I si fas servir systemd, assegura’t de declarar que no hi pot haver instàncies múltiples simultànies.

13. On guardar els backups?

Tema sensible. Guardar-los només en el mateix disc del servidor MySQL és el camí al desastre. Algunes pràctiques raonables:

1. Disc secundari muntat en només lectura fora del cron: per evitar que una fallada de script ho esborri tot accidentalment.
2. Sync programat a S3, Backblaze, Wasabi o similar amb rclone.
3. Pull backup des d’un altre host: evita exposar el servidor de producció a fallades de scripts remots.

Exemple de sync amb rclone:

rclone sync /var/backups/mysql remote:backups/mysql --backup-dir remote:backups/old --suffix ".$(data +%s)"

Això conserva versions anteriors sense sobreescriure, sense haver d’escriure gaire lògica addicional.

Script final

Aquí va el resultat final, amb els canvis i les recomanacions:

#!/bin/bash

set -euo pipefail

# =======================
# CONFIGURACIÓ
# =======================

# Ruta on s' emmagatzemen els backups
BACKUP_DIR="/var/backups/mysql"

# Arxiu de credencials de MySQL
CREDENTIALS_FILE="/root/.my.cnf"

# Data per nomenar arxius
DATE=$(date +%F_%H-%M)

# Nombre màxim de dies per a conservar backups diaris
RETENTION_DAYS=7

# Nombre de backups mensuals a conservar
RETENTION_MONTHLY_DAYS=180

# Màxim de tasques paral·leles
MAX_JOBS=3

# Log del procés
LOG_FILE="/var/log/mysql_backup.log"

# Excloure bases de dades del sistema
EXCLUDED_DBS="Database|information_schema|performance_schema|mysql|sys"

# Comprovació mínima de mida (bytes) per a considerar un dúmp com a vàlid
MIN_DUMP_SIZE=1024

# =======================
# INICI
# =======================

echo "$(date +%F_%T) - Inici de backup MySQL" >> "$LOG_FILE"

mkdir -p "$BACKUP_DIR"

# Obtenir llistat de bases de dades (excloent les del sistema)
DATABASES=$(mysql --defaults-extra-file="$CREDENTIALS_FILE" -e "SHOW DATABASES;" | grep -Ev "$EXCLUDED_DBS")

# Funció per a backup d' una base de dades
backup_database() {
    DB="$1"
    FILE="${BACKUP_DIR}/${DB}_${DATE}.sql.gz"

    echo "$(date +%F_%T) - Iniciant backup de $DB a $FILE" >> "$LOG_FILE"

    mysqldump --defaults-extra-file="$CREDENTIALS_FILE" \
        --single-transaction --routines --triggers --events \
        "$DB" | gzip > "$FILE"

    # Validar gzip i mida mínima
    if gzip -t "$FILE" 2>/dev/null && [ "$(stat -c%s "$FILE")" -gt "$MIN_DUMP_SIZE" ]; then
        SIZE=$(du -h "$FILE" | cut -f1)
        echo "$(date +%F_%T) - Backup correcte: $DB ($SIZE)" >> "$LOG_FILE"
    else
        echo "$(date +%F_%T) - ERROR: Backup erroni o corrupte per $DB" >> "$LOG_FILE"
        rm -f "$FILE"
        # Aquí pots inserir una alerta o correu
    fi
}

# Executar backups en paral·lel limitada
for DB in $DATABASES; do
    backup_database "$DB" &

    # Controlar nombre màxim de processos concurrents
    while [ "$(jobs -r | wc -l)" -ge "$MAX_JOBS" ]; do
        sleep 1
    done
done

wait

# =======================
# ROTACIÓ DE BACKUPS
# =======================

# Eliminar backups diaris que no són del dia 01 després de X dies
find "$BACKUP_DIR" -type f -name "*.sql.gz" \
    ! -name "*-01_*.sql.gz" -mtime +$RETENTION_DAYS -delete

# Eliminar backups mensuals antics (només del dia 01)
find "$BACKUP_DIR" -type f -name "*-01_*.sql.gz" -mtime +$RETENTION_MONTHLY_DAYS -delete

# =======================
# FI
# =======================

echo "$(date +%F_%T) - Backup complet" >> "$LOG_FILE"

Hi ha mil formes de fer backups de MySQL, però molt poques que resisteixin anys sense un manteniment constant. Aquest enfocament, amb els seus scripts, els seus controls mínims, i la seva atenció a detalls que només es noten quan fallen, ha funcionat en entorns reals. No és infal·lible, però si hi ha fallades, almenys sabràs per on mirar primer.