Mysql backups using LVM snapshots

Logical Volume Manager (LVM) provides the ability to take a snapshot of any logical volume for the purpose of obtaining a backup of a partition in a consistent state. As applications may access files or databases on a partition during a backup some files may be backed up in one state, while later files are backed up after an update has been made, leading to an inconsistent backup. Traditionally the solution has been to mount the partition read-only, apply table-level write locks to databases or shut down the database engine etc.; all measures which adversely impact availability (but not as much as data loss without a backup will). With LVM snapshots it is possible to obtain a consistent backup without compromising availability. The LVM snapshot works by logging the changes to the filesystem to the snapshot partition, rather than mirroring the partition. Thus when you create a snapshot partition you do not need to use space equal to the size of the partition that you are taking a snapshot of, but rather the amount of changes that it will undergo during the lifetime of the snapshot. This is a function of both how much data is being written to the partition and also how long you intend keeping the LVM snapshot. The longer you leave it, the more changes there are likely to be on the file system and the more the snapshot partition will fill up with change information. The higher the rate of change on the partition the shorter the lifespan of the snapshot. If the amount of changes on the LVM partition exceed the size of the snapshot then the snapshot is released. Step by step procedure to backup using snapshot

1. Connect to MySQL and run FLUSH TABLES WITH READ LOCK
2. While holding connection open run: lvcreate -L16G -s -n dbbackup /dev/Main/Data – This will create snapshot named dbbackup for Logical Volume Main/Data . You should specify enough of undo space to hold modifications during backup process – I’ve specified 16GB in this case. If your undo size is not large enough snapshot will get invalidated and backup will be aborted.
3. Snapshot created, now you want to let MySQL Server to continue, which is done by running UNLOCK TABLES or simply closing connection.
4. Mount backup Filesystem: mount /dev/Main/dbbackup /mnt/backup
5. Copy the data from the above mount to tape or any other source
6. Unmount filesystem umount /mnt/backup
7. Remove snapshot: lvremove -f /dev/Main/dbbackup

So how does the LVM snapshot work internally. LVM snapshots are an example of a copy-on-write snapshot solution. When you have an LVM volume with no snapshots, writes to the volume happen as you’d expect. A block is changed, and that’s it. So in the above case before the snapshot is created, any db inserts/updates that causes a write IO is normally written to the data directory. As soon as you create a snapshot, LVM creates a pool of blocks. I believe that this pool also contains a full copy of the metadata (only metadata not the full data) of the volume. When writes happen to the main volume (i.e. the directory off of which snapshot is created), the block being overwritten is copied to this new pool and the new block is written to the main volume. This is the ‘copy-on-write’. Because of this, the more data that gets changed between when a snapshot was taken and the current state of the main volume, the more space will get consumed by that snapshot pool. The normal way that LVM snapshots are used is not for long-term storage, but rather to get a consistent “picture” of the filesystem such that a backup can be taken. Once the backup is done, the snapshot is discarded. When you mount the snapshot, the meta-data written when the snapshot was taken allows the directory tree to be presented. Blocks that haven’t been changed since the snapshot was taken are read directly from the main volume. Blocks that have been changed are read from the snapshot pool. You don’t want to leave snapshots laying around because (a) they’ll fill up and become unusable, and (b) the system’s performance is impacted while a snapshot is active– things get slower.