Jan 22 2009

MySQL Snapshots using LVM

Category: Databasesjgoulah @ 2:50 PM


LVM snapshots are a quick and easy way to take a backup of your MySQL server’s data files. The idea is to acquire a read lock on all tables, flush all server caches to disk, and create a snapshot of the volume containing the MySQL data directory, then unlock the tables again. This gives an atomic snapshot that takes only milliseconds. A mysql instance can then be launched to point at this data directory and you can perform a mysqldump to get data for setting up slaves, or you can take a completely destroyed DB and use the data directory to restore it to this point in time.

Setup the LVM

Figure out the partition you want to put the LVM on, in our case we’ll use /data2

$ df -h | grep data2
/dev/sda2             371G  195M  352G   1% /data2

We’ll need to run fdisk on it.  If you have data on here, save it to somewhere else!

$ sudo fdisk /dev/sda2
Device contains neither a valid DOS partition table, nor Sun, SGI or OSF disklabel
Building a new DOS disklabel. Changes will remain in memory only,
until you decide to write them. After that, of course, the previous
content won't be recoverable.

The number of cylinders for this disk is set to 49932.
There is nothing wrong with that, but this is larger than 1024,
and could in certain setups cause problems with:
1) software that runs at boot time (e.g., old versions of LILO)
2) booting and partitioning software from other OSs
   (e.g., DOS FDISK, OS/2 FDISK)
Warning: invalid flag 0x0000 of partition table 4 will be corrected by w(rite)

Command (m for help): n
Command action
   e   extended
   p   primary partition (1-4)
Partition number (1-4): 1
First cylinder (1-49932, default 1):
Using default value 1
Last cylinder or +size or +sizeM or +sizeK (1-49932, default 49932):
Using default value 49932

Command (m for help): t
Selected partition 1
Hex code (type L to list codes): 8e
Changed system type of partition 1 to 8e (Linux LVM)

You can review the change with the ‘p’ command:

Command (m for help): p

Disk /dev/sda2: 410.7 GB, 410704680960 bytes
255 heads, 63 sectors/track, 49932 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes

     Device Boot      Start         End      Blocks   Id  System
/dev/sda2p1               1       49932   401078758+  8e  Linux LVM

You might need to reboot here, so go ahead and run sudo shutdown -r now

Now unmount the disk we want to create the lvm on and create a physical volume

$ sudo umount /dev/sda2
$ sudo pvcreate /dev/sda2
Physical volume "/dev/sda2" successfully created

You can see our new physical volume with pvdisplay

$ sudo pvdisplay
  "/dev/sda2" is a new physical volume of "382.50 GB"
  --- NEW Physical volume ---
  PV Name               /dev/sda2
  VG Name
  PV Size               382.50 GB
  Allocatable           NO
  PE Size (KByte)       0
  Total PE              0
  Free PE               0
  Allocated PE          0
  PV UUID               myxfgQ-alfA-y8sX-af4a-ifoA-3S29-YD3tuK

Now create a volume group on the physical volume, since we’re using this for mysql we’ll call it mysql

 $ sudo vgcreate mysql /dev/sda2
  Volume group "mysql" successfully created

And you can view it

$ sudo vgdisplay
  --- Volume group ---
  VG Name               mysql
  System ID
  Format                lvm2
  Metadata Areas        1
  Metadata Sequence No  1
  VG Access             read/write
  VG Status             resizable
  MAX LV                0
  Cur LV                0
  Open LV               0
  Max PV                0
  Cur PV                1
  Act PV                1
  VG Size               382.50 GB
  PE Size               4.00 MB
  Total PE              97919
  Alloc PE / Size       0 / 0
  Free  PE / Size       97919 / 382.50 GB
  VG UUID               0VZxkR-873N-E6Vj-H6SV-FCWO-3jHF-ykHRiX

Create a logical volume on the volume group, we’ll put it on our mysql volume group and call it maindb. We want it to be about half (or less) of the entire amount of space we have to make a backup, in this case thats about half of 382GB so we’ll go with 190:

$ sudo lvcreate --name maindb --size 190G mysql
  Logical volume "maindb" created

And you can view it:

$ sudo lvdisplay
  --- Logical volume ---
  LV Name                /dev/mysql/maindb
  VG Name                mysql
  LV UUID                4hlcMf-cs5a-xjYU-yxjE-Fx2f-Pngv-z0RAEH
  LV Write Access        read/write
  LV Status              available
  # open                 0
  LV Size                190.00 GB
  Current LE             48640
  Segments               1
  Allocation             inherit
  Read ahead sectors     auto
  - currently set to     256
  Block device           253:0

Now we have to put an actual filesystem on it:

$ sudo mkfs.ext3 /dev/mysql/maindb
mke2fs 1.39 (29-May-2006)
Filesystem label=
OS type: Linux
Block size=4096 (log=2)
Fragment size=4096 (log=2)
24903680 inodes, 49807360 blocks
2490368 blocks (5.00%) reserved for the super user
First data block=0
Maximum filesystem blocks=4294967296
1520 block groups
32768 blocks per group, 32768 fragments per group
16384 inodes per group
Superblock backups stored on blocks:
        32768, 98304, 163840, 229376, 294912, 819200, 884736, 1605632, 2654208,
        4096000, 7962624, 11239424, 20480000, 23887872

Writing inode tables: done
Creating journal (32768 blocks): done
Writing superblocks and filesystem accounting information: done

This filesystem will be automatically checked every 26 mounts or
180 days, whichever comes first.  Use tune2fs -c or -i to override.

And now we can actually re mount it on the /data2 folder:

$ sudo mount /dev/mysql/maindb /data2

And add it to fstab, making sure to comment out the old entry

#LABEL=/data2            /data2                  ext3    defaults        1 2
/dev/mysql/maindb       /data2                  ext3       rw,noatime   0 0

Note that we do not have to create the other logical volume on the volume group that is used for the snapshot, as the lvmbackup tool will take care of it for us.

Move MySQL to the LVM Partition

Now we want to move our mysql installation into this folder:

$ sudo mkdir -p /data2/var/lib
$ sudo mv /var/lib/mysql/ /data2/var/lib/

We need to ensure the my.cnf has settings pointing to this new directory:

log-bin = /data2/var/lib/mysql/bin.log



We can keep the log and pid outside of the LVM if we want:


Now you can start mysqld:

sudo /usr/bin/mysqld_safe &

Install mylvmbackup

Grab the latest mylvmbackup utility:

$ wget http://www.lenzg.org/mylvmbackup/mylvmbackup-current.tar.gz

Extract and install, its just a script so there is no compilation necessary:

$ tar xzvf mylvmbackup-0.11.tar.gz
$ cd mylvmbackup-0.11
$ sudo make install

You’ll also need some perl modules:

Now we need to setup the config file for mylvmbackup, located at /etc/mylvmbackup.conf

There are lots of options but at the very least we need to set:

password={your root pass here}





You may also want to change the backupdir parameter, as this is where the tarball of your database backup is stored,  so you’ll need the space for that.

You’ll want to make sure this backup directory exists, so if you are using the default:

$ sudo mkdir -p /var/tmp/mylvmbackup/backup

And also the mount directory (this is where the LVM snapshot is mounted so a mysqldump can be taken:

$ sudo mkdir -p /var/tmp/mylvmbackup/mnt

Create the Backup

Now you can simply run the mylvmbackup command as root.   Heres what the output looks like:

$ sudo mylvmbackup
20090122 13:26:47 Info: Connecting to database...
20090122 13:26:47 Info: Flushing tables with read lock...
20090122 13:26:47 Info: Taking position record...
20090122 13:26:47 Info: Running: /usr/sbin/lvcreate -s --size=190G 
--name=maindb_snapshot /dev/mysql/maindb
File descriptor 3 (socket:[101615]) leaked on lvcreate invocation.
 Parent PID 7209: /usr/bin/perl
  Logical volume "maindb_snapshot" created
20090122 13:26:47 Info: DONE: taking LVM snapshot
20090122 13:26:47 Info: Unlocking tables...
20090122 13:26:47 Info: Disconnecting from database...
20090122 13:26:47 Info: Mounting snapshot...
20090122 13:26:47 Info: Running: /bin/mount -o rw /dev/mysql/maindb_snapshot 
20090122 13:26:47 Info: DONE: mount snapshot
20090122 13:26:47 Info: Running: /bin/mount -o bind,ro 
20090122 13:26:47 Info: DONE: bind-mount position directory
20090122 13:26:47 Info: Recovering InnoDB...
20090122 13:26:47 Info: Running: echo 'select 1;' | mysqld_safe 
--skip-grant --bootstrap --skip-ndbcluster --skip-slave-start
Starting mysqld daemon with databases from /var/tmp/mylvmbackup/mnt/backup
STOPPING server from pid file /var/tmp/mylvmbackup_recoverserver.pid
090122 13:26:47  mysqld ended
20090122 13:26:47 Info: DONE: InnoDB recovery on snapshot
20090122 13:26:47 Info: Copying my.cnf...
20090122 13:26:47 Info: Taking actual backup...
20090122 13:26:47 Info: Creating tar archive 
20090122 13:26:47 Info: Running: cd '/var/tmp/mylvmbackup/mnt' ;
'/bin/tar' cvf - backup/  
 /bin/gzip --stdout --verbose 
--best ->
/bin/tar: backup/var/lib/mysql/mysql.sock: socket ignored
20090122 13:26:48 Info: DONE: create tar archive
20090122 13:26:48 Info: Cleaning up...
20090122 13:26:48 Info: LVM Usage stats:
20090122 13:26:48 Info:LV VG Attr LSize Origin Snap% Move Log Copy% Convert
20090122 13:26:48 Info:   maindb_snapshot mysql swi-a- 190.00G maindb   0.00
  Logical volume "maindb_snapshot" successfully removed

And now you should have a tarball in your backup directory, with all the files needed to restore:

$ ls /var/tmp/mylvmbackup/backup


9 Responses to “MySQL Snapshots using LVM”

  1. AQ says:

    Nice tutorial!

    I was wondering if you could offer any advice on how to start another mysql instance to do a mysqldump on the snapshot? Does starting another mysql instance require installed mysql twice?


  2. jgoulah says:


    One way to do it is using MySQL Sandbox, which I also happened to write about http://blog.johngoulah.com/2009/01/mysql-sandbox/

  3. AQ says:

    Thanks, i’ll check that out.

    In your steps above, did you move your whole MySQL Installation to /data2 or just the data files?

  4. jgoulah says:


    I moved all of /var/lib/mysql in the steps above and pointed the configuration to use that, but I didn’t move anything besides that folder.

  5. AQ says:

    Sorry I should have been more clear. In my installation all my Mysql files are contained in one folder /usr/local/mysql. It has it ALL in there. But i think your installation is scattered across folders probably from installing through a package. So my data files , bin logs, err logs are located in /usr/local/mysql/var/. So ill probably move only that folder over to the LVM partition.

  6. jgoulah says:

    Yes that should be all you need under LVM

  7. AQ says:

    ./mylvmbackup –user=root –password =12345 –mycnf=/etc/my.cnf –vgname=mysql_vg –lvname=mysql_data_lv –backup type=tar
    20090127 16:18:32 Info: Connecting to database…
    20090127 16:18:32 Info: Flushing tables with read lock…
    20090127 16:18:32 Info: Taking position record…
    20090127 16:18:32 Info: Running: /sbin/lvcreate -s –size=5G –name=mysql_data_l v_snapshot /dev/mysql_vg/mysql_data_lv
    File descriptor 3 left open
    Insufficient free extents (1279) in volume group mysql_vg: 1280 required
    20090127 16:18:32 Error: FAILED: taking LVM snapshot (exit status 5)
    20090127 16:18:32 Info: Cleaning up…

    — Volume group —
    VG Name mysql_vg
    System ID
    Format lvm2
    Metadata Areas 1
    Metadata Sequence No 2
    VG Access read/write
    VG Status resizable
    MAX LV 0
    Cur LV 1
    Open LV 1
    Max PV 0
    Cur PV 1
    Act PV 1
    VG Size 10.00 GB
    PE Size 4.00 MB
    Total PE 2559
    Alloc PE / Size 1280 / 5.00 GB
    Free PE / Size 1279 / 5.00 GB
    VG UUID 4KjUbb-ex9Y-BBoI-6Q2o-dHjX-lU2I-C1cX3G

    The script doesnt go through for me because the free PE /Size is one less PE than the Alloc PE / Size. I believe the script automatically finds the LV size and uses that?

  8. jgoulah says:


    Yes, you need equal or more free than allocated space available on the VG. You have less space free here so it doesn’t have enough room to take the snapshot.

  9. AQ says:

    sorry to clutter your comment area..

    I’m running into problems with innodb-recover option. It seems after mylvmbackup mounts the snapshot, and starts another mysql instance, the new instance is still looking in the 1st instance datadir and not the snapshot directory when syncing innodb data files.
    /var/mysql_data/var/ is current data dir

    /var/tmp/mylvmbackup/mnt/backup is the snapshot data dir

    See below for excerpt of err log:

    090202 14:17:33 mysqld_safe Starting mysqld daemon with databases from /var/tmp/
    InnoDB: Unable to lock /var/mysql_data/var/ibdata1, error: 11
    InnoDB: Check that you do not already have another mysqld process
    InnoDB: using the same InnoDB data or log files.

    Do you know what could be the problem? Seems like everything else works except syncing the innodb data.

download xiuxiu editor foto shambho shankara mp3 free download pikeno e menor perdicao download download lagu surat at taubat smarthru 4 download pl abbey road 60s drums download mac cell phone repair download download kara winter magic album download intel gma booster terbaru download mp3 tantowi yahya free download farhan ali qadri video naats free download internal medicine harrison download music ragheb alama sinin dhada songs download in ziddu download form 4852 from the irs website free invitation templates download and print download boundless by cynthia hand free la chatimi cantare mp3 download free download of deception point ebook download munni badnam hui free mp3 download jtx party like a rockstar download hide ip ng 1.40 download ooh la la goldfrapp download time after time quietdrive crime and punishment mp3 download sweety gippy mp3 download caminhos da liberdade download minions banana video download reflex flugsimulator download gratis naruto shippuden 208 pt download sniper elite wii download ntsc pirata do espaço download dublado download ptanks full version o justiceiro download pc rip microsoft hda driver download download admit card iti jharkhand 2012 bada 2.0 download wave 2 download gangster life gta apple safari 4 x download business intelligence download oracle download os x dvd installer download gta 8 vice city myegy szybcy i wściekli 5 2011 download download highschool of the dead color download shakira ft pitbull rabiosa zippy download lagu t ara zombie jelly car music download download driver sony vaio 32bit klezmer music to download free download of shreenathji bhajan aga bai arechya download serie v 3 temporada download download the jeremy kyle show download jogos fazenda gratis pc minimizer download para mu susana nothing at all download download um novo vencedor damares playback download command line mac os download paypal jar for android download zeljko vasic zanjisi kukovima marian keyes watermelon ebook download internet download manager flurry icon kruti dev free download windows download chief keef choppa go bang download msi 3.1 windows installer chessmaster free download with crack bravo hits 98 download download disrespect kirko bangz x plane 6 demo download la baby jonas brothers mp3 download netgear ga311 windows 7 download free download habib painter mp3 download tweetdeck desktop windows 7 ekhon ami mp3 download promethean the created download pdf how to download youtube videos to ipad amazon download games steam cypress hill download 2011 download treu nha hang xom 2 download kick out the epic mother wooh da kid stepped download x2 x men united download imaginasamba perfeição download download navigation for mobile zor ka jhatka song download mp3 download audi a4 owners manual download pretty little liars s01e05 avi download nitro circus season 1 download eega promotional song download the simulator 2012 demo x264 codec download mac download lições para toda vida legendado download mkisofs for ubuntu download star trek voyager scorpion lil b 855 download download pokemon black 2 jap rom spells aprilynne pike pdf download ita download ways of reading cursed crusader trainer download download darmowe gry dla dzieci download spyglass for android alaa wardi 7aram free download massive attack teardrop song download download settings for nokia x6 download account opening form obc download fl studio on a mac worth dying for download s bot download free silkroad 1 click downloader download 5 ishq ka sheen download download melodia que eu conheço stephen king novels download free 100 download psp games for free a escolha download dublado ptgui 9 mac download kz hack download gratis download driver yamaha psr 3000 download macroeconomics policy and practice download hp dv1000 sound driver download famous five movies download phim benh nhan nguoi anh download gta 1 for free download gangs of wasseypur movie free download panasonic sd jukebox software download falling up drake download trackmania sunrise extreme full version free download de simuladores de combate aereo download i bruise easily download pioneer dj software free download noah and the whale life goes on mp3 download recover deleted files software download sơ đồ kế toán download free desk phone ringtone fußball manager 12 download vollversion kostenlos even greater mp3 download planetshakers download snmp for windows 2000 free download amuse park game bejeweled 3 jar download sleeping at last quicksand download download ứng dụng cho nokia n8 coral player download luna download jill scott whenever you're around download fairy tail games for pc download photoshop 8 cs me myegy download yahoo latest version for free mp7 player free download mac download internet explorer 8 vista java hry download 128x160 download jason upton key of david download march of the wooden soldiers download barad toro be dast avordam mp3 download songs of aitraaz from songs pk com 6.72 f ai nightmare download download tiny toon adventures nes download i'm yours mp3 download 9 hours rom pipi player download vista deskjet d2360 software download download booster pack hack abhas ha mp3 song download mp3 songs download 3gp download cod 4 3rd person mod trey songz blind download free download hawaii 5 0 season 2 kenji free download fort minor download clubbed to death 2 quebrando regras 2 download portugues chamas da vingança 1984 download download sketchup 8 deutsch virtual router manager download xp download instalador chrome offline download soundgarden live to rise download canon mx410 printer driver download the harold song kesha mp3 garmin 255w download maps free download mise a jour mcafee download visio windows 7 64 bit download gen psp 3000 quasi amici film download gratis ita download plano de fuga rmvb legendado download alfonso loher name in the sky download mp3 zahra damariva alasan download j rock songs download bihar secretariat assistant admit card download project 64 64 bits download naruto chapter 589 cartoon download for ipad download tributo a bezerra da silva download roda a roda jogo cx one full download armin van buuren rapture download mp3 download nein mann video download tambor de funk download rota de fuga rmvb free download of shawty got moves mp3 download cm7 for droid 2 global download executive resume format amnesia game download mac crash bandicoot mutant island download pc ipod touch download pictures to computer download tu pirata soy yo chayanne download lg pc suite p990 harmor vst plugin download download abaqus 6.10 student edition i like cereal song download filmes alta resolucao download italian lessons download mp3 mass effect 3 download pirate download manager idm key download wh cs 2011 bau simulator download ita download efek suara unik download girl talk ultraviolet sound download bicara hati episod 4 annie khalid songs download download driver epson cx5500 free quake 3 download bots infinity blade 2 ipa download crack pk songs download list dream the game download free cydia download step by step motorola xoom rom download download let's go ricky luna remix zune manual download pdf download hit and run 2012 dvdrip download do sapo videos download do jogo cities xl 2011 download song socha na tha by alamgir ktechlab for ubuntu download download vara rece kamelia zippy download schenk mir dein herz gipsy kings volare download free welsh flag to download download afinador do cifra club 3 gatsu 9 ka download free mp3 angry birds android download download manager error the server returned an error download sdo x season 2 song download paint shop pro download turn to u justin bieber download original ruu for evo download maps to print boys over flowers download songs download free regular show episodes download yamaha psr 1500 styles satyamev jayate download song download mouse fix for windows xp download elliot in the morning kenapa tidak bisa download film h.p f4200 printer software download p square game over download mp3 carpet 3d max download download mw 3 1.07 patch the legend of zelda download snes rom download 9 temporada friends download on my freebox ne fonctionne pas download tito lopez the blues sharebeast download sweeney todd final scene papago x8.5 wince 5 download download monkey for rhino download na paz de jah download gratuito adobe reader 8 ngo accounting software download download onto mp3 from youtube somebody's me mp3 download enrique download ocarina of time 1.0 download 64 bit windows tax form 8379 download frisky tinie tempa download zippy download pro update psp go 6.60 download lagu chrisye gejolak cinta download themes for gw300 clr via c# richter download download 8195 the damned rar parayathe ariyathe malayalam song download autobiography of abraham lincoln download download phim hiep dao hoa hetaoni english download part 2 cenário de novela download mp3 sandra brown envy download pdf download sims 1 love bed sende ahasa wage mp3 download download voice changing application download feed us 2 free download open source library management system download alana grace black roses red download audi a3 manual download free irctc mobile application stand o food 3 apk download download dss dj effects visual basic software download download film khuda kay liye download intezar remix by falak sri rama rajyam mp3 download download dan seals one friend download virtual families mac free download gossip girl cecily von ziegesar download autoramas fale mal de mim gabin doo uap mp3 download free download love in this club mp3 download dead space 3 demo pc charmed download season 1 nhac chuong theo ten download angry birds season download free pc reign of hunters download download de pokemon flash download full screen theme wordpress download manager 6.05 crack download planta x zumbi download razor ramon entrance music download skype xperia 8 download lagu tercipta untukku ungu download shining inheritance ep 16 trial download microsoft project 2010 halo ce download key thermodynamics 6th cengel download vmware ova converter download download e.r legendado 1 temporada wilfred season one download download hivi mata hati download apun bola mp3 zmierzch księżyc w nowiu download peb cod tool download 1.5 web client get download speed ryback meat mp3 download music download on itunes download beenie man i'm okay vigilante 8 2nd offence download pc download movie 2012 in hindi latha tamil font free download word melhor impossivel download legendado download lloyds tsb bank statement download oki b6300 driver how to download spoutcraft free popup blocker download google chrome download tenth avenue north losing download sound intervention mw2 download jogo harry potter pc download pokemon blanc nds alda célia playback download download shwayze get you home download tower bloxx mobile game dani california official video download download jump out the gym download all killer no filler sum 41 ra one full movie download 2011 download rebelde só pro meu prazer download tees maar khan movie in avi format activex control download install download video setia band stasiun cinta download de temas nokia x2 00 download power geez 2005 computer games download com download benny and babloo songs soc pc camera driver download for xp manually download sophos virus definitions jackie chan adventures download links download dragostea se face in doi download terjemahan kitab al umm hp photosmart c3180 download scanner download ipcop for windows 7 nero 8 download windows 7 64 bit kick buttowski kick in genes download dewana 2013 mp3 download download 2 chainz birthday song free how to download correct video driver download film g 30s pki firing games download full version download free 3d motorbike racing download call of duty 4 zombies download smart mobile themes download crbl romanu n are noroc hotfiles download diggy simmons make you mine download bangla natok bhalobashi tai download kml from my maps download song if this charlie sheen shinda new album download download outcast 1 temporada keterlaluan the potters mp3 download download office 2007 upload download falling skies 2 temporada rmvb download sajan all songs oblivion mod manager download mac download toma o meu coração download pierce the veil caraphernelia mp3 download lagu jkt48 original download leaf by elle varner vandalism coming alive mp3 download download god of war betrayal 240x320 download amanda by zigi mp3 download apostila do trf hp 635 driver download windows xp download pro e student version download office 2007 turkish proofing tools download filme a era do nariz vermelho are you in download download lagu true worshippers jadi sepertimu warlords battlecry 3 download free full version apostilas calculo 1 download calof duti 2 download gratis