mylvmbackup

This blog post is a by-product of my preparation work for an upcoming talk titled "Why you should be using a distributed version control system (DVCS) for your project" at SAPO Codebits in Lisbon (December 3-5, 2009). Publishing these thoughts prior to the conference serves two purposes: getting some peer review on my findings and acting as a teaser for the actual talk. So please let me know — did I cover the relevant aspects or did I miss anything? What's your take on DVCS vs. the centralized approach? Why do you prefer one over the other? I'm looking forward to your comments!

Even though there are several distributed alternatives available for some years now (with Bazaar, git and Mercurial being the most prominent representatives here), many large and popular Open Source projects still use centralized systems like Subversion or even CVS to maintain their source code. While Subversion has eased some of the pains of CVS (e.g. better remote access, renaming/moving of files and directories, easy branching), the centralized approach by itself poses some disadvantages compared to distributed systems. So what are these? Let me give you a few examples of the limitations that a centralized system like Subversion has and how these affect the possible workflows and development practices.

I am happy to announce that mylvmbackup version 0.13 has now been released. This release includes a fix for a nasty bug in on of the recently added Perl hooks (precleanup.pm) and some added functionality (better support for remote rsync backups).

From the ChangeLog: 

• Deleted sample precleanup.pm hook as it has potential to cause harm and is too specialized on a particular use case (BUG#394668)
• Added support for rsync via SSH (BUG#392462)
• Fixed InnoDB recovery in case a relative path to the MySQL data directory is defined (BUG#38337), improved the documentation of relpath in the man page.

 

After a long hiatus, I am happy to announce that mylvmbackup version 0.12 has now been released. This release includes a large number of improvements, minor code cleanups, as well as some new functionality. In particular, I would like to thank Matthew Boehm, Tim Stoop, Baron Schwartz, Ville Skyttä and Ronald Bradford for their contributions.

Some notable highlights from the ChangeLog:

• Removed the absolute path names to external tools (make sure $PATH is correct)
• Added --log-err to the startup options of the recovery instance to avoid cluttering the server's error log
• Added support for hooks written as Perl Modules. (Matthew Boehm)
• Added support for date/time-formatted path names for backupdir and mountdir (Matthew Boehm)
• Backupdir and mountdir are now created automatically (Matthew Boehm)
• Added new hook "logerr" when an error is logged. (Matthew Boehm)
• Added Option --keep-mount... (Tim Stoop)
• Removed the bind mount, now requires LVMv2
• Support reading login/password from ~/.my.cnf (Baron Schwartz)
• Documentation fixes and improvements (Ville Skyttä) (Bug #302144)

Today I gave my first MySQL University session as a speaker, talking about Backing up MySQL using file system snapshots. The talk went quite well (at least that was my impression) and we had ~10 people attending. The slides (PDF) and a recording of the session are now available from the Wiki page. Unfortunately the recording lacks the audio track, which is a bit of a bummer. We've submitted a support request with the DimDim folks, so hopefully they can provide us with a complete recording.

There was one question during the session that I was not able to answer myself, so I'm asking for your insights here:

Consider we're using InnoDB and MyISAM tables on a file system that can be snapshotted (e.g. Linux LVM or ZFS) and we're performing the following operations:

1. FLUSH TABLES WITH READ LOCK (yes, this won't help for the InnoDB tables)
2. Create the snapshot
3. Store the output of SHOW MASTER/SLAVE status in a file to be part of the backup
4. UNLOCK TABLES
5. Mount the snapshot
6. Start a second MySQL instance that accesses the tables on the snapshot, let InnoDB perform its table recovery
7. Shut down the second instance and perform the backup of the snapshot

The question that came up was if this actually still is a consistent backup, considering that InnoDB rolled back uncommited transactions. Does the state of the tables still match the binary log positions we noted before? I assume yes, as long as the transaction does not involve modifications non-transactional tables.

Another suggestion that came up was to change InnoDB's configuration variable innodb_max_dirty_pages_pct to "0" prior to performing the snapshot, to minimize the amount of dirty pages that have not been written to disk (and thus reducing the time required for recovering later). I wonder if this would make a difference...

What other InnoDB variables might have a noteworthy effect in the context of snapshot backups? I am looking forward to your comments.

Sorry for the downtime of this site - until around a week ago I hosted my home page on a trusty Genesi Pegasos II system (powered by a PowerPC G4 Processor clocked at 1GHz, using Debian 4.0 PPC with 512 MB of RAM), serving these pages from my home DSL connection. Unfortunately this system provided no means of redundancy - the hard disk drive died.

Luckily I perform frequent backups, so I moved most parts of the site to a shared hosting space now - the picture gallery is unfortunately too big to fit into the space that I have there. I'll try to move the pictures into my Flickr account instead, but this will take some time.

Note that the primary domain name of this site is now lenzg.net - lenzg.org, (the domain that I tried to promote as the official domain for my site) used to redirect to the home machine at lenz.homelinux.org. Both now redirect to the new address instead. I've initiated the move of the lenzg.org domain to the other provider as well, so soon this site will be available from both the .org and .net domain. Please don't link to lenz.homelinux.org anymore, as that site will eventually go out of service. Until then, a small openSUSE Linux box (Intel PIII, 500 MHz, 192 MB of RAM) running lighttpd will perform the URL redirection.

Some days ago, I released version 0.11 of mylvmbackup a Perl script that performs consistent backups of a MySQL server by using LVM filesystem snapshots. The source archive as well as a generic RPM can be found on the project home page, packages for many Linux distributions are available on the openSUSE Build service.

This release includes some new functionality as well as numerous bug fixes and improvements, most notably:

• Added support for using rsnap as a backup backend (Matt Lohier)
• The documentation is now maintained in POD style instead of asciidoc (Matthew Boehm)
• Support using non-GNU tar and additional compression methods (e.g. bzip2, lzma) (Alexander Skwar)
• Code cleanups, improved error handling and logging

I would like to thank all the contributors for their support! More details about the changes in this release, directly from the ChangeLog:

• Added new option "--quiet" that suppresses informal logging output (warnings and errors will still be printed/logged)
• Applied patch from "kjetilho" that makes mylvmbackup more robust and paranoid when it comes to handling errors/failures (Bug #298175) Now the script aborts cleanly in case of any error.
• Fixed error handling in case of a failed DB connection (patch submitted by Matthew Boehm, Bug #280989)
• Merged changes from Alexander Skwar: Fix for Bug #278478 and implemented Blueprint "Improve the tar backup backend of mylvmbackup"
• Fixed Bug #271671: "overloading parameters does not work" by removing the default values for host and port from the configuration file and removing the unnecessary check for passing both host and socket at the same time. Updated documentation and configuration file comments accordingly.
• Applied patch suggested by "Jonas" to fix Bug #267944: "backup returns successfully when snapshot creation fails"
• Code cleanup: moved flushing of tables in a separate subroutine flush_tables()
• Code cleanup: moved log messages into the respective subroutines
• Code cleanup: use return values of subroutines instead of updating global variables
• Improved some log messages to explain what was DONE or FAILED
• Code cleanup: build up long command strings in a $command variable before passing it to system()
• Renamed subroutine create_snapshot() to create_lvm_snapshot()
• Merged patch from Matthew Boehm: Removed old asciidoc documentation in favor of POD style. This removes the dependency on the external program a2x for creating documentation and uses the 'built-in' pod2html and pod2man instead. Updated the Makefile to accommodate the change.
• Applied patch from Matthew Boehm to make the backup file name suffix configurable via a "--suffix" option. Updated the man page accordingly.
• Applied patch from Matt Lohier to support rsnap as a backup backend 
• Moved the list of contributors from the man page into a separate CREDITS file, added missing names

Enjoy!

I am happy to announce that mylvmbackup version 0.10 has been released.

You can download the updated package from the project home page or via the openSUSE Build Service.

This version fixes some bugs and includes new functionality:

• Applied patch from Marc Haber: added option --keep_snapshot that will skip the removal of the backup snapshot before terminating the script. Providing the option --backuptype=none will now skip creating a backup using the builtin backup modules. Both options provide more flexibility when using hooks for performing the actual backup tasks or when the snapshot is considered to be the actual backup.
• Added two new hooks: "backupsuccess" and "backupfailure" which are called respectively upon success of failure of the backup operation (Bug #264089)
• Make sure that binaries are being found ($PATH may not include /sbin when called from cron), added missing entry for "lvs" to mylvmbackup.conf (Bug #255703)
• Updated documentation

I am happy to announce that a new version (0.9) of mylvmbackup has been released. This is the first release since the source code has been moved from Subversion to Bazaar and is now hosted on Launchpad.net. I would like to thank Robin H. Johnson and Patrick Hahn for providing the patches that contributed to this new release!

mylvmbackup is a tool for quickly creating backups of MySQL server's data files. To perform a backup, mylvmbackup obtains a read lock on all tables and flushes all server caches to disk, makes an LVM snapshot of the volume containing the MySQL data directory, and unlocks the tables again. The snapshot process takes only a small amount of time. When it is done, the server can continue normal operations, while the actual file backup proceeds.

From the ChangeLog:

• Applied patch from Patrick Hahn: provide an option to call external scripts/applications (hooks) at various stages of the backup process. See the man page for instructions
• Added options "--skip_hooks" to disable the execution of hooks and "--hooksdir" to define the location for these (default is /usr/share/mylvmbackup)
• Updated documentation: added new options and instructions on how to use hooks
• Applied patch from Robin H. Johnson: Full support for an rsync:// service as the backup destination. If you include any path fragments with the rsync module name, they must already exist!
• Updated documentation to reflect these changes
• Updated TODO

You can download a source tarball or RPM from the project home page. Additional packages for various Linux distributions can be obtained from the openSUSE Build Service. Packages for Gentoo and Debian should appear shortly, too.

Enjoy! Please let me know how mylvmbackup works for you, either by posting to our mailing list or by submitting a bug report. Thanks!

 

JFYI: today I migrated the mylvmbackup source tree from my local Subversion repository on http://www.lenzg.net/ to a Bazaar repository on Launchpad.net.

This will hopefully make it easier for contributors to work on the code and share their modifications with others, removing me as the bottleneck for applying and testing patches for new releases. I chose Bazaar primarily because I wanted to get some more hands-on practice with it, now that the MySQL Server source trees have been transferred to it as well (see Kaj's announcement for details).

As mylvmbackup is closely related to the MySQL Server project, it made sense to choose the same platform and enjoy the cross-pollination effects and the infrastructure that Launchpad provides. Additionally, the distributed nature of Bazaar makes it more convenient to work with the code history and commiting changes locally without having to be online and connected to the SVN server.

I am sure that other DSCMSs like Git, Mercurial or darcs would have done the job equally well - nowadays it's very hard to choose

The "trunk" branch is now hosted on Launchpad. I assume that I will soon open up a development branch, that will receive heavier modifications first. I also plan to use the site for bug tracking and keeping track of feature requests (via Blueprints).

To create a local branch of the "trunk" repository, you can use the following command:

bzr branch lp:mylvmbackup

I also maintain a copy of that branch on my home server, just in case: http://www.lenzg.net/bzr/mylvmbackup/

To avoid confusion, I removed the Subversion repository on http://www.lenzg.net. Please use the Bazaar tree on Launchpad from now on. Thanks!

 

 

JFYI, I now placed a PDF of my MySQL Conference talk slides about "Performing MySQL backups using Linux LVM Snapshots" on my MySQL talks page. Enjoy!

 

While reading Colin's post about LugRadio Live, I stumbled over the Zumastor Linux Storage Project. Going through the project home page and their HOWTO got me curious - could this eventually become an alternative to using DRBD (for replicating data) and LVM snapshots (for performing backups)?

Zumastor is Free software that adds enterprise storage features (primarily improved snapshots and remote replication) to Linux.

Snapshots

LVM already lets administrators create snapshots, but its design has the surprising property that every block you change on the original volume consumes one block for each snapshot. The resulting speed and space penalty usually makes the use of more than one or two snapshots at a time impractical.

Zumastor keeps all snapshots for a particular volume in a common snapshot store, and shares blocks the way one would expect. Thus making a change to one block of a file in the original volume only uses one block in the snapshot store no matter how many snapshots you have.

Replication

Andrew Tridgell's rsync is a wonderful tool for replicating files remotely. However, when doing periodic replication of large numbers of infrequently changing files, the overhead for figuring out what files need to be sent can be extreme.

Zumastor keeps track of which block change between one snapshot and the next, and can easily send just the changed blocks. Thus Zumastor can do frequent replication of large filesystems much more efficiently than rsync can.

I assume it's not ready for production use yet, but it would sure be interesting to investigate on how to utilize it for the purpose of running MySQL on top of it...

I will keep an eye on this project, I wonder if I will have to add support for Zumastor snapshots to mylvmbackup at some point?

 

 

 

I am happy to announce the release of mylvmbackup version 0.8. mylvmbackup is a tool for quickly creating backups of a MySQL server's data files. To perform a backup, mylvmbackup obtains a read lock on all tables and flushes all server caches to disk, makes an LVM snapshot of the volume containing the MySQL data directory, and unlocks the tables again. The snapshot process takes only a small amount of time. When it is done, the server can continue normal operations, while the actual file backup proceeds.

Below is the list of changes since version 0.6. You may wonder what happened to version 0.7 - it had a rather short life cycle as I was informed about a bug that I fixed quickly before I made a wider release announcement of 0.7.

• Fixed a bug in the InnoDB recovery function: the second mysqld process clobbered the socket file of the primary MySQL instance (thanks to Alain Hoang for reporting this)
• Updated the man page, noted some other limitations of the InnoDB recovery function
• Bug fix: use the correct mysqld parameter to provide an alternative PID file (--pid-file instead of --pidfile) - thanks to Guillaume Boddaert and Jim Wilson for reporting this!
• Added option "--skip_mycnf" to skip including a copy of the MySQL configuration file in the backup, added a safety check that the file actually exists prior to backing it up.

Updated package are available from the home page and via the openSUSE Build Service as usual. Updated packages for Debian/Ubuntu and Gentoo Linux should also be available shortly. Enjoy!

Speaking of LVM snapshot backups: I will be giving a talk about this subject at our MySQL Conference 2008 in Santa Clara, CA next week. If you are curious about how MySQL can be backed up using this technology, please consider to stop by!

 

While browsing the many blog entries on blogs.sun.com about the MySQL Acquisition (thanks a lot for the very warm welcome!), I stumbled over this (Python-based) utility: SnapBack, a tool that uses ZFS snapshots to perform physical backups of MySQL databases on Solaris. Very cool! This is actually something I was wanting to add to the mylvmbackup script, too - I have to take a closer look at how this is done (I tried to install OpenSolaris on a VirtualBox instance, but it caused it to crash the emulator).
 

 

Version 0.6 of mylvmbackup, a script to perform backups of a MySQL server using Linux LVM snapshots, has now been released.

In addition to various code cleanups and documentation improvements, many new features have been added to this version. I'd like to specially thank Robin H. Johnson from the Gentoo project for contributing many of the improvements to this release!

• Added a new rsync backup type. This is very useful if you want to use mylvmbackup to create the initial state for your slave servers. Instead of creating a .tar.gz archive, the data directory is copied into a timestamped archive directory. (Robin)
• Added support for a trailing argument to tar, which can be used for excluding files. (Robin)
• Separated out the suffix of the tarball (Preperation for rsync and users that want to use bzip2 or no compression on the tarball.) (Robin)
• While the backup is performed, a temporary suffix at the end of the tar backup file name (or the rsync target directory name) now indicates that it is incomplete. (Robin)
• The my.cnf configuration file is now included in the backup. (Robin)
• Added the ability to run an extra FLUSH TABLES on busy databases where lvcreate might take a long time (and may overrun the interactivity timeout on the connection, losing the lock). (Robin)
• Added option "--pidfile" to provide an alternative PID file location for the second server instance that is started to perform the InnoDB recovery on the snapshot prior to backing it up (Otherwise it may default to using the same pid file location that the running server uses and safe_mysqld will abort) - thanks to Kristian Köhntopp for making me aware of this problem.
• Before discarding the snapshot LV, the output of "lvs <snapshot>" is now printed out for diagnostics. It contains useful information like "how much percent of the backing store was used", which helps tuning the size of the snapshot LV.
• Added option "--skip-flush-tables" that performs the snapshot without flushing the tables to disk beforehand (which is not supported by InnoDB tables anyway) - this would save time, as the flushing can take a while, depending on the buffer sizes. (Thanks to Peter Zaitsev for the suggestion)

Version 0.6 is now available for download from http://www.lenzg.net/mylvmbackup/ (source tarball and RPM). I also provide RPM packagages for a number of additional platforms via my home:LenzGr repository on the fabolous openSUSE Build Service.

Enjoy! Feedback, patches and suggestions are welcome - please consider joining the mylvmbackup mailing list to discuss your experiences with this tool.

 

Eric Bergen from Proven Scaling (which I had the pleasure to meet in person during the MySQL Conference & Expo in Santa Clara last month) was kind enough to send me a patch for the mylmbackup tool, which justifies a new release:

Attached is a patch file for mylvmbackup that adds the ability to use
lvm version 2 and perform innodb recovery on the snapshot prior to
creating a tar ball. The option is named --innodb-recover.

I've also fixed a bug with default value handling for command line
options. In version 0.4 if a config file was specified default values
in the script were all changed to blank. This means that the config
file had to supply values for every variable instead of just the
values that need to be changed from default.

This is a very useful addition, as it significantly reduces the time required to recover a MySQL server from a snapshot backup. Thank you, Eric! Note that you need to use LVM2, as LVM1 does not support the required writing to snapshot volumes.

Version 0.5 is now available for download from the mylvmbackup project page.

I also would like to announce that I have set up a dedicated mailing list for this tool: if you want to discuss the usage/future of mylvmbackup, propose patches or ask for help, there now is a mailinglist, hosted on FreeLists.org. To subscribe, either enter your email address on the mylvmbackup list information page or send an email with the subject "subscribe" to mylvmbackup-request@freelists.org. The list is archived here. See you there!