ZRM for Mysql Backup From APLogicsWiki Jump to: navigation, search Zmanda for Mysql Database backup 0. Install prerequisite yum install perl-XML-Parser perl-HTML-Parser 1. Download ZRM either rpm or source tarball from http://www.zmanda.com/download-zrm.php 2. RPM: rpm -ivh MySQL-zrm-2.0-1.noarch.rpm Tarball: tar zxvf MySQL-zrm-2.0-release.tar.gz Then copy contents to appropriate directory (/etc, /usr, and /var) 3. Create mysql user for Zmanda (backup-user). mysql> grant select, insert, update, create, drop, reload, shutdown, alter, super, lock tables,replication client on *.* to 'backup-user'@'localhost' identified by 'pass123'; mysql> show grants for 'backup-user'@'localhost'; * Grants for backup-user@localhost * | GRANT SELECT, INSERT, UPDATE, CREATE, DROP, RELOAD, SHUTDOWN, ALTER, SUPER, LOCK TABLES, REPLICATION CLIENT ON *.* TO 'backup-user'@'localhost' IDENTIFIED BY PASSWORD '591051bb593d2f5b' WITH GRANT OPTION | 4. Assume that I have a big database called "lc" that I want to backup 5. Create a working directory for zrm mkdir /etc/mysql-zrm/lc cp /etc/mysql-zrm/mysql-zrm.conf /etc/mysql-zrm/lc cd /etc/mysql-zrm/lc 6. Edit mysql-zrm.conf, make changes as following Uncomment: backup-level=0 # for full backup backup-mode=logical # backup using mysqldump retention-policy=10D # keep backup for 10D compress=1 # enable compress databases=lc # backup database "lc" user="backup-user" password="pass123" verbose=1 mailto="sakai@aplogics.com" html-reports= backup-method-info,backup-status-info,backup-retention-info,backup-performance-info,backup-app-performance-info, backup-status-info html-report-directory=/var/www/html/reports/ webserver-url=http://192.168.20.202/reports/html/ rss-header-location=/usr/share/mysql-zrm/plugins/RSS.header 6.1 [root@localhost lc]# more /usr/share/mysql-zrm/plugins/RSS.header Title = "Zmanda Recovery Manager for MySQL backup reports" Link = "http://192.168.20.202/reports/html/" Creator = "192.168.20.202" Publisher = "192.168.20.202" Rights = "Copyright 2008, aplogics.com" Subject = "ZRM for MySQL backup" Description = "Reports generated by Zmanda Recovery Manager (ZRM) for MySQL" Language = "en-us" ManagingEditor = "sakai\@aplogics.com" webMaster = "sakai\@aplogics.com" UpDatePeriod = Daily UpDateFrequency = 1 7. To Backup database "now": mysql-zrm-scheduler --now --backup-set lc # If the smtp server setup properly, you should get notification email like following: Backup set=lc Backup date=Wed Sep 10 09:19:17 2008 Backup level=0 Logical Databases=lc Backup size=20.89 MB Backup time=00:00:04 Backup status=Backup succeeded 8. Schedule full backup everyday at 1am mysql-zrm-scheduler --add --interval daily --start 01:00 --backup-level 0 --backup-set lc 9. To view scheduled backup mysql-zrm-scheduler --query 10. To see summary about backup Basic report: mysql-zrm-reporter --where backup-set=lc --show backup-status-info Detail report: mysql-zrm-reporter --where backup-set=lc --show backup-performance-info 11. To verify backup mysql-zrm --action verify-backup --verbose --backup-set lc 12. Restore Drop existing database mysql> drop database lc; Check backup before restoration mysql-zrm-reporter --show restore-info --where backup-set=lc Choose full backup that you want to restore then: mysql-zrm-restore --backup-set lc --source-directory /var/lib/mysql-zrm/lc/20080910092334 13. Check whether the lc database is restored correctly. 14. Complete. [edit] Backup Mysql with LVM Snapshot and ZRM Most of the steps are similar to the mysqldump, however you need to make changes to the ZRM conf files (on step 6) Uncomment: backup-level=0 # for full backup backup-mode=raw # backup using lvmsnapshot #lvm-snapshot=350M snapshot-size=350M snapshot-plugin="/usr/share/mysql-zrm/plugins/lvm-snapshot.pl" retention-policy=10D # keep backup for 10D compress=1 # enable compress databases=lc # backup database "lc" user="backup-user" password="pass123" verbose=1 mailto="sakai@aplogics.com" Assuming that the mysql data is stored on a logical volume in a volume group that has enough space for snapshot. Example: vi/etc/sudoers mysql ALL = NOPASSWD:/bin/mount, NOPASSWD:/bin/umount, ASSWD:/bin/df, NOPASSWD:/usr/sbin/lvdisplay, NOPASSWD:/usr/sbin/lvcreate, NOPASSWD:/usr/sbin/lvremove check sudo -u mysql mysql-zrm -action check -backup-set lc [root@gamma lc]# mount /dev/mapper/VolGroup00-LogVol00 on / type ext3 (rw) proc on /proc type proc (rw) sysfs on /sys type sysfs (rw) devpts on /dev/pts type devpts (rw,gid=5,mode=620) /dev/hda1 on /boot type ext3 (rw) tmpfs on /dev/shm type tmpfs (rw) none on /proc/sys/fs/binfmt_misc type binfmt_misc (rw) /dev/mapper/VolGroupData-data on /data type ext3 (rw) [root@gamma lc]# ls /data log mysql run [root@gamma lc]# df -h Filesystem Size Used Avail Use% Mounted on /dev/mapper/VolGroup00-LogVol00 14G 4.7G 8.1G 37% / /dev/hda1 99M 22M 73M 23% /boot tmpfs 125M 0 125M 0% /dev/shm /dev/mapper/VolGroupData-data 291M 52M 224M 19% /data [root@gamma lc]# vgdisplay --- Volume group --- VG Name VolGroupData System ID Format lvm2 Metadata Areas 1 Metadata Sequence No 23 VG Access read/write VG Status resizable MAX LV 0 Cur LV 1 Open LV 1 Max PV 0 Cur PV 1 Act PV 1 VG Size 996.00 MB PE Size 4.00 MB Total PE 249 Alloc PE / Size 75 / 300.00 MB Free PE / Size 174 / 696.00 MB VG UUID MT9hzq-oL4o-MZSW-HGAs-X05w-IbMM-sFXK1r [edit] LVM Partitioning In this following example, I have a LVM Volume Group (VG) called "VolGroupData" that has about 1 GB of free space. Then I created a Logical Volume (LV) called "data" under "VolGroupData" (/dev/VolGroupData/data). The size of LV "data" is about 300MB that left 700MB under VolGroupData which is enough for snapshoting. p.s. snapshot of LV data will be the same size of LV data and will be stored on the "VolGroupData". 1. Create Physical volume pvcreate /dev/hdb 2. Create Volume Group vgcreate -s 1000m /dev/VolGroupData /dev/hdb 3. Create Logical Volumn under VolGroupData lvcreate -L 300m -n data /dev/VolGroupData