Some preliminary information is needed.
- 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.
- 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
ACTIVE '/dev/vg0/var' [200.00 GB] inherit
- 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
- 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
- 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
- Back at the original MySQL session, release the read lock so that normal database operation can resume.
Snapshot MagicUsing lvscan again, the new snapshot can be seen.
ACTIVE Original '/dev/vg0/var' [200.00 GB] inherit
ACTIVE Snapshot '/dev/vg0/mysql-backup' [37.31 GB] inherit
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.