由percona-data-recovery-tool恢复ibd数据的工具的一些测试

无意间看到了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.ibdOpening file: /home/mysql/datal/cwdtest/card.ibd:64769           ID of device containing file189843585               inode number33184           protection1               number of hard links1001            user ID of owner1001            group ID of owner0               device ID (if special file)9437184         total size, in bytes4096            blocksize for filesystem I/O18432           number of blocks allocated1543312312      time of last access1543394915      time of last modification1543394915      time of last status change9437184 Size to process in bytes104857600       Disk cache size in bytes83.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.sqlLOAD 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.ibdpage offset 00000000, page type page offset 00000001, page type page offset 00000002, page type page offset 00000003, page type , page level <0000>page offset 00000000, page type page offset 00000000, page type Total number of page: 6:Freshly Allocated Page: 2Insert Buffer Bitmap: 1File Space Header: 1B-tree Node: 1File Segment inode: 1

对该表数据文件做个hexdump：

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

page type , 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文件备份。