Percona Data Recovery Tool for InnoDB工具恢复单表的案例

今天上班有个朋友询问我，相关Percona Data Recovery Tool for InnoDB恢复数据中的一些问题，比如说delete，没法恢复数据，原先做过类似的异常处理就，再次模拟了下相关的恢复操作流程，仅供学习使用；相关的配置工具策略网上很多，这里我就一笔带过了，不再进行详述，下面就开始梳理相关的细节问题；

[root@Mysql64 local]# mysql -uroot -proot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 33
Server version: 5.5.28-log Source distribution


Type 'help;' or '\h' for help. Type '\c' to clear the buffer.


mysql> create database gg;
ERROR 1007 (HY000): Can't create database 'gg'; database exists
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema | 
| cacti              | 
| gg                 | 
| mysql              | 
| performance_schema | 
| temp               | 
| test               | 
+--------------------+
7 rows in set (0.12 sec)


mysql> use gg;
Database changed
mysql> 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/a.ibd 
Opening file: /usr/local/mysql/data/gg/a.ibd:
64768           ID of device containing file
5836121         inode number
33200           protection
1               number of hard links
500             user ID of owner
500             group ID of owner
0               device ID (if special file)
98304           total size, in bytes
4096            blocksize for filesystem I/O
200             number of blocks allocated
1377829472      time of last access
1377831011      time of last modification
1377831011      time of last status change
98304   Size to process in bytes
104857600       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-recovery-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,
                                        char_ascii_only: 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,
                                        char_ascii_only: 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.page 
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]# make
gcc -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 -c tables_dict.c -o lib/tables_dict.o
gcc -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 -c print_data.c -o lib/print_data.o 
gcc -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 -c check_data.c -o lib/check_data.o
gcc -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.a
gcc -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 -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]# 
这里需要重新编译才可以生产，不能只变异一次，不然后期你会看不到数据都为空值的；记住这里需要进行2次make哦，不能少一次！
[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"     "Y"     "Y"      "Y"     "Y"     "Y"     "Y"     "Y"     "Y"     "Y"     "Y"     "Y"     "Y"     "Y"     "Y"     "Y"     "Y"     "Y"     "Y"     "Y"     "Y"     "Y"     "Y"     "Y"     ""                      00       0       0       "                                                                "      ""
a       "Mysql64                                                     "  "root            "      "*81F5E21E35407D884A6CD4A731AEBFB6AF209E1B"     "Y"     "Y"     "Y"     "Y"     "Y"     "Y"     "Y"     "Y"      "Y"     "Y"     "Y"     "Y"     "Y"     "Y"     "Y"     "Y"     "Y"     "Y"     "Y"     "Y"     "Y"     "Y"     "Y"     "Y"