Introduction

Responding to growing demand for a professional level backup and recovery solution, Zmanda, a leading vendor for open source backup solutions, has introduced an Ubuntu server version of their Zmanda Recovery Manager (ZRM) for MySQL.

At LinuxWorld 2008 in San Francisco, 451 Group analyst Jay Lyman observed that Ubuntu will continue to gain acceptance in the enterprise as organizations increasingly use free, community-driven Linux distributions and grow their own in-house Linux expertise. As a bellwether example, Wikipedia has switched its 400 servers to Ubuntu.

MySQL is equally gaining in importance to the enterprise. This same Wikipedia is also powered by MySQL. Acquired by Sun in late 2007, MySQL has been propelled further and further into the big leagues. As larger enterprises adopt Ubuntu together with MySQL, the need for a robust, well integrated database backup solution only grows.

To meet the widest possible range of backup solution requirements, Zmanda offers three versions of ZRM for MySQL on Ubuntu: the ZRM Enterprise edition, the ZRM Cluster edition and a ZRM Community edition. In this article, we'll take a tour of the features in the latest release of ZRM Enterprise edition 3.0 for Ubuntu Server. We'll focus on the product's core capabilities for database backup and recovery and then we'll look at some new features -- application configuration backup, full-text search in ZRM's management console (ZMC) and support for 64-bit platforms.

What is Zmanda Recovery Manager (ZRM)

ZRM Enterprise edition 3.0 brings a feature-complete backup and recovery solution for MySQL databases to Ubuntu Server. In this section, we look at the major components of ZRM and then survey the most important product features. We'll also describe the support options available for ZRM.

ZRM Enterprise edition 3.0 includes the following three components: Zmanda Management Console, a command line interface and a plugin framework.

Figure 1: ZMC Login screen

Zmanda Management Console (ZMC) --

ZMC is a web-based console that provides an easy to use GUI wizard for essential backup and recovery tasks. ZMC controls reporting and monitoring for all tasks in ZRM. An interface with Zmanda Network enables easy access to the latest product documentation and context-sensitive help.

Core functions such as "Backup", "Monitor", "Report", "Admin", and "Restore" are presented as a tabbed interface in ZMC. The Backup and Restore tabs break down the respective processes into individual screens. For example, in the Backup tab you'll see links for each step of the backup process labeled "what", "where", "when", "how", "summary", respectively. As the user navigates through each screen, they fill in the necessary information and proceed to schedule or run the backup. Similarly, in the Restore tab, links for "what", "where", "how", and "restore" are displayed at the top. The user navigates each screen to complete the recovery process. The other tabs -- "Monitor", "Report", "Admin" -- follow the same pattern, with links for related tasks under each tab.

Context-sensitive help is available throughout the ZMC by clicking on the question mark icon at the top left corner of each screen. Also, common error messages are hyperlinked to the Zmanda Network knowledge base, where you can find detailed information about the error message and how to resolve it.

Command Line Interface (CLI) --

The CLI provides an alternate scriptable interface to the major functionality of ZRM. Using the CLI tools, ZRM can be integrated with existing backup and recovery systems.

Plugin Framework --

ZRM's capabilities can be extended with plugins. Pre-backup and post-backup plugins execute custom tasks before or after backup jobs. Copy plugins are used to specify the data transfer mechanism (e.g., ssh, socket) between the ZRM backup server and remote MySQL server. Filesystem snaphot plugins support NetApp SnapManager, Solaris ZFS and Veritas VxFS. Also, storage volume snapshot plugins support Linux LVM, Windows VSS, and EMC CLARiiON SnapView. Note that plugins for NetApp SnapManager, Veritas VxFS and EMC CLARiiON SnapView must be purchased separately. Other plugins are available for dynamic rescheduling of backups and binary log parsing.

Core Features

ZRM's core features are database backup and recovery, monitoring, and running pre-defined and custom reports.

Backup

ZRM is a flexible platform that lets you to backup multiple databases and tables from one or more MySQL servers.

ZRM offers a variety of options for scheduling backups. You can schedule backups during planned downtime or run instantaneous hot backups without taking your MySQL server offline. You can request daily, weekly, and monthly backups from the "Backup Schedule" panel under the "Backup / When" screen of the ZMC. It is also possible to schedule hourly backups by defining multiple daily jobs. Once defined, backup schedules can be modified as needed, for example, to avoid database peak usage periods by postponing a backup job using the pre-scheduler plugin.

ZRM handles multiple storage engines including MyISAM, InnoDB, NDB, Archive, and Falcon. Depending on the storage engine being used by your database table, ZRM automatically selects the appropriate backup method. If you are using the InnoDB hotbackup tool from Oracle, ZRM provides seamless integration with a management console for the tool. ZRM has been tested with MySQL versions 4.1.x and 5.x. To support legacy database installations, ZRM can also work with MySQL 4.0.

For large databases you may want to use ZRM's raw backup feature. Raw or physical backups are exact copies of your data. This shortens the time needed for backups as well as for restores. Calculating storage requirements ahead of time is easier since raw backups are just a copy of your database files. However, the major drawback of raw backups is that they can only be restored to the identical MySQL server version and machine architecture used to produce the original backup.

ZRM takes advantage of filesystem and storage volume snapshots when performing raw backups. Snapshots capture changes to your data at a given point in time without having to copy the entire filesystem or volume. As noted in the Plugin Framework section above, ZRM supports filesystem snapshots for NetApp SnapManager, Solaris ZFS, Veritas VxFS, as well as storage volume snapshots for Linux LVM, Windows VSS, and EMC CLARiiON SnapView.

Logical backups should be considered if you need to support multiple machine architectures and your data must be highly portable. Logical backups offer table level granularity and can be performed without taking the MySQL server offline. However, logical backups are slower because MySQL server must fetch the database structure and content and then convert the data into a logical format, usually a text file with SQL statements. Due to the conversion, storage requirements for logical backups may be larger than the size of the original data source. To offset the conversion overhead, ZRM can be configured to compress, using gzip or other compression tools, the backup images produced.

ZRM supports full and incremental backups. Full backups save a complete copy of your database or table, whereas incremental backups save only the changes since the last full or incremental backup.

Many MySQL-backed applications depend on custom configuration files. ZRM can back up these files along with application data stored in the database for quick recovery of the entire application. Examples of such applications include Wordpress, SugarCRM, and MediaWiki. In ZRM 3.0, this feature is available by manually editing the ZRM backup set configuration file mysql-zrm.conf.

To protect sensitive data, ZRM can perform secure backups of remote MySQL servers by using standard SSL encryption over-the-wire as well as by using on-disk GPG encryption. ZRM ensures backup integrity with checksums of the backup image.

Recovery

ZRM provides continuous data protection (CDP) by integrating filesystem snapshots as well as by tracking MySQL database binary logs. CDP backup captures all changes to your data to enable fast, point-in-time data recovery. ZMC performs point-in-time recovery using selective incremental restores derived from MySQL binary log positions. Alternatively, the user can specify a recovery point (e.g., 2008-12-23 11:15:32) via the "Restore" tab of the ZMC.

ZRM offers a fine granularity of control over data recovery ranging from the entire MySQL server, database, table, down to individual transactions.

The ZMC Visual Log Analyzer (DB events viewer) parses MySQL binary logs to drive an easy-to-use GUI for browsing the contents of incremental backups, identifying recovery points (e.g., specific transactions), and monitoring operational and security aspects of your database. Starting with ZRM 3.0, the keyword search feature in Visual Log Analyzer takes advantage of MySQL full-text searching to provide accurate results more quickly.

Reporting

ZRM offers a diverse range of reports to help you stay informed about the status of your backup and recovery jobs. Automated reports generated after each backup job summarize the status of the job with useful information such as backup type (e.g., logical or raw), backup level (e.g., full or incremental), performance statistics, and backup image location. If configured, ZRM will send backup reports via email in HTML or text format. Backup reports available via RSS can be used to show backup status within other applications. In addition to automated backup summary reports, multiple pre-defined reports provide commonly requested information about backup jobs. Each pre-defined report can be customized with over 30 backup and MySQL data fields to display information specific to your needs.

Product Support Options

Zmanda provides three levels of support (Basic, Standard, and Premium) for ZRM Enterprise edition customers. All support levels come with access to the Zmanda Network resources, security updates and feature upgrades, and with web-based technical support. Standard and Premium support levels offer telephone technical support with unlimited support incidents. Premium support adds 24x7 technical support and guarantees a 3-hour incident support response time. Annual support pricing is based on the number of MySQL server instances being handled by ZRM.

Conclusion

ZRM 3.0 for MySQL on Ubuntu brings a polished, easy-to-use and full-featured, professional backup and recovery solution to Ubuntu, today's most popular Linux distribution.

The Web-based management console lets you schedule and control backup and recovery tasks from the comfort of your favorite browser. There's also a command line if you need it. Plugins let you extend ZRM. All the major MySQL storage engines are supported. There are also high performance options available, like raw backups and file system snapshots. Other features let you back up your application's configuration files along with the databases it uses.

More advanced recovery facilities include CDP and fine grained, transaction level data recovery which can be initiated by an easy-to-use GUI log analyzer and events viewer. Details about backup and recovery jobs are easily tracked through pre-defined and ad-hoc reports. Finally, ZRM is bundled with a variety of customer assistance options ranging from Web to e-mail to telephone support.

With the release of ZRM 3.0, Zmanda provides features and support that meet the increasing needs of the enterprise for professional backup and recovery of MySQL on Ubuntu.

Review: Taking ZRM 3.0 for a spin on Ubuntu Server

In this section we present a hands-on try-out of the most important features of ZRM 3.0 on Ubuntu. We ran a series of backups and restores using a MySQL database for a popular multi-user WordPress blog. The primary database table (wp_posts) contained approximately 500K rows, averaging 2Kb per row. In order to run backups of our WordPress database, we first set up a ZRM backup server, as well as created a MySQL user account on our MySQL server host. The hardware and software configuration we used is shown in Table 1.

Table 1: Hardware / software configuration for ZRM 3.0 test drive

	tahoe: ZRM Backup Server (w/ Local MySQL instance)	bigsky: ZRM Backup Client (Remote MySQL Instance)
Hostname	tahoe	bigsky
MySQL Server	Community Edition v5.0.51a	Community Edition v5.0.67
Operating System	Ubuntu Server 8.04.1	Ubuntu Server 8.10
Processor	Intel Core 2 Duo 3Ghz	Intel Atom N230 1.6Ghz
Memory	8Gb	2Gb
Storage	1Tb RAID 5	750Gb SATA

Installing Zmanda Recovery Manager

ZRM Enterprise edition provides two binary installation packages -- ZRM server rapid installer (ZRM-enterprise-3.0-installer.bin) and ZRM client (mysql-zrm-enterprise-client_3.0-1_all.deb). First we will discuss the ZRM server installation, and then describe the ZRM client installation.

ZRM Server Installation

Figure 2: ZMC Rapid Installer

The rapid installer is a graphical interface which steps you through installation of the server software components: the ZRM server, Zmanda Management Console and its dependencies, and ZRM command-line tools. To launch the installer, we ran the following commands on our ZRM backup server host (tahoe).

root@tahoe:~# chmod +x ZRM-enterprise-3.0-installer.bin
root@tahoe:~# ./ZRM-enterprise-3.0-installer.bin

The installation process takes only a few minutes, during which the user must select how the Zmanda Management Console web server is accessed, whether by http or by https. The installation process ends with a dialog box prompting the user to launch the ZMC.

To complete the ZRM server installation, we downloaded the Zmanda License Key file (zmanda_license) and placed it in '/etc/zmanda/zmanda_license'. We set file permissions to 644 (chmod 644 /etc/zmanda/zmanda_license) and changed file ownership to 'root' (chown root /etc/zmanda/zmanda_license).

ZRM Client Installation

The ZRM client for Ubuntu is a Debian package that you install using standard package management tools such as dpkg. The ZRM client should be installed if the MySQL server being backed up (ZRM backup client) is running on a separate machine from the ZRM backup server and you want to run raw backups. To install the ZRM client, we executed the following command on our MySQL server host (bigsky).

root@bigsky:~# dpkg -i mysql-zrm-enterprise-client_3.0-1_all.deb 

Note that we configured the 'mysql' system user on our MySQL server host (bigsky) with sudo privileges by editing the '/etc/sudoers' file and adding a line 'mysql: ALL=(ALL) NOPASSWD: ALL'. This grants the 'mysql' user account permission to execute LVM system commands, necessary for Linux LVM-based filesystem snapshots.

Then we created a MySQL database user which ZRM used for backup and recovery on our MySQL server host (bigsky). Listing 2 shows the SQL statements we used to create the user account and grant privileges.

Listing 2: SQL statements to create user account and setup privileges

mysql> GRANT LOCK TABLES, SELECT, FILE, CREATE, DROP, INDEX, SHUTDOWN, ALTER, INSERT, ALTER ROUTINE, CREATE ROUTINE, SUPER, RELOAD ON *.* TO 'zrmbackup'@'tahoe.local.pri' IDENTIFIED BY 'somepassword';
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT LOCK TABLES, SELECT, FILE, CREATE, DROP, INDEX, SHUTDOWN, ALTER, INSERT, ALTER ROUTINE, CREATE ROUTINE, SUPER, RELOAD ON *.* TO 'zrmbackup'@'bigsky.local.pri' IDENTIFIED BY 'somepassword';
Query OK, 0 rows affected (0.00 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

We set up SSH for login without passwords by using public keys for the 'mysql' system user on our MySQL server host (bigsky). This enabled the use of SSH connections, by the default SSH copy plugin, between the ZRM backup server (tahoe) and our MySQL server host (bigsky) for backup and restore tasks.

Doing backup and restore

The steps below are listed out in detail to serve as an example of how backups and restores can be performed with ZRM on Ubuntu.

Backup --

Backup Level: Full, Backup Mode: Raw

1. Create backup set -- We created a new backup set using the "sets" screen within the "Admin" tab. In the "Create backup set" panel, we selected a ZRM userid from the "Backup Set Owner" drop-down list (admin). We then entered a "Backup Set Name" to identify this backup set (wordpress_backup). Finally, we saved the backup set configuration by clicking the "Add" button.
2. Configure backup set -- We clicked the "Backup" tab. Doing so gets us to the screen labeled "what".
1. what -- We filled in values for each panel -- "MySQL Server Parameters", "MySQL User Parameters", and "What to backup". Then clicked the "Save" button.
1. MySQL Server Parameters -- select the "Server Type" (MySQL server), "Connection Type" (Port), "Port Number" (3306), "Host" (bigsky.local.pri), "MySQL Client Utilities Path" (/usr/bin)
2. MySQL User Parameters -- "Username" (zrmbackup), "Password" (somepassword), "SSL Options" (leave blank)
3. What to backup -- "Backup Source Type" (Specific Database(s)), "Select Database(s)" (mark checkbox next to wordpress_db).
2. where -- In the "Backup Where Parameters" panel, we filled in values for "Destination Directory" (/var/lib/mysql-zrm), "Temporary Directory" (/tmp), and "Retention Policy" (2 weeks). Then clicked the "Save" button.
3. when -- In the "Backup Schedule" panel, we filled in values for "Backup Level" (Full), "Time Range" (daily), and "Backup Time" (10Hr 15Mins). Then clicked the "Add Schedule" button to save these settings.
4. how -- Although we did not modify the default values on this screen, you should double-check values for "Backup Mode", "SSH User", and "Email Address" (if you are using email notification).
3. Application configuration file backup -- As part of this backup set, we wanted to preserve the WordPress configuration file 'wp-config.php'. ZRM requires an intermediate text file that lists the full path to each configuration file to be backed up, separated by newlines. Therefore, we created '/etc/mysql-zrm/wordpress_backup/app_conf_files_list', with the full path to wp-config.php on the first line. Next, we edited the ZRM backup set configuration file '/etc/mysql-zrm/wordpress_backup/mysql-zrm.conf' (on tahoe) and added the line 'config-file-list="/etc/mysql-zrm/wordpress_backup/app_conf_files_list"'. Note that you can also dynamically generate the list of application configuration files using a pre-backup plugin script available from the Zmanda network.
4. With the steps above we had now scheduled a daily full raw backup of all the tables in the 'wordpress_db' database every morning at 10:15am.

Figure 3: ZMC Backup screen

Backup -- Backup Level: Incremental, Backup Mode: Raw

Before running an incremental backup, we added some blog posts to our running WordPress instance, in effect modifying the underlying database.

1. Configure backup set -- Using the "Backup Set" drop-down list at the top left corner of the ZMC, we selected "wordpress_backup". Doing so displays the screen labeled "what" within the "Backup" tab. Next, we scheduled another backup job via the screen labeled "when". In the "Backup Schedule" panel, we filled in values for "Backup Level" (Incremental), "Time Range" (daily), and "Backup Time" (12Hr 00Mins). Then we clicked the "Add Schedule" button.

Monitoring

As we ran on-demand backups, we were able to track their progress using the monitoring feature available via the "Monitor" tab of the ZMC. The "Backup Run Details" pane is updated live as each phase of the backup completes.

Restore

1. Identify recovery point -- Using the "db events" (viewer) within the "Report" tab of the ZMC, we clicked on the backup timestamp (TimeStamp: 21:53:24) hyperlink to begin configuring the details of the restore process. Clicking the timestamp hyperlink displayed the screen labeled "what" within the "Restore" tab.
2. Configure restore settings
1. what -- Information on this screen notified us that all databases would be restored from the last successful full backup. We clicked the "Next Step" button at the bottom to proceed further.
2. where -- We reviewed the connectivity and user settings for the MySQL server to which the data would be restored. All the default values were left unchanged. We then clicked the "Next Step" button to proceed further.
3. how -- The settings on this screen such as "Temporary Directory", "MySQL Shutdown Options", and "Copy Plugin Parameters" specify how your data is restored. The default values were left unchanged. We clicked the "Next step" button.
4. restore -- Once we reviewed the restore settings on this screen, we clicked the "Restore" button to actually start the process.
3. In a few easy steps we were able to successfully restore the WordPress database from a full raw backup.

Figure 4: ZMC Restore screen

Reports

After running numerous backups and restores, the pre-defined reports we found most useful include the "Backup Status Report" and the "Application Impact Report".

Application Impact Report -- Provides backup job statistics such as "Time Taken", "Read Locks Time", and "Flush Logs time" -- all of which let you tune your backup schedules to avoid database peak usage periods.

Backup Status Report -- Gives you an at-a-glance status overview for every backup job that has been run. This can prove very useful for quickly tracking down error conditions across all your backup jobs.

In Summary

Our exercise to backup and restore a moderately sized WordPress MySQL database has demonstrated that Zmanda Recovery Manager 3.0 is a capable MySQL backup and recovery solution for the enterprise Ubuntu user. Whether you are a system administrator or a seasoned DBA, you can easily use ZRM's friendly and functional GUI to schedule and run your MySQL data backups.

Resources

• Zmanda Recovery Manager http://zmanda.com/backup-mysql.html
• ZRM Wiki http://mysqlbackup.zmanda.com/