Simple Backup Server

I have not written an article in a while, I partially blame it on the World Cup and my day job. The time has come to share some of my recent experiences with a neat project to provide several teams internally with current MySQL backups.

When faced with these types of challenges is my first step is to look into OSS packages and how can they be combined into an actual solution. It helps me understand the underlying technologies and challenges.

ZRM Backup

I have reviewed Zmanda's Recovery Manager for MySQL Community Edition in the Fall 2008 issue of MySQL magazine. It remains one of my favorite backup tools for MySQL since it greatly simplifies the task and configuration of MySQL backups taking care of most of the details. Its flexible reporting capabilities came in handy for this project as I'll explain later. Some of the key settings:

We included the hostname in the ZRM backup-set to make it easier to locate. Linux shell example:

export BKUP_SET=`hostname -s`-logical

Following ZRM conventions, generate a HTML report in the main backup directory.

mysql-zrm-reporter --where backup-set=$BKUP_SET --type html \
   --show backup-status-info >/dir/to/backup/$BKUP_SET/report.html

The actual backup files live under the subdirectories:

/dir/to/backup/$BKUP_SET/<timestamp>/

where /dir/to/backup could be mounted on a NFS server

Please check the ZRM for MySQL documentation for details on its configuration and operation. Use the report format that best suits your needs, ZRM provides plenty of options and if none fits your needs exactly, you can still generate your own.

lighttpd HTTP Server

As a web server, lighty adds very little overhead so it can run on the same MySQL server and the backups can be served directly from it. If this is not acceptable and the backups are stored in an NFS volume, lighty can be installed on the NFS server, the configuration will remain very similar to the one I describe here.

For this example I’ll assume that the MySQL server host is named dbhost, in which case the /etc/lighttpd/lighttpd.conf file should include:

server.modules  = ( 
                                "mod_alias",
                                "mod_access",
                                "mod_accesslog" )

## Default root  directory set to the main backup set
server.document-root = "/dir/to/backup/dbhost-logical/"

## Set one alias per  backup type
alias.url            = ( "/logical/" => "/dir/to/backup/dbhost-logical/")
alias.url           += ( "/incremental/" => "/dir/to/backup/dbhost-incremental/")

## Enable logs for  diagnosis
server.errorlog      = "/var/log/lighttpd/error.log"
accesslog.filename   = "/var/log/lighttpd/access.log"

server.port          = 8088

## virtual directory listings enabled
dir-listing.activate = "enable"

##  enable debugging to facilitate diagnosis
debug.log-request-header    = "enable"
debug.log-response-header   = "enable"
debug.log-request-handling  = "enable"
debug.log-file-not-found    = "enable"

The server.document-root and alias.url settings should match the main directories for the ZRM backup sets.

Make sure that the user and / or group defined for the lighttpd process have proper permissions to access the backup set directory tree. The backups will be available as a directory listing when using the following URLs: http://dbhost:8088/logical/ or http://dbhost:8088/incremental/. Clicking on the report.html file in those directories (see the previous section in the article), the users have access to the backup reports and verify if any of them had errors. The files are also accessible using wget.

If you need to enable tighter security, lighty supports https and LDAP authentication the details are in its documentation and it takes less than 10 minutes to setup.

monit Monitoring

When you need a service to be running 24/7, monit is a great tool in the Linux arsenal to achieve it. It monitors a number of system and services variables and it will start, stop and/or restart any service under a number of conditions. For this POC, the goal is to keep lighty running, restarting it after an eventual crash. We are using the following configuration:

check process lighttpd
   with pidfile  "/var/run/lighttpd.pid"
   alert some@yourorg.com NOT { instance,  pid, ppid, action }
   start program = "/etc/init.d/lighttpd start" with timeout  60 seconds
   stop program = "/etc/init.d/lighttpd stop" with timeout 30 seconds
   if 2 restarts  within 3 cycles then timeout
   if failed port 8088 protocol http with  timeout 15 seconds within 3 cycles then restart

If the process crashes or port 8088 becomes unresponsive, monit will (re)start lighty automatically.

Conclusion (sort of)

Implement all these tools in a new server takes less than 1 hour. During the the first couple of implementations I learned a lot about how the packages interacted, security issues and users’ needs and expectations. As the users started looking into the solution, they also came up with newer use cases.

While we look for a more suitable solution, these free (as in freedom and beer) packages provided us with the opportunity to learn while still achieving our goals and delivering results to our users. Now we know what to look for if we decide to evaluate an open core or commercial solution.

ZRM for MySQL Issues

I really like ZRM for MySQL Community Edition for backups. I find
it very simple to install and configure and it gets the job done. It's default settings suits most installations. The challenges arise once you need to adapt it to more complex organizations. Please note that all the issues described on this article refer to Linux installations.

What If DBAs Have No sudo?

This was the 1st challenge that I ever faced. ZRM requires full root access at least during installation and in some cases, it requires some sudo permissions to be able to manage its files as well. Some organizations don't grant these permissions to the DBAs complicating the installation. When I looked into the scripts, I didn't see any reason why they wouldn't run in the user space, other than a few hard coded paths. I posted the question in the Zmanda forums and I got an answer with some instructions on how to patch the scripts. The instructions are pretty straight forward, but I haven't had a chance to test them. May be someone else could try and post the results as a comment? Just follow the link to the original post and read through the thread.

Install From RPM or Tarball?

So far I have always used the tarball since it offers the most flexibility. However, this requires to properly set up permissions for the different directories and subdirectories. Although the locations of the main files are listed in the documentation, the tarball includes a script (run automatically if you use the deb or rpm packages) that sets the ownerships properly. Example:

sudo mysql-zrm-migrate-file-ownership --user mysql --group mysql

For multi-DBA organizations, you can easily modify the script to also grant group access to the directories using chmod as needed.

More on Permissions

Once the right access permissions have been set properly, the scripts will
run OK, however a new problem arises. No matter how you set the
permissions, the directory holding the backup files will only be created with user access. With the example above it means that only the user mysql will have access, effectively leaving the DBAs with no proper access to the files and reports even if they are included into the mysql group. I haven't been alble to locate where to patch the scripts properly to avoid this issue. For now, writing a wrapper that will include the lines:chmod
-R g+rx /path/to/backups after the backup works as an easy solution.

Crontab Entries

The most traditional method to run any process periodically is to use crontab, but modern distributions are favoring the use of files in the /etc/cron* subdirectories that will be run by run-parts. Chances are your system already has some daily tasks scheduled, which can be listed using:

run-parts --list /etc/cron.daily/

The security in many of these modern systems don't allow the use of crontab anymore. In these cases the mysql-zrm-scheduler utility can't be used. In it's place you'll need to create a file in /etc/cron.d (or a wrapper script in the /etc/cron.daily sub directory) containing the proper entry in a format similar to the traditional crontab. The main difference is that you need to specified the user it will use to execute. A typical file would look like:

# /etc/cron.d/zrm-backup: crontab entries for mysql-zrm

SHELL=/bin/sh
PATH=/usr/local/sbin:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin
# add other environment variables you might need, like: PERL5LIB

30 1  * * *   mysql    mysql-zrm --action backup --backup-set backup

Conclusion

ZRM for MySQL continues to be one of my favorite MySQL tools. None of these issues are showstoppers, however the fact remains that Zmanda hasn't released any major update to these scripts in a long time and it's starting to show. If they don't do something about it, someone else will stealing their thunder. Such is the nature of Open Source.