Percona Data Recovery Tool for InnoDB工具恢复单表的案例 - mysql数据库栏目 - 阿里云大学

[](https://edu.aliyun.com/a/31327)

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" "Y" "Y" "Y" "Y" "Y" "" 00 0 0 " " ""
a "127.0.0.1 " "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 "::1 " "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 "localhost " " " " " "N" "N" "N" "N" "N" "N" "N" "N" "N" "N" "N" "N" "N" "N" "N" "N" "N" "N" "N" "N" "N" "N" "N" "N" "N" "N" "N" "N" "N" "" 00 0 0 " " NULL
a "Mysql64 " " " " " "N" "N" "N" "N" "N" "N" "N" "N" "N" "N" "N" "N" "N" "N" "N" "N" "N" "N" "N" "N" "N" "N" "N" "N" "N" "N" "N" "N" "N" "" 00 0 0 " " NULL
a "localhost " "cacti " "*43DD7940383044FBDE5B177730FAD3405BC6DAD7" "N" "N" "N" "N" "N" "N" "N" "N" "N" "N" "N" "N" "N" "N" "N" "N" "N" "N" "N" "N" "N" "N" "N" "N" "N" "N" "N" "N" "N" "" 00 0 0 " " NULL
a "192.168.155.229 " "cacti " "*43DD7940383044FBDE5B177730FAD3405BC6DAD7" "N" "N" "N" "N" "N" "N" "N" "N" "Y" "N" "N" "N" "N" "N" "N" "Y" "N" "N" "N" "N" "Y" "N" "N" "N" "N" "N" "N" "N" "N" "" 00 0 0 " " NULL
a "% " "cacti " "*43DD7940383044FBDE5B177730FAD3405BC6DAD7" "Y" "Y" "Y" "Y" "Y" "Y" "Y" "Y" "Y" "Y" "N" "Y" "Y" "Y" "Y" "Y" "Y" "Y" "Y" "Y" "Y" "Y" "Y" "Y" "Y" "Y" "Y" "Y" "Y" "" 00 0 0 " " NULL
a "127.0.0.1 " "cacti " "*43DD7940383044FBDE5B177730FAD3405BC6DAD7" "N" "N" "N" "N" "N" "N" "N" "N" "N" "N" "N" "N" "N" "N" "N" "N" "N" "N" "N" "N" "N" "N" "N" "N" "N" "N" "N" "N" "N" "" 00 0 0 " " NULL
a ". " "cacti " " " "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 " " NULL
a "% " "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 " " NULL
LOAD DATA INFILE '/usr/local/percona-data-recovery-tool-for-innodb-0.5/dumps/default/a' REPLACE INTO TABLE `a` FIELDS TERMINATED BY '\t' OPTIONALLY ENCLOSED BY '"' LINES STARTING BY 'a\t' (Host， User， Password， Select_priv， Insert_priv， Update_priv， Delete_priv， Create_priv， Drop_priv， Reload_priv， Shutdown_priv， Process_priv， File_priv， Grant_priv， References_priv， Index_priv， Alter_priv， Show_db_priv， Super_priv， Create_tmp_table_priv， Lock_tables_priv， Execute_priv， Repl_slave_priv， Repl_client_priv， Create_view_priv， Show_view_priv， Create_routine_priv， Alter_routine_priv， Create_user_priv， Event_priv， Trigger_priv， Create_tablespace_priv， ssl_type， @var_ssl_cipher， @var_x509_issuer， @var_x509_subject， max_questions， max_updates， max_connections， max_user_connections， plugin， authentication_string)
SET

ssl\_cipher = UNHEX(@var\_ssl_cipher)，
x509\_issuer = UNHEX(@var\_x509_issuer)，
x509\_subject = UNHEX(@var\_x509_subject);

[root@Mysql64 percona-data-recovery-tool-for-innodb-0.5]#
结下了看到了我从ibd里面删除的数据，我要把它放到一个目录内，记住这里我需要放到含有mysql访问的目录内，原先我存放在tmp啊，data目录啊都不行，最后放在mysql权限内的目录就可以了；
[root@Mysql64 percona-data-recovery-tool-for-innodb-0.5]# ./constraints_parser -5 -f pages-1377849796/FIL_PAGE_INDEX/0-18/0-00000003.page > /usr/local/mysql/data/find.sql
LOAD DATA INFILE '/usr/local/percona-data-recovery-tool-for-innodb-0.5/dumps/default/a' REPLACE INTO TABLE `a` FIELDS TERMINATED BY '\t' OPTIONALLY ENCLOSED BY '"' LINES STARTING BY 'a\t' (Host， User， Password， Select_priv， Insert_priv， Update_priv， Delete_priv， Create_priv， Drop_priv， Reload_priv， Shutdown_priv， Process_priv， File_priv， Grant_priv， References_priv， Index_priv， Alter_priv， Show_db_priv， Super_priv， Create_tmp_table_priv， Lock_tables_priv， Execute_priv， Repl_slave_priv， Repl_client_priv， Create_view_priv， Show_view_priv， Create_routine_priv， Alter_routine_priv， Create_user_priv， Event_priv， Trigger_priv， Create_tablespace_priv， ssl_type， @var_ssl_cipher， @var_x509_issuer， @var_x509_subject， max_questions， max_updates， max_connections， max_user_connections， plugin， authentication_string)
SET

ssl\_cipher = UNHEX(@var\_ssl_cipher)，
x509\_issuer = UNHEX(@var\_x509_issuer)，
x509\_subject = UNHEX(@var\_x509_subject);

[root@Mysql64 percona-data-recovery-tool-for-innodb-0.5]#
查询目录的内容是否正确；
[root@Mysql64 data]# pwd
/usr/local/mysql/data
[root@Mysql64 data]# ll -h | grep find*
-rw-r--r-- 1 root root 3.9K 08-30 16:31 find.sql 看到是有数据的有3.9k左右大小，当然这里是测试数据量比较小(mysql.user的数据量)。
[root@Mysql64 data]#
[root@Mysql64 data]# vi find.sql ---看看内容；
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" "" 0 0 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" "Y" "Y" "Y" "Y" "Y" "" 0 0 0 0 " " ""
a "127.0.0.1 " "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" "" 0 0 0 0 " " ""
a "::1 " "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" "" 0 0 0 0 " " ""
a "localhost " " " " " "N" "N" "N" "N" "N" "N" "N" "N" "N" "N" "N" "N" "N" "N" "N" "N" "N" "N" "N" "N" "N" "N" "N" "N" "N" "N" "N" "N" "N" "" 0 0 0 0 " " NULL
a "Mysql64 " " " " " "N" "N" "N" "N" "N" "N" "N" "N" "N" "N" "N" "N" "N" "N" "N" "N" "N" "N" "N" "N" "N" "N" "N" "N" "N" "N" "N" "N" "N" "" 0 0 0 0 " " NULL
a "localhost " "cacti " "*43DD7940383044FBDE5B177730FAD3405BC6DAD7" "N" "N" "N" "N" "N" "N" "N" "N" "N" "N" "N" "N" "N" "N" "N" "N" "N" "N" "N" "N" "N" "N" "N" "N" "N" "N" "N" "N" "N" "" 0 0 0 0 " " NULL
a "192.168.155.229 " "cacti " "*43DD7940383044FBDE5B177730FAD3405BC6DAD7" "N" "N" "N" "N" "N" "N" "N" "N" "Y" "N" "N" "N" "N" "N" "N" "Y" "N" "N" "N" "N" "Y" "N" "N" "N" "N" "N" "N" "N" "N" "" 0 0 0 0 " " NULL
a "% " "cacti " "*43DD7940383044FBDE5B177730FAD3405BC6DAD7" "Y" "Y" "Y" "Y" "Y" "Y" "Y" "Y" "Y" "Y" "N" "Y" "Y" "Y" "Y" "Y" "Y" "Y" "Y" "Y" "Y" "Y" "Y" "Y" "Y" "Y" "Y" "Y" "Y" "" 0 0 0 0 " " NULL
a "127.0.0.1 " "cacti " "*43DD7940383044FBDE5B177730FAD3405BC6DAD7" "N" "N" "N" "N" "N" "N" "N" "N" "N" "N" "N" "N" "N" "N" "N" "N" "N" "N" "N" "N" "N" "N" "N" "N" "N" "N" "N" "N" "N" "" 0 0 0 0 " " NULL
a ". " "cacti " " " "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" "" 0 0 0 0 " " NULL
a "% " "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" "" 0 0 0 0 " " NULL
~
~
~
~
~
~
~
~
~
~
~
[root@Mysql64 data]# 下面我们需要把sql文件内的文本load到我们的库内；这里我需要提出的是load的路径，要进行设置下； 上面我在导入的时候估计变量忘记设置，导致出现部分失误，这里需要做的同学注意下；
mysql> use gg;
Database changed
mysql> select * from a;
Empty set (0.00 sec)

mysql> LOAD DATA INFILE '/usr/local/mysql/data/find.sql' REPLACE INTO TABLE `a` FIELDS TERMINATED BY '\t' OPTIONALLY ENCLOSED BY '"' LINES STARTING BY 'a\t' (Host， User， Password， Select_priv， Insert_priv， Update_priv， Delete_priv， Create_priv， Drop_priv， Reload_priv， Shutdown_priv， Process_priv， File_priv， Grant_priv， References_priv， Index_priv， Alter_priv， Show_db_priv， Super_priv， Create_tmp_table_priv， Lock_tables_priv， Execute_priv， Repl_slave_priv， Repl_client_priv， Create_view_priv， Show_view_priv， Create_routine_priv， Alter_routine_priv， Create_user_priv， Event_priv， Trigger_priv， Create_tablespace_priv， ssl_type， @var_ssl_cipher， @var_x509_issuer， @var_x509_subject， max_questions， max_updates， max_connections， max_user_connections， plugin， authentication_string)

-\> SET
-\>     ssl\_cipher = UNHEX(@var\_ssl_cipher)，
-\>     x509\_issuer = UNHEX(@var\_x509_issuer)，
-\>     x509\_subject = UNHEX(@var\_x509_subject);

Query OK， 12 rows affected (0.03 sec)
Records: 12 Deleted: 0 Skipped: 0 Warnings: 0

mysql> select * from a;
HostUserPasswordSelect_privInsert_privUpdate_privDelete_privCreate_privDrop_privReload_privShutdown_privProcess_privFile_privGrant_privReferences_privIndex_privAlter_privShow_db_privSuper_privCreate_tmp_table_privLock_tables_privExecute_privRepl_slave_privRepl_client_privCreate_view_privShow_view_privCreate_routine_privAlter_routine_privCreate_user_privEvent_privTrigger_privCreate_tablespace_privssl_typessl_cipherx509_issuerx509_subjectmax_questionsmax_updatesmax_connectionsmax_user_connectionspluginauthentication_string
localhostroot*81F5E21E35407D884A6CD4A731AEBFB6AF209E1BYYYYYYYYYYYYYYYYYYYYYYYYYYYYY 0000
Mysql64root*81F5E21E35407D884A6CD4A731AEBFB6AF209E1BYYYYYYYYYYYYYYYYYYYYYYYYYYYYY 0000
127.0.0.1root*81F5E21E35407D884A6CD4A731AEBFB6AF209E1BYYYYYYYYYYYYYYYYYYYYYYYYYYYYY 0000
::1root*81F5E21E35407D884A6CD4A731AEBFB6AF209E1BYYYYYYYYYYYYYYYYYYYYYYYYYYYYY 0000
localhost NNNNNNNNNNNNNNNNNNNNNNNNNNNNN 0000 NULL
Mysql64 NNNNNNNNNNNNNNNNNNNNNNNNNNNNN 0000 NULL
localhostcacti*43DD7940383044FBDE5B177730FAD3405BC6DAD7NNNNNNNNNNNNNNNNNNNNNNNNNNNNN 0000 NULL
192.168.155.229cacti*43DD7940383044FBDE5B177730FAD3405BC6DAD7NNNNNNNNYNNNNNNYNNNNYNNNNNNNN 0000 NULL
%cacti*43DD7940383044FBDE5B177730FAD3405BC6DAD7YYYYYYYYYYNYYYYYYYYYYYYYYYYYY 0000 NULL
127.0.0.1cacti*43DD7940383044FBDE5B177730FAD3405BC6DAD7NNNNNNNNNNNNNNNNNNNNNNNNNNNNN 0000 NULL
*.*cacti YYYYYYYYYYYYYYYYYYYYYYYYYYYYY 0000 NULL
%root*81F5E21E35407D884A6CD4A731AEBFB6AF209E1BYYYYYYYYYYYYYYYYYYYYYYYYYYYYY 0000 NULL

12 rows in set (0.00 sec)

mysql>

以上是互联网用户为您的的内容，在阿里云内部有更多的关于Percona Data Recovery Tool for InnoDB工具恢复单表的案例 - mysql数据库栏目的内容，欢迎继续使用右上角搜索按钮进行搜索工具、单表、案例、以便于您获取更多的相关信息。

Original url: Access
Created at: 2018-10-23 18:37:52
Category: default
Tags: none

未标明原创文章均为采集，版权归作者所有，转载无需和我联系，请注明原出处，南摩阿彌陀佛，知识，不只知道，要得到

请先后发表评论
  • 最新评论
  • 总共0条评论