MySQL Data Recovery full backup recovery and incremental recovery (for example, hand-deleted libraries)

650) this.width=650; "src=" Http://s1.51cto.com/wyfs02/M01/87/F9/wKioL1fmI3vD8aEIAACH7C0ZO3w479.png "title=" Data recovery. png "alt=" Wkiol1fmi3vd8aeiaach7c0zo3w479.png "/>

data recovery schematic diagram

Test environment MySQL5.5

1 first create a new database Lampol data table test

Create Database Lampol;

Use Lampol;

CREATE TABLE test (ID int (ten), name varchar (10));

2 Inserting data information

INSERT into test values (1, ' lampol1 ');
INSERT into test values (2, ' lampol2 ');

3 post-Insert information

Mysql> select * from Lampol.test;
+------+---------+
| ID | name |
+------+---------+
| 1 | Lampol1 |
| 2 | Lampol2 |
+------+---------+

4 start to simulate full backup of the wee hours

Mysqldump-uroot-proot--flush-logs--single-transaction--master-data=2 lampol >/shell/test.sql

(1)--flush-logs Refresh the cutting log for easy recovery of incremental Binlog

(2)--single-transaction InnoDB storage engine MyISAM--lock-all-tables (lock table)

(3)--master-data=2 record the location of the backup Binlog logs and POS

650) this.width=650; "src=" Http://s5.51cto.com/wyfs02/M02/87/FD/wKiom1fmJoXg7r1NAAANHIW3bts849.png "title=" Qq20160924151518.png "alt=" Wkiom1fmjoxg7r1naaanhiw3bts849.png "/>

Full-volume backup successfully logs Msyql-bin logs and locations

5 after backup start to continue inserting data this data is not in full reserve

INSERT into test values (3, ' lampol3 ');
INSERT into test values (4, ' lampol4 ');

650) this.width=650; "src=" Http://s1.51cto.com/wyfs02/M01/87/FD/wKiom1fmJ4_ytEkaAAAVrBJ0VAw797.png "title=" 1.png " alt= "Wkiom1fmj4_ytekaaaavrbj0vaw797.png"/>

6 Execute drop database Lampol;

After the database has been deleted

Start recovery

1 stop external access first; Stop the Web server to start preparing for recovery

Flush logs Refresh Binlog log otherwise the full amount of late imports may be written into binlog impact recovery

650) this.width=650; "src=" Http://s5.51cto.com/wyfs02/M00/87/FD/wKiom1fmLpLgwhzRAABP8-SaWzs461.png "title=" 6.png " alt= "Wkiom1fmlplgwhzraabp8-sawzs461.png"/>

2 Start restore full volume backup create deleted database

650) this.width=650; "src=" Http://s4.51cto.com/wyfs02/M02/87/FA/wKioL1fmKImCaZ7-AAAO1yJs1XE475.png "title=" 3.png " alt= "Wkiol1fmkimcaz7-aaao1yjs1xe475.png"/>

Mysql-uroot-proot Lampol<test.sql
650) this.width=650; "src=" Http://s1.51cto.com/wyfs02/M01/87/FA/wKioL1fmKN6iCVwMAAAfaY7Idhs392.png "title=" 4.png " alt= "Wkiol1fmkn6icvwmaaafay7idhs392.png"/>

The full amount has been restored to the database, and later inserts can only be recovered with increments.

3 incremental Binlog recovery into Test.sql find Binlog location

Remove binlog into SQL statements Mysqlbinlog mysql-bin.000012 >/shell/bin.sql

Enter Bin.sql to find the drop database Lampol; Delete this sentence

650) this.width=650; "src=" Http://s2.51cto.com/wyfs02/M00/87/FD/wKiom1fmLyWBroAIAAA1pRgc07o774.png "title=" 7.png " alt= "Wkiom1fmlywbroaiaaa1prgc07o774.png"/>

Import Bin.sql into the database

Mysql-uroot-proot Lampol<bin.sql

650) this.width=650; "src=" Http://s2.51cto.com/wyfs02/M00/87/FA/wKioL1fmL43BvoYeAAAWPrcipcE238.png "title=" 8.png " alt= "Wkiol1fml43bvoyeaaawprcipce238.png"/>

There are a lot of shortcomings in the recovery of success after the gradual improvement

This article is from the "Ideal for Life" blog, please be sure to keep this source http://lampol.blog.51cto.com/11662628/1856084

MySQL Data Recovery full backup recovery and incremental recovery (for example, hand-deleted libraries)