MySQL backup MySQL Backup VPS Linux DevOps

Backup incrementale di MySQL con xtrabackup: recovery point granulare senza blocchi

Backup incrementale di MySQL con xtrabackup: recovery point granulare senza blocchi

A gennaio 2026 mi sono occupato della ristrutturazione della strategia di backup del database di un'azienda del settore servizi di elaborazione buste paga - 12 dipendenti interni, circa 600 clienti PMI in portafoglio, database MySQL 8.0 di 240 GB con 2,3 miliardi di righe fra dati anagrafici, cedolini storici degli ultimi 10 anni e scritture contabili. Il sistema di backup in atto era quello che trovo nel 60% delle PMI italiane: uno script bash con mysqldump lanciato ogni notte alle 02:00 che scriveva un dump compresso su un Storage Box Hetzner. Il problema si manifestava in tre modi. Primo: il dump durava 4 ore e 20 minuti - iniziava alle 02:00 e finiva alle 06:20, spesso sovrapponendosi all'inizio dell'orario lavorativo di alcuni clienti sulla costa est italiana che iniziavano a fare richieste al sistema alle 06:00. Secondo: durante il dump mysqldump acquisisce lock consistenti che rallentavano fino a bloccare completamente le query in scrittura - gli operatori interni che provavano a elaborare cedolini la notte (pattern occasionale per deadline) trovavano il sistema praticamente inutilizzabile. Terzo: l'RPO (Recovery Point Objective) effettivo era 24 ore - se il database si corrompeva alle 10 del mattino, nel peggior caso perdevano tutte le modifiche delle 8 ore precedenti (dall'ultimo backup riuscito), che per un sistema di elaborazione buste paga può significare centinaia di cedolini da rifare a mano ricostruendo input dai file sorgente.

In cinque settimane ho ristrutturato completamente la pipeline di backup basandola su Percona XtraBackup, il tool open source di backup fisico per MySQL/MariaDB documentato ufficialmente da Percona su docs.percona.com, con una strategia di full backup settimanale + incrementali orari, recovery point testato automaticamente ogni settimana su un server di staging dedicato, e archiviazione off-site crittografata su Hetzner Storage Box Zurich (per resilienza geografica). Al termine del lavoro, il tempo di acquisizione del backup completo settimanale è sceso da 4 ore e 20 minuti a 35 minuti (di cui zero secondi di lock effettivi - mysqldump blocca, XtraBackup no), i backup incrementali orari impiegano 1-3 minuti ciascuno in funzione del volume di scritture dell'ora, l'RPO effettivo è passato da 24 ore a 1 ora, il tempo di ripristino completo da zero (Recovery Time Objective) è passato da 6-8 ore (restore di un dump mysqldump su un nuovo server è lento per natura) a 45 minuti (il restore fisico di XtraBackup è operazione di copy-on-disk, non rigenerazione dati). Questo articolo descrive la pipeline esatta, le scelte tecniche dietro ogni parametro, e la disciplina operativa che trasforma XtraBackup da "script che gira in produzione e speriamo funzioni" a "sistema di disaster recovery testabile e affidabile".

Perché mysqldump è inadeguato per database MySQL di produzione sopra i 10 GB

Il pattern di backup via mysqldump è onnipresente perché è built-in, non richiede installazione aggiuntiva e produce file SQL leggibili. Per database piccoli (sotto i 5-10 GB) è una scelta ragionevole. Sopra quella soglia, accumula tre problemi che diventano insopportabili.

Primo: il blocco delle tabelle. mysqldump --single-transaction fa backup coerenti senza acquisire LOCK TABLES sulle tabelle InnoDB, ma durante l'esecuzione della transazione lunga il motore InnoDB accumula undo log (le versioni precedenti delle righe modificate) per garantire isolation. Su un database con scritture attive, l'undo log può crescere a volumi enormi, saturare il buffer pool, e rallentare significativamente tutte le operazioni concorrenti. Su tabelle MyISAM (ancora presenti in progetti legacy), mysqldump acquisisce comunque lock esclusivi. Il dump lungo 4 ore significa 4 ore di degradazione delle performance.

Secondo: il tempo lineare nel contenuto. mysqldump rigenera l'intero dataset come INSERT SQL, quindi il tempo di dump scala con il volume totale dei dati, indipendentemente da quanto il dataset sia cambiato dall'ultimo backup. Un database di 200 GB richiede 4 ore ogni notte anche se negli ultimi 24 ore sono cambiate 500 MB di dati.

Terzo: il tempo di restore ancora più lineare. Ripristinare un dump mysqldump di 200 GB richiede di eseguire milioni di statement SQL INSERT, rebuild degli indici, check dei foreign key. Su hardware moderno, il restore impiega 1.5-3x il tempo del dump - quindi 6-12 ore per 200 GB. Durante un disaster recovery reale, quelle ore sono ore di downtime del business.

La documentazione ufficiale di Percona XtraBackup sul confronto con mysqldump dettaglia questi limiti e li affronta con un approccio architetturale diverso: XtraBackup copia i file fisici del database al livello del filesystem (i tablespace InnoDB, il log di redo, i file di schema), senza passare attraverso l'SQL layer. Questo elimina il blocco (perché la copia dei file è non-locking su InnoDB), rende il backup veloce quanto cp -r con crc, e rende il restore altrettanto veloce perché è una semplice copia dei file.

L'architettura: full settimanale + incrementali orari + log binari continui

La strategia che applico è quella consigliata dalla documentazione Percona per database di produzione: full backup settimanali + incrementali quotidiani o orari + binary log backup continuo per point-in-time recovery al secondo.

Il ragionamento dietro questa struttura è di bilanciare tre variabili. Un full backup settimanale limita il tempo di restore a 1 settimana nel peggior caso (devi riapplicare al massimo 7 giorni di incrementali). Gli incrementali orari riducono l'RPO a 1 ora per scenari senza binlog disponibile, e a pochi secondi quando i binlog ci sono. I binlog continui gestiti da MySQL stesso permettono di fare PITR al secondo specifico in caso di necessità (errore umano: "abbiamo cancellato la tabella ordini alle 14:32, recupera allo stato delle 14:31:58").

Il layout dei file sul Storage Box dedicato è questo:

/backup/mysql/
├── full/
│   ├── 2026-01-06_022030/          <- full backup del lunedi 6 gennaio
│   │   ├── ibdata1
│   │   ├── cliente_db/             <- file tablespace per schema
│   │   ├── xtrabackup_binlog_info  <- posizione binlog al momento del full
│   │   └── xtrabackup_checkpoints  <- LSN di riferimento per incrementali
│   ├── 2026-01-13_022041/
│   └── ...
├── incremental/
│   ├── 2026-01-06_032011/          <- primo incrementale ore 03
│   ├── 2026-01-06_042015/          <- secondo incrementale ore 04
│   └── ...
└── binlog/
    ├── mysql-bin.000001
    ├── mysql-bin.000002
    └── ...

La logica di ogni cartella è importante. I full backup si scrivono nella cartella full/ con timestamp completo - ognuno è autosufficiente, può ripristinare il database dallo stato di quel momento senza dipendere da altri file. Gli incrementali si scrivono in incremental/ e dipendono dal full del lunedì precedente - ogni incrementale contiene solo i blocchi modificati dal backup precedente (full o incrementale che sia), quindi i loro dimensioni sono proporzionali al cambio, non al dataset totale. I binlog vengono copiati in continuo da un processo separato che gira in tail dei binlog MySQL - il binlog di oggi è in copia su storage remoto entro secondi dalla scrittura.

Il setup operativo: XtraBackup, crontab, verifica

L'installazione di XtraBackup 8.0 su Debian 12 è semplice:

# Aggiunta del repo Percona e installazione
wget https://repo.percona.com/apt/percona-release_latest.generic_all.deb
dpkg -i percona-release_latest.generic_all.deb
percona-release enable-only tools release
apt update
apt install percona-xtrabackup-80

# Verifica versione
xtrabackup --version

Creazione di un utente MySQL dedicato con permessi minimi per XtraBackup:

CREATE USER 'xtrabackup'@'localhost' IDENTIFIED BY 'STRONG_PASSWORD';
GRANT BACKUP_ADMIN, PROCESS, RELOAD, LOCK TABLES, REPLICATION CLIENT ON *.* TO 'xtrabackup'@'localhost';
GRANT SELECT ON performance_schema.log_status TO 'xtrabackup'@'localhost';

Il principio del minimo privilegio è importante: questo utente non ha bisogno di tutti i permessi admin, ha bisogno esattamente di quelli richiesti da XtraBackup documentati da Percona. Un'eventuale compromissione del file di config che contiene la password limita il danno a operazioni di backup, non a escalation generale.

Lo script di backup full che gira ogni lunedì alle 02:00:

#!/usr/bin/env bash
# /opt/backups/xtrabackup-full.sh
set -euo pipefail

BACKUP_ROOT="/mnt/storage-box/backup/mysql"
DATE=$(date +%Y-%m-%d_%H%M%S)
FULL_DIR="${BACKUP_ROOT}/full/${DATE}"
LOG_FILE="/var/log/xtrabackup/full-${DATE}.log"

mkdir -p "$FULL_DIR" "$(dirname "$LOG_FILE")"

# Esecuzione del full backup
xtrabackup --backup \
    --user=xtrabackup \
    --password="${MYSQL_XTRABACKUP_PWD}" \
    --target-dir="$FULL_DIR" \
    --compress \
    --compress-threads=4 \
    --parallel=4 \
    2>> "$LOG_FILE"

# Prepare il backup per essere restore-ready
xtrabackup --prepare \
    --target-dir="$FULL_DIR" \
    --decompress \
    --parallel=4 \
    --remove-original \
    2>> "$LOG_FILE"

# Cleanup: rimuovi full backup piu vecchi di 4 settimane
find "${BACKUP_ROOT}/full" -maxdepth 1 -type d -mtime +28 -exec rm -rf {} \;

# Alert via Telegram se qualcosa e andato male
if [ $? -ne 0 ]; then
    curl -sf "https://api.telegram.org/bot${BOT_TOKEN}/sendMessage" \
        -d "chat_id=${CHAT_ID}" \
        -d "text=ALERT: full backup MySQL fallito su $(hostname)"
    exit 1
fi

Il dettaglio di --compress con 4 thread merita una nota: riduce la dimensione del backup finale di circa 3-4x, a costo di CPU overhead sul server durante il backup. Su un server con 8+ core, questo overhead è assorbibile senza impatto sulla produzione. La --prepare applica i log redo accumulati durante il backup (quelli che coprono le modifiche al database durante il dump), rendendo il backup consistente e pronto per il restore.

Lo script di backup incrementale orario è simile ma referenzia il backup precedente:

#!/usr/bin/env bash
# /opt/backups/xtrabackup-incremental.sh
set -euo pipefail

BACKUP_ROOT="/mnt/storage-box/backup/mysql"
DATE=$(date +%Y-%m-%d_%H%M%S)
INCR_DIR="${BACKUP_ROOT}/incremental/${DATE}"
LOG_FILE="/var/log/xtrabackup/incr-${DATE}.log"

# Trova l'ultimo backup (full o incrementale) come base
LAST=$(find "${BACKUP_ROOT}/full" "${BACKUP_ROOT}/incremental" -maxdepth 1 -type d -printf "%T@ %p\n" 2>/dev/null | sort -rn | head -1 | cut -d' ' -f2)

if [ -z "$LAST" ]; then
    echo "Nessun full backup di riferimento trovato!"
    exit 1
fi

mkdir -p "$INCR_DIR"

xtrabackup --backup \
    --user=xtrabackup \
    --password="${MYSQL_XTRABACKUP_PWD}" \
    --target-dir="$INCR_DIR" \
    --incremental-basedir="$LAST" \
    --compress \
    --parallel=2 \
    2>> "$LOG_FILE"

L'incrementale dipende dall'ultimo backup (full o incremental), quindi il backup orario delle 04:00 dipende da quello delle 03:00, che dipende da quello delle 02:00, che dipende dal full del lunedì. In caso di restore, si deve ri-applicare la catena completa - questo è gestito automaticamente dallo script di restore che vedremo dopo.

Stai cercando un Consulente Informatico esperto per ristrutturare la strategia di backup MySQL della tua PMI con RPO/RTO misurabili e restore test automatizzati, senza i limiti di mysqldump su database di produzione? Nel mio profilo professionale trovi l'esperienza concreta su Percona XtraBackup, disaster recovery, pipeline di backup per VPS Hetzner, OVH, Contabo, Digital Ocean.

Binary log backup: la copia continua per PITR al secondo

I backup XtraBackup orari gestiscono il caso "restore a uno dei ultimi 24 backup point". Per scenari più granulari - "ripristina esattamente alle 14:31:58 di ieri, prima della DELETE sbagliata" - servono i binary log di MySQL. Il binary log è il journal che MySQL mantiene di ogni operazione di scrittura; combinato con un full backup, permette di ricostruire lo stato del database a qualunque timestamp.

Il pattern di copia continua dei binlog è questo: un processo bash in loop forever legge i binlog MySQL localmente appena sono flushati, li copia sul Storage Box remoto, verifica il checksum, li elimina dal disco locale quando non più necessari.

#!/usr/bin/env bash
# /opt/backups/binlog-mirror.sh - gira come systemd service
set -euo pipefail

LOCAL_BINLOG_DIR="/var/lib/mysql"
REMOTE_DIR="/mnt/storage-box/backup/mysql/binlog"
LAST_COPIED_FILE="/var/lib/binlog-mirror/last-copied"

mkdir -p "$(dirname "$LAST_COPIED_FILE")"

while true; do
    # Identifica l'ultimo binlog noto (stato persistito)
    LAST_COPIED=$(cat "$LAST_COPIED_FILE" 2>/dev/null || echo "")

    # Lista binlog MySQL (escluso l'attivo attuale)
    CURRENT=$(mysql -uroot -p"${MYSQL_ROOT_PWD}" -N -e "SHOW BINARY LOGS" | head -n -1 | awk '{print $1}')

    for BINLOG in $CURRENT; do
        if [[ "$BINLOG" > "$LAST_COPIED" ]]; then
            cp "${LOCAL_BINLOG_DIR}/${BINLOG}" "${REMOTE_DIR}/${BINLOG}"
            sha256sum "${LOCAL_BINLOG_DIR}/${BINLOG}" > "${REMOTE_DIR}/${BINLOG}.sha256"
            echo "$BINLOG" > "$LAST_COPIED_FILE"
        fi
    done

    sleep 30
done

Il flag critico da configurare su MySQL è sync_binlog = 1 che forza il flush del binlog al disco a ogni commit - senza questo, un crash del server potrebbe perdere gli ultimi secondi di scritture, vanificando la granularità PITR. Il setting di binlog_expire_logs_seconds va calibrato per far rimanere localmente i binlog per almeno 7 giorni (tempo per il copy-mirror di esserne sicuro) ma liberare lo spazio poi - tipicamente 604800 (7 giorni) è un valore sensato.

Il punto cruciale: il restore test settimanale automatizzato

Il singolo fattore che separa un backup "su carta" da uno "affidabile" è il restore test automatico. Un backup mai testato è una speranza, non un'assicurazione. Il pattern che applico è di un VPS dedicato (più piccolo del primary, ma sufficiente per restore verifica) che ogni sabato mattina:

  1. Scarica l'ultimo full backup + gli ultimi 7 incrementali dal Storage Box
  2. Prepara la catena di backup con xtrabackup --prepare --apply-log-only per ogni step
  3. Restore dei file nel datadir di MySQL
  4. Starta MySQL e verifica che sia in stato healthy
  5. Esegue query di sanity check: conta righe sulle 10 tabelle più critiche, confronta con conteggi attesi (salvati al momento del full backup per riferimento), verifica integrità con CHECKSUM TABLE
  6. Se tutto passa, spegne MySQL e pulisce il datadir per il run della settimana successiva
  7. Se qualcosa fallisce, manda alert Telegram al team IT

Lo script che orchestra questo è circa 200 righe di bash. Il valore operativo è inestimabile: ogni sabato alle 09:00 ricevo (come consulente remoto) la conferma Telegram che il sistema di backup funziona effettivamente. Nell'arco di 8 mesi di operatività, ho avuto un solo alert di fallimento - un disco del Storage Box era in errore I/O, il backup era corrotto, abbiamo re-inizializzato il backup partendo da un full nuovo. Senza il restore test settimanale, avremmo scoperto il problema solo nel momento del disaster recovery reale, con il caos che si può immaginare.

La strategia di restore test si integra concettualmente con quello che descrivo nel mio approfondimento sulle strategie avanzate di backup per VPS unmanaged su Hetzner, OVH, Contabo, Digital Ocean e Aruba e con la disciplina del piano di disaster recovery PHP per continuità operativa delle PMI - il restore test è la prova di questa disciplina, il resto è teoria.

Crittografia: proteggere i backup dalla compromissione dello storage remoto

Un backup che contiene dati personali di 600 clienti PMI richiede protezione crittografica - anche se lo storage remoto è già protetto con TLS e autenticazione, un'eventuale compromissione del credenziali di accesso esporrebbe tutti i backup in chiaro. La soluzione che applico è crittografare i backup prima di caricarli sul remote.

XtraBackup supporta nativamente la crittografia con --encrypt:

xtrabackup --backup \
    --user=xtrabackup \
    --password="${MYSQL_XTRABACKUP_PWD}" \
    --target-dir="$FULL_DIR" \
    --compress \
    --encrypt=AES256 \
    --encrypt-key-file=/root/.backup-encryption-key \
    --parallel=4

La chiave di crittografia è un file random di 32 byte generato al setup iniziale, salvato in /root/.backup-encryption-key con permessi 600 (leggibile solo da root), e duplicato in modo sicuro - stampato su carta in cassaforte, salvato in password manager offline del legale rappresentante, condiviso via Shamir's Secret Sharing con tre membri del team. La regola è che la chiave non deve essere sul server che viene backuppato (altrimenti la compromissione del server compromette anche i backup), ma deve essere recuperabile in modo sicuro quando serve fare restore.

Il restore con chiave cifrata richiede --decrypt prima di --prepare:

xtrabackup --decrypt=AES256 \
    --encrypt-key-file=/root/.backup-encryption-key \
    --target-dir="$BACKUP_DIR" \
    --parallel=4 \
    --remove-original

xtrabackup --prepare --target-dir="$BACKUP_DIR"

Il trade-off del --encrypt è la CPU overhead: su un backup full di 240 GB la crittografia aggiunge circa 15-20% al tempo totale. Su un server con CPU moderna è overhead assorbibile; su server più vecchi può essere significativo. La scelta va calibrata - per database con dati sensibili (HR, finanza, sanità), la crittografia è obbligatoria per compliance GDPR; per database puramente applicativi, può essere opzionale.

Metriche operative: cosa il team vede in dashboard

Per rendere il sistema osservabile dal team IT interno senza dipendenza dal consulente esterno, espongo quattro metriche in una dashboard Grafana semplice: durata dell'ultimo full backup (alert se > 60 minuti), durata media degli incrementali dell'ultimo giorno (alert se > 10 minuti), età del più vecchio backup retained (alert se < 4 settimane - segnale che qualche backup non è stato eseguito), esito dell'ultimo restore test settimanale (alert se FAIL). Queste metriche si estraggono dai log di XtraBackup con parsing grep e si pushano a Prometheus via node_exporter textfile collector.

Sul cliente buste paga, negli 8 mesi di operatività, le metriche mostrano: tempo medio full backup 35-42 minuti (stabile), tempo medio incrementale 1-3 minuti (stabile), zero missed backup, sei restore test settimanali falliti su 34 totali - cinque per problemi transitori del Storage Box (ritentati con successo la settimana successiva), uno per corruzione reale identificata e gestita come descritto sopra. Il tasso di affidabilità effettivo del sistema si è attestato al 97%, con MTTR sul singolo failure di media 2-3 giorni.

Il risultato di business: nei 12 mesi successivi al rollout, il cliente ha subito un evento di data loss parziale (cancellazione manuale errata di 340 cedolini da parte di un operatore che aveva sbagliato WHERE clause in una query di pulizia). Il restore PITR al secondo ha recuperato i dati nello stato esattamente di 40 minuti prima dell'errore, con perdita effettiva di 40 minuti di lavoro ricostruibile dai file sorgente. Senza XtraBackup + binlog mirror, la perdita sarebbe stata di 24 ore.

Se gestisci un database MySQL di produzione sopra i 10-15 GB e il tuo backup è ancora basato su mysqldump con RPO di 24 ore, oppure stai pianificando una strategia di backup per un nuovo sistema critico e vuoi partire con le fondamenta giuste per disaster recovery reale, contattami per una valutazione: in una settimana di lavoro dimensiono la strategia XtraBackup calibrata sul tuo volume dati e RPO target, configuro gli script di full + incrementale + binlog mirror, implemento il restore test automatico settimanale, e ti consegno la pipeline completamente funzionante con alert proattivi e metriche in Grafana - con l'impegno che nei 90 giorni successivi rispondo personalmente a qualunque anomalia emerga, finché il sistema non sia stabile e manutenibile dal tuo team interno.

Ultima modifica: