MySQL Backup recovery based on LVM Snapshot

Mysql5.36 database has been installed in the test environment, but the data directory is not stored in the LVM volume. This demonstration is based on the LVM data backup and recovery, so the mysql DATA first

The mysql 5.5.36 database is installed in the test environment, but the data directory is not stored in the LVM volume. This demonstration is based on the LVM data backup and recovery.

Directory
1. Full database backup
2. Prepare LVM volumes
3. Data Recovery to LVM volumes
4. Back up data based on LVM snapshots
5. Data Disaster Recovery
6. Summary

Preface:
The mysql 5.5.36 database is installed in the test environment, but the data directory is not stored in the LVM volume. This shows how to back up and restore Data Based on LVM, therefore, the mysql DATA is first migrated to the LVM and then backed up the snapshot volume.
If innodb tables are backed up based on LVM, the transaction log files and data files must be on the same LVM logical volume, because at the same time, we can only take snapshots of one logical volume, if the transaction log and the data file are not on the same volume, the transaction log event will be inconsistent with the transaction committed in the real data after the snapshot is created.

1. Full database backup
Use mysqldum to back up existing data:
[Root @ mariadb ~] # Mysqldump-uroot-p123456 -- lock-all-tables -- flush-logs -- events -- routines -- master-data = 2 -- all-databases>/backup/data_dir/fulldata-'date + % F '. SQL
[Root @ mariadb ~] # Ls/backup/data_dir/
Fulldata-2015-04-14. SQL
[Root @ mariadb ~] # Service mysqld stop # stop mysql

2. Prepare LVM volumes

Prepare two volumes as the PV of LVM in advance:
[Root @ mariadb ~] # Fdisk-l
Disk/dev/sda: 53.7 GB, 53687091200 bytes
255 heads, 63 sectors/track, 6527 cylinders
Units = cylinders of 16065*512 = 8225280 bytes
Sector size (logical/physical): 512 bytes/512 bytes
I/O size (minimum/optimal): 512 bytes/512 bytes
Disk identifier: 0x000bf287
Device Boot Start End Blocks Id System
/Dev/sda1*1 13 102400 83 Linux
Partition 1 does not end on cylinder boundary.
/Dev/sda2 13 1926 15360000 83 Linux
/Dev/sda3 1926 2056 1048576 82 Linux swap/Solaris
/Dev/sda4 2056 6527 35916127 + 5 Extended
/Dev/sda5 2056 2709 5248011 8e Linux LVM
/Dev/sda6 2710 3363 5253223 + 8e Linux LVM

Create PV:
[Root @ mariadb ~] # Pvcreate/dev/sda5/dev/sda6
Physical volume "/dev/sda5" successfully created
Physical volume "/dev/sda6" successfully created

Create VG:
12 [root @ mariadb ~] # Vgcreate mygroup/dev/sda5/dev/sda6
Volume group "mygroup" successfully created

Create LV:
[Root @ mariadb ~] # Lvcreate-n mysqldata -- size 3G mygroup # Set the size to 3 GB
Logical volume "mysqldata" created

Format and mount the LV:
[Root @ mariadb ~] # Lvdisplay # display detailed information about a logical volume
[Root @ mariadb ~] # Mke2fs-t ext4/dev/mygroup/mysqldata # format
[Root @ mariadb ~] # Blkid/dev/mygroup/mysqldata # obtain UUID
/Dev/mygroup/mysqldata: UUID = "f863e626-e34e-4207-b9cb-7fbb9c5b7f1e" TYPE = "ext4"
[Root @ mariadb ~] # Mkdir/mnt/mydata # create a mount directory

Add the following line to the/etc/fstab file:
[Root @ mariadb ~] # Vim/etc/fstab
UUID = f863e626-e34e-4207-b9cb-7fbb9c5b7f1e/mnt/mydata ext4 defaults 0 0
[Root @ mariadb ~] # Mount-a # remount
[Root @ mariadb ~] # Mount
/Dev/sda2 on/type ext4 (rw)
Proc on/proc type proc (rw)
Sysfs on/sys type sysfs (rw)
Devpts on/dev/pts type devpts (rw, gid = 5, mode = 620)
Tmpfs on/dev/shm type tmpfs (rw)
/Dev/sda1 on/boot type ext4 (rw)
None on/proc/sys/fs/binfmt_misc type binfmt_misc (rw)
/Dev/mapper/mygroup-mysqldata on/mnt/mydata type ext4 (rw)

3. Data Recovery to LVM volumes

Create a data directory And set permissions:
[Root @ mariadb ~] # Mkdir/mnt/mydata/data
[Root @ mariadb ~] # Chown-R mysql: mysql/mnt/mydata/data
[Root @ mariadb ~] # Vim/etc/my. cnf # modify the data directory location
Datadir =/mnt/mydata/data
[Root @ mariadb ~] # Cd/opt/lamp/mysql55/# Switch to the mysql installation directory

Initialize the database:

[Root @ mariadb mysql55] # scripts/mysql_install_db -- user = mysql -- datadir =/mnt/mydata/data

Modify the directory to which datadir points:

[Root @ mariadb mysql55] # vim/etc/rc. d/init. d/mysqld
Datadir =/mnt/mydata/data

It is strange that mysql can be started normally when I have not modified datadir IN THE mysqld script file:

[Root @ mariadb mysql55] # service mysqld start

Access mysql and change the access password to import backup data:
Mysql> set password );
Mysql> flush privileges;
Mysql> source/backup/data_dir/fulldata-2015-04-14. SQL
Mysql> show databases; # Data recovered
+ -------------------- +
| Database |
+ -------------------- +
| Information_schema |
| Mydb1 |
| Mydb2 |
| Mysql |
| Performance_schema |
| Test |
+ -------------------- +