Backup of MySQL Databases on Logical Volumes

From The Open Source Backup Wiki (Amanda, MySQL Backup, BackupPC)
Revision as of 02:43, 7 March 2013 by Paddy (talk | contribs) (→‎Disadvantage of Using LVM Snapshots for Backup)
(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)
Jump to navigationJump to search

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 a mechanism to help backup such installations using snapshot plugin. 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.

For file systems such as XFS, VxFS (Veritas file systems) that support freeze/thaw operations, their I/O is stopped before taking a snapshot.

MySQL Configuration

To be able to take advantage of lvm snapshots, the mysql data should be residing 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 logical volumes.
    • The InnoDB shared data files are on a separate logical volume
    • The InnoDB logs are on a separate logical volume
MySQL ZRM Snapshot.png
  • If the ZRM user is the default user 'mysql' or non-root user, sudo(8) configuration file must be configured. Please see Pre-installation steps chapter for more information.

Pre-conditions for Using LVM Snapshots

  • Additional free extents in the logical volume are needed for creating snapshots.
  • This can be checked with 'vgdisplay' command.
  • The free extents required are specified in mysql-zrm.conf.
  • Note that during the process of creating the backups the disk space may fall short.
  • 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 snapshot volume may run out of space.
  • If the snapshot volume runs out of space, the backup will not be consistent.
  • Hence specifying the right amount of space for creating the snapshot is critical.
  • See backup parameters in mysql-zrm.conf section.
  • 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, backup using snapshots will not be performed. Instead raw backup via mysqlhotcopy or a logical backup using mysqldump will be performed based on the storage engines of the tables in each of the databases.
  • Backup image directory (/var/lib/mysql-zrm) and the database being backed up must not be on the same ZFS filesystem.

Configuration Parameters

The snapshot-plugin parameter must be specified in the mysql-zrm.conf. It should point to LVM snapshot plugin location.

snapshot-plugin="/usr/share/mysql-zrm/plugins/lvm-snapshot.pl"

The snapshot-size parameter can be specified in the mysql-zrm.conf file to specify the size of the snapshot volume to be create.

snapshot-size=10M

The above will create a snapshot of size 10 MB during the backup run. Please see Backup Parameters for more details.

The parameter can be configured in:

  • The global configuration file,
  • Specific file relating to a backup set, or
  • Passed as an argument to backup action on the Command line.

Note

  • This option is ignored if the backup-mode is logical.

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

  • Additional disk space for logical volume snapshots is required.