Easy Database Backups with Zmanda Recovery Manager for MySQL

 in
Zmanda Recovery Manager makes it easy to dump your database and that homegrown backup solution you've been using and meaning to replace.
Installing and Using 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:

  • /usr/share/mysql-zrm — a “plugins” folder with several Perl scripts inside.

  • /usr/share/doc/mysql-zrm — various docs and README files.

  • /usr/lib/mysql-zrm — various Perl modules.

  • /etc/mysql-zrm — configuration files.

  • /var/log/mysql-zrm — empty directory for log files.

  • /var/lib/mysql-zrm — the folder where backups go (initially empty).

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.

______________________

Comments

Comment viewing options

Select your preferred way to display the comments and click "Save settings" to activate your changes.

A few questions...

Mike Diehl's picture

Daniel,

I enjoyed reading your article and hope that ZRM will solve a problem I have.

I sometimes have the desire to restore just a single table from a backup set, particularly, if I can restore it into a new table. Do you know if Zmanda can do that?

On the website, I see a few screenshots of a scheduling utility. I assume that I'm looking at the Enterprise version, right?

Is it safe to assume that with Zmanda, I'll still be using shell script, but the tools will just be better?

Finally, would you like to take a few sentences to make a case for me to try MariaDB? I currently have a MM replicated Mysql DB.

Thanks,
Mike Diehl.

Mike Diehl is a freelance Computer Nerd specializing in Linux administration, programing, and VoIP. Mike lives in Albuquerque, NM. with his wife and 3 sons. He can be reached at mdiehl@diehlnet.com

White Paper
Linux Management with Red Hat Satellite: Measuring Business Impact and ROI

Linux has become a key foundation for supporting today's rapidly growing IT environments. Linux is being used to deploy business applications and databases, trading on its reputation as a low-cost operating environment. For many IT organizations, Linux is a mainstay for deploying Web servers and has evolved from handling basic file, print, and utility workloads to running mission-critical applications and databases, physically, virtually, and in the cloud. As Linux grows in importance in terms of value to the business, managing Linux environments to high standards of service quality — availability, security, and performance — becomes an essential requirement for business success.

Learn More

Sponsored by Red Hat

White Paper
Private PaaS for the Agile Enterprise

If you already use virtualized infrastructure, you are well on your way to leveraging the power of the cloud. Virtualization offers the promise of limitless resources, but how do you manage that scalability when your DevOps team doesn’t scale? In today’s hypercompetitive markets, fast results can make a difference between leading the pack vs. obsolescence. Organizations need more benefits from cloud computing than just raw resources. They need agility, flexibility, convenience, ROI, and control.

Stackato private Platform-as-a-Service technology from ActiveState extends your private cloud infrastructure by creating a private PaaS to provide on-demand availability, flexibility, control, and ultimately, faster time-to-market for your enterprise.

Learn More

Sponsored by ActiveState