Easy Database Backups with Zmanda Recovery Manager for MySQL

Recently, I had a chance to test the community edition of Zmanda Recovery Manager for MySQL. I was partly testing to make sure it worked with MariaDB, Monty Program's drop-in replacement for MySQL, but I also was testing to see whether it would work well for our own database backups.

Monty Program has arguably the most in-depth knowledge of the MySQL codebase on the planet. But apart from some large servers we use for performance and other testing, our actual database usage and needs are similar to many other small- to medium-size companies. For our Web sites, we need only a couple small database servers. The datasets for each database are not large, a couple hundred megabytes each. But, we still don't want to lose any of it, so backups are a must.

I've long used a homegrown set of shell scripts to manage backing up our databases. They're simple and work well enough to get the job done. They lack some features that I've never gotten around to implementing, such as automated retention periods and easy restores. The setup process also is more involved than I would prefer. They get the job done, but I've always wanted something a little better, and I've never had the time to do it myself.

This is where Zmanda Recovery Manager for MySQL enters the picture. ZRM Enterprise edition was reviewed by Alolita Sharma in the September 2008 issue of Linux Journal, but I'm never very interested in Enterprise editions. They always have proprietary bits, and I've never trusted GUI tools as much as their command-line counterparts. Luckily, where there is an “enterprise” version there almost always is a “community” version lurking somewhere in the shadows.

Like many other community editions, Zmanda Recovery Manager for MySQL, Community Edition (let's just call it ZRM) lacks the “flashy bits”. Things like the graphical “console” application, Windows compatibility, 24x7 support and other high-profile features of its Enterprise sibling are missing in the Community version. But the essentials are there, and it has one big feature I like: it is fully open source (GPL v2). The key metric, however, is does it do what I need it to do?

To find out, I set up a small test environment (I didn't want to test on live, production databases) and gave it a spin. See the Setting Up a Test Environment sidebar for details on what I did prior to installing and testing ZRM.

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.

At the end of my evaluation, I decided to use ZRM for our database backups. My use case is logical backups over the network, and for those, the Open Source community edition of ZRM works very well.

I like how easy scheduling new backups and creating new backup jobs is. With Zmanda, I can configure backups for a new database server effortlessly, something that could not be said about my homegrown solution. Restores also are easy, which will be appreciated if the unthinkable happens and I need to restore from a backup. And, thanks to ZRM's use of standard tools, even if I can't restore using ZRM, the backup contains a file that I can load into the database manually either as-is (if I'm not encrypting or compressing my backups) or after a little processing using the standard gunzip and GPG tools.

Zmanda Recovery Manager for MySQL is not perfect. During my testing, I was never able to get raw backups working properly over the network, for example. Another issue, though minor, is that the man pages have formatting issues that make them hard to read. Some of the error messages are not the most informative as well, and the documentation could be improved and expanded. But, the software is built using solid open-source tools, it doesn't try to re-invent the wheel at every turn, and it works for the backups I want to do.

In the end, the thing that tipped the scales for me was that ZRM offers several things that my homegrown scripts do not. These include automatically creating a checksum for verifying that a backup is still good, faster and very customizable setup for new database servers, and easy restores. I could add all of these to my scripts, given time. But it's time I don't have at the moment, and I never seem to have enough (if you know where to find some, let me know). So despite some rough edges, I've found Zmanda Recovery Manager for MySQL, Community Edition to be a good backup solution for all my MariaDB servers.