Mysql – To make a LVM backup of a MySQL database, would I have to reduce the logical volume size

backupMySQLUbuntu

I am trying to learn how to do a Ubuntu LVM backup of a MySQL database.

For this test I installed a fresh copy of Ubuntu 12.04.2 LTS on a new box, along with MySQL 5.5. The box has one hard drive. During the Ubuntu installation, I chose the partitioning option "Use entire disk and setup LVM".

Here is the space I have:

root@server2:~# df 
Filesystem               1K-blocks    Used Available Use% Mounted on
/dev/mapper/server2-root 238088848 1206832 224787760   1% /
udev                        996264       4    996260   1% /dev
tmpfs                       402324     384    401940   1% /run
none                          5120       0      5120   0% /run/lock
none                       1005800       0   1005800   0% /run/shm
/dev/sda1                   233191   26733    194017  13% /boot

Ubuntu gave me a LVM setup of two logical volumes on one volume group:

root@server2:~# pvs
  PV         VG      Fmt  Attr PSize   PFree
  /dev/sda5  server2 lvm2 a-   232.64g 4.00m

root@server2:~# vgs
  VG      #PV #LV #SN Attr   VSize   VFree
  server2   1   2   0 wz--n- 232.64g 4.00m

root@server2:~# lvs
  LV     VG      Attr   LSize   Origin Snap%  Move Log Copy%  Convert
  root   server2 -wi-ao 230.68g                                      
  swap_1 server2 -wi-ao   1.96g

The database data file is at /var/lib/mysql/:

root@server2:~# mysqladmin variables | grep datadir
| datadir  |  /var/lib/mysql/

Which is on the logical volume:

root@server2:~# df /var/lib/mysql
Filesystem               1K-blocks    Used Available Use% Mounted on
/dev/mapper/server2-root 238088848 1206836 224787756   1% /

There is not much free space left:

root@server2:~# vgdisplay server2 | grep Free
  Free  PE / Size       1 / 4.00 MiB

And finally I get to my question, in order to make a LVM backup of this MySQL database, will I first have to reduce the size of the logical volume so that there is more space?

Best Answer

Unfortunately, yes, you'll need to reduce the size of your root LV.

(You could reduce the size of swap, too, but that's already a fairly small volume)

Even if you can use that 4MB, a snapshot of your root volume will become invalid quite quickly when LVM runs out of "free" disk space.

You get prompted with a text box asking how much of your disk to use when you do an Ubuntu install (even if you select "use entire disk"). If you're building systems to muck around on, or virtual machines, I recommend setting this to 90% (leaving 10% for LVM) - you probably won't miss the space, and you never know when it'll come in handy.

The mechanics of how to shrink the root volume aren't too hard to find on the web, but questions about that are probably best suited to unix.se.

Update: Make sure you don't rely on LVM as your only backup strategy - if you change enough data on disk and your snapshot runs out of free LVM space to use, the snapshot will simply stop working. Using LVM is one of the only ways you can take file-based backups of MySQL (esp. in conjunction with FLUSH TABLES WITH READ LOCK) without shutting down the server, though, which is quite handy.

Related Question