无意间看到了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-auditingpercona-data-recovery-tool-for-innodb-0.5]#./page_parser-5-f/home/mysql/datal/cwdtest/card.ibdOpeningfile:/home/mysql/datal/cwdtest/card.ibd:64769IDofdevicecontainingfile189843585inodenumber33184protection1numberofhardlinks1001userIDofowner1001groupIDofowner0deviceID(ifspecialfile)9437184totalsize,inbytes4096blocksizeforfilesystemI/O18432numberofblocksallocated1543312312timeoflastaccess1543394915timeoflastmodification1543394915timeoflaststatuschange9437184Sizetoprocessinbytes104857600Diskcachesizeinbytes83.25%done.2018-11-2816:52:19ETA(in00:00hours).Processingspeed:7856357B/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-auditingpercona-data-recovery-tool-for-innodb-0.5]#./constraints_parser-5-D-f./pages-1543395138/FIL_PAGE_INDEX/0-602/40-00000071.page>/tmp/00000071.sqlLOADDATAINFILE'/usr/local/src/percona-data-recovery-tool-for-innodb-0.5/dumps/default/SYS_TABLES'REPLACEINTOTABLE`SYS_TABLES`FIELDSTERMINATEDBY'\t'OPTIONALLYENCLOSEDBY'"'LINESSTARTINGBY'SYS_TABLES\t'(NAME,ID,N_COLS,TYPE,MIX_ID,MIX_LEN,CLUSTER_NAME,SPACE);[root@xxxx-sql-auditingpercona-data-recovery-tool-for-innodb-0.5]#'

然后生成的文件就用load data 方式插入，其实该方法介绍的文章很多，这里只给出关键步骤。而对于在误删数据后是否数据还真的存于ibd中，我这里做个小小实验。

表中有三行数据，删除其中hhhhhhh一行。

mysql>select*fromtest;+----------+----------+|col1|col2|+----------+----------+||aaaaaaaa||ccccccc|NULL||hhhhhhhh|xxxxxxxx|+----------+----------+3rowsinset(0.00sec)mysql>deletefromtestwherecol1='hhhhhhhh';QueryOK,1rowaffected(0.01sec)

用hexdump来查看ibd文件：

查看页结构：pythonpy_innodb_page_info.py-v/data/mysql/cwdtest/test.ibdpageoffset00000000,pagetype<FileSpaceHeader>pageoffset00000001,pagetype<InsertBufferBitmap>pageoffset00000002,pagetype<FileSegmentinode>pageoffset00000003,pagetype<B-treeNode>,pagelevel<0000>pageoffset00000000,pagetype<FreshlyAllocatedPage>pageoffset00000000,pagetype<FreshlyAllocatedPage>Totalnumberofpage:6:FreshlyAllocatedPage:2InsertBufferBitmap:1FileSpaceHeader:1B-treeNode:1FileSegmentinode: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

0000c0008c69ae6a00000003ffffffffffffffff|.i.j............|0000c0100000000000296aa545bf000000000000|.....)j.E.......|0000c020000000000021000200e7800500000000|.....!..........|0000c03000c40002000000030000000000000000|................|0000c04000000000000000000033000000210000|.........3...!..|0000c050000200f2000000210000000200320100|.......!.....2..|0000c06002001d696e66696d756d0004000b0000|...infimum......|0000c07073757072656d756d0800000000100022|supremum......."|0000c08000000000020d000000000b36ab000001|...........6....|0000c09022011061616161616161610701000018|"..aaaaaaaa.....|0000c0a0002200000000020e000000000b37ac00|."...........7..|0000c0b000012301106363636363636308080020|..#..ccccccc...|0000c0c00020ffac00000000020f000000000ba1|...............|0000c0d076000001720110686868686868686878|v...r..hhhhhhhhx|<<<<<<<<<<0000c0e078787878787878000000000000000000|xxxxxxx.........|0000c0f000000000000000000000000000000000|................|0000c10000000000000000000000000000000000|................|0000c11000000000000000000000000000000000|................|

发现hhhhhhhh一行是在的，于是做个 analyze，查看是否会被清除。

mysql>optimizetabletest;+--------------+----------+----------+-------------------------------------------------------------------+|Table|Op|Msg_type|Msg_text|+--------------+----------+----------+-------------------------------------------------------------------+|cwdtest.test|optimize|note|Tabledoesnotsupportoptimize,doingrecreate+analyzeinstead||cwdtest.test|optimize|status|OK|+--------------+----------+----------+-------------------------------------------------------------------+2rowsinset(0.03sec)mysql>analyzetabletest;+--------------+---------+----------+----------+|Table|Op|Msg_type|Msg_text|+--------------+---------+----------+----------+|cwdtest.test|analyze|status|OK|+--------------+---------+----------+----------+

再查hexdump

0000c000f0d326ea00000003ffffffffffffffff|..&.............|0000c0100000000000297bce45bf000000000000|.....){.E.......|0000c020000000000024000200bc800400000000|.....$..........|0000c03000a20002000000020000000000000000|................|0000c04000000000000000000036000000240000|.........6...$..|0000c050000200f2000000240000000200320100|.......$.....2..|0000c06002001d696e66696d756d0003000b0000|...infimum......|0000c07073757072656d756d0800000000100022|supremum......."|0000c08000000000020d000000000b36ab000001|...........6....|0000c09022011061616161616161610701000018|"..aaaaaaaa.....|0000c0a0ffce00000000020e000000000b37ac00|.............7..|0000c0b000012301106363636363636300000000|..#..ccccccc....|0000c0c000000000000000000000000000000000|................|0000c0d000000000000000000000000000000000|................|0000c0e000000000000000000000000000000000|................|0000c0f000000000000000000000000000000000|................|0000c10000000000000000000000000000000000|................|

此时已被清理掉了。所以，一旦发送误删操作且没有备份，第一时间应该赶紧把ibd文件备份。