Obsolete: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:
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): http://fbq.hamal.nl/blobs/snaprotate.pl that is installed on new db's via puppet to /usr/local/sbin/snaprotate.pl - then manually add this to root's crontab when ready:
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/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/3307.pid
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.