LVM Snapshot backup MySQL

Principle of snapshot backup (from other blogs):

Principle: Through LVM snapshot to the LVM to take a picture, when the LVM host changes, the LVM snapshot of the LVM changes the content before the change in the snapshot, so that in the time of the LVM snapshot is valid, we see the LVM snapshot content is always LVM in the creation of LVM snapshot content, By backing up LVM snapshots, you can achieve the purpose of online backup of LVM. It should be noted that when LVM snapshot is more than LVM, if the change of LVM is greater than LVM snapshot, the LVM snapshot will become unreadable and fail; If the LVM snapshot is greater than or equal to the LVM true, the preceding situation does not occur.
When a snapshot is created, only the metadata (meta-data) of the data in the original volume is copied. When created, there is no physical copy of the data, so the creation of snapshot is almost real-time, and when a write operation is performed on the original volume, snapshot tracks the change of the original volume block, when the data to be changed in the original volume is copied to the snapshot reserved space before the change. So the implementation of this principle is called write-time Replication (copy-on-write).
Before a write is written to a block, cow moves the raw data into the snapshot space, ensuring that all data remains consistent when snapshot is created. For the snapshot read operation, if the read data block is not modified, then the read operation will be redirected directly to the original volume, if you want to read the modified block, then read the copy to the snapshot block.

LVM snapshot backups are almost hot prepared:

Premise:
1, the data file should be on the logical volume;
2. The volume group where this logical volume is located must have sufficient space to use the snapshot volume;
3. The data file and transaction log should be on the same logical volume;

[Email protected] mydata]# df-hfilesystem                    Size  used Avail use% mounted on/dev/mapper/volgroup-lv_root  4.0G  3.7G  125M  97%/tmpfs                         935M     0  935M   0%/dev/shm/dev/sda1                     485M   39M  421M   9%/boot/dev/mapper/myvg-mylv         886M  151M  691M  18%/mydata           

Process:

1. Add a lock to the MySQL table

Mysql> flush tables with read lock; Query OK, 0 rows Affected (0.00 sec)

2, through another terminal, to save the binary log files and related location information;

[Email protected] mydata]# mysql-e ' show Master Status\g ' >/backup/master.info*************************** 1. Row ***************************            file:mysql-bin.000030        position:472    

3. Create a snapshot volume for/mydata, named Back_test

[[email protected] mydata]# lvcreate-l 30m-s-n back_test/dev/mapper/myvg-mylv rounding up size to full   physical ex Tent 32.00 MiB  Logical Volume "Back_test" created

4. Release the lock and insert the data

mysql> INSERT into student (name) value (' David '); ERROR 1223 (HY000): Can ' t execute the query because you have a conflicting read lockmysql> unlock tables; Query OK, 0 rows Affected (0.00 sec) mysql> INSERT into student (name) value (' David2 '); Query OK, 1 row affected, 1 Warning (0.00 sec) mysql> INSERT into student (name) value (' David3 '); Query OK, 1 row affected, 1 Warning (0.00 sec) Mysql> commit    ; Query OK, 0 rows affected (0.05 sec)

5. Mount the snapshot volume, backup, delete the snapshot volume

[Email protected] mydata]# mount-r/dev/myvg/back_test/mnt/[[email protected] mnt]# cp-rfia./*  

Do you really want to remove active logical volume Back_test? [y/n]: Y
Logical volume "back_test" successfully removed

6, incremental backup of the binary log, if there are multiple transactions can be based on 472 corresponding point in time to back up

[Email protected] mydata]# mysqlbinlog--start-position=472 mysql-bin.000030 mysql-bin.000031 >/root/lv_back.sql

Back up multiple transactions based on point in time, note the time format
[Email protected] mydata]# mysqlbinlog--start-time= ' 2015-08-06 17:32:08 ' mysql-bin.000030 mysql-bin.000031 >/root /lv_back.sql  

7. Simulate MySQL corruption

[Email protected]/]# rm/mydata/*-RF
[[Email protected]/]# service mysqld stop
MySQL server PID file could not being found! [FAILED]

8. Recovery

You can delete the binary log without copying the binary log

[[email protected] mydata]# cp-riaf/tmp/*  /mydata///Copy files to MySQL data file location chown-r mysql:mysql   /mydata/* 　　　　　　　　　　　　　　　　　　If the/mydata under the master is not the MySQL permission, you need to modify the MySQL permissions mysql>set sql_log_bin=0                                   //Depending on the situation whether to turn off mysql> \./root/lv_back.sql Restore Binary Log transactions

To see if a transaction performed after a hot spare recovers

Mysql> select * from student;
+----+--------+-----+------+
| ID | name | Age | Cid |
+----+--------+-----+------+
| 1 |  Sean |    22 | 6 |
| 5 |   Alice | 0 | NULL |
| 6 |   Tom | 0 | NULL |
| 7 |   David2 | 0 | NULL |
| 8 |   DAVID3 | 0 | NULL |
+----+--------+-----+------+

LVM Snapshot backup MySQL