=head1 NAME

mylvmbackup - a utility for creating MySQL backups using LVM snapshots

=head1 SYNOPSIS

mylvmbackup [OPTIONS]

=head1 DESCRIPTION

I<mylvmbackup> is a tool for quickly creating full physical backups of a MySQL
server's data files. To perform a backup, I<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 I<tar> program by default. The archive files are created using names
in the form of I<backup-YYYYMMDD_hhmmss_mysql.tar.gz>, where I<YYYY>, I<MM>,
I<DD>, I<hh>, I<mm> and I<ss> represent the year, month, day, hour, minute,
and second of the time at which the backup occurred. The default prefix
I<backup>, date format and file suffix may be modified. The use of timestamped
archive names allows you to run I<mylvmbackup> many times without risking to
overwrite old archives. It is possible to preserve only a defined number of
last backups, to avoid running out of disk space.

Alternatively, instead of I<tar>, you can use I<rsync>, I<rsnap> or I<zbackup>
to perform the archiving.

The I<rsync> backup can perform both local backups as well as backing up to a
remote server using rsyncd or rsync via SSH.

I<rsnap> is a wrapper around I<rsync> to automatically maintain and rotate a
given number of last backups (7 by default). It utilizes hard links to link to
unchanged files for saving disk space.

I<zbackup> is a globally-deduplicating backup tool. Feed a large .tar into it,
and it will store duplicate regions of it only once, then compress and
optionally encrypt the result. Feed another .tar file, and it will also re-use
any data found in any previous backups. This way only new changes are stored,
and as long as the files are not very different, the amount of storage required
is very low. Any of the backup files stored previously can be read back in full
at any time.

Moreover, a backup type I<none> is provided for cases where the user wants
to use I<mylvmbackup> only for creating the snapshots and intends to perform the
actual backup by using the appropriate hooks. (Or for cases where the snapshot
itself is considered to be the backup).

I<mylvmbackup> also provides several methods for logging and reporting the
progress and success of a backup run. The log messages can either be printed
to the console (STDOUT) or logged via I<syslog>. Additionally, a report can be
sent to you via email.

=head1 GENERAL HINTS

It is required to run I<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 I<--host> or I<--socket>. Even though
I<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 I<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 I<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 log 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 I<--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 a default InnoDB configuration in which all InnoDB table
spaces and log files are stored inside of the data directory - it does not
work properly if you use options like I<--innodb-data-home-dir>,
I<--innodb-data-file-path> or I<--innodb-log-group-home-dir> that modify
the default file layout for InnoDB tables.

If you use InnoDB tables exclusively, you may also want to consider to include
the option I<--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!

=head1 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 I<hooksdir> configuration option points to
(I</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.

Hook scripts can also be implemented as Perl modules. The module must be named
I<hookname.pm> and must be a package of type I<hookname>. The module must
implement I<execute()> which is called by I<mylvmbackup> to initiate the hook. It
must return boolean true/false (1 or 0) on success/failure. I<execute()> will
be passed 2 parameters. The first parameter is a clone() of the global database
handle $dbh. This will allow hook scripts to interact with the database using
the established connection.  The second parameter is a string containing any
messages passed to the I<run_hook()> function.  The module must also implement
I<errmsg()> which will return a string error message to be sent to
I<log_msg()>. This will be called by I<mylvmbackup> when I<execute()> returns
false/0.

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

=over 4

=item B<preconnect>

before a connection to the database server is established

=item B<preflush>

before calling FLUSH TABLES

=item B<presnapshot>

before the file system snapshot is created

=item B<preunlock>

before the database tables are unlocked again

=item B<predisconnect>

before the connection to the database server is released

=item B<premount>

before the snapshot volume is mounted

=item B<prebackup>

before the snapshot backup will be performed

=item B<backupsuccess>

after a successful backup

=item B<backupfailure>

after a failed backup

=item B<logerr>

when an error is logged

=item B<precleanup>

before the snapshot is unmounted and discarded

=back

These hooks are optional and will only be called if a file for the particular
stage exists and is executable. Note that hooks implemented as Perl modules
(I<hookname.pm>) have priority over "plain" hook scripts (I<hookname>), if both
exist, only the first one will be used. The execution of all hooks can be
suppressed by passing the I<--skip_hooks> option or by setting the
I<skip_hooks> configuration option to I<1>;

=head1 OPTIONS

I<mylvmbackup> supports the following command line options. The same options can
also be defined in the I</etc/mylvmbackup.conf> configuration file (omitting
the leading dashes, of course). A sample configuration file is included in
the distribution.

=over 4

=item B<--action=string>

Selects the mode of action. Possible values are I<backup> and I<purge>.

When this option is omitted, the I<backup> action is assumed by default, which
performs the actual backup creation. Older backup files are preserved.

The I<purge> action can be used to remove older tar or local rsync backups from
the backup directory, except for the a configurable number of most recent
backups, defined by the I<backupretention> option. The backup creation process
is skipped in this case.

Note that this option erases B<all> files contained in this directory that
match the criterion of being older than the last n backup files (with the
exception of hidden (dot) files!

=item B<--user=string>

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

=item B<--password=string>

Specifies the password to use for connecting to the MySQL server.
The default is the empty string (no password). Alternatively, you can
set the password by defining the environment variable I<MYSQL_PWD>
prior to starting the script. Note however that this method is considered
to be highly insecure, as it's possible for other users to obtain the
password by examining the environment variables of the running process.
See the MySQL Reference manual for more details on password security.

=item B<--host=string>

Specifies the host name to use for connecting to the MySQL server. Note that
I<mylvmbackup> needs to be run on the same system that the MySQL server to be
backed up runs on - do not enter a remote host's host name or IP address here!
A non-empty value for I<host> other than I<localhost> overrides any given
I<socket> path value.
The default is the empty string.

=item B<--port=number>

Specifies the TCP port number to use for connecting to the MySQL server.
This value is only honoured, if I<host> is provided as well and is not
equal to I<localhost>.
The default is the empty string.

=item B<--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.

=item B<--quiet>

Suppresses logging of informal messages. Warnings and errors will still
be printed or logged (depending on the selected logging mechanism).
The default is verbose logging.

=item B<--innodb_recover>

Run InnoDB recovery on the writable snapshot prior to performing the backup.

=item B<--recoveryopts>

Additional values to pass to the startup options of the separate MySQL
instance that gets spawned to perform the InnoDB log recovery option.
Depending on your MySQL Server version, you may have to modify these
startup parameters.

The default is I<--skip-networking --skip-grant --bootstrap --skip-syslog --skip-slave-start>.

=item B<--skip_flush_tables>

Don't issue a I<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.

=item B<--extra_flush_tables>

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

=item B<--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
I<--innodb_recover>). Must be different from the PID file that the actual
running server uses.
The default is I</var/run/mysqld/mylvmbackup_recoverserver.pid>

=item B<--lvcreate=string>

Specifies the pathname for the I<lvcreate> program.
The default is I<lvcreate>.

=item B<--lvremove=string>

Specifies the pathname for the I<lvremove> program.
The default is I<lvremove>.

=item B<--lvs=string>

Specifies the pathname for the I<lvs> program.
The default is I<lvs>.

=item B<--mysqld_safe=string>

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

=item B<--mycnf=string>

Specifies the name of a MySQL config file (e.g. I</etc/my.cnf>) or an entire
config directory (e.g. I</etc/mysql>) to include in the backup.
The default is I</etc/my.cnf>.

=item B<--skip_mycnf>

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

=item B<--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 I</usr/share/mylvmbackup>.

=item B<--skip_hooks>

Skip invoking any external hooks during the backup.

=item B<--vgname=string>

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

=item B<--lvname=string>

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

=item B<--backuplv=string>

Specifies the name used for the snapshot volume. If left empty, B<_snapshot>
will simply be appended to the original volume name (e.g. I<data_snapshot>).

It is possible to use selected I<timestr()> formatting sequences to create
snapshot volume names which contain a dynamic date value. This can be useful
if you use thin provisioned snapshots as the actual backup, by enabling the
I<keep_snapshot> option.

Currently, the following format strings are supported:

=over

=item I<%Y>

4-digit year (e.g. 2009)

=item I<%m>

Month (01..12)

=item I<%d>

Day of month, leading zero

=item I<%h>

Month abbreviation,

=item I<%H>

Hour, 24 hour clock, leading zero

=item I<%M>

Minute, leading zero

=item I<%S>

Seconds, leading zero

=back

Example: I<$backuplv=backup-%Y-%m-%d-%H-%M-%S> will expand to
I<backup-2013-06-07-14-08-45>.

=item B<--keep_snapshot>

If this option is given, I<mylvmbackup> will not remove the snapshot
before terminating. Note that keeping multiple LVM snapshots open at
the same time can reduce I/O performance and you will need to manually
discard the snapshot before invoking I<mylvmbackup> again.

=item B<--keep_mount>

If this option is given, I<mylvmbackup> will not remove the mounted partition
before terminating. This option also implies I<keep_snapshot=1>, as it would
not be useful if the snapshot is removed. You need to manually unmount this
directory before invoking I<mylvmbackup> again.

=item B<--thin>

If this option is given, I<mylvmbackup> will expect that the LVM volume is
using thin provisioning and that the snapshot will use physical space
from the existing thin pool. Any size specified with I<lvsize> is ignored.

=item B<--relpath=string>

Relative path on the logical volume to the MySQL data directory (no leading or
trailing slash). Example: the logical volume is mounted on I</var/lib>, but the
MySQL data directory is /var/lib/mysql. In this case, I<relpath> should be set
to I<mysql>.
The default is the empty string.

=item B<--lvsize=string>

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

=item B<--backuptype=string>

Specifies what type of backup to perform. The available options are
I<tar>, I<rsync>, I<rsnap>, I<zbackup> and I<none>. Note that using
I<zbackup> still requires a I<tar> executable to prepare the backup
archives.

=item B<--backupretention=string>

Specifies how many previous backups (tar archives or rsync directories only) to
keep in the backup directory when performing the I<purge> action. The default
is I<0> (keep all backups).

Note that this feature only works on a local backup directory with
a static directory name! If you use I<timestr()> formatting sequences
for the backup directory, the retention mode will not work.

The script looks at the last modification time (mtime) of each file and
directory to determine which files will be removed.

Be advised that this operation deletes B<all> files and directories in
the backup directory that are older than the last n files (with the
exception of hidden (dot) files!

=item B<--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 I<backup>.

=item B<--suffix=string>

Suffix added to the backup file names (after the time stamp).
The default value is I<_mysql>.

=item B<--datefmt=string>

Format of the time stamp included in the backup file name. See
the I<Date::Format> perldoc page for a description of the format.
The default value is I<%Y%m%d_%H%M%S>, which creates a time stamp
like I<YYYYMMDD_HHMMSS>, e.g. I<20070531_112549>
Can be empty as well, to suppress adding a time stamp (e.g. when
using rsync to always sync into the same backup directory).

=item B<--mountdir=string>

Path for mounting the snapshot volume to.
The default value is I</var/run/mysqld/mylvmbackup/mnt/>.
If the directory does not exist, it will be created.

It is possible to use selected I<timestr()> formatting sequences to create
directory names which contain a dynamic date value. Currently, the following
format strings are supported:

=over

=item I<%Y>

4-digit year (e.g. 2009)

=item I<%m>

Month (01..12)

=item I<%d>

Day of month, leading zero

=item I<%h>

Month abbreviation,

=item I<%H>

Hour, 24 hour clock, leading zero

=item I<%M>

Minute, leading zero

=item I<%S>

Seconds, leading zero

=back

Example: I<$mountdir=/path/to/%Y-%m-%d> will expand to I</path/to/2009-06-13>

=item B<--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. If the directory does not exist, it will be created.

It is possible to use selected I<timestr()> formatting sequences to create
directory names which contain a dynamic date value. Currently, the following
format strings are supported:

=over

=item I<%Y>

4-digit year (e.g. 2009)

=item I<%m>

Month (01..12)

=item I<%d>

Day of month, leading zero

=item I<%h>

Month abbreviation,

=item I<%H>

Hour, 24 hour clock, leading zero

=item I<%M>

Minute, leading zero

=item I<%S>

Seconds, leading zero

=back

Example: I<$mountdir=/path/to/%Y-%m-%d> will expand to I</path/to/2009-06-13>

Instead of a local directory, you can also provide a valid rsync URL here, e.g.
I<username@hostname:/path>, I<hostname:path> or I<hostname::rsync-module/path>.
This requires a properly configured remote rsync setup (e.g. pre-setup SSH keys
or a working rsyncd configuration).

Note that the I<backupretention> option does not work for rsync URLs or directory
names that use format strings. You need to define a static local directory name
in I<backupdir> if you want to use the I<purge> action to automatically remove
older backups from the backup directory.

The default is I</var/run/mysqld/mylvmbackup/backup/>

=item B<--mount=string>

Specifies the pathname for the I<mount> program.
The default is I<mount>.

=item B<--umount=string>

Specifies the pathname for the I<umount> program.
The default is I<umount>.

=item B<--tar=string>

Specifies the pathname for the I<tar> program.
The default is I<tar>.

=item B<--tararg=string>

Specifies the initial arguments for the I<tar> program.
The default is I<cvf>.

=item B<--tarsuffixarg=string>

Specifies the suffix arguments for the I<tar> program.
The default is the empty string.
To exclude a database, you would pass I<--exclude dbname> here.

=item B<--tarfilesuffix=string>

Specifies the suffix for the tarball. This value should be set according
to the selected compression method (e.g. I<.tar.bz2> for bzip2 compression).
The default is I<.tar.gz>.

=item B<--compress=string>

Specifies the name of the compression program. Only used if I<backuptype> is
set to I<tar>. Some possibilities are I<gzip>, I<bzip2> or I<lzma>.
The program must support reading the to be compressed data from I<stdin> and
writing to I<stdout>, without requiring intermediate temporary files (for this
reason, 7zip cannot be used). It's also possible to use I<cat>. In this case,
no compression will be done. Make sure to update the I<compressarg> option
and the I<tarfilesuffix> accordingly.
The default is I<gzip>. Can be left empty.

=item B<--compressarg=string>

Specifies the command line options given to the I<compress> program. For I<gzip>,
that would be I<--stdout --verbose --best>, for I<lzma> or I<bzip2> I<--stdout --verbose -7>
and for I<cat>, it would be empty.
The default is I<--stdout --verbose --best>.

=item B<--rsnap=string>

Specifies the pathname for the I<rsnap> program.
The default is I<rsnap>.

=item B<--rsnaparg=string>

Specifies the arguments for the I<rsnap> program.
The default is I<7>, which causes it to keep the last 7 snapshot (useful
when running I<mylvmbackup> once per day).

=item B<--rsnaprsyncarg=string>

Specifies the arguments for the I<rsync> process that is spawned by
I<rsnap>, e.g. I<--exclude \*.o --bwlimit=8>. You don't need to provide
the double dashes usually required by I<rsnap> to separate these arguments.
Default value is the empty string.

=item B<--rsync=string>

Specifies the pathname for the I<rsync> program.
The default is I<rsync>.

=item B<--rsyncarg=string>

Specifies the arguments for the I<rsync> program.
The default is I<-avWP>. You must ensure that the recursive option is
included either implicitly by I<-a>, or explicitly by using I<-r>.

=item B<--zbackup=string>

Specifies the pathname for the I<zbackup> program.
The default is I<zbackup>.

=item B<--zbackuparg=string>

Specifies the arguments for the I<zbackup> program.
The default is I<--non-encrypted>.

You may use I<--password-file /path/to/pass> to create an encrypted zbackup
repository. The backup repository located in I<backupdir> will be initialized
automatically by running I<zbackup init> before the first invocation.

=item B<--xfs>

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

=item B<--log_method=string>

How to log output from this script. Valid options are I<console>, I<syslog>
or I<both>.
The default value is I<console>. Enabling the I<syslog> option requires
an installed I<Sys::Syslog> Perl module.

=item B<--syslog_socktype=string>

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

=item B<--syslog_facility=string>

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

=item B<--syslog_remotehost=string>

Host name of a remote syslog server.

=item B<--mail_report_on=string>

Enable sending the logging output via email to a specified email
address.

This option requires an installed I<MIME::Lite> Perl module as
well as a functional local sendmail (or alternative) facility.

You should also review and adjust the I<mail_from>, I<mail_to>
and I<mail_subject> configuration options to match you requirements.

Supported values are I<never>, this disables the mail reporting
completely. A value of I<always> sends an email report for each
invocation of I<mylvmbackup>, I<errors> only sends a report in
case of an error condition.

The default value is I<never>.

=item B<--mail_from=string>

The email address to be used in the I<From:> header for email
reports (requires the I<mail_report> option to be set).
The default value is I<root@localhost>.

=item B<--mail_to=string>

The email address to be used to send email reports to
(requires the I<mail_report> option to be set).
The default value is I<root@localhost>.

=item B<--mail_subject=string>

The text to be used in the I<Subject:> header for email
reports (requires the I<mail_report> option to be set).
The default value is "mylvmbackup report for localhost".

=item B<--configfile=string>

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

=item B<--help>

Displays a help message showing the available options.

=back

=head1 FILES

=over 4

=item B</etc/mylvbackup.conf>

The I<mylvmbackup> configuration file

=item B<mylvmbackup>

The executable Perl script that performs the work.

=back

=head1 REQUIREMENTS

For proper operation I<mylvmbackup> requires Perl 5 with the I<DBI> and
I<DBD::mysql> modules. It also needs the I<Config::IniFiles> to read the global
configuration file of the program.  I<Date::Format> is required to create the
time stamp used in the backup file names. In addition, it utilizes
I<Getopt::Long>, I<File::Basename> and I<File::Temp>, which usually are part of
the default Perl distribution.
I<File::Copy::Recursive> is used to copy the MySQL configuration file(s).
I<Sys::Syslog> is only required in case you want to enable the syslog log
facility.
The I<MIME::Lite> module is required when you enable the mail reporting
functionality. It also requires a functional local sendmail (or alternative)
facility.

It also requires several other external programs: GNU I<tar> and I<gzip> to back
up the data, LVM utilities (I<lvcreate>, I<lvremove> and I<lvs>) to create and
remove the LVM snapshot, and the system utilities I<mount> and I<umount>.
Please note that I<mylvmbackup> requires Linux LVM Version 2 or higher. It does not
work on LVMv1, as this version does not support writable snapshots.

Optionally, I<rsync> or I<rsnap> may be required instead of I<tar> and I<gzip>,
depending on which backup type you choose.

=head1 SEE ALSO

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

=head1 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>

=head1 RESOURCES

Main web site: L<http://www.lenzg.net/mylvmbackup>

Mailing list: L<https://launchpad.net/~mylvmbackup-discuss>

Source code, bug tracker: L<https://launchpad.net/mylvmbackup>

=head1 CREDITS

See the file CREDITS included in the distribution for a list of individual
contributors.

=head1 COPYING

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