Database snapshots

Revision as of 22:23, 24 August 2010

Says Domas on 2009-03-18:

'lvcreate -s', every eight hours, two snapshots are held. reaching them is mounting it, copying data somewhere and running mysql instance on top of that.

Setting up

To set up snapshot capabilities one has to rebuild filesystem on top of LVM. On a standard wikimedia machine it would look like this:

umount /a
apt-get install lvm2 xfsprogs
modprobe dm-snapshot
pvcreate /dev/sda6
vgcreate tank /dev/sda6
lvcreate -L 500G -n data tank
mkfs.xfs /dev/tank/data
echo /dev/tank/data /a xfs noatime,nobarrier 0 2 >> /etc/fstab
mount /a

Then, the actual snapshot rotation has to be deployed. Currently we use this one (OH I HACKED OUT READONLY SNAPSHOT PERMISSION CAREFUL): http://fbq.hamal.nl/blobs/snaprotate.pl (probably needs packaging :). I placed them to /usr/local/sbin/snaprotate.pl - then actual setup is as easy as putting such line to crontab:

18 */8 * * * /usr/local/sbin/snaprotate.pl -a swap -V tank -s data -L 100G

(Maintain two snapshots with rotation every 8 hours).

Using a snapshot

I hope we never need this :)

One can found active snapshots using 'lvs':

root@db24:~# lvs
 LV           VG   Attr   LSize   Origin Snap%  Move Log Copy% 
 data         tank owi-ao 500.00G                              
 snap03200018 tank sri-a- 100.00G data    20.23                
 snap03200818 tank sri-a- 100.00G data     9.47                
           

Then it is as easy as:

mount -o noatime,nobarrier,nouuid /dev/tank/snap03200018 /mnt
rsync /mnt/ someotherhost:/.../

For small data recovery needs one can run MySQL on snapshot locally (need to have my.cnf adjusted to use snapshot paths, different ports/sockets etc). This will edit the snapshot - so it may be necessary to resize the snapshot area first.

Do note, instances with snapshots usually have smaller buffer pool (to allow some headroom for in-memory LVM tracking) and transaction log sizes (faster recovery) defined - it may be easier to use my.cnf of snapshot hosts.

Current snapshot machines

• db23 - s5
• db24 - s2
• db25 - s3
• db26 - s1
• ixia - s4