Backup di Mysql con mysqldump senza lock sulle tabelle

In un progetto di migrazione infrastrutturale per una PMI mi è capitato di ereditare uno script di backup notturno che girava da anni senza che nessuno l'avesse mai messo in discussione. Faceva esattamente quello che la documentazione interna prometteva: un mysqldump con --lock-tables=false, lanciato alle tre di notte, che non bloccava il sito. Il problema è emerso il giorno in cui abbiamo dovuto usare davvero uno di quei backup per ricostruire l'ambiente: il dump si ripristinava, ma alcune relazioni fra tabelle erano incoerenti, con ordini che riferivano righe di anagrafica che nel dump non c'erano. Non era corruzione del disco né un bug di MySQL. Era il backup stesso a essere stato catturato in uno stato internamente incoerente, e nessuno se n'era mai accorto perché un backup che non viene mai ripristinato sembra sempre funzionante.

Quella esperienza è il motivo per cui ogni volta che parlo di mysqldump su un server di produzione parto dalla distinzione che conta più di ogni parametro: evitare i lock e garantire la consistenza sono due cose diverse, e l'errore classico è credere che la prima implichi la seconda. Vediamo come si fa un backup logico a caldo fatto bene nel 2026, partendo proprio dallo script che si trova ancora in giro.

TL;DR

• Obiettivo: un dump a caldo che non blocca la produzione ma resta consistente.
• Comando giusto (tabelle InnoDB): mysqldump --single-transaction --quick ...
• Errore comune: --lock-tables=false evita i lock ma produce un dump inconsistente, non è la stessa cosa.
• Oltre i ~30 GB o per il mission-critical: passa al backup fisico (Percona XtraBackup), il restore di un dump logico è troppo lento.
• Sempre: cifra il dump, mandalo offsite (regola 3-2-1) e verifica il ripristino.

Perché --lock-tables=false non basta per un backup consistente?

La ricetta diffusa è questa: per non bloccare la produzione si disattiva il locking delle tabelle, tipicamente con qualcosa come mysqldump --skip-add-locks --lock-tables=false. Il comando in effetti non blocca i servizi: gli altri client continuano a leggere e scrivere. Ma proprio per questo il dump che ne esce è una fotografia mossa. mysqldump legge le tabelle una dopo l'altra; se nel frattempo l'applicazione scrive, la tabella letta per prima riflette lo stato del database in un certo istante, mentre quella letta dieci minuti dopo riflette uno stato diverso. Una transazione che ha toccato due tabelle può finire nel dump a metà: la riga figlia c'è, la riga padre no.

La risposta corretta per le tabelle InnoDB, che è il motore di archiviazione predefinito di MySQL da oltre un decennio, è --single-transaction. Come spiega la documentazione ufficiale di mysqldump, questa opzione apre una transazione con consistent read prima di iniziare il dump, così che tutto ciò che mysqldump legge corrisponda a un unico istante coerente del database, indipendentemente da cosa scrivono gli altri client nel frattempo. Il dettaglio che la rende adatta alla produzione è che non tiene lock prolungati: acquisisce un lock di lettura globale solo per un istante all'inizio, il tempo di fissare il punto di consistenza e leggere le coordinate del binary log, poi lo rilascia e prosegue senza disturbare letture e scritture.

mysqldump --single-transaction --quick \
  --user=UTENTE --host=127.0.0.1 \
  --databases DB1 DB2 DB3 | gzip > backup.sql.gz

Due note importanti che cambiano il risultato. La prima: --single-transaction e --lock-tables sono mutualmente esclusive, perché un LOCK TABLES provoca un commit implicito che chiuderebbe la transazione di consistenza. Non vanno combinate, e la presenza di --lock-tables=false nello script vecchio è il segnale che chi l'ha scritto stava cercando di risolvere il problema del lock senza conoscere lo strumento giusto. La seconda, ed è cruciale: la garanzia di consistenza vale solo per InnoDB. Se nel database sopravvivono tabelle MyISAM, magari residui di una migrazione mai completata, quelle vengono comunque catturate in modo non consistente, perché MyISAM non è transazionale. Il primo controllo che faccio su un database di cui devo impostare il backup è proprio verificare che tutte le tabelle siano InnoDB; se non lo sono, la migrazione del motore viene prima del backup, non dopo.

C'è anche un vincolo operativo da rispettare durante il dump: nessuna connessione deve eseguire DDL (ALTER TABLE, CREATE TABLE, DROP TABLE, RENAME TABLE, TRUNCATE TABLE) sulle tabelle che si stanno esportando, perché il consistent read non è isolato da quelle operazioni e il dump ne uscirebbe corrotto. In pratica significa non schedulare migrazioni dello schema nella stessa finestra del backup, una regola di igiene che vale la pena scrivere nero su bianco nelle procedure operative.

La gestione della priorità di sistema, fatta col criterio giusto

Lo script originale usava nice -15 per abbassare la priorità del processo, ed era un'intuizione corretta: su un server carico, un backup non deve competere con il traffico applicativo. Il punto è che nice agisce solo sulla priorità della CPU, e un backup di database è raramente CPU-bound: il collo di bottiglia è quasi sempre l'I/O del disco e, se comprimi al volo, la banda. Per questo, oltre a nice, su sistemi con disco sotto pressione aggiungo ionice -c2 -n7, che abbassa la priorità di I/O del processo lasciando respirare le query applicative. E sposto la compressione fuori dal percorso critico quando posso, usando un compressore più leggero come zstd al posto di gzip, che a parità di rapporto comprime molto più in fretta consumando meno CPU.

nice -n 19 ionice -c2 -n7 \
  mysqldump --single-transaction --quick \
  --user=UTENTE --host=127.0.0.1 --databases DB1 DB2 DB3 \
  | zstd -3 > backup.sql.zst

L'opzione --quick, che lo script vecchio già usava, resta giusta e va tenuta: forza mysqldump a recuperare le righe una alla volta dal server invece di bufferizzare l'intera tabella in memoria, ed è ciò che permette di esportare tabelle enormi senza far esplodere la RAM del processo. Su un database con centinaia di milioni di righe, come quello su cui ho misurato i tempi nello scenario che descrivo più avanti, è la differenza fra un dump che gira e uno che viene ucciso dall'OOM killer.

Se stai gestendo un VPS di produzione e questi dettagli di tuning ti sembrano un campo minato, è esattamente il tipo di lavoro su cui intervengo: nel mio profilo professionale trovi l'esperienza concreta sulla gestione di server dedicati e VPS, hardening Linux e procedure di backup e disaster recovery testate, non solo configurate.

Quando mysqldump smette di bastare: backup logico contro fisico

mysqldump produce un backup logico: un file SQL con le istruzioni per ricostruire schema e dati. È portabile, leggibile, ideale per database piccoli e medi e per spostarsi fra versioni. Ma ha un limite di scala preciso, sia in fase di dump sia, soprattutto, in fase di ripristino. Su un VPS Debian con doppio SSD e abbondante RAM, un dump --single-transaction di un database di circa 30 GB con centinaia di milioni di righe si completa nell'ordine dell'ora senza bloccare i servizi, ed è perfettamente accettabile come finestra notturna. Il problema è il ripristino: ricaricare quel file SQL significa rieseguire ogni INSERT e ricostruire ogni indice da zero, e su quei volumi il restore può richiedere diverse ore. Quando il tuo Recovery Time Objective è "il sito deve tornare su entro un'ora", un backup logico da 30 GB non lo rispetta, per quanto perfetto sia il dump.

Sopra una certa soglia, il backup giusto è fisico: una copia dei file dati di InnoDB a livello di filesystem, presa a caldo e in modo consistente. Lo strumento di riferimento per MySQL è Percona XtraBackup, che esegue backup fisici online senza lock, supporta i backup incrementali (copi solo le pagine cambiate dall'ultimo backup) e ripristina in una frazione del tempo di un dump logico, perché non ricostruisce nulla: rimette i file al loro posto. Per MariaDB l'equivalente è mariabackup, derivato dallo stesso progetto. Il modello incrementale di XtraBackup è il tema dell'articolo dedicato in cui spiego come costruire una catena di recovery point su MySQL senza blocchi: lo trovi nell'approfondimento sul backup incrementale di MySQL con XtraBackup, ed è la lettura naturale quando mysqldump inizia a starti stretto.

Vale anche la pena sapere che il mondo mysqldump si è evoluto. MySQL include ora MySQL Shell con le utility di dump (util.dumpInstance(), util.dumpSchemas()), che eseguono l'esportazione in parallelo su più thread con compressione integrata e progress, riducendo sensibilmente i tempi rispetto al mysqldump single-thread. E un dettaglio di compatibilità da non sbagliare nelle versioni recenti: per includere le coordinate del binary log a fini di point-in-time recovery, l'opzione storica --master-data è stata rinominata --source-data a partire da MySQL 8.4, e va usata insieme a --single-transaction per ottenere un backup online coerente da cui ripartire per un recovery puntuale.

Il backup è metà del lavoro: dove lo metti conta quanto come lo fai

L'ultimo consiglio dello script originale era corretto e vale la pena ribadirlo con forza: un backup conservato sulla stessa macchina che ha generato non è un backup, è un file. Se il disco si rompe, se il datacenter ha un problema, se un ransomware cifra il server, il backup sparisce insieme a tutto il resto. La regola operativa è la cosiddetta 3-2-1: tre copie dei dati, su due supporti diversi, di cui almeno una fuori sede. Il dump notturno va quindi spostato subito su una destinazione remota, idealmente una macchina o uno storage dedicato che non condivide nulla con il server di produzione.

Sul come trasferirlo, qui devo essere netto, perché lo script originale mostrava un comando con sshpass e password in chiaro. Quel comando va bene esclusivamente per un test rapido e mnemonico, mai a regime: mettere una password SSH in chiaro dentro uno script di backup significa che chiunque legga quello script, o un backup di quello script, ottiene le credenziali del server di destinazione. A regime si usa sempre l'autenticazione a chiave pubblica, con una chiave dedicata al backup, senza passphrase per l'automazione ma con permessi ristretti lato server (idealmente una chiave confinata a un comando rsync/scp specifico tramite le opzioni di authorized_keys). Il trasferimento conviene farlo con rsync over SSH invece che scp, perché supporta i delta e riprende i trasferimenti interrotti, e si può limitare la banda con --bwlimit per non saturare l'upload del server di produzione.

rsync -av --bwlimit=3000 -e "ssh -i /etc/backup/id_backup" \
  backup.sql.zst utente@server-backup:/srv/backups/mysql/

Per chi cerca una destinazione affidabile fuori sede, uso volentieri Hetzner per i nodi di backup, ed è facile dedicarne uno proprio a questo:

Per nuovi clienti Hetzner, puoi ottenere €20.00 di credito gratuito utilizzando questo link con codice sconto - una scelta ideale per aziende europee che necessitano conformità GDPR e che vogliono un nodo dedicato ai backup separato dal server di produzione.

Tutto questo, però, ha senso solo se chiude un cerchio: il backup va verificato ripristinandolo davvero, periodicamente, in un ambiente separato. Il caso da cui sono partito, quello del dump incoerente scoperto solo al momento del bisogno, è la dimostrazione che un backup non testato è una scommessa, non una garanzia. Su come trasformare una serie di file di dump in una strategia di continuità con obiettivi di RTO e RPO definiti ho scritto in dettaglio nell'articolo sulla strategia di backup aziendale legata al rischio di business, e se nel frattempo noti che il database stesso fatica sotto carico, può valere la pena partire dalla diagnosi delle connessioni MySQL lente su VPS, perché un database già in affanno rende ogni finestra di backup più rischiosa.

Cifrare e ruotare i backup: dentro c'è un database di dati personali

C'è un aspetto che lo script notturno originale ignorava del tutto e che oggi non è opzionale: un dump di MySQL contiene, quasi sempre, dati personali. Anagrafiche clienti, email, indirizzi, a volte molto di più. Nel momento in cui quel file lascia il server di produzione e viaggia verso un nodo di backup, e per tutto il tempo in cui resta archiviato lì, è un trattamento di dati personali a tutti gli effetti, e GDPR e NIS2 chiedono che sia protetto. Un dump in chiaro su uno storage di backup è una violazione che aspetta solo di accadere: se quello storage viene compromesso, hai esfiltrato l'intero database in un colpo solo, già pronto e leggibile.

La contromisura è cifrare il backup a riposo, e va fatto prima che il file lasci la macchina sorgente, non dopo. Si può inserire la cifratura direttamente nella pipeline del dump, concatenandola alla compressione, con uno strumento moderno come age oppure con gpg a chiave pubblica. La logica della chiave pubblica è elegante per i backup automatici: il server di produzione ha solo la chiave pubblica con cui cifra, mentre la chiave privata che permette di decifrare sta altrove, al sicuro, e non transita mai sul server esposto. Così, anche se la macchina di produzione viene bucata, i backup che ha prodotto restano illeggibili per l'attaccante.

mysqldump --single-transaction --quick --databases DB1 \
  | zstd -3 | age -r age1qny... > backup.sql.zst.age

L'altro tassello è la rotazione. Conservare ogni dump per sempre riempie il disco e moltiplica la superficie di dati personali da custodire; non conservarne abbastanza ti lascia senza recovery point quando un problema viene scoperto giorni dopo. Lo schema che applico è a finestre: i dump giornalieri tenuti per una settimana, uno settimanale tenuto per un mese, uno mensile per un anno, con una pulizia automatica che elimina il resto. E va aggiunto un monitoraggio minimo ma indispensabile: un alert che scatta se il backup di stanotte manca, è di dimensione zero, o non si è completato, perché lo scenario peggiore non è il backup che fallisce con un errore, è quello che fallisce in silenzio e te ne accorgi solo al momento del bisogno. Su questo terreno ho costruito anche uno strumento open source, ArkHive, proprio per avere backup cifrati AES-256 con invio offsite via SSH e rotazione gestita, perché gli stessi tre requisiti, cifratura, trasferimento sicuro e retention, tornano in ogni progetto.

Fare il backup di MySQL senza bloccare la produzione, nel 2026, non è una questione di trovare il parametro magico che disattiva i lock: è capire che la consistenza si ottiene con --single-transaction su tabelle InnoDB, che la priorità di sistema va governata sull'I/O e non solo sulla CPU, che oltre una certa scala il backup logico lascia il posto a quello fisico con XtraBackup, e che dove e come conservi il dump conta esattamente quanto il comando che lo genera. Lo script che gira da anni e non blocca il sito può averti dato per tutto questo tempo una falsa sicurezza, proprio come è successo nel progetto da cui sono partito. Se vuoi una verifica seria della tua catena di backup e ripristino, dal comando di dump fino alla prova di restore, contattami per un confronto diretto: di solito basta provare a ripristinare un backup per scoprire se la tua strategia regge o se stai solo accumulando file che nessuno ha mai aperto.