MySQL Online Backup

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
Corruption
# No
SELECT . . .
INTO OUTFILE
Engine
Dependent
No No Yes Yes Yes No Yes Yes 3-4
mysqldump No Option No Yes Yes Yes Yes Yes Yes 2-3
Replication Yes No Yes Yes Yes Yes Yes No No 3
OS level copy No Yes No No Yes No Yes No Yes 5
mysqlhotcopy Yes Yes Yes No Yes No Yes No Yes 3
InnoDB Hot
Backup
Yes Yes Yes No No No Yes No Yes 4
MySQL Online
Backup
Yes Yes Yes Yes Yes Yes Yes No Yes 1

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.

4 Responses to “MySQL Online Backup”

  1. Ben Krug says:

    Dmitriy - mysqldump is listed as a backup method. It’s pretty easy to restore a table from a mysqldump file. Edit the dump file and take the part you want. I’ve done it many times. Perfect for restoring a table “from a month ago”.

  2. [...] A good pre-read for this webinar would be Paddy’s blog on online MySQL Backup [...]

  3. ZRM for MySQL supports selective recovery of a database from a backup containing multiple databases (http://mysqlbackup.zmanda.com/index.php/How_do_I_recover_data_when_there_is_a_failure_or_data_loss%3F#Selective_restoration_of_specific_databases).

    We can extend it to support recovery of select set of tables from a database backup. The difficulty is maintaining
    referential integrity. Restoring just one table may not result in a consistent database from the application
    point of view.

  4. As far as I can see, all the backup methods you mention have one simple drawback. Granularity. I have a database of a few gigs in size and I have yet to be in a position where I have to restore the whole thing from backup. But I have encountered more then once, when someone needs some data from a table that was dropped a month ago. A full database restore takes hours. A table restore (if I can do it) should take minutes. People do not want to wait till tomorrow to get the data they need. There are several ways to avoid this. At the moment I have a custom shell script doing backup by table, but the overall consistency is obviously a problem, since each table is backed up separately. I am planning to solve this by rewriting the whole thing in some more reasonable language and having a single transaction encompass all the backup actions (as in backup each table separately, but inside a single transaction). Another possible way of doing consistent backups would be to use InnopDB hotbackup utility to get a consistent snapshot of the storage files, start a separate instance of MySQL using this backup and backup the tables using mysqldump or similar technique. This is obviously more stable, but is a lot more time wasting.