Skip to content

# Tips for taking MySQL backups using LVM

MySQL Backup Linux scripts August 3, 2010August 12, 2010

LVM uses copy-on-write to implement snapshots. Whenever you’re writing data to some page, LVM copies the original page (the way it looked like when the snapshot was taken) to the snapshot volume. The snapshot volume must be large enough to accommodate all pages written to for the duration of the snapshot’s lifetime. In other words, you must be able to copy the data somewhere outside (tape, NFS, rsync, etc.) in less time than it would take for the snapshot to fill up.

While LVM allows for hot backups of MySQL, it still poses an impact on the disks. An LVM snapshot backup may not go unnoticed by the MySQL users.

Some general guidelines for making life easier with LVM backups follow.

#### Lighter, longer snapshots

If you’re confident that you have enough space on your snapshot volume, you may take the opportunity to make for a longer backup time. Why? Because you would then be able to reduce the stress from the file system. Use ionice when copying your data from the snapshot volume:

>     ionice -c 2 cp -R /mnt/mysql_snapshot /mnt/backup/daily/20100719/

[Update: this is only on the cfq I/O scheduler; thanks, Vojtech]

#### Are you running out of space?

Monitor snapshot’s allocated size: if there’s just one snapshot, do it like this:

>     lvdisplay | grep Allocated                                                                                                                  Mon Jul 19 09:51:29 2010

>      Allocated to snapshot  3.63%

Don’t let it reach 100%.

#### Avoid running out of space

To make sure you don’t run out of snapshot allocated size, stop all administrative scripts.

- Are you running your weekly purging of old data? You will be writing a lot of pages, and all will have to fit in the snapshot.
- Building your reports? You may be creating large temporary tables; make sure these are not on the snapshot volume.
- Rebuilding your Sphinx fulltext index? Make sure it is not on the snapshot volume, or postpone till after backup.

You will gain not only snapshot space, but also faster backups.

#### Someone did the job before you

Use mylvmbackup: the MySQL LVM backup script by Lenz Grimmer. Or do it manually: follow this old-yet-relevant post by Peter Zaitsev.

- Twitter
- LinkedIn
- WhatsApp
- Email
- Facebook
- Print

###
5
Leave a Reply

5 Comment threads
0 Thread replies
0 Followers
Most reacted comment
Hottest comment thread
4 Comment authors
Recent comment authors

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Subscribe
Notify of
Guest
Vojtech Kurka

Some important notes:
– ionice works only with CFQ IO scheduler. However, CFQ is usually not recommended for MySQL
– LVM snapshots are costly, if I was looking for a new binary backup solution, I would choose XtraBackup instead of LVM

Author
shlomi

@Vojtech,

With regard to XtraBackup: I use it frequently. I’ve had some occasions where, due to problems with innodb tablespace, it was unable to perform the backup (failed either while mid-taking the backup or at recovery time). Failure actually looks more like a bug.

The advantage of LVM snapshot is that it is DB-unaware.

Guest
Andrew

In many cases, this seems like this technique may lead to priority inversion by increasing the time the snapshot is open and increasing the time for suffering under snapshot write overhead.

Author
shlomi

@Andrew – more time to suffer, but the pain is lighter. This is my experience.

Guest
LenZ

Hi Shlomi! Good post, thanks for providing this background information and the plug for mylvmbackup. I’ve added a link to your post to the “Documentation and Links” section on the project page at http://lenzg.net/mylvmbackup/#Docs

Cheers!

LenZ

wpDiscuz
Send to Email Address Your Name Your Email Address Cancel
Post was not sent - check your email addresses!
Email check failed, please try again
Sorry, your blog cannot share posts by email.