Introduction

To backup a large MySQL database on a regular basis, I am going to use a method of taking snapshots of the database files using LVM in linux. The volume I will be using to create the logical volume is a RAID 1 (mirrored) array, as I created in the mdadm creating a new array guide.

Creating the volume group with lvm

The array we are using as the basis of the volume group is /dev/md0. Firstly we need to initialise the RAID array (you can use raw block devices or other partitions) for use as a physical volume. This is simple:

$ pvcreate /dev/md0
  Physical volume "/dev/md0" successfully created

The volume can now be used to make a volume group (I am calling this volume group vgssd, to state that it’s the SSD group):

$ vgcreate vgssd /dev/md0
  Volume group "vgssd" successfully created

Show some information about the newly created volume group

$ vgdisplay vgssd
  --- Volume group ---
  VG Name               vgssd
  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               439.87 GiB
  PE Size               4.00 MiB
  Total PE              112607
  Alloc PE / Size       0 / 0   
  Free  PE / Size       112607 / 439.87 GiB
  VG UUID               ae5CoA-J0v4-eo0j-GyJd-DGvl-t7bF-zQd1wV

This shows we’ve got around the 440GB free for creating logical volumes on this device. We won’t be using it all, as we need to allow room for snapshots.

Creating the logical volume with lvm

It’s up to you to decide how to create and use your partitions. Remember that using LVM allows you to resize (extend and shrink) your volumes, as well as add more physical hard drives to the system. We have created a block device that now is mirrored over 2 SSD harddrives. I will be setting up a 250GB partition for use with mysql data, and allowing loads of space for snapshots, and extending it going forward.

$ lvcreate -L250G -n mysql_data vgssd
  Logical volume "mysql_data" created

I am going to mount this at: /var/mysql_data, and format it to be an ext4 filesystem, and make sure that access times are not recorded on mounting in fstab (noatime)

$ mkdir /var/mysql_data
$ mkfs.ext4 /dev/vgssd/mysql_data
mke2fs 1.42.9 (4-Feb-2014)
Discarding device blocks: done                            
Filesystem label=
OS type: Linux
Block size=4096 (log=2)
Fragment size=4096 (log=2)
Stride=0 blocks, Stripe width=0 blocks
16384000 inodes, 65536000 blocks
3276800 blocks (5.00%) reserved for the super user
First data block=0
Maximum filesystem blocks=4294967296
2000 block groups
32768 blocks per group, 32768 fragments per group
8192 inodes per group
Superblock backups stored on blocks: 
	32768, 98304, 163840, 229376, 294912, 819200, 884736, 1605632, 2654208, 
	4096000, 7962624, 11239424, 20480000, 23887872

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

And the line added to /etc/fstab is as follows:

/dev/vgssd/mysql_data /var/mysql_data ext4 defaults,noatime 0 0

So that shows how to use lvm to create a partition that can be easily extended, backed up, etc.