Use LVM snapshots for backup of MySQL database

There are a number of ways to database backup, and today the snapshot feature of LVM is used to complete the backup of MySQL database. LVM provides a near-hot-standby way of backing up data by creating a snapshot volume at a time in the same directory as the database, whereby the data accessed through the snapshot volume is the data at the moment the snapshot is saved, and then the data in the snapshot volume is copied to a different directory. The reason that he is almost hot-standby is because the database needs to be read-locked when the snapshot is created, during which time the database cannot write.

Prerequisites for backing up databases using LVM

1, the data file must be in the LV

2, the volume group where this logical volume resides must have sufficient space to use the snapshot volume

3, the data file and transaction log are on the same logical volume

In this case, the database mytest as an example to implement backup

mysql> use mytest;mysql> show tables; +------------------+| Tables_in_mytest |+------------------+| Students | +------------------+1 row in Set (0.00 sec)

Mysql> SELECT * from students;+----+-------+-----+--------+| ID | Name | Age |  Gender |+----+-------+-----+--------+| 1 |  Tom | 20 |  M | | 2 |  Jerry | 23 |  F | | 3 |  Sara | 19 | F | +----+-------+-----+--------+3 rows in Set (0.09 sec)

Operation Steps:

1. Open MySQL session, apply read lock, Roll binary log

Mysql> flush TABLES with READ lock;mysql> flush LOGS;

2, through another terminal, save the binary log file and related location information

# Mysql-uroot-p-E "SHOW MASTER status\g" >/backup/master-' Date +%f '. Info

3. Create a Snapshot volume

# lvcreate-l 30m-s-P r-n mydata-snap/dev/vg_root/mydata

4, Release the lock

Mysql> UNLOCK TABLES;

5. Mount the snapshot volume and start the backup

# mkdir/mnt/snap# Mount/dev/vg_root/mydata-snap/mnt/snap-o ro# mkdir/backup/full-backup-' date +%F ' # cp-a/mnt/snap/d ata/*/backup/full-backup-2015-09-20/# cd/backup/full-backup-2015-09-20/# rm-f mysql-bin.* # After a full backup, the binary log can be deleted

6. Unmount the snapshot volume and delete

# cd # umount/mnt/snap# Lvremove--force/dev/vg_root/mydata-snap

7, incremental backup binary log

# mysqlbinlog--start-datetime= "2015-09-20 11:34:57" mysql-bin.000026 mysql-bin.000027 >/backup/increment-' Date +% f-%h-%m-%s '. sql

At this point, the LVM-based snapshot backup database is complete

Use LVM snapshots for backup of MySQL database