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:
/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="email@example.com"
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:
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.
- Synchronize Your Life with ownCloud
- Days Between Dates?
- A GUI for Your CLI?
- Tech Tip: Really Simple HTTP Server with Python
- An Introduction to OpenGL Programming
- Download "Linux Management with Red Hat Satellite: Measuring Business Impact and ROI"
- RSS Feeds
- Ubuntu & SUSE & CentOS, Oh My!
- Returning Values from Bash Functions
- The Only Mac I Use
Editorial Advisory Panel
Thank you to our 2014 Editorial Advisors!
- Jeff Parent
- Brad Baillio
- Nick Baronian
- Steve Case
- Chadalavada Kalyana
- Caleb Cullen
- Keir Davis
- Michael Eager
- Nick Faltys
- Dennis Frey
- Philip Jacob
- Jay Kruizenga
- Steve Marquez
- Dave McAllister
- Craig Oda
- Mike Roberts
- Chris Stark
- Patrick Swartz
- David Lynch
- Alicia Gibb
- Thomas Quinlan
- Carson McDonald
- Kristen Shoemaker
- Charnell Luchich
- James Walker
- Victor Gregorio
- Hari Boukis
- Brian Conner
- David Lane