Database snapshots

From Wikitech

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:

vi /etc/fstab # remove /a 
umount /a
apt-get install lvm2 xfsprogs
modprobe dm-snapshot
pvcreate /dev/sda6
vgcreate tank /dev/sda6
lvcreate -L 1670G -n data tank
lvcreate -L 100G -n tmp tank
mkfs.xfs -d sunit=512,swidth=4096 /dev/tank/data
mkfs.xfs -d sunit=512,swidth=4096 /dev/tank/tmp
echo /dev/tank/data /a xfs noatime,nobarrier 0 2 >> /etc/fstab
echo /dev/tank/tmp /a/tmp xfs noatime,nobarrier 0 2 >> /etc/fstab
mount /a
mkdir /a/tmp
mount /a/tmp

Then, the actual snapshot rotation has to be deployed. Currently we use a heavily modified version of (OH I HACKED OUT READONLY SNAPSHOT PERMISSION CAREFUL --domas): that is installed on new db's via puppet to /usr/local/sbin/ - then manually add this to root's crontab when ready:

18 */8 * * * /usr/local/sbin/ -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/snap
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), or just run mysqld_safe with alternative settings, e.g:

/usr/local/mysql/bin/mysqld_safe --socket=/tmp/mysql.3307 --port=3307 --innodb_buffer_pool_size=1G --datadir=/mnt/snap/sqldata/ --pid-file=/tmp/

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