Easy Database Backups with Zmanda Recovery Manager for MySQL

Zmanda offers packages for Debian, RPM and Solaris/OpenSolaris systems and their derivatives on its Web site. A source package also is available. Because I'm using Ubuntu 10.04, I downloaded the latest stable Debian package (mysql-zrm_2.2.0_all.deb at the time of this writing) from the ZRM download page.

ZRM requires the libxml-parser-perl and libdbi-perl packages, the mariadb-client or mysql-client package, and something that allows it to send e-mail messages (for notifying the correct people of a backup's success or failure). If you are running ZRM on the same server as your database, the Perl and client packages likely already will be installed. If you elect to do what I did and run ZRM from a dedicated backup server, these will need to be installed:

apt-get install libxml-parser-perl libdbi-perl \
                mariadb-client bsd-mailx

When mailx is installed on Ubuntu, it also will install postfix (unless you already have a different MTA installed), but other MTAs (mail transport agents) may be the default on your distribution. During the installation of the postfix package, I chose the basic “Internet site” setting, which provides just enough of a configuration to allow the server to send e-mail.

The ZRM package expects a user named “mysql” to exist. This user typically is created when MySQL or MariaDB is installed, but because my backup server has only the mariadb-client package installed, the mysql user didn't exist, so I needed to create it. I also chose to give the new user the same home directory that the user would have had if the user had been created as part of an Ubuntu mariadb-server installation:

sudo adduser --system --group --home="/var/lib/mysql" mysql

With dependencies finally out of the way, I was ready to install Zmanda Recovery Manager. I installed it like so:

dpkg -i mysql-zrm_2.2.0_all.deb

The installation itself is pretty boring, and it looks no different from any other package install:

me@backuphost:~$ sudo dpkg -i mysql-zrm_2.2.0_all.deb
Selecting previously deselected package mysql-zrm.
(Reading database ...
    42938 files and directories currently installed.)
Unpacking mysql-zrm (from mysql-zrm_2.2.0_all.deb) ...
Setting up mysql-zrm (2.2.0) ...
Updating ownership of previously backed up data sets

Processing triggers for man-db ...
me@backuphost:~$

So what did the package install? A look at the output of dpkg -L mysql-zrm reveals that the package installs several Perl scripts into the /usr/bin/ folder and creates the following directories:

The package also installs man pages for the scripts and config files, and xinetd and logrotate config files.

Now I was ready to set up some backups. ZRM uses the concept of “backup sets” to refer to backup settings for a single server or backup job. To create a new backup set, you create a new directory under /etc/mysql-zrm/ and copy the default configuration file into the new directory, like so:

cd /etc/mysql-zrm
mkdir -v backupsetname
cp -vi mysql-zrm.conf backupsetname/

The folder can have any name you want. The mysql-zrm.conf file is, by default, completely commented out. The file has inline documentation for each configuration directive, and it is pretty easy to read. For my project, I wanted compressed and encrypted logical backups, so the lines I customized and uncommented were these:

backup-mode=logical
backup-type=regular
retention-policy=30D
compress=1
compress-plugin=/bin/gzip
encrypt=1
encrypt-plugin="/usr/share/mysql-zrm/plugins/encrypt.pl"
all-databases=1
user="backup-user"
password="examplepassword"
host="db1.example.org"
mailto="my-email@example.org"

The user and password in the set of variables above is a MariaDB database user, not a system user. This user is created like other database users using the mysql command-line tool and a GRANT statement. Here's the GRANT statement Zmanda recommends:

GRANT select, show view, create view, insert, update,
      create, drop, reload, shutdown, alter, super, lock tables,
      replication client
          on *.*
          to 'backup-user'@'backuphost'
          identified by 'examplepassword';

If you set up ZRM on the host it is backing up, backuphost in the above statement would be changed to localhost. At this point, I also needed to configure one of our database servers to allow remote logins. This is done by setting the bind-address variable in the /etc/mysql/my.cnf file to the IP address of the database server and then restarting mysqld.

Backups can be either “raw” or “logical”. Raw backups are actual copies of the database files. Logical backups are a dump (using mysqldump) of the contents of your database in SQL. Raw backups can be restored only to a server running the same version of MariaDB or MySQL. Logical backups do not have this restriction and can be loaded successfully onto servers running older or newer MariaDB/MySQL versions (depending on whether the new server to which you're restoring supports the same features that the old one did).

Backup types are “regular” and “quick”. The quick type applies only to raw backups and only if your database is stored on an LVM logical volume. A raw+regular backup is a copy of your MariaDB/MySQL data files made using mysqlhotcopy. A raw+quick backup is an LVM snapshot of those data files. If you are doing a logical backup, the quick backup type is not available.

The retention-policy variable tells ZRM how long you want to keep backups. The default is 10W, which stands for ten weeks. Other suffixes you can specify include D for days, M for months or Y for years.

ZRM uses “plugins” to extend its functionality. Several plugins come with ZRM, including a couple that can be used to copy backups from a remote database server to the server running ZRM, and a plugin to encrypt backups. Some plugins are just wrapper scripts, like the encryption plugin, which is a wrapper around GPG. Other plugins are just system binaries. For example, the default “compress” plugin is just the gzip program, no wrapper script required. Any or all of these can be replaced with your own preferred solutions.

Configuration and setup varies per plugin. The encryption plugin, for example, requires the creation of a file named .passphrase in the /etc/mysql-zrm/ folder. This file contains the password used when encrypting backups. The steps I followed when creating this file are:

touch /etc/mysql-zrm/.passphrase
echo 'mysupercoolhardtoguesspassword' > /etc/mysql-zrm/.passphrase
chmod -v 700 /etc/mysql-zrm/.passphrase

Furthermore, because the encryption plugin uses GPG, the .gnupg folder needs to be present in the root user's home directory (the backups are spawned by root). It wasn't present for me, so I created it:

mkdir -v /root/.gnupg
chmod -v 600 /root/.gnupg

Finally, I was ready to perform some backups. Running a manual backup is pretty easy:

mysql-zrm-scheduler --backup-set backupsetname \
                    --backup-level 0 --now

Scheduling backups also is easy. Like running a manual backup, to schedule backups, you use the mysql-zrm-scheduler script, but instead of having the backup start “now” you set an interval and a start time, like so:

sudo mysql-zrm-scheduler --add --backup-set backupsetname \
                         --backup-level 0 \
                         --interval daily --start-time 01:00

The above backup will run every day starting at 1am. You can view the schedule with mysql-zrm-scheduler --query, or because ZRM schedules backups using cron, you simply can query the root crontab with crontab -l (running the command as root).

When you add your first schedule, ZRM also will add a cronjob for running the “purge” action for removing backups that are older than the retention period.

To check that your backup data hasn't been corrupted since the backup was made, use the mysql-zrm script with the verify-backup action:

mysql-zrm --action verify-backup --backup-set backupsetname

To view stats on recent backups, the mysql-zrm-reporter can help:

mysql-zrm-reporter --show backup-performance-info

If disaster strikes and you need to restore a backup to a server, the first thing is to determine the location of the most recent successful backup, and then to use it. The mysql-zrm-reporter script is an easy way to reveal the location:

mysql-zrm-reporter --show restore-info \
                   --where backup-set=backupsetname

In the output, look for the backup_directory of the most recent backup where the backup_status is “Backup succeeded”. The backup_directory path will look something like this:

/var/lib/mysql-zrm/backupsetname/20100607141122

With this information, you can perform a restore, like so:

backup_dir=/var/lib/mysql-zrm/backupsetname/20100607141122
mysql-zrm-restore --backup-set backupsetname \
                  --source-directory $backup_dir

Expect restores to take a while, depending on the size of your database. In my testing setup, after the restore completed, I verified the data as described in the Setting up a Test Environment sidebar, and everything checked out.