Plugin-based backup for MySQL

I’ve been working on a new project to fulfill a specific need: consistent, fast, cheap, and flexible backups for MySQL, for all storage engines1. To that end I’m creating a tool called dbsnapper—a plugin-based backup tool. The tool itself is very basic and handles a few jobs: getting configuration information from the user, running through a “run sheet” of different configurable tasks, and reporting status and errors to the user.

The tasks then—the actual backup steps—are fully configurable, via plugins. In fact, the whole process isn’t even MySQL specific, and can potentially be used for PostgreSQL2 and other database as well. Remember the requirements for backups (above):

  • Consistent—We need to do some locking inside MySQL to make sure that the backups are consistent, for both MyISAM and InnoDB tables. This generally means the FLUSH TABLES WITH READ LOCK command.
  • Fast—There are two ways to get fast, but they both involve snapshotting: either inside the database, or on the volume level. The best way to get a backup quickly is by using Linux’s LVM, the Logical Volume Manager, to take a snapshot of the whole filesystem. Using mysqldump for backups fails miserably on this point.
  • Cheap—Well, backups should be free, and open source. Sorry ibbackup, sorry commercial utilities, become open source and we’ll talk.
  • Flexible—Everyone wants to do something slightly different with their backups, and in order for them to use one common tool, that tool needs to be very flexible. Most backup tools for MySQL are completely inflexible (other than the destination of the backup files). People often have slightly different requirements, why not try to make a single tool work?

It’s possible to meet all of the above requirements right now, but you would likely have to write your own backup script. When writing that script, you would likely do the minimum to make it work in your environment. Why should everyone write their own? My project3, dbsnapper is designed from the start to handle backups in a flexible and configurable way—to allow the user to decide what tools and processes to use, but to do it for them.

Keep an eye out, I’ll blog again once I’ve published the code!

1 Yes, yes, I know about the blue-sky internal online backup plans. Need I mention that online backup was originally planned for 4.0? Then it was moved to 4.1, where it would definitely get done… then to 5.0, a major new version, surely it will get done then. Now it’s likely not going to make it in 5.1, and slotted for 5.2, as far as I know. In the end, even if it does get done, that still doesn’t help people who want to backup their 4.0 or 4.1 installations, which is very common.

2 If someone is interested in working on the plugins for PostgreSQL, let me know, and I’ll give you a nudge once the plugin API is stable!

3 It need not be only “my” project. Anyone interested in helping?

5 thoughts on “Plugin-based backup for MySQL

  1. Hi Jeremy,

    I released phpMySQLAutobackup many years ago:

    http://www.dwalker.co.uk/phpmysqlautobackup/

    (released under GPL License)
    This creates a backup of the MySQL database and emails the compressed backup to a specified email address, yes it has its limitations, but for small databases its ideal.

    If you think this could be of any help/grounding for your project let me know.

    Good luck with your project

    Dave.

  2. Hi Jeremy!

    Good finally meeting you last night! :)

    You may want to have a chat with Paddy from Zmanda (he’s at the conference and giving a session on backups) They have a flexible backup tool based on Amanda that seems promising… could also be up for collaborating.

    Cheers,

    Jay

  3. Hi Jeremy,

    Jay mentioned about your backup plugin effort. We would be very interested in working with you developing/testing the plugin with Amanda. If you are around in MySQL users conference today (Apr 26), please stop by our
    booth or you can talk to me at MySQL BOF tonight at 8pm.

    Of course, you can send me an email to discuss details. For Amanda details, see http://wiki.zmanda.com

    Thanks,
    Paddy

  4. > Fast—There are two ways to get fast, but they both involve snapshotting: > either inside the database, or on the volume level. The best way to get a > backup quickly is by using Linux’s LVM, the Logical Volume Manager, to > take a snapshot of the whole filesystem. Using mysqldump for backups fails > miserably on this point.

    I’ve gotten in to the habbit of running a 2nd mysql instance on a snapshot in order to use mysqldump…

  5. Hi Robo,

    Yes, this works too, but I’d say it applies more to “consistent” than “fast”… as it’s still dog slow, but at least doesn’t block your main database during its run.

What do you think?

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s