• Suicide
  • having no backups
  • depending on slaves for backup
  • keeping backups on same SAN
  • having a single DBA - Frank didn't like this one at all
  • not keeping binlogs
• Restoring from backup
  • how much time?
  • uncompressed backup ready to mount?
  • separate network for recovery?
• In Fotolog, 1TB of data was severely hit.
  • first problem: backup was highly compressed (tar.gz)
  • uncompressing took hours
  • so keep uncompressed backups (at least last N days)
  • it should be mountable, rather than transferable
• Frank going over recovery modes at …

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 …

MySQL Backups Using ZRM For MySQL 2.0

Zmanda Recovery Manager (ZRM) for MySQL simplifies life of a database administrator who needs an easy to use yet flexible and robust backup and recovery solution for MySQL server.

MySQL Backups Using ZRM For MySQL 2.0

Zmanda Recovery Manager (ZRM) for MySQL simplifies life of a database administrator who needs an easy to use yet flexible and robust backup and recovery solution for MySQL server.

The slides for today's presentation at Shmoocon are here:

http://technocation.org/files/doc/2008_02_SecuringDBs.pdf

Just a note so folks at the conference can download them whenever they want. Folks not at the conference can, too but the slides don't give away the whole talk so I don't know how useful they're going to be.....

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).
 

I am happy to announce that I have been selected to speak at the MySQL Conference & Expo 2008 on April 14-17, 2008 in Santa Clara, CA. The topic of my talk will be "Performing MySQL Backups using LVM Snapshots" - and I will of course include a plug for the mylvmbackup tool

From the abstract:

Modern Linux distributions provide a very advanced technology to maintain and administer storage devices, called “Logical Volume Manager” (LVM). Among providing several benefits over using plain hard disk partitions, one of the unique features of LVM is the capability to create snapshots of file systems on the fly. This functionality comes in handy for performing consistent …

The folks over at zmanda who specialize in MySQL backup are doing a webinar on December 13th, this Thursday.  They specifically mention it as relevant for Oracle DBAs or anyone moving to MySQL.  Looks to be an interesting event.

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 …

A while ago Peter Zaitsev wrote about his wishes for mysqldump. These included multi-threaded dumps and "safe" dumps that would wait for a server to restart if it crashed, then keep dumping other tables. I've had sketches of this done for a while, but during this week I fleshed it out while writing about backup and recovery for our upcoming book.