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

Source: Internet
Author: User

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)

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.