Shulou(Shulou.com)06/01 Report--

I came across the tool percona-data-recovery-tool, which is used to recover data in innodb data files. It seems to suggest that row_format must be REDUNDANT or COMPACT. The default is Dynamic above mysql5.7.8, but there is no problem for me to test under Dynamic here.

When we delete a table data by mistake, the actual number still exists in the data file, so we can extract the corresponding data from the data file by special means.

After the data is deleted by mistake, it is necessary to protect the site, stop the database and copy the idb file as soon as possible to prevent the ibd file from being overwritten.

Installation method:

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

Parsing the ibd file: here the ibd file is divided into n files in 16k units

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

Parameter explanation:

-5: represents row format as Compact

-f: represents the file to be parsed, the ibd file of innodb

[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

Generate table definition

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

Make

Start recovering deleted data in pages:

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

Parameters:

The parameters of-5-f are the same as page_parser.

-D: this parameter means to restore deleted data pages

[root@xxxx-sql-auditing percona-data-recovery-tool-for-innodb-0.5] #. / constraints_parser-5-D-f. / pages-1543395138/FIL_PAGE_INDEX/0-602 SYS_TABLES 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] #'

Then the generated file is inserted in load data mode. In fact, this method introduces a lot of articles, and only the key steps are given here. As for whether the data is really stored in ibd after the data is deleted by mistake, I will do a little experiment here.

There are three rows of data in the table, in which one hhhhhhh row is deleted.

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)

Use hexdump to view the ibd file:

View page structure: 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 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 the table data file:

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

Page type, page level starts from the fourth page, and the corresponding location can be found in hexdump. 0x0000c000, 16k*3=49152=0x0000c000.

0000c000 8c 69 ae 6a 0000 00 03 ff ff ff ff ff ff ff ff | .i.j. | 0000c010 0000 0000 00 29 6a a5 45 bf 0000 0000 0000 |.) j.E. | 0000c020 0000 0000 00 21 00 02 00 E7 80 05 0000 0000 |. | 0000c030 00 c4 00 02 0000 00 03 0000 0000 0000000 | | | 0000c040 0000 0000 0000 00 33 0000 00 21 0000 | .3...!... | 0000c050 00 02 00 f2 0000 00 21 0000 00 02 00 32 01 00 |.! .2..| 0000c060 02 00 1D 69 6e 66 69 6d 75 0400b 0000 |. Infimum. | 0000c070 73 75 70 72 65 6d 75 6d 08 0000 0000 10 00 22 | supremum. "| 0000c080 0000 0000 02 0d 0000 0000 0b 36 ab 0000 01 | .6.... | 0000c090 22 01 10 61 61 61 07 07 01 18 |" .aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa 63 63 63 08 08 00 20 |.. #.. ccccccc... | 0000c0c0 00 20 ff ac 0000 0000 02 0f 0000 0000 0b A1 |. | | 0000c0d0 76 0000 01 72 01 10 68 68 68 78 | v...r..hhhhhhhhx |

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.