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.