Home | 簡體中文 | 繁體中文 | 雜文 | Search | ITEYE 博客 | OSChina 博客 | Facebook | Linkedin | 作品與服務 | Email

資料庫恢復方案

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 請註明“讀者”

2016-03-29

摘要

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


目錄

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'


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

comments powered by Disqus