mylvmbackup(1)

NAME

mylvmbackup - a utility for creating MySQL backups using LVM snapshots

SYNOPSIS

mylvmbackup [OPTIONS]

DESCRIPTION

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.

The LVM snapshot is mounted to a temporary directory and all data is
backed up using the tar program. By default, the archive file is created using a name of the form backup-YYYYMMDD_hhmmss_mysql.tar.gz, where YYYY, MM, DD, hh, mm, and ss represent the year, month, day, hour, minute, and second of the time at which the backup occurred. The prefix backup, date format and file suffix may be modified. The use of timestamped archive names allows you to run mylvmbackup many times
without danger of overwriting old archives.

Alternatively, instead of tar, you may use rsync. This process is nearly identical, with the exception that the file suffix is not used. Currently, the rsync backup is primarily developed for performing local backups. You can configure it for backing up to a remote rsync server, but this is not fully tested in all possible configurations yet and may change at some point.

GENERAL HINTS

It is required to run mylvmbackup on the same host where the MySQL
server runs. If your MySQL daemon is not listening on localhost or
using the default socket location, you must specify --host or --socket. Even though mylvmbackup communicates with the server through a normal
client connection to obtain the read lock and flush data, it performs
the actual backup by accessing the file system directly. It is also a
requirement that the MySQL server´s data directory resides on an LVM
volume. (It is, however, a good idea to do the LVM backup to a
different partition than the one where the data directory resides.
Otherwise, there is a good chance that LVM will run out of undo space
for LVM snapshot maintenance and the backup will fail.)

The user who invokes mylvmbackup must have sufficient filesystem
permissions to create the LVM snapshot and mount it. This includes
read/write access to the backup directory.

If you plan to back up InnoDB tables using LVM snapshots, be advised
that it is not sufficient to lock the tables and issue the FLUSH TABLES command to get the table files into a consistent state. When starting
the MySQL server from these restored files, InnoDB will detect these
tables as being in an inconsistent state and will perform a recovery
run before the tables can be accessed again. As this can potentially
take some time (which you may not want to spend after restoring a
server and trying to get it back on its feet as fast as possible),
consider using the option --innodb_recover, which will perform the recovery operation on the backup snapshot prior to archiving it.

The recovery operation is performed by spawning a second mysqld
instance that uses the snapshot volume as the data directory. Note that this functionality currently assumes the default InnoDB configuration it does not work properly if you use options like
--innodb-file-per-table, --innodb-data-home-dir, --innodb-data-file-path or --innodb-log-group-home-dir that modify the default file layout for InnoDB tables.

Also note that this only works when using writable LVM snapshots and
thus requires LVM2 (the script performs a test for this and will
disable log recovery in the case it finds an LVM1 system). This will
prolong the time needed to perform the actual backup, but will save you precious time when you eventually have to restore from this backup set.

If you use InnoDB tables exclusively, you may also want to consider to include the option --skip_flush_tables, to avoid the probably time-consuming and in this case unnecessary flushing of buffers. But
don´t enable this option when MyISAM tables are involved!

HOOKS

It is possible to run arbitrary external programs or scripts (hooks) at various stages of the backup process, to perform additional actions as part of the backup process.

These scripts or symbolic links to executables should be placed in the directory that the hooksdir configuration option points to (/usr/share/mylvmbackup by default). They should return zero upon successful completion, any non-zero return value will be considered a
failure which will be logged.

The names of the scripts or symbolic links reflect the stage in which
the hook will be called. Currently, the following stages exist:

· preconnect: before a connection to the database server is

established

· preflush: before calling FLUSH TABLES

· presnapshot: before the file system snapshot is created

· preunlock: before the database tables are unlocked again

· predisconnect: before the connection to the database server is

released

· premount: before the snapshot volume is mounted

· prebackup: before the snapshot backup will be performed

· precleanup: before the snapshot is unmounted and discarded These hooks are optional and will only be called if a file for the
particular stage exists and is executable. The execution of all hooks
can be supressed by passing the --skip_hooks option or by setting the skip_hooks configuration option to 1;

OPTIONS

mylvmbackup supports the following command line options. The same
options can also be defined in the /etc/mylvmbackup.conf configuration file (omitting the leading dashes, of course).

--user=string

Specifies the username to use for connecting to the MySQL server.
The default is root.

--password=string

Specifies the password to use for connecting to the MySQL server.
The default is the empty string (no password).

--host=string

Specifies the host name to use for connecting to the MySQL server. The default is the empty string.

--port=number

Specifies the TCP port number to use for connecting to the MySQL
server. The default is 3306.

--socket=string

Specifies the path to the local socket file, if it is not located
at the default location. The default is the empty string.

--innodb_recover

Run InnoDB recovery on the writable snapshot (LVM2 only) prior to
performing the backup.

--skip_flush_tables

Don´t issue a FLUSH TABLES WITH READ LOCK command before creating the snapshot. Only use this option when backing up InnoDB tables
(as they don´t support this function anyway and will require
recovery in any case). This option skips the (probably time
consuming) flushing of buffers.

--extra_flush_tables

If your database performs a lot of writes, it may help to perform
an extra initial FLUSH TABLES so that the lvcreate can finish within the interactivity timeout during the read-locked flush.

--pidfile=string

Specifies the full path and file name to the PID file of the server instance that is spawned to perform the InnoDB recovery (see option --innodb_recover). Must be different from the PID file that the actual running server uses. The default is
/var/run/mysqld/mylvmbackup_recoverserver.pid.

--lvcreate=string

Specifies the pathname for the lvcreate program. The default is lvcreate.

--lvremove=string

Specifies the pathname for the lvremove program. The default is lvremove.

--lvs=string

Specifies the pathname for the lvs program. The default is lvs.

--mysqld_safe=string

Specifies the pathname for the mysqld_safe program. The default is mysqld_safe. Only used to perform InnoDB recovery.

--mycnf=string

Specifies the name of the MySQL config file to include in the
backup. The default is /etc/my.cnf.

--skip_mycnf

Skip backing up the MySQL configuration file. The default is to
include a copy of the configuration file in the backup.

--hooksdir=string

The location of external scripts or executable to be called during various stages of the backup. See the HOOKS section in this manual page for more info. The default is /usr/share/mylvmbackup.

--skip_hooks

Skip invoking any external hooks during the backup.

--vgname=string

Specifies the volume group of the logical volume where the MySQL
data directory is located. The default is mysql.

--lvname=string

Specifies the name of the logical volume where the MySQL data
directory is located. The default is data.

--backuplv=string

Specifies the name of the logical volume for the snapshot volume.
The default is appending "_snapshot" to the lvname.

--relpath=string

Relative path on the logical volume to the MySQL data directory.
The default is the empty string.

--lvsize=string

Specifies the size for the snapshot volume. The default is 5G (5 gigabytes).

--backuptype=string

Specifies what type of backup to perform. The available options are tar and rsync.

--prefix=string

Prefix added to the backup file names. It is also appended to the
name of the directory used to mount the snapshot volume. The
default value is backup.

--datefmt=string

Format of the time stamp included in the backup file name. See the Date::Format perldoc page for a description of the format. The default value is %Y%m%d_%H%M%S, which creates a time stamp like YYYYMMDD_HHMMSS, e.g. 20070531_112549

--mountdir=string

Path for mounting the snapshot volume to. The default value is
/var/cache/mylvmbackup/mnt/.

--backupdir=string

Specifies the pathname of the directory where the archive files
will be written to. The backup directory must not be on the same
volume as the MySQL data directory. Instead of a local directory,
you can also provide a valid rsync URL here, e.g.
hostname::rsync-module/path. This requires a properly configured remote rsync setup (e.g. pre-setup SSH keys and a valid rsyncd.conf file). Note that using rsync for remote backups is still under
development and the way to configure it may change! The default is /var/cache/mylvmbackup/backup/.

--mount=string

Specifies the pathname for the mount program. The default is mount.

--umount=string

Specifies the pathname for the umount program. The default is umount.

--tar=string

Specifies the pathname for the tar program. The default is tar.

--tararg=string

Specifies the initial arguments for the tar program. The default is cvzf.

--tarsuffixarg=string

Specifies the suffix arguments for the tar program. The default is ´. To exclude a database, you would pass --exclude dbname´ here.

--tarfilesuffix=string

Specifies the suffix for the tarball. The default is .tar.gz.

--rsync=string

Specifies the pathname for the rsync program. The default is rsync.

--rsyncarg=string

Specifies the arguments for the rsync program. The default is -avWP. Should must ensure that the recursive option is included either implicitly by -a, or explicitly.

--xfs

Use the nouuid mount option to safely mount snapshot partitions that use the XFS file system.

--log_method=string

How to log output from this script. Valid options are console, syslog or both. The default value is console.

--syslog_socktype=string

What type of socket to use for connecting to the syslog service.
Valid options are native, tcp and udp. The default value is native.

--syslog_facility=string

Define a particular syslog facility Default value is the empty
string.

--syslog_remotehost=string

Host name of a remote syslog server.

--configfile=string

Specify an alternative configuration file. The default is
/etc/mylvmbackup.conf.

--help

Displays a help message showing the available options.

FILES

/etc/mylvbackup.conf

The mylvmbackup configuration file

mylvmbackup

The executable Perl script that performs the work.

REQUIREMENTS

For proper operation mylvmbackup requires Perl 5 with the DBI and DBD::mysql modules. It also needs the Config::IniFiles to read the global configuration file of the program and Sys::Syslog in case you want to enable the syslog log facility. Date::Format is required to create the time stamp used in the backup file names. In addition, it
utilizes Getopt::Long, File::Basename and File::Temp, which usually are part of the default Perl distribution.

It also requires several other external programs: GNU tar and gzip to back up the data, LVM utilities (lvcreate, lvremove and lvs) to create and remove the LVM snapshot, and the system utilities mount and umount.

rsync may be used in place of tar and gzip.

SEE ALSO

mount(8), tar(1), lvcreate(8), lvremove(8), lvs(8), ´umount(8), rsync(1)

AUTHOR

This program was initially written by Aleksey "Walrus" Kishkin from
MySQL AB, with suggestions from Peter Zaitsev and Lenz Grimmer.

It is currently maintained by Lenz Grimmer, <lenz@grimmer.com>

RESOURCES

Main web site: http://www.lenzg.org/mylvmbackup/ Mailing list:
http://www.freelists.org/list/mylvmbackup/ Source code, bug tracker:
https://launchpad.net/mylvmbackup/

CREDITS

Several people have contributed to the script since it has been
released. See the ChangeLog for more details.

Robin H. Johnson from the Gentoo project cleaned up the code and added several useful features.

Fred Blaise contributed the initial support to use an external
configuration file and logging via syslog.

Eric Bergen provided the code that performs the InnoDB recovery prior
to performing the backup of LVM2 snapshot volumes. He also fixed the
broken handling of default options.

Kristian Köhntopp for suggesting the --pidfile option and suggesting to add the output of the LVM statistics for being able to better tune
the snapshot size.

COPYING

mylvmbackup is distributed under the GNU public license. See the file
COPYING for details.

NOTES

1. lenz@grimmer.com>

mailto:lenz@grimmer.com>

07/11/2008 MYLVMBACKUP(1)