資料庫恢復方案

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

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


中國廣東省深圳市龍華新區民治街道溪山美地
518131
+86 13113668890


$Id: b4ba1e8a4e7c23f60300b43f75864bc0fb30d85f

版權聲明

轉載請與作者聯繫，轉載時請務必標明文章原始出處和作者信息及本聲明。
文檔出處:
http://netkiller.github.io
http://netkiller.sourceforge.net

微信掃瞄二維碼進入 Netkiller 微信訂閲號

QQ群：128659835 請註明“讀者”

2017-06-16

摘要

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

目錄

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)

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

這種備份參數如下

mysqldump -C -hlocalhost -ubackup -p1AyBoc5mp02LDJki5wJ8 --skip-extended-insert -t db_name tbl_name

這種方式備份，備份檔案尺寸會比較大。

正時序恢復案例，

zcat dbname1.sql.gz | sed 's/^INSERT INTO/INSERT IGNORE INTO/' | mysql -u user -p pass -h localhost yourdb
zcat dbname2.sql.gz | sed 's/^INSERT INTO/INSERT IGNORE INTO/' | mysql -u user -p pass -h localhost yourdb
zcat dbname3.sql.gz | sed 's/^INSERT INTO/INSERT IGNORE INTO/' | mysql -u user -p pass -h localhost yourdb

逆時序恢復數據

zcat dbname3.sql.gz | sed 's/^INSERT INTO/INSERT IGNORE INTO/' | mysql -u user -p pass -h localhost yourdb
zcat dbname2.sql.gz | sed 's/^INSERT INTO/INSERT IGNORE INTO/' | mysql -u user -p pass -h localhost yourdb
zcat dbname1.sql.gz | sed 's/^INSERT INTO/INSERT IGNORE INTO/' | mysql -u user -p pass -h localhost yourdb

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

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 IGNORE INTO tbl_name_new SELECT * FROM tbl_name_old WHERE name = 'netkiller';
INSERT IGNORE INTO db_new.tbl_name SELECT * FROM db_old.tbl_name WHERE id > '10000';

IGNORE 將忽略 ERROR 1062 (23000) at line 24: Duplicate entry '100' for key 'PRIMARY'


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