MySQL Backup And Recovery With mysql-zrm On Debian Sarge - Page 3

4 Backup Examples

With mysql-zrm you can maintain multiple backups, called backup sets, e.g. a daily backup, a weekly backup, a backup for your osCommerce database, a backup for your vBulletin database, etc.

Now let's create our first backup which we create in the backup set dailyrun:

mysql-zrm-scheduler --now --backup-set dailyrun --backup-level 0

This will immediatley back up all databases to the directory /var/lib/mysql-zrm/dailyrun, and it will also create the directory /etc/mysql-zrm/dailyrun. --backup-level 0 means: do a full backup (you can also do incremental backups (--backup-level 1), but I'll say something about this later on - for now we only do full backups).

If you have enabled email notifications, you should have received an email with the status of the backup now. But you can also generate a report about the backup status on the command line like this:

mysql-zrm-reporter --where backup-set=dailyrun --show backup-status-info

The output will look like this:

          backup_set  backup_date                             backup_level  backup_status         comment
----------------------------------------------------------------------------------------------------------------------
            dailyrun  Tue 26 Sep 2006 07:57:47 PM CEST                   0  Backup succeeded      ----

You can also generate a 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

Try each of them to see what information they provide.

Now we want to create a daily and a weekly backup that is run automatically by the system (i.e., without our interaction). This is how we can set up these backups:

mysql-zrm-scheduler --add --interval daily --backup-set dailyrun --backup-level 0
mysql-zrm-scheduler --add --interval weekly --backup-set weeklyrun --backup-level 0

(With the second command we have created a new backup set called weeklyrun.)

Now let's check our scheduled backups:

mysql-zrm-scheduler --query

The output looks like this:

Logging to /var/log/mysql-zrm/mysql-zrm-scheduler.log
0 3 * * * /usr/bin/mysql-zrm --action backup --destination /var/lib/mysql-zrm --backup-set dailyrun --backup-level 0
0 4 * * * /usr/bin/mysql-zrm --action purge --destination /var/lib/mysql-zrm
0 2 * * 0 /usr/bin/mysql-zrm --action backup --destination /var/lib/mysql-zrm --backup-set weeklyrun --backup-level 0

As you can see, the daily backup is run each day at 03:00h, and the weekly backup is run each Sunday at 02:00h.

(BTW, instead of

mysql-zrm-scheduler --query

you could as well run

crontab -l

to get the same information.)

If you want to start the daily backup at another time, e.g. 13:35h, you can do it like this:

mysql-zrm-scheduler --add --interval daily --backup-set dailyrun --start 13:35 --backup-level 0

To remove a scheduled backup from cron, you can do it like this:

mysql-zrm-scheduler --delete --interval weekly

This would remove the weekly backup run on Sundays at 02:00h from cron. If you want to remove the daily backup that is scheduled for 13:35h, you can do it like this:

mysql-zrm-scheduler --delete --interval daily --start 13:35

But you can as well run

crontab -e

to edit your cron jobs which is sometimes easier than dealing with mysql-zrm-scheduler.

5 Customized Reports, HTML Reports

In chapter 4 I have already mentioned what kind of reports are available. But you can also generate customized reports, i.e., you can specify which columns/information you want to see. For example,

mysql-zrm-reporter --fields backup-set,backup-date,backup-level,backup-status --where backup-set=dailyrun

generates a report for the backup set dailyrun that shows the columns backup-set, backup-date, backup-level, and backup-status:

          backup_set  backup_date                             backup_level  backup_status
------------------------------------------------------------------------------------------------
            dailyrun  Tue 26 Sep 2006 07:57:47 PM CEST                   0  Backup succeeded
            dailyrun  Tue 26 Sep 2006 07:58:08 PM CEST                   0  Backup succeeded
            dailyrun  Tue 26 Sep 2006 07:58:31 PM CEST                   0  Backup succeeded
            dailyrun  Tue 26 Sep 2006 08:24:04 PM CEST                   0  Backup succeeded

A list of all columns is available on http://mysqlbackup.zmanda.com/index.php/What_information_can_be_obtained_from_a_backup_report%3F.

mysql-zrm lets you also create HTML reports. Let's assume you have a web server (e.g. Apache) installed on your system with the document root /var/www. Now run

mysql-zrm-reporter --show backup-status-info --where backup-set=dailyrun --type html --output /var/www/backup-status-dailyrun.html

This creates the HTML file backup-status-dailyrun.html in your /var/www directory that you can now access in your browser (e.g. http://server1.example.com/backup-status-dailyrun.html):