資料庫恢複方案

資料庫恢複方案http://netkiller.github.io/journal/db.restore.htmlMr. Neo Chen(netkiller), 陳景峰(BG7NYT)

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

$Id$

著作權 2011, 2012, 2013 http://netkiller.github.io

$Date$

摘要

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

我的系列文檔

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 開源軟體 手劄

目錄

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。gzcat 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 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

也可以跳躍恢複資料

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

反向恢複資料

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

總之怎麼恢複都可以

3.3. 第三種

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

正時序恢複案例，

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

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

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

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