Linux Logical Volume Snapshots

MySQL Configuration

• Sudo privileges must be configured for mysql user on the MySQL server (see the next section).
• If you are backing up a remote MySQL server, the destination directory specified on the Backup Where page must exist on the MySQL server as well as the ZRM server. The MySQL backup user (OS-level) must have read/write permission to access this directory.

The mysql data must reside on logical volumes. The following are some of the possible configurations

• All MySQL data is on a single logical volume.
• Specific database directories are on different logical volumes.
• For databases containing InnoDB based tables, the lvm snapshot can only be used if the database directory, the InnoDB data files, and the InnoDB logs are on the same logical volume.

Pre-conditions for Using LVM Snapshots

The MySQL backup user must be granted sudo privileges to execute lvm commands on the MySQL server. Add a line similar to the following example to /etc/sudoers on the MySQL server:

mysql <FQDN of MySQL Server>=NOPASSWD:/bin/mount,NOPASSWD:/bin/umount,NOPASSWD:/bin/df,NOPASSWD:/usr/sbin/lvdisplay,NOPASSWD:/usr/sbin/lvcreate,NOPASSWD:/usr/sbin/lvremove,NOPASSWD:/sbin/fuser

Where MySQLserver.mycompany.com is the fully-qualified domain name for the MySQL server. Note that if lvmcommands are installed in other locations, the above example would not work without editing it to reflect the different paths. Please see KB article for more information sudo configuration.

Additional free extents in the logical volume are needed for creating snapshots. You can check extents using thevgdisplay command.

The free extents required are specified in mysql-zrm.conf

LVM stores the snapshot blocks corresponding to the blocks that are modified in the original logical volume in the snapshot volume. If the database is highly active during the backup, many blocks will be modified and the snapshot volume may run out of space.

Specifying a sufficient amount of space for creating the snapshot is critical; if the snapshot volume runs out of space, the backup will not be consistent.

All MySQL database files (data, log, indexes) must be stored in LVM logical volumes to ensure consistency. If any of the files are not on LVM, the snapshot is skipped, and either a raw backup via mysqlhotcopy or a logical backup using mysqldump will be taken based on the storage engines of the tables in each of the databases.

Configuration Parameters

The Backup How page allows you to select LVM snapshots as a backup mechanism.

Snapshot Size

Set the size of the LVM snapshot. For raw backups, each specified database is first checked to ensure that it is on an LVM volume, and then a snapshot of the specified size is created and used to backup the database (unless the quick (no-copy) option is selected; see below). If the specified database is not on a LVM volume, either mysqlhotcopy or mysqldump is used to create the backup.

Size of LVM snapshot depends on amount of activity in the logical volume during the backup window. This is difficult to predict. If the value is too small, the backup will fail. Select a value conservatively for the first backup run. The ZRM logs on the server (/var/log/mysql-zrm/mysql-zrm.log) shows the amount of snapshot space that was used during backup window when the backup completes successfully. This value can be used to tune the snapshot size configuration. Look for the value of COW-table size as shown below in the log message:

Tue May 04 12:59:28 2010: INFO: Output of the command sudo lvdisplay
/dev/nik_vg/zrm5pEeycW9LA 2>/tmp/ZRMKLOSo2o9 is   --- Logical volume ---
   LV Name                /dev/nik_vg/zrm5pEeycW9LA
   VG Name                nik_vg
   LV UUID                DronVf-GybO-rSQf-3Uqb-RG6I-krvP-aLTw8o
   LV Write Access        read/write
   LV snapshot status     active destination for /dev/nik_vg/lv_mysql
   LV Status              available
   # open                 0
   LV Size                30.00 GB
   Current LE             7680
   COW-table size         12.00 MB
   COW-table LE           3
   Allocated to snapshot  0.65% 
   Snapshot chunk size    8.00 KB
   Segments               1
   Allocation             inherit
   Read ahead sectors     0
   Block device           253:4

Snapshot Mount Options

List of file system mount options used when the lvm snapshot(s) are mounted on the MySQL server during full backup process. This field is optional.

Backup Type

Choose the method of snapshot backup. The Standard (Copy) option specifies that the snapshot should be copied to a standard ZRM for MySQL & MariaDB backup archive. The Quick (No Copy) option specifies that the snapshot itself should be used as a near-line backup. Quick backups are convenient as they provide faster backups and restores, but because they remain on the MySQL server they do not protect against media or server failure. Note that if the quick option is specified, the compress and encrypt options are ignored, and the backup size will always be zero. In addition, no checksums are performed, which means that quick snapshot backups cannot be verified. Quick snapshot backups may be converted to standard backups stored on the ZRM server using the Convert Backup option available from the Reports menu tab.

Fallback for Logical

If this field is set to yes and snapshot backup fails, the logical backup is attempted. Set the value to No if you do not want to do logical backup if there is a snapshot backup failure.

Remote MySQL Binary Path

Path to the MySQL commands on the MySQL server

Binary Log Path

Location of binary logs on the MySQL server that are used for log incremental backups.

Advantages of Using LVM Snapshots for Backup

• Hot backup for transaction based storage engines (no impact on the application using the database) and Warm backups for other storage engines.
• Backup time is not dependent on the size of the database. As a result, this backup method is suitable for large databases.
• Almost instantaneous. The database gets locked only for the time taken to create the snapshot.

Disadvantage of Using LVM Snapshots for Backup

• Works well only for filesystems that support freeze operation such XFS, VxFS.
• Additional disk space for logical volume snapshots is required.
• LVM snapshots can be used only for local backups.