1

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?

3

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.

  • Thanks for taking the time to elaborate your answer. For MyISAM, there is FLUSH TABLES WITH READ LOCK. I wonder if I can do something like that for InnoDB instead of turning off the server to avoid a lengthy recovery. – Question Overflow Aug 4 '14 at 4:00
  • 1
    Flushing the data will reduce the potential inconsistency but not remove it. the flush and the LVM action that follows are not guaranteed to follow instantaneously even though the snapshot itself is an atomic operation. For "naive" structures like MyISAM this could mean unrecoverable data corruption, for logged structures like InnoDB it might mean some partial transaction rollbacks (which will maintain integrity from the databases PoV but not necessarily from the applications's if it doesn't' use transactions correctly) and other cleanup need to be done when the backup is first used. – David Spillett Aug 4 '14 at 7:56
  • 1
    Thanks again. Just to clarify your point the flush and the LVM action that follows are not guaranteed to follow instantaneously. If I can obtain the read lock, hold it, do a snapshot, then release the lock, shouldn't that ensure consistency? – Question Overflow Aug 4 '14 at 9:22
  • 1
    Possibly, but I wouldn't rely upon that behaviour in production without finding official documentation that explicitly states all data will definitely be written to disk at that point so the resulting block-device level snapshot is consistent. You will also have to deal with (detect and handle cleanly) potential deadlock situations as you try lock every table for consistency before taking the snapshot, and so on. – David Spillett Aug 4 '14 at 11:02
4

Yes, it is safe. There is a tool http://www.lenzg.net/mylvmbackup/ that uses LVM snapshots to take backups of MySQL.

From InnoDB's standpoint a backup copy taken with LVM snapshots looks like as InnoDB has suddenly crashed. When you start MySQL from the backup copy it will start crash recovery. To save some time it's better to do the crash recovery immediately after the backup is taken.

  • mylvmbackup has an option to run InnoDB crash recovery after it takes a snapshot: innodb_recover=1 – Boden Garman Nov 23 '15 at 22:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.