小編寄語

想必大家都知道，Oracle ACE李真旭（Roger）是國內最專業的Oracle 數據庫恢復專家。但知識都是觸類旁通，真正的專家，從來不會局限在一個方向上。今天分享的內容，是他在MySQL數據恢復上所做的嘗試。

本文主要分享在沒有備份的情況下，MySQL數據庫如何恢復被刪除的表。

包含兩個主要的場景：

1、drop table后的恢復

2、truncate table后的恢復

正文：

我們都知道，MySQL Server都很多存儲引擎，并不是每種都可以進行異常情況之下都恢復，比如drop table/tuncate table/delete table/update table /drop database又或者是ibdata文件損壞之類的。用的最多的就是Myisam和innodb存儲引擎。目前基本上都是5.5＋版本了，我想幾乎沒有人再去使用Myisam了吧。我這里所測試都5.6，5.7版本中默認都存儲引擎已經是Innodb了。因此這里我以Innodb引擎為例子進行說明。

MySQL drop table

這里我們首先來測試innodb_file_per_table為off的情況，即表結構和數據存在同一個文件中。這里我分別測試了表存在主鍵和不存在主鍵的情況，供參考。

innodb_file_per_table參數為off(有主鍵的情況）

1創建測試表

2備份表結構

[root@killdb ~]

# mysqldump –opt -d -uroot -proger recover test_drop0801 > /tmp/innodb_recovery/recover/test_drop0801.sql

3刪除表

mysql> drop table test_drop0801;

Query OK, 0 rows affected (0.00 sec)

4掃描數據文件

5創建用于恢復的數據字典

6查詢需要恢復表的信息

7確認數據page中數據是否存在

8抽取page中的數據

9加載數據到數據庫

我們可以看到，順利完成了drop table的恢復，而且數據完好無損。實際上我這里還同時測試了無主鍵的情況，經過測試都類似，可以進行完美的恢復。這里不再累述。

MySQL truncate table

首先這里我要利用undrop_for_innodb 這個開源工具包（當然需要編譯),目前該工具已經在2017年1月宣布閉源了，而且開始收費。但是我們仍然開源使用之前都開源工具包。另外這里可以告訴大家，不久的將來，odu 也會支持MySQL.

如下是我的truncate table 測試過程：

1創建測試表

2備份表結構

[root@killdb
innodb_recovery]

# mysqldump –opt -d -uroot -proger recover t_enmotech > /tmp/innodb_recovery/recover/t_enmotech.sql

[root@killdb innodb_recovery]

#

3truncate table

mysql> truncate table t_enmotech;

Query OK, 0 rows affected (0.00 sec)

4獲取數據字典

5掃描邏輯卷

6創建數據字典表

該工具包提供的recover_dictionary腳本會創建一個test數據庫，并創建一些數據字典表供恢復查詢使用。同時也會在當前目錄創建dictionary目錄，該目錄下會存放數據字典信息。

7查詢需要恢復的表的index_id信息

可以看到被truncate的表的index_id 為178，我們應該進一步從178 的page中獲取數據。

8確認數據是否存在

9抽取page中的數據

抽取數據之前，必須提前準備好表的表結構，由于這里是truncate，因此表結構是存在的，很容易獲取。我這里是測試，所以之前就備份了結構。

那么如果是drop table 呢？ 實際上我們也可以通過該工具來恢復表結構。

10加載數據到mysql server

11驗證數據

我們可以看到，被truncate 掉的數據被成功恢復了回來。

這里我測試的truncate table的場景，其實對于drop table、delete table 恢復方法均類似（已測試過）。另外，對于更為嚴重的drop database 其實也是可以進行恢復的。

當然，對于實際的生產庫來講，數據不一定能夠恢復，因為有可能被覆蓋而導致數據恢復不全。MySQL 對于空間的重用機制與Oracle 有很大區別，對于Oracle 而言，如果是delete的數據，還是很難被覆蓋掉的，對于drop 和truncate 則領導別論。然而MySQL則有所不同，MySQL 默認會啟動一些purge 進程來進行空間重用，這是MySQL 5.6的情況：

在MySQL 5.7 版本中更為坑爹，MySQL 默認會啟動4個purge 線程，因此很容易就會導致空間被重用，最終導致數據無法恢復，如下是MySQL 5.7的purge相關參數：

因此，一旦你遭遇turncate table/drop table/delete /drop database等情況，建議立刻停止服務或者停止數據庫，保留現場，以防止環境進一步惡化，最終導致數據無法恢復的情況出現。