lvm mysql backup

Question

I have been reading about mysql backup through the use of lvm I understand that you create a lvm partition and allocate a specific size to mysql, leaving enough space for the snapshots.

I read that the advantage is that backups are very quick.

Are there any pitfalls to watch out for or disadvantages?

Thanks

score 5 · Answer 1 · answered Feb 06 '12 at 18:57

5

Running with LVM snapshot enabled can cause I/O performance to degrade up to 6x.

http://www.mysqlperformanceblog.com/2009/02/05/disaster-lvm-performance-in-snapshot-mode/

I suggest using Percona XtraBackup as a much better hot backup tool (disclaimer: I work for Percona).

answered Feb 06 '12 at 18:57

Bill Karwin

538,548
86
673
828

Thanks for the article. Did not come across that one. Very interesting. Regarding backup however, I am looking for something free right now – Thomas Feb 06 '12 at 20:06
@Thomas, Percona XtraBackup is free, licensed under the GNU Public License. Percona is an advocate of open-source, free software. – Bill Karwin Feb 06 '12 at 21:01
Hi Bill. Sorry for my lats comment. My mistake. Will look into your solution for sure. Thanks again – Thomas Feb 07 '12 at 12:02
No worries! I understand why you may have assumed I was recommending a commercial product. :) – Bill Karwin Feb 07 '12 at 13:08
@BillKarwin I was considering using this for system tests - making a snapshot at the start, then reverting to it after each suite of tests (currently the tables are truncated and repopulated manually which seems less than ideal). Would Percona be a good choice for a use case like this where there would be many "recoveries" in a short space of time, or am I better off continuing to just drop and repopulate everything? – Vala Jan 04 '13 at 18:02
@Thor84no, if I were creating a system test infrastructure, I would keep a clean copy of the datadir with initial data state. After each test suite, shut down mysqld and destructively copy the clean datadir over your working datadir, then restart mysqld. Easy to do with shell commands like `rsync`, but you could optionally use XtraBackup for this: http://www.percona.com/doc/percona-xtrabackup/innobackupex/restoring_a_backup_ibk.html – Bill Karwin Jan 04 '13 at 18:10
Or, if you already have replication in place, you can do the LVM snapshot on the slave, in which case you don't really care about any slowdown. – rinogo Dec 28 '16 at 20:09
@rinogo, yes, that's true, as long as you are confident that the slave is a true replica. – Bill Karwin Dec 28 '16 at 22:27

score 2 · Answer 2 · edited May 23 '17 at 10:30

LVM snapshots are very scary for InnoDB under certain circumstances. Why?

If you have innodb_file_per_table disabled, ibdata1 will have everything and its grandmother in it. What lives in ibdata1? Four things:

Data Pages
Index Pages
Metadata (such as List of TableSpave IDs)
MVCC Data

If you are trying to perform LVM snapshots in a heavy-write DB environment with innodb_file_per_table off, you may be shooting yourself in the foot. An LVM snapshot needs the ibdata1 file to be well coaleseced beforehand.

I recently performed an experiment as follows:

A client at my employer's web hosting company has the following issues with their MySQL Setup:

innodb_file_per_table off
1.4TB ibdata1
only 29GB free within ibdata1
ext3 filesystem (Single File Size Limit of 2GB, yuck)

I wanted to setup a MySQL Slave by rsycning the /var/lib/mysql folder to another DB Server. When the rsync was done on ibdata1 without downtime, it took 42 hours. The second rsync against ibdata1 took 84 hours, only found 220GB of changes, and was barely 15% done. I aborted that mission.

An LVM snapshot may perform much better that an rsync. Notwithstanding, any LVM snapshot involving a very large ibdata1 will be subject to the same issues.

If you go with LVM snapshot, please use these options:

OPTION 01) Use innodb_file_per_table. LVM snapshots will love you for it because it will be dealing with smaller files. You can also permanently shrink ibdata1.
OPTION 02) Use MySQL Replication and do LVM snapshots on the Slave

With a MySQL Replication Slave, you can

STOP SLAVE; (if you have --skip-slave-start in my.cnf)
service mysql stop
perform the LVM snpshot
service mysql start
START SLAVE; (if you have --skip-slave-start in my.cnf)

That way, these LVM snapshot issues will never see the light of day on a Production Master.

Give it a Try, and have fun with this !!!

I thought the whole point of LVM snapshots was that the slave (or master) didn't need to be stopped in any way. Just all tables flushed to disk with read locks, at which point the LVM snapshot can be made and the read locks released. Is that not the case, Rolando? — rinogo, Dec 28 '16 at 20:08

lvm mysql backup

2 Answers2