使用Percona Data Recovery Tool for InnoDB恢复数据 - zengkefu - 博客园

安宇雨 - 随手采集
2018-10-23 18:44:48
随手采集
0000-未整理-等待研究

运维工作中难免会发生一些误操作，当数据库表被误操作删除需要紧急恢复，或者没有备份时，Percona Data Recovery Tool for InnoDB这个工具也已提供一些便捷的恢复。

当然这个工具也有相当的限制：

1、只对innodb表有效

2、一旦发生误操作，需要尽快停止对事故表的写入，将idb文件拷贝出来

3、数据不一定总是能恢复，比如被重新写入的情况等

原理简述：

InnoDB的数据都是索引的方式组织的，而且所有的数据都是存储在16KB的数据块中。恢复的过程分几步，分解所有数据文件为单个16KB大小的页面，根据每个页面的标记的数据起点开始尝试匹配，如果与给定表定义的size合适，认为匹配成功，则输出记录。

操作步骤：

一、安装编译：

tar xvf percona-data-recovery-tool-for-innodb-0.5.tar.gz

...

[root@zabbix percona]# cd percona-data-recovery-tool-for-innodb-0.5

[root@zabbix percona-data-recovery-tool-for-innodb-0.5]# cd mysql-source/

[root@zabbix mysql-source]# ./configure

checking build system type... x86_64-unknown-linux-gnu

checking host system type... x86_64-unknown-linux-gnu

...

[root@zabbix percona-data-recovery-tool-for-innodb-0.5]# make

...

二、实验数据：

mysql> create table luna (id int,name varchar(10),sex varchar(2),logtime date);

Query OK, 0 rows affected (0.10 sec)

mysql> insert into luna values(1,'kuja','m','1986-01-19');

Query OK, 1 row affected (0.00 sec)

mysql> insert into luna values(2,'ben','m','1986-01-19');

Query OK, 1 row affected (0.00 sec)

mysql> insert into luna values(3,'lulu','m','1986-01-19');

Query OK, 1 row affected (0.00 sec)

mysql> select * from luna;

| id | name | sex | logtime |

| 1 | kuja | m | 1986-01-19 |

| 2 | ben | m | 1986-01-19 |

| 3 | lulu | m | 1986-01-19 |

3 rows in set (0.00 sec)

mysql> delete from luna;

Query OK, 3 rows affected (0.00 sec)

三、备份出要恢复表的idb文件并解析：

[root@zabbix percona-data-recovery-tool-for-innodb-0.5]# cp /usr/local/mysql/data/test/luna.ibd /app/qipai_data/kuja/percona/percona-data-recovery-tool-for-innodb-0.5

[root@zabbix percona-data-recovery-tool-for-innodb-0.5]# ./page_parser -5 -f luna.ibd

Opening file: luna.ibd:

64768 ID of device containing file

12419559 inode number

33184 protection

1 number of hard links

0 user ID of owner

0 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

1397468556 time of last access

1397468556 time of last modification

1397468590 time of last status change

98304 Size to process in bytes

104857600 Disk cache size in bytes

生成目录：

drwxr-xr-x 3 root root 4096 Apr 14 17:43 pages-1397468622

四、生成表定义：

[root@zabbix percona-data-recovery-tool-for-innodb-0.5]# ./create_defs.pl --user=kuja --password=kuja --host=192.168.13.21 --db=test --table=luna >include/table_defs.h

[root@zabbix 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

五、将数据导入sql文件

[root@zabbix percona-data-recovery-tool-for-innodb-0.5]# ./constraints_parser -5 -D -f pages-1397468622/FIL_PAGE_INDEX/0-13394/0-00000003.page > /tmp/111.sql

LOAD DATA INFILE '/app/qipai_data/kuja/percona/percona-data-recovery-tool-for-innodb-0.5/dumps/default/luna' REPLACE INTO TABLE `luna` FIELDS TERMINATED BY '\t' OPTIONALLY ENCLOSED BY '"' LINES STARTING BY 'luna\t' (id, name, sex, logtime);

[root@zabbix percona-data-recovery-tool-for-innodb-0.5]# more /tmp/111.sql

luna 1 "kuja" "m" "1986-01-19"

luna 2 "ben" "m" "1986-01-19"

luna 3 "lulu" "m" "1986-01-19"

至此得到数据恢复文本。

六、附加参数释义：

[root@zabbix percona-data-recovery-tool-for-innodb-0.5]# ./constraints_parser -h

Error: Usage: ./constraints_parser -4|-5 [-dDV] -f <InnoDB page or dir> [-T N:M] [-b <extrenal pages directory>]

Where

-f <InnoDB page(s)> -- InnoDB page or directory with pages

-h -- Print this help

-d -- Process only those pages which potentially could have deleted records (default = NO)

-D -- Recover deleted rows only (default = NO)

-U -- Recover UNdeleted rows only (default = NO)

-V -- Verbode mode (lots of debug information)

-4 -- innodb_datafile is in REDUNDANT format

-5 -- innodb_datafile is in COMPACT format

-T -- retrieves only pages with index id = NM (N - high word, M - low word of id)

-b <dir> -- Directory where external pages can be found. Usually it is pages-XXX/FIL_PAGE_TYPE_BLOB/

七、实验中的碰到的问题：

生成表定义时报错

[root@zabbix percona-data-recovery-tool-for-innodb-0.5]# ./create_defs.pl --db test --table luna >include/table_defs.h

install_driver(mysql) failed: Can't locate DBD/mysql.pm in @INC (@INC contains: /usr/lib64/perl5/site_perl/5.8.8/x86_64-linux-thread-multi /usr/lib/perl5/site_perl/5.8.8 /usr/lib/perl5/site_perl /usr/lib64/perl5/vendor_perl/5.8.8/x86_64-linux-thread-multi /usr/lib/perl5/vendor_perl/5.8.8 /usr/lib/perl5/vendor_perl /usr/lib64/perl5/5.8.8/x86_64-linux-thread-multi /usr/lib/perl5/5.8.8 .) at (eval 3) line 3.

Perhaps the DBD::mysql perl module hasn't been fully installed,

or perhaps the capitalisation of 'mysql' isn't right.

Available drivers: DBM, ExampleP, File, Proxy, Sponge.

at ./create_defs.pl line 37

该问题的原因是没有安装perl-DBD-MySQL

安装后解决：

[root@zabbix include]# yum install perl-DBD-MySQL

Loaded plugins: fastestmirror

Loading mirror speeds from cached hostfile

* addons: centos.ustc.edu.cn

* base: centos.ustc.edu.cn

* extras: centos.ustc.edu.cn

* updates: centos.ustc.edu.cn

Setting up Install Process

Resolving Dependencies

--> Running transaction check

---> Package perl-DBD-MySQL.x86_64 0:3.0007-2.el5 set to be updated

--> Finished Dependency Resolution

Dependencies Resolved

=====================================================================================================================================================================

Package Arch Version Repository Size

=====================================================================================================================================================================

Installing:

perl-DBD-MySQL x86_64 3.0007-2.el5 base 148 k

Transaction Summary

=====================================================================================================================================================================

Install 1 Package(s)

Update 0 Package(s)

Remove 0 Package(s)

Total download size: 148 k

Is this ok [y/N]: y

Downloading Packages:

perl-DBD-MySQL-3.0007-2.el5.x86_64.rpm | 148 kB 00:00

Running rpm_check_debug

Running Transaction Test

Finished Transaction Test

Transaction Test Succeeded

Running Transaction