mysqldump blocca le tabelle per impostazione predefinita, così il dump risulta coerente. Su un database piccolo ci vuole un secondo. Su uno grande il sito resta congelato per minuti, e il backup notturno diventa un'interruzione notturna.

L'opzione

mysqldump --single-transaction --quick --routines --triggers \
          --default-character-set=utf8mb4 shop | gzip > shop.sql.gz

--single-transaction prende un'istantanea coerente sfruttando il versionamento interno di InnoDB: chi legge e chi scrive prosegue, e il dump vede comunque un unico istante.

Le sue due condizioni

  • Solo InnoDB. Una tabella MyISAM nello stesso database viene copiata senza quella garanzia. Convertile: ALTER TABLE t ENGINE=InnoDB;
  • Nessuna modifica di struttura durante il dump. Un ALTER TABLE mentre è in corso può romperlo: non far girare migrazioni e backup nello stesso momento.

Se il database è grande

# dump from a replica instead of production
mysqldump --single-transaction -h replica.internal shop | gzip > shop.sql.gz

# or use a physical tool
xtrabackup --backup --target-dir=/srv/backup/full

Una replica toglie del tutto il carico dal database in produzione. Gli strumenti fisici copiano i file invece di generare SQL, e oltre qualche decina di gigabyte si ripristinano molto più in fretta.

Tieni d'occhio quanto costa mentre gira

mysql -e "SHOW PROCESSLIST" | head -20
mysql -e "SHOW ENGINE INNODB STATUS\G" | grep -A5 "TRANSACTIONS"
Un dump lungo con --single-transaction costringe InnoDB a conservare le vecchie versioni delle righe per tutta la sua durata, così il registro di annullamento cresce. Per qualche minuto va bene, per qualche ora è un problema — ed è a quel punto che la risposta diventa una replica o un backup fisico.

Cosa controllare nel file finito è in Dump di database che si ripristinano davvero.