使用Percona Data Recovery Tool for InnoDB恢复数据

 
运维工作中难免会发生一些误操作,当数据库表被误操作删除需要紧急恢复,或者没有备份时,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
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
 
五、将数据导入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
 Installing     : perl-DBD-MySQL                                                                                                                                1/1
 
Installed:
 perl-DBD-MySQL.x86_64 0:3.0007-2.el5                                                                                                                              
 
Complete!
上一篇:真想用c#开发个 wp五笔输入法。。。奈何网上资料太少,源码都是c++写的。求大神指点!!!


下一篇:Domain应用之 根据某个Many2one的对象的 X2many对象 过滤