Unclear why crash consistent backups are unsafe for MySQL

My understanding of the InnoDB engine is that the database is always recoverable after a crash, due to the internal architecture of the engine itself. That is, InnoDB can correctly recover from a crash consistent state. I.e. if the power is pulled out.

On the other hand, when snapshotting a virtual machine in vSphere, common wisdom on the internet says that the backup will be crash consistent but may cause corruption of the InnoDB database unless the virtual machine is quiesced. This seems inconsistent with the above.

Tldr; is InnoDB truly able to recover from a crash consistent state, and if so, shouldn't snapshotting a VM without quiescing be a safe backup method?


Never assume something is always recoverable and consistent!

Crash recovering an file system doesn't necessarily mean your files are intact. A (hopefully small) amount of uncommitted file system writes may be lost. Worst case, the metadata becomes inconsistent, and a fsck gets the file system back but may lose files. This also applies to your database logs, which depending on your recovery point objective may be problematic.

File consistency is a stronger claim: files are in a stable state. fsck should be already clean. A few ways to do this, VMware tools vmsync, Linux LVM snapshots or fsfreeze, Windows VSS.

Even files at a point in time does not imply application consistency. The database will have in memory updates not committed to disk. And new updates are always happening on a running system. Ideally, flush database writes to disk and suspend further writes, until the storage level snapshot is taken.

Or, sure it is possible to take an inconsistent backup and rely more on logs for recovery. This moves a little complexity from taking the backup to restoring it.


So far, I have assumed storage level backups, based on your choice of storage snapshots. A more complete evaluation of backup methods would look at both logical dumps and physical raw files. MariaDB's A Quick Start Guide to Backup Technologies compares multiple implementations of each. For example, have a look at how mylvmbackup does flush and snapshot, and how XtraBackup does redo log recovery.

Of course, backups are meaningless without restores. Always do a restore test. While doing so, verify the recovery point: what was the very last data recovered?