MySQL-LVM-Backup

From DevRandom

Using LVM snapshots for Mysql Backups

Setup

In this case , we have one LVM group mysql on top of a clustered volume group

# vgdisplay lvm-clust1
  --- Volume group ---
  VG Name               lvm-clust1
  System ID             
  Format                lvm2
  Metadata Areas        1
  Metadata Sequence No  711
  VG Access             read/write
  VG Status             resizable
  Clustered             yes
  Shared                no
  MAX LV                256
  Cur LV                8
  Open LV               2
  Max PV                256
  Cur PV                1
  Act PV                1
  VG Size               8.00 GB
  PE Size               4.00 MB
  Total PE              2047
  Alloc PE / Size       1150 / 4.49 GB
  Free  PE / Size       897 / 3.50 GB
  VG UUID               2NlKPT-FrcL-OKJb-x2iq-shaa-nwHm-cQWeyU
   
[node1:~] lvdisplay  /dev/lvm-clust1/mysql
  --- Logical volume ---
  LV Name                /dev/lvm-clust1/mysql
  VG Name                lvm-clust1
  LV UUID                SwG86b-RDYg-Xaef-8ZoZ-MuhT-rCkP-E88BmP
  LV Write Access        read/write
  LV Status              available
  # open                 1
  LV Size                1.00 GB
  Current LE             256
  Segments               1
  Allocation             inherit
  Read ahead sectors     auto
  - currently set to     256
  Block device           253:3

So we have this mounted

/dev/mapper/lvm--clust1-mysql         1008M   47M  911M   5% /storage/mysql

Creating a snapshot

 lvcreate -L300M -s -n mysqlbackup /dev/lvm-clust1/mysql
  Clustered snapshots are not yet supported.

So looks like this is not supported since this Volume Group is part of a 2 node cluster using clustered lvm and shared storage.

Since there is no way around this we will need to disable the clustered part for time of backups. Note, this presents a significant risk especially if the cluster node encounter a failover or if the other node decides to use the LVM mount for some reason.

vgchange -cn lvm-clust1
  Volume group "lvm-clust1" successfully changed

Try again

lvcreate -L300M -s -n mysqlbackup /dev/lvm-clust1/mysql
  Logical volume "mysqlbackup" created

mount /dev/lvm-clust1/mysqlbackup  /storage/mysqlbackup/

Now to go to backups

Using Mysqldump

Create a simple config file with different settings (port, socket, datadir etc) from the other instance

[mysqld]
datadir=/storage/mysqlbackup
socket=/storage/mysqlbackup/mysql.sock
innodb_file_per_table = 1
server-id=1
port=13306
     
[mysql.server]
user=mysql

[mysqld_safe]
err-log=/var/log/mysqld-backup.log
pid-file=/var/run/mysqld/mysqld-backup.pid

[client]
socket=/storage/mysqlbackup/mysql.sock

Start mysql directly

/usr/libexec/mysqld  --defaults-file=/etc/my.cnf.backup  --user=mysql

Use mysqldump to backup the db

 mysqldump --socket=/storage/mysqlbackup/mysql.sock -A > alldb.sql

Once done kill the the above mysqld process with kill <pid>

File Copy

This is simple. Just copy the raw files to tape or backup location

Destroying the snapshot

Use lvremove

lvremove  /dev/lvm-clust1/mysqlbackup 
Do you really want to remove active logical volume "mysqlbackup"? [y/n]: y
  Logical volume "mysqlbackup" successfully removed

Comments

As with every backups, make sure you can restore fine from it periodically.

There is a tool called mylvmbackup which automates some of this. See https://launchpad.net/mylvmbackup