Many hosts offer a full-server backup once per day. However, I've been told that a backup of this kind doesn't guarantee a reliable backup of running databases, because even if the database uses transactions and is ACID compliant, what can possibly happen is:
- The backup procedure starts reading the database file, but when it's eg. half way through..
- A transaction to the database is made that changes something both at the beginning of the database file (which has already been read by the backup), and at the end (which is yet to be read).
- The backup procedure continues reading the file, and therefore gets the beginning of the file before the transaction, and the end of the file after the transaction - which spells trouble.
I've been told the only way to reliably backup a database is to use the database's in-built backup procedure. Those backups can be stored on the server, and they in turn can be backed up but the whole-server backup.
Is this all correct?
The reason I ask is the host is offering whole-server backups only, and I need to know if I this is sufficient, or if I need to sort out something separate for the database. The database is MySQL (InnoDB) - though I'm interested if the logic applies to all databases.