在和資料打交道的過程中，總會遇到誤删除資料的情況,在沒有備份和binlog的情況下，如何恢複呢？本文介紹一個工具percona data recovery tool for innodb
使用 該工具的 注意事項:
1 the tools work only for innodb/xtradb tables, and will not work with myisam tables. percona does have a preliminary set of tools for myisam data recovery;
2 the tools work on a saved copy of your data files, not on the running mysql server.
3 there is no guarantee. even with these tools, data is sometimes unrecoverable. for example, data that is overwritten cannot be recovered with these tools. there may be file system specific or physical means to recover overwritten data.
<b>如果資料被覆寫,則使用該工具無法恢複。必須使用系統或者實體方法來恢複</b>
4 time is of the essence. the best chance for recovery comes when you act immediately to save a copy of your raw data files as soon as you discover the loss or corruption.
當發生誤删除資料時必須立即備份表檔案以便確定資料被覆寫或者損壞。
5 there is manual work to do. not everything is automatic.
6 recovery depends on knowing your data. as part of the process you may have to choose between two versions of your data. the better you know your data, the better the chance you'll be able to recover it.
需要了解的是innodb-tools工具不是通過連接配接到線上的database進行資料恢複，而是通過離線拷貝資料的方式進行的。
注意：不要在mysql運作的時候，直接拷貝innodb檔案，這樣是不安全的，會影響資料恢複過程。不過這點我做了測試，在資料庫運作的時候是可以進行資料庫恢複的。
<b>一 安裝</b>
進入解壓後根目錄下的mysql-source目錄，運作配置指令,不運作make指令
wget https://launchpad.net/percona-data-recovery-tool-for-innodb/trunk/release-0.5/+download/percona-data-recovery-tool-for-innodb-0.5.tar.gz
cd percona-data-recovery-tool-for-innodb-0.5/mysql_source/
./configure
cd ..
make
編譯生成page_parser和constraints_parser工具
注意create_defs.pl腳本需要依賴dbd,dbi,安裝過程中可能會遇到錯誤。
<b>二 模拟誤删除資料</b>
[email protected] : test 22:12:22> delete from mac where id < 51398;
query ok, 4999 rows affected (0.62 sec)
[email protected] : test 22:12:29>
<b>三 擷取資料頁</b>
innodb頁的預設大小是16k，innodb的page分為兩大部分，一部分一級索引部分，另一部分為二級索引部分。page_parser工具通過讀取資料檔案，根據頁頭中的index id，拷貝每個頁到一個單獨的檔案中。
如果你的my.cnf配置了innodb_file_per_table=1，那麼系統已經幫你實作上述過程。所有需要的頁都在單獨的.ibd檔案，而且通常你不需要再切分它
如果.ibd檔案中可能包含多個index，那麼将頁單獨切分開還是有必要的。如果mysql server沒有配置innodb_file_per_table，那麼資料會被儲存在一個全局的表命名空間，這時候就需要按頁對檔案進行切分。
[root@rac1 recovery-tool]# ./page_parser -5 -f /opt/mysql/data/test/mac.ibd
-5：代表 row format為compact
-f：代表要解析的檔案
輸出資訊:
opening file: /opt/mysql/data/test/mac.ibd:
2051 id of device containing file
20283635 inode number
33200 protection
1 number of hard links
103 user id of owner
106 group id of owner
0 device id (if special file)
11534336 total size, in bytes
4096 blocksize for filesystem i/o
22560 number of blocks allocated
1377958353 time of last access
1377958359 time of last modification
1377958359 time of last status change
11534336 size to process in bytes
104857600 disk cache size in bytes
[root@rac1 recovery-tool]# less pages-1377958391/fil_page_index/0-205
0-2057/ 0-2058/ 0-2059/
以上三個為索引檔案 0-2057/主鍵，0-2058/ 0-2059/ 二級索引。可以安裝開啟innodb_table_monitor擷取。
<b>四 擷取表結構的定義</b>
<b> </b>
./create_defs.pl --host 127.0.0.1 --user root --port 3306 --db test --table mac > include/table_defs.h
[root@rac1 recovery-tool]# more include/table_defs.h
#ifndef table_defs_h
#define table_defs_h
// table definitions
table_def_t table_definitions[] = {
{
name: "mac",
{
{ /* int(10) unsigned */
name: "id",
type: ft_uint,
fixed_length: 4,
has_limits: false,
limits: {
can_be_null: false,
uint_min_val: 0,
uint_max_val: 4294967295ull
},
can_be_null: false
},
{ /* */
name: "db_trx_id",
type: ft_internal,
fixed_length: 6,
name: "db_roll_ptr",
fixed_length: 7,
{ /* varchar(50) */
name: "mac",
type: ft_char,
min_length: 0,
max_length: 150,
char_min_len: 0,
char_max_len: 150,
char_ascii_only: true
name: "name",
can_be_null: true,
can_be_null: true
{ /* tinyint(4) */
name: "scope",
type: ft_int,
fixed_length: 1,
int_min_val: -128,
int_max_val: 127
{ /* datetime */
name: "gmt_create",
type: ft_datetime,
fixed_length: 8,
name: "gmt_modify",
{ type: ft_none }
}
},
};
#endif
<b>五 根據include/table_defs.h，重新編譯constraints_parser工具：</b>
[root@rac1 recovery-tool]# make
gcc -dhave_offset64_t -d_file_offset_bits=64 -d_largefile64_source=1 -d_largefile_source=1 -wall -o3 -g -i include -i mysql-source/include -i mysql-source/innobase/include -c tables_dict.c -o lib/tables_dict.o
gcc -dhave_offset64_t -d_file_offset_bits=64 -d_largefile64_source=1 -d_largefile_source=1 -wall -o3 -g -i include -i mysql-source/include -i mysql-source/innobase/include -c check_data.c -o lib/check_data.o
gcc -dhave_offset64_t -d_file_offset_bits=64 -d_largefile64_source=1 -d_largefile_source=1 -wall -o3 -g -i include -i mysql-source/include -i mysql-source/innobase/include -o constraints_parser constraints_parser.c lib/tables_dict.o lib/print_data.o lib/check_data.o lib/libut.a lib/libmystrings.a
gcc -dhave_offset64_t -d_file_offset_bits=64 -d_largefile64_source=1 -d_largefile_source=1 -wall -o3 -g -i include -i mysql-source/include -i mysql-source/innobase/include -static -lrt -o page_parser page_parser.c lib/tables_dict.o lib/libut.a
恢複誤删除的資料:
./constraints_parser -d -5 -f pages-1377958391/fil_page_index/0-2057/ > /tmp/mac.rec
load data infile '/root/recovery-tool/dumps/default/mac' replace into table `mac` fields terminated by '\t' optionally enclosed by '"' lines starting by 'mac\t' (id, mac, name, scope, gmt_create, gmt_modify);
執行 constraints_parser 指令會生成上午load data 的指令，在資料庫中執行上述指令即可.
[email protected] : test 22:20:54> select count(1) from mac;
+----------+
| count(1) |
| 9973 |
1 row in set (0.00 sec)
[email protected] : test 22:21:09> load data infile '/tmp/mac.rec' replace into table `mac` fields terminated by '\t' optionally enclosed by '"' lines starting by 'mac\t' (id, mac, name, scope, gmt_create, gmt_modify);
query ok, 4999 rows affected (0.22 sec)
records: 4999 deleted: 0 skipped: 0 warnings: 0
[email protected] : test 22:21:13> select count(1) from mac;
| 14972 |
[email protected] : test 22:21:18> exit
總結
1 整個恢複過程并不順利,percona 依賴于perl，在安裝的時候遇到dbd安裝不了的問題。
2 可以恢複delete的資料，如果執行truncate table 是恢複失敗的，drop的時候ibd檔案丢同樣沒有檔案來擷取資料頁而無法進行恢複。
對于truncate的測試例子大家可以手動測試一下。
![【MySQL資料庫】資料庫索引事務1.索引2.事務[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)