MySQL – Is It Safe to Copy DB Files from LVM Snapshot?

backupinnodbmariadbMySQLsnapshot

I saw two different approaches for backing up database with LVM snapshot. This article suggests spawning another MySQL instance to do an SQL dump. MYSQL Performance Blog suggests that it is safe to copy directly the database files:

6) Copy data to backup. Normally you can skip slow query logs and
error log while taking backup. You also can skip most of binary logs –
however if some of your slaves are far behind you might want to keep
some of last binary logs just in case, or you can assume in case of
recovery from the backup you will need to restore slaves as well and
skip binary logs in your backup process.

For database in InnoDB format, is it ok that I just backup the /var/lib/mysql/ folder?

Best Answer

For InnoDB and other transactional data stores it should be OK purely from a referential integrity point of view as the normal crash recovery procedure will roll out any incomplete transactions that were in progress when the backup was taken, but there are potential problems that mean it is not recommended that you backup databases this way:

  • Any multi-transaction business logic may be partially complete, leaving complex combinations of records in an odd state from the view of the app's logic (but consistent to your integrity rules as imposed by the DB). If your app is well designed and no such processes exist then this is not an issue (but even with a good design you need to allow for less experienced coders not getting it quite right)
  • The restore will take longer as it will take time for this rollback and other integrity checks to take pace (you can mitigate this by immediately restoring the backup elsewhere then backing it up again)
  • Your backup process is still competing with the main application DB access which may have a performance impact
  • If your data files are not all in the same filesystem (i.e. if you've spread them over different spindle-sets to improve performance for some workloads) you can't take the filesystem/block-device snapshot at exactly the same point for every file so your data integrity could fail here
  • If you are backing up anything that is not ACID compliant (myISAM tables, external data such as binary files stored in the filesytem) then you might also have similar problems.

If you can cope with a small amount of down-time then you can keep it to a minimum by:

  1. stopping the DB service
  2. taking the LVM snapshots
  3. immediately starting the DB service
  4. taking the backup (while the service still runs)
  5. dropping the LVM snapshot when that completes

This way you are only down for the length of time needed to stop and restart the DB service (starting the LVM snapshot should be near instantaneous).

If you can't afford a time any time for this short service outage, you could perhaps setup replication and you could then backup from that instead:

  1. pausing replication to the slave
  2. stopping the slave DB service
  3. taking the LVM snapshots
  4. immediately starting the DB service and resuming replication
  5. taking the backup (while the service still runs)
  6. dropping the LVM snapshot when that completes

In fact, you don't need the LVM snapshots for this if the backup doesn't take so long that resuming replication becomes problematical.

Related Question