Full & Incremental MySQL Backup via Mysql-ZRM

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