資料庫恢復方案

netkiller發表於2016-05-14

資料庫

資料庫恢復方案

http://netkiller.github.io/journal/db.restore.html

Mr. Neo Chen (netkiller), 陳景峰(BG7NYT)

中國廣東省深圳市龍華新區民治街道溪山美地
518131
+86 13113668890
+86 755 29812080
<netkiller@msn.com>

$Id$

$Date$

摘要

這裡所談的內容是對備份資料的恢復，不是對損壞資料表的恢復，或者說災難恢復。

我的系列文件

1. 背景2. 備份方式分析3. 恢復方案3.1. 第一種3.2. 第二種3.3. 第三種3.4. 第四種4. 手工恢復

1. 背景

我們來假設一個場景。

你是否適用 mysqldump 每隔一段時間備份一次資料庫，每個備份一個資料檔案。

公司決策你是不是因為資料持續增加，有些資料已經不會再查詢，會刪除舊的歷史資料。

有時公司突然說要恢復歷史資料，有可能全補回復，有可能部分恢復。

你將怎麼做？

2. 備份方式分析

首先看看備份方式，你是不是採用這種方法備份

我使用一串數字表述資料庫資料遞增情況，資料的增長變化

垂直軸表示備份時間軸

最常見的備份方法，完全備份

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 。。。
|.......| 第一次備份
|.................| 第二次備份
|...........................| 第三次備份
|......................................| 第四次備份
|................................................| 第五次備份

下面這種備份方式也比較常見，這種方式很有規律。

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 。。。
|.......| 第一次備份
        |..........| 刪除上一次以備份內容，第二次備份
                   |..........| 刪除上一次以備份內容，第三次備份資料庫
                              |..........| 刪除上一次以備份內容，第四次備份
                                         |.........| 刪除上一次以備份內容，第五次備份

更復雜的情況，無規律可循

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 。。。
|.......| 第一次備份
|..................| 第二次備份
        |......................| 刪除一部分資料後同時做第三次備份資料庫
                   |......................| 又刪除一部分資料，第四次備份
                   |.............................| 第五次備份，沒有刪除資料
                   |......................................| 第六次備份，依然沒有刪除資料
                                          |..........................| 刪除很多資料，第七次備份

以此類推，刪除原因有多種，如空間不足，改善查詢效能。。。等等

最雜的情況，無規律可循，同時交叉資料可能會有更新

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 。。。
|...o...| 第一次備份
|.....o............| 第二次備份
        |....o...o.............| 刪除一部分資料後同時做第三次備份資料庫
                   |.o..o..o..............| 又刪除一部分資料，第四次備份
                   |....o......o.......o.........| 第五次備份，沒有刪除資料
                   |.......o.......o.........o............| 第六次備份，依然沒有刪除資料
                                          |.o....o......o............| 刪除很多資料，第七次備份

我用`o` 表示與上次備份中有差異的部分。

3. 恢復方案

，最好恢復，第二種。

上面所提三種備份方式

第一種

最好恢復，100% 都能搞定.

第二種

恢復起來稍複雜，仍能搞得定.

第三種

比較複雜，因為本檔案中存在重複記錄，費點腦筋

第四種

最複雜，看似複雜，其實也不復雜，跟第三種差不多.

3.1. 第一種

這種備份非常簡單，菜鳥也搞搞定

文字格式回覆

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

壓縮格式恢復

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

或者先使用gunzip解壓，再恢復資料

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

提示

很多人喜歡用tar打包，我不見這樣做，一個檔案時無需使用tar打包的，畫蛇添足

僅使用gzip壓縮，可以方便使用zcat直接操作檔案。

3.2. 第二種

這種備份時連續的，只要依次按順序恢復即可。

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

也可以跳躍恢復資料

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

反向恢復資料

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

總之怎麼恢復都可以

3.3. 第三種

這種恢復建議按照順序進行，即可以順時間軸恢復也可以逆時間軸，條件是表結構需要有主鍵(PK)

正時序恢復案例，

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

逆時序恢復資料

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

因為有主鍵，所以已存在的重複記錄不會被重複插入。

insert 方式有要求

必須是

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);

不能是

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

3.4. 第四種

這種恢復必須按照順序進行，即可以順時間軸恢復也可以逆時間軸，但處理上稍有不同.一旦操作錯誤資料就會損壞，同時也有很多條件。

順時序恢復資料，只需將 insert 替換為 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);

新資料總會覆蓋舊資料

但逆向就不同了，逆時序恢復資料與上面第三種相同, 恢復過程中舊資料在 insert 的時候不會覆蓋現有的新資料。僅僅將失去的資料恢復到資料庫中。

操作要十分謹慎，理解正向與逆向的不同，方能操作。

4. 手工恢復

有時上面所講的四種恢復方法不能滿足你需求，我們模擬一個場景，假如你需要恢復一個時間段的資料，或者ID欄位去一個範圍等等，上面所舉例子均為一刀切。該怎麼辦呢？

不用擔心方法總是有的

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 ...

例 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`;

這裡僅給一個簡單例項，因為每個人的需求都不同，你只需靈活變通，發揮你的想象力。

Netkiller Architect 手札	Netkiller Developer 手札	Netkiller PHP 手札	Netkiller Python 手札	Netkiller Testing 手札	Netkiller Cryptography 手札
Netkiller Linux 手札	Netkiller CentOS 手札	Netkiller FreeBSD 手札	Netkiller Security 手札	Netkiller Version 手札	Netkiller Web 手札
Netkiller Monitoring 手札	Netkiller Storage 手札	Netkiller Mail 手札	Netkiller Shell 手札	Netkiller Network 手札	Netkiller Database 手札
Netkiller PostgreSQL 手札	Netkiller MySQL 手札	Netkiller NoSQL 手札	Netkiller LDAP 手札	Netkiller Cisco IOS 手札	Netkiller H3C 手札
Netkiller Multimedia 手札	Netkiller Docbook 手札	Netkiller 開源軟體手札

資料庫恢復方案