mysqldump verrouille les tables par défaut pour que la sauvegarde soit cohérente. Sur une petite base, cela prend une seconde. Sur une grande, le site est figé plusieurs minutes, et la sauvegarde nocturne devient une panne nocturne.

L'option

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

--single-transaction prend un instantané cohérent grâce au versionnement propre à InnoDB : lecteurs et écrivains continuent, et la sauvegarde voit malgré tout un unique instant.

Ses deux conditions

  • InnoDB seulement. Une table MyISAM dans la même base est sauvegardée sans cette garantie. Convertissez-les : ALTER TABLE t ENGINE=InnoDB;
  • Aucune modification de schéma pendant la sauvegarde. Un ALTER TABLE en cours de route peut la casser — ne faites donc pas tourner migrations et sauvegardes en même temps.

Si la base est 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

Un réplica retire entièrement la charge de la base en production. Les outils physiques copient les fichiers au lieu de produire du SQL, ce qui est bien plus rapide à restaurer au-delà de quelques dizaines de gigaoctets.

Surveillez ce qu'elle coûte pendant qu'elle tourne

mysql -e "SHOW PROCESSLIST" | head -20
mysql -e "SHOW ENGINE INNODB STATUS\G" | grep -A5 "TRANSACTIONS"
Une longue sauvegarde en --single-transaction oblige InnoDB à conserver les anciennes versions de lignes pendant toute sa durée : le journal d'annulation grossit. C'est très bien pour quelques minutes, et problématique pour quelques heures — moment où le réplica ou la sauvegarde physique devient la réponse.

Ce qu'il faut vérifier dans le fichier terminé se trouve dans Des sauvegardes de base qui se restaurent vraiment.