Does a whole-server backup guarantee a reliable backup of running databases?

Question

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:

1. The backup procedure starts reading the database file, but when it's eg. half way through..
2. 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).
3. 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.

Answer 1

For a filesystem backup to be consistent MySQL requires that the DB be stopped http://dev.mysql.com/doc/mysql-backup-excerpt/5.0/en/backup-methods.html but as you said there are ways of using the DB provided tools to create consistent online backups.

Other DB engines such as Oracle have a mode you can put the DB files into prior to a known time of a filesystem copy to ensure that what's on disk at that time is consistent but you have to know the exact time the backup will start and finish.

there are other DB formats that are also consistent by virtue of the fact that their files are 100% append only such as CouchDB.

But for the most part backups of a running DB require some kind of intervention using the DB engine itself.

Answer 2

What version of MySQL are you running? Are you running InnoDB, MyISAM, or some other engine? If you do not mind some downtime, then go ahead and shutdown MySQL and get the file backups.

If you want to avoid downtime, look into MySQL Enterprise Backup as a method of getting a consistent backup of your database. It does a hot backup of InnoDB and a warm backup if using MyISAM or another engine.

Here is a synopsis which contains a link for further details. http://dev.mysql.com/doc/refman/5.7/en/mysql-enterprise-backup.html

Of course, there are probably also some other third-party backup tools that you can investigate.

I much prefer not to take down the database in order to get a backup of it since that locks out the users. This might not be a significant problem for a less busy database.