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. 

How to creating LVM mirroring ?

How to creating LVM mirroring ?

Example 1: Checking the free space to create partition.

[root@localhost ~]# fdisk -l

Disk /dev/sda: 10.7 GB, 10737418240 bytes
255 heads, 63 sectors/track, 1305 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes

Device Boot Start End Blocks Id System
/dev/sda1 * 1 765 6144831 83 Linux
/dev/sda2 766 892 1020127+ 82 Linux swap / Solaris

Above command shows total cylinders 1305 and used cylinders are 892. It means still you have free cylinders, hence we can create partition.

Example: 2 Creating new 3 partition using fdisk.

[root@localhost ~]# fdisk /dev/sda

Press n
Press p
Press “Enter” for default starting cylinder”
Enter 100MB+

Now Change the partition type to 83 and finally reboot the system.
Similarly create one more partition of 100MB.

Note: Make sure that partition ID must be 8e, while creating partitions.

Step 2. Create Physical Volumes

Example: 3

[root@localhost ~]# pvcreate /dev/sda[5,6,7]

Physical volume “/dev/sda5″ successfully created
Physical volume “/dev/sda6″ successfully created
Physical volume “/dev/sda7″ successfully created

Above command will initialize partition as lvm partition. Note: Here LVM will assign PV UUID to the partition only.

Step 3. Create Volume Group

Example: 4

[root@localhost ~]# vgcreate datavg /dev/sda[5,6,7]

Volume group “datavg” successfully created

Above command will crate volume group by name datavg. LVM create VGDA(Volume Group Descriptor Area. The VGDA Contains information about Volume Group. LVM Create PE (Physical Extent)

Step 4. Creating Mirrored Logical Volumes.

[root@localhost ~]# lvcreate -L 50M -m1 -n mirrorlv datavg

Rounding up size to full physical extent 52.00 MB
Logical volume “mirrorlv” created

While creating mirrored volumes in particular volume group, you have to specify the number of copies of the data required, –m argument of the lvcreate command. For creating 1 mirror copy of data, you have to specify the –m1

In above command, we have created 50MB of size logical volume with 1 copy of data to be maintained.

Step 5. Creating File system.

[root@localhost ~]# mkfs /dev/datavg/mirrorlv

mke2fs 1.39 (29-May-2006)
Filesystem label=
OS type: Linux
Block size=1024 (log=0)
Fragment size=1024 (log=0)
13328 inodes, 53248 blocks
2662 blocks (5.00%) reserved for the super user
First data block=1
Maximum filesystem blocks=54525952
7 block groups
8192 blocks per group, 8192 fragments per group
1904 inodes per group
Superblock backups stored on blocks:
8193, 24577, 40961

Writing inode tables: done

Writing superblocks and filesystem accounting information: done

This filesystem will be automatically checked every 38 mounts or
180 days, whichever comes first. Use tune2fs -c or -i to override.

[root@localhost ~]#

Step 6. Mounting Filesystem.

Most commonly used method for mounting filesystem is either manually using mount command or by adding entries in /etd/fstab, so that filesystem mount during boot time.

Syntax:

Mount [option]

Example

[root@localhost ~]# mount /dev/datavg/mirrorlv /database

In above example, we have mounted /dev/datavg/mirrorlv logical volumes to /database directory. You can verify by executing following command.