How to create LVM Snapshot and Take a backup of mysql dir ?

Some preliminary information is needed.

1. Where are the MySQL data files stored?

# mysqladmin -u root -p12345 variables | grep datadir

 
| datadir                         | /var/lib/mysql/    

         |

This shows that the data files are located at /var/lib/mysql.

2. Which logical volume hosts this location?

# df /var/lib/mysql

 
Filesystem           1K-blocks      Used Available Use% Mounted on
/dev/mapper/vg0-var  209698268  45532856 164165412  22% /var

This shows that the volume group is vg0, and the logical volume name is var. So, the full block device path that the LVM tools will understand is /dev/vg0/var. This can be confirmed with the following

# lvscan

 
  ACTIVE            '/dev/vg0/var' [200.00 GB] inherit

3. How much unallocated space is available for the snapshot in the vg0 volume group?

# vgdisplay vg0 | grep Free

 
  Free  PE / Size       9551 / 37.31 GB

This shows that there are 37.31GB of free space on the vg0 volume group. This is important to note, because this free space is where changes to the live database are tracked while the snapshot is present.

Create an LVM Snapshot

1. Connect to MySQL, flush the tables to disk, and lock them. Do not do this with mysqladmin, and be sure to leave the database session open. As soon as a client (such as mysqladmin) disconnects, this lock is lifted. In order to guarantee data integrity, the database must remained locked until the LVM snapshot is created. The amount of time that this operation takes will vary based on how much data needs to be flushed to disk, but it is generally very quick.

FLUSH TABLES WITH READ LOCK

2. In another terminal session, create the LVM snapshot. This snapshot needs to be large enough to accommodate the changes that will be made to the database while the snapshot is present. Because this snapshot will be short lived, the shortcut "100%FREE" can be used, which will use all 37.31GB of unallocated space in this case. This process is nearly instantaneous because LVM uses a copy-on-write (COW) snapshot method.

lvcreate -l100%FREE -s -n mysql-backup /dev/vg0/var

3. Back at the original MySQL session, release the read lock so that normal database operation can resume.

UNLOCK TABLES

At this point, there is a consistent, point-in-time snapshot of the MySQL file structure stored in the LVM snapshot. The database can now go on with its business, and the only locking required was to flush any data from memory to disk.

Snapshot Magic

Using lvscan again, the new snapshot can be seen.

# lvscan

 
  ACTIVE   Original '/dev/vg0/var' [200.00 GB] inherit
  ACTIVE   Snapshot '/dev/vg0/mysql-backup' [37.31 GB] inherit

The snapshot can now be mounted at an arbitrary location (If /dev/vg0/var is an XFS volume, add "-o nouuid -t xfs" to the mount command).

mkdir -p /mnt/snapshot

 
mount /dev/vg0/mysql-backup /mnt/snapshot

Any standard filesystem backup method can be used to store a copy of /var/lib/mysql as mounted under /mnt/snapshot.