MySQL Backup recovery based on LVM Snapshot

Source: Internet
Author: User
Tags mysql backup
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.

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

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 |
+ -------------------- +

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.