mysqldump sperrt standardmäßig Tabellen, damit der Dump konsistent ist. Bei einer kleinen Datenbank dauert das eine Sekunde. Bei einer großen steht die Website minutenlang still, und aus der nächtlichen Sicherung wird ein nächtlicher Ausfall.

Der Schalter

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

--single-transaction nimmt über InnoDBs eigene Versionierung einen konsistenten Snapshot: Lesende und Schreibende machen weiter, und der Dump sieht trotzdem einen einzigen Zeitpunkt.

Seine zwei Bedingungen

  • Nur InnoDB. Eine MyISAM-Tabelle in derselben Datenbank wird ohne diese Garantie gesichert. Konvertieren Sie sie: ALTER TABLE t ENGINE=InnoDB;
  • Keine Schema-Änderungen während des Dumps. Ein ALTER TABLE währenddessen kann ihn zerlegen — lassen Sie Migrationen und Sicherungen also nicht gleichzeitig laufen.

Wenn die Datenbank groß ist

# 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

Ein Replikat nimmt die Last vollständig von der Live-Datenbank. Physische Werkzeuge kopieren die Dateien, statt SQL zu erzeugen — das ist ab einigen zehn Gigabyte deutlich schneller wiederherzustellen.

Behalten Sie im Blick, was er währenddessen kostet

mysql -e "SHOW PROCESSLIST" | head -20
mysql -e "SHOW ENGINE INNODB STATUS\G" | grep -A5 "TRANSACTIONS"
Ein langer --single-transaction-Dump zwingt InnoDB, für seine gesamte Dauer alte Zeilenversionen vorzuhalten, also wächst das Undo-Log. Für Minuten ist das in Ordnung, für Stunden ein Problem — und ab da ist ein Replikat oder eine physische Sicherung die Antwort.

Was Sie in der fertigen Datei prüfen, steht in Dumps, die sich wirklich zurückspielen lassen.