Database recovery plan

Database recovery planHttp://netkiller.github.io/journal/db.restore.htmlMr. Neo Chen (netkiller), Chen jingfeng (BG7NYT)

Xishan Meidi, Minzhi Street, Longhua new district, Shenzhen City, Guangdong Province, China
518131
+ 86 13113668890
+ 86 755 29812080
<netkiller@msn.com>

$ Id $

Copyright 2011,201 2, 2013 http://netkiller.github.io

$ Date $

Summary

What we will talk about here is restoration of backup data, not restoration of damaged data tables, or disaster recovery.

My Documents

Netkiller impact ect shouzha	Netkiller Developer notebook	Netkiller PHP notebook	Netkiller Python notebook	Netkiller Testing shouzha	Netkiller Cryptography notebook
Netkiller Linux shouzha	Netkiller CentOS notebook	Netkiller FreeBSD notebook	Netkiller Security statement	Netkiller Version	Netkiller Web shouzha
Netkiller Monitoring shouzha	Netkiller Storage shouzha	Netkiller Mail shouzhi	Netkiller Shell shouzha	Netkiller Network shouzha	Netkiller Database shouzhi
Netkiller PostgreSQL notebook	Netkiller MySQL notebook	Netkiller NoSQL notebook	Netkiller LDAP notebook	Netkiller Cisco IOS notebook	Netkiller H3C notebook
Netkiller Multimedia notebook	Netkiller Docbook notebook	Netkiller open source software

Directory

1. Background

2. Backup Method Analysis

3. Recovery Plan

3.1. First

3.2. Second

3.3. Category 3

3.4. Fourth

4. manual recovery

1. Background

Let's assume a scenario.

Does mysqldump apply to backing up a database every time and every backup of a data file.

The company decides if you want to delete old historical data because the data continues to increase and some data will no longer be queried.

Sometimes the company suddenly says that it wants to restore the historical data. It may make up for the reply and partially recover the data.

What will you do?

2. Backup Method Analysis

First, check whether the backup method is used.

I use a string of numbers to describe how database data increases and changes

Vertical Axis indicates the backup timeline

The most common backup method is full backup.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 19 20... | ....... | First backup | ................. | second backup | ........................... | the third backup | ...................................... | fourth backup | ................................... ............. | fifth backup

The following backup methods are also common, which are quite regular.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 19 20... | ....... | First backup | .......... | Delete the last backup content and the second backup | .......... | Delete the last backup content and the third backup database | .......... | deletes the last backup content and the fourth backup | ......... | Delete the last backup content and the fifth backup

More complex situations, without rules

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 19 20... | ....... | First backup | .................. | second backup | ...................... | after deleting a part of the data, the database is backed up for the third time. | ...................... | deletes a part of the data and backs up the data for the fourth time. | ............................. | fifth backup, no data was deleted. | .................................... .. | data is still not deleted in the sixth backup. | .......................... | deletes a large amount of data. The seventh backup

Similarly, there are many reasons for deletion, such as insufficient space to improve query performance... And so on.

The most complex situations are irregular, and cross-data may be updated.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 19 20... |... O... | First backup | ..... o ............ | second backup | .... o... o ............. | deletes a part of the data and backs up the database for the third time. |. o .. o .. o .............. | delete a part of the data and perform the fourth backup | .... o ...... o ....... o ......... | data was not deleted during the fifth backup. | ....... o ....... o ......... o ............ | the sixth backup still does not delete data |. o .... o ...... o ............ | deletes a large amount of data. The seventh backup

I use 'O' to indicate the difference from the previous backup.

3. Recovery Plan

, It is best to restore, the second type.

The above three backup methods

First

It is best to recover, 100% can be done.

Second

Recovery is a little complicated and can still be done.

Third

This is complicated because there are repeated records in this file.

Fourth

The most complex, seemingly complex, is actually not complex, similar to the third.

3.1. First

This kind of backup is very simple, and cainiao is also doing it

Text Format reply

cat dbname.sql | mysql -u user -p pass -h localhost yourdb

Compression format recovery

zcat dbname.sql。gz | mysql -u user -p pass -h localhost yourdb

Or use gunzip to decompress the file and restore the data.

gunzip dbname.sql。gzcat dbname.sql | mysql -u user -p pass -h localhost yourdb

Prompt

Many people prefer to use tar for packaging. I don't need to use tar for packaging a file.

You can use zcat to directly operate files only by using gzip compression.

3.2. Second

This type of backup is continuous and can be recovered sequentially.

zcat dbname1.sql。gz | mysql -u user -p pass -h localhost yourdbzcat dbname2.sql。gz | mysql -u user -p pass -h localhost yourdbzcat dbname3.sql。gz | mysql -u user -p pass -h localhost yourdb......zcat dbname10.sql。gz | mysql -u user -p pass -h localhost yourdb

You can also skip to restore data.

zcat dbname2.sql。gz | mysql -u user -p pass -h localhost yourdbzcat dbname3.sql。gz | mysql -u user -p pass -h localhost yourdbzcat dbname5.sql。gz | mysql -u user -p pass -h localhost yourdbzcat dbname10.sql。gz | mysql -u user -p pass -h localhost yourdb

Reverse data recovery

zcat dbname20.sql。gz | mysql -u user -p pass -h localhost yourdbzcat dbname15.sql。gz | mysql -u user -p pass -h localhost yourdbzcat dbname13.sql。gz | mysql -u user -p pass -h localhost yourdbzcat dbname1.sql。gz | mysql -u user -p pass -h localhost yourdb

In short, you can recover all data.

3.3. Category 3

We recommend that you restore the table in sequence, that is, you can restore the table along the timeline or reverse the timeline. The condition is that the table structure requires a primary key (PK)

Positive Sequence recovery case,

zcat dbname1.sql。gz | mysql -u user -p pass -h localhost yourdbzcat dbname2.sql。gz | mysql -u user -p pass -h localhost yourdbzcat dbname3.sql。gz | mysql -u user -p pass -h localhost yourdb

Reverse Sequence data recovery

zcat dbname3.sql。gz | mysql -u user -p pass -h localhost yourdbzcat dbname2.sql。gz | mysql -u user -p pass -h localhost yourdbzcat dbname1.sql。gz | mysql -u user -p pass -h localhost yourdb

Because primary keys exist, existing duplicate records are not inserted.

The insert method is required.

Required

insert into dbtable(f1, f2, f3...) value (v1, v2, v3);insert into dbtable(f1, f2, f3...) value (v1, v2, v3);insert into dbtable(f1, f2, f3...) value (v1, v2, v3);

Cannot be

insert into dbtable(f1, f2, f3...) value (v1, v2, v3), (v1, v2, v3), value (v1, v2, v3);

3.4. Fourth

This type of recovery must be performed in sequence, that is, it can be restored along the timeline or against the timeline, but it is slightly different in processing. Once the operation fails, the data will be damaged, and there are also many conditions.

To restore Data in sequence, replace insert with replace.

replace into dbtable(f1, f2, f3...) value (v1, v2, v3);replace into dbtable(f1, f2, f3...) value (v1, v2, v3);replace into dbtable(f1, f2, f3...) value (v1, v2, v3);

New data will always overwrite old data

However, reverse order is different. Reverse Sequence recovery data is the same as the preceding three types. During the recovery process, the old data will not overwrite the existing new data during the insert operation. Only recover lost data to the database.

The operation must be very careful, and the difference between forward and reverse operations can be understood.

4. manual recovery

Sometimes the four restoration methods mentioned above cannot meet your needs. We simulate a scenario. If you need to restore data in a time period, or use the ID field to a range, etc, the above examples are all one-size-fits-all. What should I do?

Don't worry, there are always Methods

INSERT... SELECT

INSERT [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE]    [INTO] tbl_name [(col_name,...)]    SELECT ...    [ ON DUPLICATE KEY UPDATE col_name=expr, ... ]

REPLACE... SELECT

REPLACE [LOW_PRIORITY | DELAYED]    [INTO] tbl_name    [PARTITION (partition_name,...)]      [(col_name,...)]    SELECT ...

Example 1: INSERT... SELECT

INSERT INTO tbl_name_new SELECT * FROM tbl_name_old WHERE name = 'netkiller';INSERT INTO db_new.tbl_name SELECT * FROM db_old.tbl_name WHERE id > '10000';

Here is only a simple example. Because everyone has different needs, you only need to be flexible and use your imagination.