Snapshot backup MySQL database and examples

Snapshot backup MySQL database and example 1. create logical volume

Create a logical volume according to the following connection document

Http://www.178-go.com/tags/lvm

Requirement: physical volume 20 GB; volume Group name: vg0; logical volume 2 GB; logical volume name: mydata

Create and mount a logical volume mount point

# Mkdir/mydata

# Mount/dev/vgo/mydata

II. install the MySQL database

If mysql is installed on the machine, skip this step.

Modify the value of datadir in the mysql configuration file to/mysata.

Copy all the data files of the original database to the/mydata file, that is, transfer the location of the database

Modify the owner group of the/mydata directory and its content

Restart the mydql service.

If you need to reinstall it, you can install it according to the following documents:

Http://www.178-go.com/archives/

Mysql.html

After the installation is complete, you only need to change the value of datadir in the mysql configuration file to/mydata

The procedure is as follows:

# Sed-I's/^ datadir. * $/datadir = // mydata/g'/etc/my. cnf

# Service mysqld restart

Modify the owner group of the/mydata directory and its content

# Chown-R mysql: mysql/mydata /*

# Chown mydql: mysql/mydql

Start mysql service

# Service mysqld start

3. create a snapshot

Lock All tables first

# Mydql

> Flush tables with read lock;

>/Q

Create Snapshot

# Pvvreat-L 20 M-s-n mysnap/dev/vgo/mydata

Note: For more information about this command, see the following documents:

Http://www.178-go.com/archives/lvm.html

Unlock table

# Mysql

> Unlock tables;

>/Q

Create a snapshot mount point and mount it

# Mkdir/mysnap

# Mount/dev/vgo/mysnap

# Ls

The files in the/mysnap directory are exactly the same as those in the/mydata directory.

At this time, any modifications made to the original volume will not affect the files in/mysnap.

IV. test

Modify the database

# Mysql

> Create database mydb;

> USE mydb;

> Create table sanptest (

> Name char (30 ),

> Id int );

>/Q

Copy the mydb file from the snapshot directory to another directory for physical backup.

Note: the entire database should be backed up in production applications.

# Cd/mysanp

# Cp-rp mydb/tmp/

Detach a snapshot volume

# Umount/mysnap

# Lvremove/dev/vgo/mysnap

The following are some database deletion operations.

# Mydql

> Flush logs;

> Drop database mydb;

>/Q

At this time, the/mydata directory does not have the mydb file

Now copy the mydb file that was physically backed up back.

# Copy-rp/tmp/mydb/mydata

# Service mysqld restart

View database

# Mysql

> Show databases;

It is found that the database is exactly the same as the database before modification, and there is no mydb database just created

That is to say, snapshot backup only saves the data information at the time when the snapshot is made.

At this time, if you want to retrieve the lost data after the snapshot, you just need to useBinary log

View binary logs

# Mysql

> Show binlog events in 'MySQL-bin.20.x ';

Here, X indicates the data. the log content varies with numbers. the larger the number, the closer it is to the present.

All these logs are stored in the/mydata file.

The display is roughly as follows:

To restore an insert operation from the binary log file mysql-bin.0000013, run the following command:

# Mysqlbinlog-start-position 106-end-position 220 mysql-bin.0000013>/tmp/a. mysql

# Mysql-uroot-p </tmp/a. SQL

The result is as follows: