mysql-zrm is short for Zmanda Recovery Manager for MySQL. It lets you create full logical or raw backups of your databases , generate reports about the backups, verify the integrity of the backups, and recover your databases. It can also send email notifcations about the backup status, and you can implement multiple backup policies.
This procedure was successfully tested and implemented on CentOS 5.X and RHEL 5.X servers.
Pre-requisites:
1.mysql client, MySQL server 4.1 and above
2.perl-DBI and perl-XML-Parser
yum install perl-DBI perl-XML-Parser
Installation:
1.Download mysql-zrm on http://www.zmanda.com/download-zrm.php
2.Get the RPM
wget http://www.zmanda.com/downloads/community/ZRM-MySQL/2.1.1/RPM/MySQL-zrm-2.1.1-1.noarch.rpm
3.Install
rpm -ivh MySQL-zrm-2.1.1-1.noarch.rpm
-The executable files have been moved to /usr/bin, the configuration files are in /etc/mysql-zrm
Important executable files are:
mysql-zrm
mysql-zrm-reporter
mysql-zrm-scheduler
4.Create a backup user in MySQL
mysql>GRANT ALL ON *.* TO ‘backup_user’@localhost IDENTIFIED BY ‘backup_pass’;
mysql> flush privileges;
Configuration:
Main configuration file is /etc/mysql-zrm/mysql-zrm.conf.
Fill important configuration:
vi /etc/mysql-zrm/mysql-zrm.conf
1.Backup level, use 0 for full, 1 is incremental,default is full backup
backup-level=0
2.Backup method,either “raw” or “logical”.use logical
backup-mode=logical
3.Backup type,use regular
backup-type=regular
4.Destination, directory where backups will be stored, default is /var/lib/mysql-zrm. You can specify the location here
destination=/mysql-backup
5.Retention policy, specify in days (D), week(W),months (M) or years(Y)
retention-policy=6M
6.If mysql zrm is done on replication slave, set replication to 1
replication=1
7.Set compression using gzip
compress=1
8.Set all databases of backup set
all-databases=1
9.Specify list of databases ,multiple database separated by space.
databases=database1 database2 database3
10.Set MySQL user parameter
user=backup_user
11.Set MySQL pass
password=backup_pass
12.Host of MySQL server
host=localhost
13.Set mailto adddress
mailto=email@freelinuxtutorials.com
14.Set backup reports
html-reports=backup-status-info
15.Directory where the reports will be created
html-report-directory=/var/www/html/mysql-zrm/reports/
Here’s what it looks like: (mysql-zrm.conf)
backup-level=0
backup-mode=logical
backup-type=regular
destination=/mysql-backup
retention-policy=6M
compress=1
all-databases=1
user=backup_user
password=backup_pass
host=localhost
mailto=email@freelinuxtutorials.com
html-reports=backup-status-info
html-report-directory=/var/www/html/mysql-zrm/reports/
Testing:
Create first backup in backup set dailyrun. This will immediatley back up all databases to the specified directory and will also create directory $backupdir/dailyrun.
#mysql-zrm-scheduler –now –backup-set dailyrun –backup-level 0
Email notification will be sent out as well with the status of backup.
Reporting:
To generate a report about the backup status on the command line , execute:
#mysql-zrm-reporter –where backup-set=dailyrun –show backup-status-info
You can also generate few other reports with the following commands:
mysql-zrm-reporter –where backup-set=dailyrun –show backup-method-info
mysql-zrm-reporter –where backup-set=dailyrun –show backup-retention-info
mysql-zrm-reporter –where backup-set=dailyrun –show backup-performance-info
mysql-zrm-reporter –where backup-set=dailyrun –show restore-full-info
mysql-zrm-reporter –where backup-set=dailyrun –show restore-incr-info
mysql-zrm-reporter –where backup-set=dailyrun –show replication-info
Scheduling:
To schedule daily incremental and weekly full backups, set this:
#mysql-zrm-scheduler –add –interval weekly –backup-set weeklyrun –backup-level 0
#mysql-zrm-scheduler –add –interval daily –backup-set dailyrun –backup-level 1
Check:
# mysql-zrm-scheduler –query
schedule:INFO: ZRM for MySQL Community Edition – version 2.1.1
Logging to /var/log/mysql-zrm/mysql-zrm-scheduler.log
0 2 * * 0 /usr/bin/zrm-pre-scheduler –action backup –backup-set weeklyrun –backup-level 0 –interval weekly
0 3 * * * /usr/bin/zrm-pre-scheduler –action backup –backup-set dailyrun –backup-level 1 –interval daily
Or use “crontab -l” to get same information
If you want to start your daily backup at different time, let say at 1:30AM
mysql-zrm-scheduler –add –interval daily –backup-set dailyrun –start 1:30 –backup-level 1
Customized HTML Reports
To create HTML file backup-status-dailyrun.html in /var/www/html/mysql-zrm,execute this:
#mysql-zrm-reporter –show backup-status-info –where backup-set=dailyrun –type html –output /var/www/html/mysql-zrm/backup-status-dailyrun.html
Verifying Backups:
To check integrity of backup set in dailyrun, use:
#mysql-zrm –action verify-backup –backup-set dailyrun
Output something like this:
Use of uninitialized value in concatenation (.) or string at /usr/bin/mysql-zrm line 1305.
INFO: mysql-zrm-version
INFO: Verification successful
Data Recovery
1.To restore data from our MySQL backups, run this command first to find which backups are available.
2. If incremental in daily,
#mysql-zrm-reporter -show restore-info –where backup-set=dailyrun
Sample:
# mysql-zrm-reporter -show restore-info –where backup-set=dailyrun
REPORT TYPE : restore-info
backup_set backup_date backup_level backup_directory backup_status comment
—————————————————————————————————————————————————–
dailyrun Thu 14 Jan 2010 03:00:02 1 /mysql-backup/dailyrun/20100114030002 Backup succeeded —-
AM SGT
dailyrun Wed 13 Jan 2010 05:35:53 1 /mysql-backup/dailyrun/20100113173553 Backup succeeded —-
PM SGT
dailyrun Wed 13 Jan 2010 05:35:24 1 /mysql-backup/dailyrun/20100113173524 Backup succeeded —-
PM SGT
dailyrun Wed 13 Jan 2010 05:34:48 0 /mysql-backup/dailyrun/20100113173448 Backup succeeded —-
PM SGT
Restore:
If we want to restore the incremental backup 20100113173553, then issue the command.
#mysql-zrm –action restore –source-directory /mysql-backup/dailyrun/20100113173553 –backup-set dailyrun
3.If full backup need to restore, first verify the full backup
#mysql-zrm-reporter -show restore-info –where backup-set=weeklyrun
Sample Output:
# mysql-zrm-reporter -show restore-info –where backup-set=weeklyrun
REPORT TYPE : restore-info
backup_set backup_date backup_level backup_directory backup_status comment
—————————————————————————————————————————————————–
weeklyrun Wed 13 Jan 2010 05:56:51 0 /mysql-backup/weeklyrun/20100113175651 Backup succeeded —- PM SGT
Restore:
#mysql-zrm –action restore –source-directory /mysql-backup/weeklyrun/20100113175651 –backup-set weeklyrun