Backing up the MySQL database using Lvm-snapshot

Prerequisite Requirements:

The transaction log must be on the same volume as the data file;

To request a global lock on MySQL before the snapshot volume is created, release the lock manually after the snapshot creation is complete;

Once the global lock is completed, log scrolling is done, and binary log files and location tags (manual) are done;

1. A read lock is applied to the data, the binary log file is scrolled, and the current binary file start time period is recorded:

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/53/C4/wKioL1RvSi_D4vfuAAFtKk5G1DY490.jpg "title=" 1.jpg " alt= "Wkiol1rvsi_d4vfuaaftkk5g1dy490.jpg"/>

2, do a snapshot;

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M01/53/C6/wKiom1RvSoDiv_mdAABmlT0HCyc181.jpg "title=" 1.jpg " alt= "Wkiom1rvsodiv_mdaabmlt0hcyc181.jpg"/>

3. Release the Read lock:

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M01/53/C4/wKioL1RvSx7D11F2AAAutnKi6FY453.jpg "title=" 1.jpg " alt= "Wkiol1rvsx7d11f2aaautnki6fy453.jpg"/>

4. Mount the snapshot, copy the data from the snapshot, and delete the snapshot:

[Email protected] mydata]# mount/dev/vg/mysql/mnt/-o ro

[Email protected] mydata]# Mkdir/mysqlback

[Email protected] mydata]# cp-a/mnt//mysqlback/20141121

[Email protected] mydata]# umount/mnt/

[Email protected] mydata]# Lvremove/dev/vg/mysql

Do you really want to remove active logical volume MySQL? [y/n]: Y

Logical volume "MySQL" successfully removed

5. Inserting data into a table is a change in the data, to restore this information at the end using binary logs.

Mysql> INSERT into NEWTB values (' Jack ');

Query OK, 1 row affected (0.01 sec)

Mysql> select * from NEWTB;

+------+

| Name |

+------+

| Tom |

| Jack |

+------+

2 rows in Set (0.00 sec)

6, combined with the previous recorded binary log start time to export to a file

[Email protected] mydata]# mysqlbinlog--start-position=107 mysql-bin.000011 >/tmp/20141121.sql

7. Stop the database, delete the data, try to recover the data

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/53/C6/wKiom1RvTj2gy3E0AAJsJ4-Pw4g601.jpg "title=" 1.jpg " alt= "Wkiom1rvtj2gy3e0aajsj4-pw4g601.jpg"/>

8. Start the MySQL service and see if the data is restored:

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/53/C6/wKiom1RvTrjR7p6fAAM4fDuE2lU178.jpg "title=" 1.jpg " alt= "Wkiom1rvtrjr7p6faam4fdue2lu178.jpg"/>

9. Use the binary log to restore the user that was created later:

Mysql> Source/tmp/20141121.sql;

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/53/C4/wKioL1RvUK7zh8yUAAA5lIuAUOw949.jpg "title=" 1.jpg " alt= "Wkiol1rvuk7zh8yuaaa5liuauow949.jpg"/>

Summarize:

Backup process:

1. A read lock is applied to the MySQL database to prevent user data from being inserted during the backup process, resulting in inconsistent data after the backup

2, Roll back the log, make a full backup of the current state of data, after the data with binary log recovery

3. Use the show MASTER status command to record the start time of the log

4. Use the Lvcreate command to take a snapshot of LV

5. Remove the Read lock

Recovery process:

6. Mount the created LV snapshot to the directory and copy the data inside

7. Unmount the snapshot, and then delete the snapshot

8. Copy the data copied from the snapshot to/data/mydata

9. Export the required binary data to a xx.sql file using Mysqlbinlog combined with the start point of the previous record

10, the SQL file into the database is completed rectification recovery process

This article is from the "Linux" blog, so be sure to keep this source http://zhangshijie.blog.51cto.com/806066/1581096

Backing up the MySQL database using Lvm-snapshot