由percona-data-recovery-tool恢復ibd數據的工具的一些測試

2024-09-07 22:12:43

字體：大中小

來源：轉載

供稿：網友

無意間看到了percona-data-recovery-tool 這個工具，這個工具是用來恢復innodb數據文件中的數據，貌似都建議row_format必須是REDUNDANT或者COMPACT。而在mysql5.7.8以上默認為Dynamic，但其實在此處我在Dynamic下測試是沒有問題的。

當我們誤刪除某個表數據時，實際上數還存留在數據文件中，因此可通過特殊手段從數據文件中抽取相應的數據。

數據被誤刪除后，需要盡快將保護現場，停止數據庫，把 idb 文件拷貝出來，防止 ibd 文件寫入數據被覆蓋。

安裝方法：

https://launchpadlibrarian.net/78359944/percona-data-recovery-tool-for-innodb-0.5.tar.gz

yum install glibc-static

yum -y install perl-DBD-MySQL.x86_64

tar -xf percona-data-recovery-tool-for-innodb-0.5.tar.gz

cd percona-data-recovery-tool-for-innodb-0.5

cd mysql-source/

./configure

cd ..

make

解析 ibd 文件：此處會將ibd文件以16k為單位分割成n個文件

./page_parser -5 -f /home/mysql/datal/cwdtest/card.ibd

參數解釋：

-5：代表 row format 為 Compact

-f：代表要解析的文件，innodb的ibd文件

[root@xxxx-sql-auditing percona-data-recovery-tool-for-innodb-0.5]# ./page_parser -5 -f /home/mysql/datal/cwdtest/card.ibd
Opening file: /home/mysql/datal/cwdtest/card.ibd:
64769 ID of device containing file
189843585 inode number
33184 protection
1 number of hard links
1001 user ID of owner
1001 group ID of owner
0 device ID (if special file)
9437184 total size, in bytes
4096 blocksize for filesystem I/O
18432 number of blocks allocated
1543312312 time of last access
1543394915 time of last modification
1543394915 time of last status change
9437184 Size to process in bytes
104857600 Disk cache size in bytes
83.25% done. 2018-11-28 16:52:19 ETA(in 00:00 hours). Processing speed: 7856357 B/sec
生成表定義

./create_defs.pl -host 127.0.0.1 -port 3306 -user root -password mysql123 -db cwdtest -table card >include/table_defs.h

make

開始恢復 pages 中刪除的數據：

./constraints_parser -5 -D -f ./pages-1543395138/FIL_PAGE_INDEX/0-602/22-00000025.page >/tmp/22-00000025.page.txt

參數：

-5 -f 的參數和 page_parser 相同；

-D:該參數的含義為代表恢復刪除的數據頁；

[root@xxxx-sql-auditing percona-data-recovery-tool-for-innodb-0.5]# ./constraints_parser -5 -D -f ./pages-1543395138/FIL_PAGE_INDEX/0-602/40-00000071.page >/tmp/00000071.sql
LOAD DATA INFILE '/usr/local/src/percona-data-recovery-tool-for-innodb-0.5/dumps/default/SYS_TABLES' REPLACE INTO TABLE `SYS_TABLES` FIELDS TERMINATED BY '/t' OPTIONALLY ENCLOSED BY '"' LINES STARTING BY 'SYS_TABLES/t' (NAME, ID, N_COLS, TYPE, MIX_ID, MIX_LEN, CLUSTER_NAME, SPACE);
[root@xxxx-sql-auditing percona-data-recovery-tool-for-innodb-0.5]#
'
然后生成的文件就用load data 方式插入，其實該方法介紹的文章很多，這里只給出關鍵步驟。而對于在誤刪數據后是否數據還真的存于ibd中，我這里做個小小實驗。

表中有三行數據，刪除其中hhhhhhh一行。

mysql> select * from test;
+----------+----------+
| col1     | col2     |
+----------+----------+
|          | aaaaaaaa |
| ccccccc | NULL     |
| hhhhhhhh | xxxxxxxx |
+----------+----------+
3 rows in set (0.00 sec)
mysql> delete from test where col1='hhhhhhhh';
Query OK, 1 row affected (0.01 sec)
用hexdump來查看ibd文件：

查看頁結構：
python py_innodb_page_info.py -v /data/mysql/cwdtest/test.ibd
page offset 00000000, page type <File Space Header>
page offset 00000001, page type <Insert Buffer Bitmap>
page offset 00000002, page type <File Segment inode>
page offset 00000003, page type <B-tree Node>, page level <0000>
page offset 00000000, page type <Freshly Allocated Page>
page offset 00000000, page type <Freshly Allocated Page>
Total number of page: 6:
Freshly Allocated Page: 2
Insert Buffer Bitmap: 1
File Space Header: 1
B-tree Node: 1
File Segment inode: 1
對該表數據文件做個hexdump：

hexdump -C -v /data/mysql/cwdtest/test2.ibd >/tmp/udb.txt

page type <B-tree Node>, page level <0000> 從第四個頁開始，從hexdump中可找到相應的位置0x0000c000開始，16k*3=49152=0x0000c000

0000c000 8c 69 ae 6a 00 00 00 03 ff ff ff ff ff ff ff ff |.i.j............|
0000c010 00 00 00 00 00 29 6a a5 45 bf 00 00 00 00 00 00 |.....)j.E.......|
0000c020 00 00 00 00 00 21 00 02 00 e7 80 05 00 00 00 00 |.....!..........|
0000c030 00 c4 00 02 00 00 00 03 00 00 00 00 00 00 00 00 |................|
0000c040 00 00 00 00 00 00 00 00 00 33 00 00 00 21 00 00 |.........3...!..|
0000c050 00 02 00 f2 00 00 00 21 00 00 00 02 00 32 01 00 |.......!.....2..|
0000c060 02 00 1d 69 6e 66 69 6d 75 6d 00 04 00 0b 00 00 |...infimum......|
0000c070 73 75 70 72 65 6d 75 6d 08 00 00 00 00 10 00 22 |supremum......."|
0000c080 00 00 00 00 02 0d 00 00 00 00 0b 36 ab 00 00 01 |...........6....|
0000c090 22 01 10 61 61 61 61 61 61 61 61 07 01 00 00 18 |"..aaaaaaaa.....|
0000c0a0 00 22 00 00 00 00 02 0e 00 00 00 00 0b 37 ac 00 |."...........7..|
0000c0b0 00 01 23 01 10 63 63 63 63 63 63 63 08 08 00 20 |..#..ccccccc... |
0000c0c0 00 20 ff ac 00 00 00 00 02 0f 00 00 00 00 0b a1 |. ..............|
0000c0d0 76 00 00 01 72 01 10 68 68 68 68 68 68 68 68 78 |v...r..hhhhhhhhx|<<<<<<<<<<
0000c0e0 78 78 78 78 78 78 78 00 00 00 00 00 00 00 00 00 |xxxxxxx.........|
0000c0f0 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 |................|
0000c100 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 |................|
0000c110 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 |................|
發現hhhhhhhh一行是在的，于是做個 analyze，查看是否會被清除。

mysql> optimize table test;
+--------------+----------+----------+-------------------------------------------------------------------+
| Table        | Op       | Msg_type | Msg_text                                                          |
+--------------+----------+----------+-------------------------------------------------------------------+
| cwdtest.test | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
| cwdtest.test | optimize | status   | OK                                                                |
+--------------+----------+----------+-------------------------------------------------------------------+
2 rows in set (0.03 sec)
mysql> analyze table test;
+--------------+---------+----------+----------+
| Table        | Op      | Msg_type | Msg_text |
+--------------+---------+----------+----------+
| cwdtest.test | analyze | status   | OK       |
+--------------+---------+----------+----------+
再查hexdump

0000c000 f0 d3 26 ea 00 00 00 03 ff ff ff ff ff ff ff ff |..&.............|
0000c010 00 00 00 00 00 29 7b ce 45 bf 00 00 00 00 00 00 |.....){.E.......|
0000c020 00 00 00 00 00 24 00 02 00 bc 80 04 00 00 00 00 |.....$..........|
0000c030 00 a2 00 02 00 00 00 02 00 00 00 00 00 00 00 00 |................|
0000c040 00 00 00 00 00 00 00 00 00 36 00 00 00 24 00 00 |.........6...$..|
0000c050 00 02 00 f2 00 00 00 24 00 00 00 02 00 32 01 00 |.......$.....2..|
0000c060 02 00 1d 69 6e 66 69 6d 75 6d 00 03 00 0b 00 00 |...infimum......|
0000c070 73 75 70 72 65 6d 75 6d 08 00 00 00 00 10 00 22 |supremum......."|
0000c080 00 00 00 00 02 0d 00 00 00 00 0b 36 ab 00 00 01 |...........6....|
0000c090 22 01 10 61 61 61 61 61 61 61 61 07 01 00 00 18 |"..aaaaaaaa.....|
0000c0a0 ff ce 00 00 00 00 02 0e 00 00 00 00 0b 37 ac 00 |.............7..|
0000c0b0 00 01 23 01 10 63 63 63 63 63 63 63 00 00 00 00 |..#..ccccccc....|
0000c0c0 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 |................|
0000c0d0 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 |................|
0000c0e0 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 |................|
0000c0f0 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 |................|
0000c100 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 |................|
此時已被清理掉了。所以，一旦發送誤刪操作且沒有備份，第一時間應該趕緊把ibd文件備份。

（編輯：武林網）

上一篇：MyISAM Table Storage Formats

下一篇：ERROR 1114 HY000 The table test1 is full 的解決