Digg this article
There are several methods to do live MySQL database backups. These methods are either storage engine specific (InnoDB hot backup, mysqlhotcopy), or require read locks (mysqldump), or require additional hardware (backup using replication, LVM snapshot). Some of these backup methods can do backups of remote MySQL servers also. So far, there is no backup method that provides storage engine agnostic, consistent full backups of local and remote servers. Goal of Zmanda Recovery Manager (ZRM) of MySQL is to consolidate all these and future methods of MySQL backup and use the optimal method for the MySQL configuration.
One of the exciting developments in MySQL is the development of MySQL Online backup. The functional specification for the MySQL Online Backup APIs are available in MySQL forge and currently, the initial implementation of ARCHIVE storage engine backup and recovery is available in the bitkeeper source tree.
The first implementation of MySQL Online backup will provide consistent, full backups for transactional as well as non-transactional tables while maintaining referential integrity. Databases with tables using different storage engines can be backed up in a consistent manner. The backup operation will be non-blocking for DML statements and blocking for DDL statements. The backup archive format has been designed to support incremental and differential backups and granular backups in future.
The MySQL Online Backup implementation involves implementation of backup interfaces for storage engines, MySQL server internal interface to backup, MySQL SQL commands for management and interface to integrate with external management products . Actual backup is implemented by the storage engine. The version 1.0 will not allow restoration of selected databases/tables from the backup image. Version 1.0 will have BACKUP and RESTORE SQL commands and support for XBSA is likely to be added in later releases. The following table compares between current MySQL full backup methods and MySQL Online Backup: (Thanks to Sheeri Kritzer for the table comparing current MySQL backup methods)
|Method||No Locking||DDL||Snapshot||Remote||Free||All Engines||All Tables||Text File||Recover
|SELECT . . .
|OS level copy||No||Yes||No||No||Yes||No||Yes||No||Yes||5|
Note: Lower “#No” (last column in the table) is good.
Using MySQL Online Backup for full backups in conjunction with MySQL binary logs for incremental backups (and point in time recovery) will provide a total backup solution for MySQL databases. ZRM for MySQL will implement MySQL online backup API. In addition to implementing MySQL online backup API, ZRM for MySQL provides enterprise features - backup encryption, compression, verification, scheduling and reporting. Addition of MySQL online Backup as one of the backup methods provided by ZRM for MySQL will be a significant enhancement for the enterprise users.
I will be talking about MySQL Online Backup and ZRM for MySQL at MySQL Camp at Google, Mountain View on Saturday, Nov 11 at 11am PT. Since you have read this far, you must be interested in this topic. Please participate in the MySQL camp and join us in the MySQL backup session.