Strict Standards: Redefining already defined constructor for class wpdb in /home/thornock/public_html/wordpress/wp-includes/wp-db.php on line 52

Deprecated: Assigning the return value of new by reference is deprecated in /home/thornock/public_html/wordpress/wp-includes/cache.php on line 36

Strict Standards: Redefining already defined constructor for class WP_Object_Cache in /home/thornock/public_html/wordpress/wp-includes/cache.php on line 389

Strict Standards: Declaration of Walker_Page::start_lvl() should be compatible with Walker::start_lvl($output) in /home/thornock/public_html/wordpress/wp-includes/classes.php on line 537

Strict Standards: Declaration of Walker_Page::end_lvl() should be compatible with Walker::end_lvl($output) in /home/thornock/public_html/wordpress/wp-includes/classes.php on line 537

Strict Standards: Declaration of Walker_Page::start_el() should be compatible with Walker::start_el($output) in /home/thornock/public_html/wordpress/wp-includes/classes.php on line 537

Strict Standards: Declaration of Walker_Page::end_el() should be compatible with Walker::end_el($output) in /home/thornock/public_html/wordpress/wp-includes/classes.php on line 537

Strict Standards: Declaration of Walker_PageDropdown::start_el() should be compatible with Walker::start_el($output) in /home/thornock/public_html/wordpress/wp-includes/classes.php on line 556

Strict Standards: Declaration of Walker_Category::start_lvl() should be compatible with Walker::start_lvl($output) in /home/thornock/public_html/wordpress/wp-includes/classes.php on line 653

Strict Standards: Declaration of Walker_Category::end_lvl() should be compatible with Walker::end_lvl($output) in /home/thornock/public_html/wordpress/wp-includes/classes.php on line 653

Strict Standards: Declaration of Walker_Category::start_el() should be compatible with Walker::start_el($output) in /home/thornock/public_html/wordpress/wp-includes/classes.php on line 653

Strict Standards: Declaration of Walker_Category::end_el() should be compatible with Walker::end_el($output) in /home/thornock/public_html/wordpress/wp-includes/classes.php on line 653

Strict Standards: Declaration of Walker_CategoryDropdown::start_el() should be compatible with Walker::start_el($output) in /home/thornock/public_html/wordpress/wp-includes/classes.php on line 678

Deprecated: Assigning the return value of new by reference is deprecated in /home/thornock/public_html/wordpress/wp-includes/query.php on line 21

Deprecated: Assigning the return value of new by reference is deprecated in /home/thornock/public_html/wordpress/wp-includes/theme.php on line 508

Strict Standards: Non-static method GoogleSitemapGenerator::Enable() should not be called statically in /home/thornock/public_html/wordpress/wp-content/plugins/sitemap.php on line 2452

Strict Standards: call_user_func_array() expects parameter 1 to be a valid callback, non-static method UltimateTagWarriorActions::ultimate_query_vars() should not be called statically in /home/thornock/public_html/wordpress/wp-includes/plugin.php on line 57

Strict Standards: call_user_func_array() expects parameter 1 to be a valid callback, non-static method UltimateTagWarriorActions::ultimate_posts_where() should not be called statically in /home/thornock/public_html/wordpress/wp-includes/plugin.php on line 57

Strict Standards: call_user_func_array() expects parameter 1 to be a valid callback, non-static method UltimateTagWarriorActions::ultimate_posts_join() should not be called statically in /home/thornock/public_html/wordpress/wp-includes/plugin.php on line 57

Strict Standards: call_user_func_array() expects parameter 1 to be a valid callback, non-static method UltimateTagWarriorActions::ultimate_tag_templates() should not be called statically in /home/thornock/public_html/wordpress/wp-includes/plugin.php on line 160
Gary Thornock's Weblog » MySQL backups using filesystem snapshots
Strict Standards: call_user_func_array() expects parameter 1 to be a valid callback, non-static method UltimateTagWarriorActions::ultimate_add_ajax_javascript() should not be called statically in /home/thornock/public_html/wordpress/wp-includes/plugin.php on line 160
20th Jul, 2006

MySQL backups using filesystem snapshots


Deprecated: preg_replace(): The /e modifier is deprecated, use preg_replace_callback instead in /home/thornock/public_html/wordpress/wp-includes/formatting.php on line 74

Strict Standards: call_user_func_array() expects parameter 1 to be a valid callback, non-static method UltimateTagWarriorActions::ultimate_the_content_filter() should not be called statically in /home/thornock/public_html/wordpress/wp-includes/plugin.php on line 57

I've been looking for a good way to do MySQL live backups. Several people recommend using a Linux LVM snapshot and taking the backup from there. (See Mike Kruckenberg's article, for example.) Note that you must first run FLUSH TABLES WITH READ LOCK if you're backing up MyISAM tables that way.

Of course, I'm on FreeBSD, so I don't have Linux LVM. In my limited testing, though, it looks like FreeBSD's UFS snapshots will accomplish exactly the same thing, and they don't require that you set up LVM beforehand.

Assuming you've installed Ralf Engenschall's snapshot tools (/usr/ports/sysutils/freebsd-snapshot or see the above article for a download link), the steps go like this:

  1. In MySQL, run "FLUSH TABLES WITH READ LOCK"
  2. Still in MySQL, run "\! snapshot -v make -g 1 /var:dbsnap" (note, I assume your database is on /var, substitute other filesystem as appropriate)
  3. Still in MySQL, run "UNLOCK TABLES"
  4. Exit MySQL and run "snapshot mount -o ro /var:dbsnap.0 /some/mount/point"
  5. Back up /some/mount/point/db/mysql using the tool of your choice (tar will do fine)
  6. Run "snapshot umount /some/mount/point"
  7. If you need to free up the space the snapshot is using, run "snapshot make -g 0 /var:dbsnap"
, ,

Responses


Deprecated: preg_replace(): The /e modifier is deprecated, use preg_replace_callback instead in /home/thornock/public_html/wordpress/wp-includes/formatting.php on line 74

It might be easier to use dump. Since 5.x dump has a new option -L. This causes dump to take a snapshot first, dump it, and delete it in one fell swoop.

Pros:
backup script is easier (flush lock, dump, unlock)
gives you fancy dump options like differential backups

Cons:
No control over which parts of the filesystem to backup


Deprecated: preg_replace(): The /e modifier is deprecated, use preg_replace_callback instead in /home/thornock/public_html/wordpress/wp-includes/formatting.php on line 74

dump might be a good option, particularly if /var/db/mysql is a distinct filesystem. There's still the issue of flushing the tables and getting a read lock while the snapshot is created, though. Is there a way to do that with dump that also allows you to release the read lock after the snapshot is created?

Categories