Database snapshots
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: 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.
