Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

So I was looking into LVM snapshots and it seems like a feasible method for backing up mysql so that it's at least crash consistent.

My issue is that I have mysql servers where the mysql data directories live on a SAN and I'd like to utilize the arrays snapshotting capability for the mysql LUN.

I was thinking that I could add a second LUN, create a PV on it, add it to the VG on the server, create an LV that would be a snapshot of the mysql LV and mount that.

At this point, i could copy the data to wherever i needed to copy it.

That part is good but it takes time to do and depending on the size of the database because I have to actually copy the data.

Once I have the snapshot created, Can i take an array snapshot of the mysql LUN and then release the LVM snapshot and remove it?

has anyone tried this?

My understanding is that the changes made after an LVM snapshot are stored on the snapshot LV. Is this correct?

thanks!

share|improve this question
add comment

2 Answers

up vote 1 down vote accepted

I don't see a problem with it at all. Just make sure you have plenty of space free in the lv holding the snapshot, so that you don't run out of time copying the files elsewhere (otherwise the snapshot will be dropped).

The typical process for MySQL backups via snapshot is to first flush with read lock, then start the snapshot, then release the read lock. At that point, you can copy the /var/lib/mysql directory to wherever you want - and do whatever you want with it.

The Percona crew did a good article on it here, http://www.mysqlperformanceblog.com/2006/08/21/using-lvm-for-mysql-backup-and-replication-setup/

Percona guide to LVM MySQL Backups

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.

Sometimes you might run into the errors on this step, The most common one I’ve resently seen is: snapshot: Required device-mapper target(s) not detected in your kernel – This means snapshot module is not loaded in your kernel by default and you need to load it, which is done by running modprobe dm-snapshot

3) Now you have created logical volume and can unlock the tables, but before that you should probably record binary log position which is done by running SHOW MASTER STATUS – This is binary log position you’ll need to point your MySQL Slaves created from this snapshot.

4) Snapshot created, now you want to let MySQL Server to continue, which is done by running UNLOCK TABLES or simply closing connection.

5) Mount backup Filesystem: mount /dev/Main/dbbackup /mnt/backup

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.

7) Unmount filesystem umount /mnt/backup

8) Remove snapshot: lvremove -f /dev/Main/dbbackup

If you want to create slave based on such snapshot you need to perform couple of more simple steps

9) Extract/Copy database to the slave database directory.

10) Start MySQL Server. Wait for it to perform recovery.

11) Use CHANGE MASTER TO to point slave to saved binary log position: change master to master_host="master", master_user="user", master_password="password", master_log_file="host-bin.000335", master_log_pos=401934686;

12) Run SLAVE START to restart replication.

share|improve this answer
    
Right. This method effectively "moves" the data to another location. That takes time. I'm wondering if LVM snapshots + array snapshots are a better alternative and take almost no time at all. The purpose here is only to get a point in time of the LV so that i can snap it on the array. I guess i'll have to try this out :) –  anoopb Oct 2 '12 at 19:56
    
Eh? You have misunderstood. Re-read my answer. I just described exactly that. You take the snapshot - then backup the data. In the mean time - MySQL carries on writing/reading as normal. –  sonassi Oct 2 '12 at 19:58
    
Whether you snap it on the host OS side or on the storage controller makes no difference - the steps are the same (excluding things like renaming the VG on snapshot mount, etc). Doing it at the host is probably easiest. –  MikeyB Oct 2 '12 at 19:58
    
@sonassi, i didn't misunderstand. Copy data to backup is a time consuming operation where as snapping on the controller is much much faster. –  anoopb Oct 2 '12 at 20:14
    
@MikeyB ok. So when i snap the LV, there is no data actually written to the PV that the LV is on yes? All the changes are written to the snapshot PV? and when i remove the snapshot, somehow those changes are put back into place on the original? that's the part that gets me. –  anoopb Oct 2 '12 at 20:16
show 3 more comments

That's not how LVM snapshot works, the best thing to do is use mysqldump to do backups of MySQL.

* UPDATE *

plan B:

configure replication and use mysqldump from slave, this way it doesn't affect your master mysql server.

I also looked up following

Taking a Backup Using Snapshots

share|improve this answer
    
mysqldump takes lots of time and isn't feasible for large innodb databases. –  anoopb Oct 2 '12 at 19:41
1  
@alexus ... that is exactly how snapshots work. The whole principal is to allow file copy with a frozen in time filesystem. Using a slave however, is a suitable (albeit, more complicated) alternative. –  sonassi Oct 2 '12 at 19:43
    
right. slaves work but i don't really have that option. not to mention it is perhaps the most ridiculous method that for some reason everyone seems to be ok with. I might have 10TB of MySQL data on a SAN. I'm supposed to replicate it again just so i can back it up? It's not feasible and it's not scalable. –  anoopb Oct 2 '12 at 19:55
    
A MySQL slave isn't exactly unscalable. Plus, you've got bigger concerns if you are the DBA in charge of a MySQL server with 10TB of data ... and you don't even know how to perform a backup. –  sonassi Oct 2 '12 at 20:04
    
i'm not a dba. i don't have 10TB of data but I have seen 10TB of mysql data before. Not every environment in the real world is ideal. All i'm saying is that backing up mysql databases using a slave is a hack. if mysql databases could be quiesced, then array snapshots would be all that are needed. –  anoopb Oct 2 '12 at 20:39
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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