MySQL Backup recovery based on LVM Snapshot

MySQL Backup recovery based on LVM Snapshot

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 for root @ localhost = PASSWORD ('20140901 ');
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 |
+ -------------------- +

4. Back up the database
4.1 prepare the Backup Directory
[Root @ mariadb mysql55] # mkdir-pv/backup/'date + % F'/{data, binlog}
# Use the date command as a directory, and create data and binlog directories to store data and binary log information respectively.

4.2 modify variables and request full table read locks
Mysql> set global sync_binlog = 1; # SET this variable to 1, so that each event can be synchronized to the binary log file as much as possible, so as to consume IO to ensure data consistency as much as possible
Mysql> show master status; # view the binary log and position. This information is saved in binlog.txt of/backup/2015-04-14/binlog.
+ ------------------ + ---------- + -------------- + ------------------ +
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+ ------------------ + ---------- + -------------- + ------------------ +
| Mysql-bin.000016 | 1068076 |
+ ------------------ + ---------- + -------------- + ------------------ +
1 row in set (0.00 sec)
Mysql> flush logs; # refresh LOGS
Mysql> flush tables with read lock; # Do not close this mysql interaction interface after the read lock request arrives

In the innodb table, even if a read lock is requested, InnoDB may still perform read/write operations in the background. You can use "mysql> show engine innodb status;" to view the STATUS of background processes, after no write request is sent, back up the data.
4.2 create a snapshot
Create a 1 GB snapshot volume data-snap in read-only mode:
[Root @ mariadb mysql55] # lvcreate -- snapshot/dev/mygroup/mysqldata-n data-snap -- size 1G -- permission r

View the snapshot volume details (the snapshot volume is also LV ):
[Root @ mariadb mysql55] # lvdisplay

4.3 unlock
Go back to the mysql interactive interface of the locked table and unlock:
Mysql> unlock tables;
Mysql> set global sync_binlog = 0; # This parameter can be adjusted based on the server disk I/O load.

4.4 Mount snapshot volumes and back up data
[Root @ mariadb ~] # Mount/dev/mygroup/data-snap/tmp/# mount the snapshot volume
[Root @ mariadb ~] # Ls/tmp/data/
Ibdata1 ib_logfile0 ib_logfile1 mariadb. pid mydb1 mydb2 mysql performance_schema test
[Root @ mariadb mysql55] # cp-aR/tmp/data/backup/2015-04-14/data/# Back up data
[Root @ mariadb mysql55] # ls/backup/2015-04-14/data/
Ibdata1 ib_logfile0 ib_logfile1 mariadb. pid mydb1 mydb2 mysql performance_schema test

4.5 release snapshot volumes

[Root @ mariadb ~] # Umount/tmp/
[Root @ mariadb ~] # Lvremove/dev/mygroup/data-snap
Do you really want to remove active logical volume data-snap? [Y/n]: y
Logical volume "data-snap" successfully removed

5. Data Disaster Recovery

Destroys data and deletes all files in the data directory:
[Root @ mariadb mysql55] # rm-rf/mnt/mydata/data /*
[Root @ mariadb mysql55] # service mysqld stop # The PID file is also deleted and the service cannot be stopped normally
ERROR! MySQL server PID file cocould not be found!
[Root @ mariadb mysql55] # killall mysqld

Copy the backup data to the data directory:
[Root @ mariadb mysql55] # cp-Ra/backup/2015-04-14/data/*/mnt/mydata/data/
[Root @ mariadb mysql55] # ll/mnt/mydata/data/# Check whether the permissions of each file are mysql
Start the service:
[Root @ mariadb mysql55] # service mysqld start
Starting MySQL SUCCESS!

Check whether the data has been restored:
Mysql> show databases;
+ -------------------- +
| Database |
+ -------------------- +
| Information_schema |
| Mydb1 |
| Mydb2 |
| Mysql |
| Performance_schema |
| Test |
+ -------------------- +
6 rows in set (0.00 sec)
Mysql> SELECT * FROM mydb1.tb1;
+ ---- + ------ +
| Id | name | age |
+ ---- + ------ +
| 1 | tom | 10 |
| 2 | jack | 20 |
| 3 | zcj | 18 |
+ ---- + ------ +

6. Summary

The LVM-based data backup solution almost implements hot backup, but the online services are affected when the read lock request arrives. Once the read lock request arrives, the snapshot creation is almost completed instantly, the Snapshot volume can be unlocked after being created, and the backup is as simple as copying or archiving files. During data recovery, you only need to shut down the mysql process and copy the backup data to the corresponding data directory to ensure that the permissions of the Data Directory are correct and enable the Service. Because the data file is copied directly, therefore, indexes do not need to be rebuilt.

• Use LVM to create elastic disk storage-Part 1
• Expanding/downgrading LVM in Linux (Part 2)
• Recording and restoring logical volume snapshots in LVM (part 3)
• Set a streamlined resource allocation volume in LVM (part 4)
• Block multiple LVM disks (Part 5)

This article permanently updates the link address: