Logical Volume Management (LVM) is a way of virtually partitioning a hard disk space such that it can be flexibly allocated to various applications. It is being utilized by an increasing number of MySQL installations.
ZRM for MySQL has an optional mechanism to help backup such installations using snapshots (a feature license is required). It can create temporary snapshots of the logical volumes and use the snapshot volume to do backups. The advantage of using snapshots is that you need to lock the database tables only for the time taken to create a snapshot. The snapshots are removed when the backups are completed. Snapshots help to create a consistent copy of the MySQL database as the consistency is ensured before the snapshot is taken.
On file systems such as XFS, VxFS (Veritas file systems) that support freeze/thaw operations, file system activity is stopped before taking a snapshot.
The mysql data must reside on logical volumes. The following are some of the possible configurations
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.
The Backup How page allows you to select LVM snapshots as a backup mechanism.
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
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.