Use PerconaDataRecoveryToolforInnoDB to restore a single table _ MySQL

Case Study of recovering a single table using PerconaDataRecoveryToolforInnoDB

Case Study of recovering a single table using Percona Data Recovery Tool for InnoDB

A friend at work today asked me about some problems related to Percona Data Recovery Tool for InnoDB's Data Recovery, such as delete, which cannot be recovered. I have done similar exception processing before, I again simulated the relevant recovery operation procedures for learning and use only. There are many related configuration tool policies online. here I will go over them and will not go into details, next we will start to sort out relevant details;

[Root @ Mysql64 local] # mysql-uroot-prootWelcome to the MySQL monitor. commands end with; or/g. your MySQL connection id is 33 Server version: 5.5.28-log Source distributionType 'help; 'or'/h' for help. type '/C' to clear the buffer. mysql> create database gg; ERROR 1007 (HY000): Can't create database 'GG '; database existsmysql> show databases; + -------------------- + | Database | + -------------------- + | Information_schema | cacti | gg | mysql | performance_schema | temp | test | + ------------------ + 7 rows in set (0.12 sec) mysql> use gg; database changedmysql> show tables; Empty set (0.00 sec) mysql> create table a select * from mysql. user; Query OK, 12 rows affected (0.17 sec) Records: 12 Duplicates: 0 Warnings: 0 mysql> delete from a; Query OK, 12 rows affected (0.12 sec) mysql> select * From a; Empty set (0.00 sec) mysql> [root @ Mysql64 percona-data-recovery-tool-for-innodb-0.5] #. /page_parser-5-f/usr/local/mysql/data/gg/. ibd Opening file:/usr/local/mysql/data/gg/. ibd: 64768 ID of device containing file5836121 inode number33200 protection1 number of hard links500 user ID of owner500 group ID of owner0 device ID (if special file) 98304 total size, in bytes4096 blocksize for file System I/O200 number of blocks runtime time of last access1377831011 time of last modification1377831011 time of last status change98304 Size to process in bytes104857600 Disk cache size in bytes [root @ Mysql64 percona-data-recovery-tool-for-innodb-0.5] #. /create_defs.pl -- host = localhost -- user = root -- password = root -- db = gg -- table = a> include/table_defs.h [root @ Mysql64 percona-data-recover Y-tool-for-innodb-0.5] # cat include/table_defs.h can_be_null: FALSE, uint_min_val: 0, uint_max_val: 4294967295ULL}, can_be_null: FALSE, {/* char (64) */name: "plugin ", type: FT_CHAR, min_length: 64, max_length: 192, has_limits: FALSE, limits: {can_be_null: TRUE, char_min_len: 0, char_max_len: 192, character: TRUE}, can_be_null: TRUE}, {/* text */name: "authentication_string", type: FT_TEXT, min_length: 0, max_length: 65535, has_limits: FALSE, limits: {can_be_null: TRUE, char_min_len: 0, char_max_len: 65535, character: TRUE}, can_be_null: TRUE }, {type: FT_NONE }},}; # endif [root @ Mysql64 percona-data-recovery-tool-for-innodb-0.5] # [root @ Mysql64 percona-data-recovery-tool-for-innodb-0.5] #. /constraints_parser-5-D-f pages-1377849796/FIL_PAGE_INDEX/0-18/0-00000003.p Age load data infile '/usr/local/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 @ Mysql64 percona-data-recovery-tool-for-innodb-0.5] # makegcc-DHAVE_OFFSET64_T-D_FILE_OFFSET_BITS = 64-D_LARGEFIL E64_SOURCE = 1-D_LARGEFILE_SOURCE = 1-Wall-O3-g-I include-I mysql-source/innobase/include-c tables_dict.c-o lib/tables_dict.ogcc -DHAVE_OFFSET64_T-D_FILE_OFFSET_BITS = 64-D_LARGEFILE64_SOURCE = 1-D_LARGEFILE_SOURCE = 1-Wall-O3-g-I include-I mysql-source/innobase/include- c print_data.c-o lib/print_data.o gcc-DHAVE_OFFSET64_T-D_FILE_OFFSET_BI TS = 64-D_LARGEFILE64_SOURCE = 1-D_LARGEFILE_SOURCE = 1-Wall-O3-g-I include-I mysql-source/innobase/include-c check_data.c- o lib/check_data.ogcc-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. agcc-DHAVE_OFFSET64_T-D_FILE_OFFSET_BITS = 64-D_LARGEFILE64_SOURCE = 1-D_LARGEFILE_SOURCE = 1-Wall-O3-g-I include-I mysql-source/innobase/include -static-lrt-o page_parser page_parser.c lib/tables_dict.o lib/libut. a [root @ Mysql64 percona-data-recovery-tool-for-innodb-0.5] # Here you need to re-compile to produce, not only mutates once, or later you will not see the data All of them are null values. remember to make them twice! [Root @ Mysql64 percona-data-recovery-tool-for-innodb-0.5] #. /constraints_parser-5-f pages-1377849796/FIL_PAGE_INDEX/0-18/0-00000003. page a "localhost" "root" "* 81F5E21E35407D884A6CD4A731AEBFB6AF209E1B" "Y "" Y "" Y "Y" "Y """ Y "00 0 0" a "Mysql64" "root" "* 81F5E21E35407D884A6CD4A731AEBFB6AF209E1B "" Y "" Y "Y" "Y" "" Y" "Y" "Y" 00 0 0 "a" 127.0.0.1 "" root "" * 81F5E21E35407D884A6CD4A731AEBFB6AF209E1B "" Y "" Y """ Y" "Y" Y "00 0 "" "" ":: 1 "" root "" * 81F5E21E35407D884A6CD4A731AEBFB6AF209E1B "" Y "" Y "" Y "Y" "Y" "" Y "00 0 0" a "localhost" N "" N "" N "" N "N """ N" "" 00 0 0 "" NULLa "Mysql64" N "" N "" N "" N "N" "" N "00 0 0" "NULLa" localhost "" cacti "" * 43DD7940383044FBDE5B177730FAD3405BC6DAD7" "N" N "" N "" N "N """ N "" N "00 0 0" "NULLa" 192.168.155.229 "" cacti "" * 43DD7940383044FBDE5B177730FAD3405BC6DAD7 "" N "" N "N" "N" "Y" "N" "Y" "N" "N" N "" N "" N "" Y "" N "" 00 0 "" NULLa "%" "cacti" "* 43DD7940383044FBDE5B177730FAD3405BC6DAD7" "Y"