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

发表于 | 分类于
字数统计: | 阅读时长 ≈

前言

数据是一个企业最重要的资产，备份的重要性不言而喻，有效的备份能够减少意外情况下的公司损失。当使用MySQL数据库时，mysqldump、mysqlpump、mydumper、xtrabackup等都是不错的备份工具，极端情况下，当开启了binlog，也可以成为恢复的救命稻草。但是，还有存在无备份，没有开启binlog的情况，如果是InnoDB表，这时percona推出的Percona Data Recovery Tool工具可以派上用场。

注意事项

1
2
3
4
5
6
此工具仅支持InnoDB/XtraDB表，不支持MyISAM表
此工具可恢复数据文件的副本，并不要求是正在运行的MySQL服务器
此工具并不保证数据一定可恢复，如数据被覆盖
支持的误操作类型：DELETE、TRUNCATE，表格式为Compact
在文件系统级别删除的文件
如数据文件损失，此工具也无法恢复

原理

通过尝试在页面中找到有效的行来工作，InnoDB的数据都是索引的方式组织的，所有的数据都是存储在16KB的数据块中，每个页面都有属于特定表的特定索引。恢复时分解所有的数据文件为单个16kb大小的页面，根据每个页面的标记的数据起点开始尝试匹配，如果与给定表定义的属于匹配，则输出匹配记录。

表空间

当服务器设置了innodb_file_per_table=1时，工具不需要再额外的从ibdata1中拆分数据，所有的数据都保存在ibd文件中。
当服务器设置了innodb_file_per_table=0时，工具需要先使用page_parser，把数据文件从ibdata1中拆分出来，再按照规则匹配记录。

准备

操作系统：centos 6.5 x86_64

安装依赖

1
yum -y install gcc gcc-c++ glibc glibc-static  perl-DBI perl-DBD-MySQL

安装Percona Data Recovery Tool

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
[root@iZuf6c08fdv8duubho2b0rZ ~]# cd /usr/src/
[root@iZuf6c08fdv8duubho2b0rZ src]# wget https://launchpad.net/percona-data-recovery-tool-for-innodb/trunk/release-0.5/+download/percona-data-recovery-tool-for-innodb-0.5.tar.gz --no-check-certificate 
[root@iZuf6c08fdv8duubho2b0rZ src]# tar -zxf percona-data-recovery-tool-for-innodb-0.5.tar.gz 
[root@iZuf6c08fdv8duubho2b0rZ src]# cd percona-data-recovery-tool-for-innodb-0.5/mysql-source/
[root@iZuf6c08fdv8duubho2b0rZ mysql-source]# ./configure 
[root@iZuf6c08fdv8duubho2b0rZ mysql-source]# cd ..
[root@iZuf6c08fdv8duubho2b0rZ percona-data-recovery-tool-for-innodb-0.5]# make
[root@iZuf6c08fdv8duubho2b0rZ percona-data-recovery-tool-for-innodb-0.5]# ll
总用量 3168
-rw-r--r--  1  510 wheel    6269 8月  28 2011 check_data.c
-rwxr-xr-x  1 root root   748761 7月  17 09:06 constraints_parser
-rw-r--r--  1  510 wheel   22172 8月  28 2011 constraints_parser.c
-rwxr-xr-x  1  510 wheel   12051 8月  28 2011 create_defs.pl
drwxr-xr-x  2  510 wheel    4096 8月  28 2011 docs
-rwxr-xr-x  1  510 wheel    1978 8月  28 2011 fetch_data.sh
-rwxr-xr-x  1 root root   979051 7月  17 09:06 ibdconnect
-rw-r--r--  1  510 wheel   12200 8月  28 2011 ibdconnect.c
drwxr-xr-x  2  510 wheel    4096 8月  28 2011 include
-rw-r--r--  1  510 wheel    8262 8月  28 2011 incrementalupdate.c
-rwxr-xr-x  1 root root    14873 7月  17 09:06 innochecksum
-rw-r--r--  1  510 wheel    9117 8月  28 2011 innochecksum.c
-rw-r--r--  1  510 wheel      74 8月  28 2011 INSTALL
drwxr-xr-x  2 root root     4096 7月  17 09:06 lib
-rw-r--r--  1  510 wheel    2676 8月  28 2011 Makefile
drwxr-xr-x 40  510 wheel    4096 7月  17 09:06 mysql-source
-rwxr-xr-x  1 root root  1346155 7月  17 09:06 page_parser
-rw-r--r--  1  510 wheel   15239 8月  28 2011 page_parser.c
-rw-r--r--  1  510 wheel   10608 8月  28 2011 print_data.c
-rwxr-xr-x  1  510 wheel     302 8月  28 2011 split_dump.pl
-rw-r--r--  1  510 wheel    2046 8月  28 2011 tables_dict.c

恢复实战(delete误操作)

innodb_file_per_table=1

模拟数据被误删除

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
[root@iZuf6c08fdv8duubho2b0rZ percona-data-recovery-tool-for-innodb-0.5]# mysql -uroot -p123456
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 35514
Server version: 5.6.36-82.0-log Source distribution

Copyright (c) 2009-2017 Percona LLC and/or its affiliates
Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MySQL [(none)] 09:25:34 > create database test111;
Query OK, 1 row affected (0.00 sec)

MySQL [(none)] 09:25:40 > use test111;
Database changed
MySQL [test111] 09:25:43 > create table t222(id int unsigned not null auto_increment,name varchar(20),age int,primary key(`id`),key idx_age(`age`));   
Query OK, 0 rows affected (0.02 sec)

MySQL [test111] 09:25:51 > insert into t222 values(null,'jiessie',18);           
Query OK, 1 row affected (0.00 sec)

MySQL [test111] 09:26:00 > insert into t222 (name,age) select name,age from t222;     
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

MySQL [test111] 09:26:10 > insert into t222 (name,age) select name,age from t222;
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

MySQL [test111] 09:26:11 > insert into t222 (name,age) select name,age from t222;
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

MySQL [test111] 09:26:11 > insert into t222 (name,age) select name,age from t222;
Query OK, 8 rows affected (0.00 sec)
Records: 8  Duplicates: 0  Warnings: 0

MySQL [test111] 09:26:12 > insert into t222 (name,age) select name,age from t222;
Query OK, 16 rows affected (0.00 sec)
Records: 16  Duplicates: 0  Warnings: 0

MySQL [test111] 09:26:12 > insert into t222 (name,age) select name,age from t222;
Query OK, 32 rows affected (0.00 sec)
Records: 32  Duplicates: 0  Warnings: 0

MySQL [test111] 09:26:12 > insert into t222 (name,age) select name,age from t222;
Query OK, 64 rows affected (0.00 sec)
Records: 64  Duplicates: 0  Warnings: 0

MySQL [test111] 09:26:13 > insert into t222 (name,age) select name,age from t222;
Query OK, 128 rows affected (0.01 sec)
Records: 128  Duplicates: 0  Warnings: 0

MySQL [test111] 09:26:13 > insert into t222 (name,age) select name,age from t222;
Query OK, 256 rows affected (0.00 sec)
Records: 256  Duplicates: 0  Warnings: 0

MySQL [test111] 09:26:14 > insert into t222 (name,age) select name,age from t222;
Query OK, 512 rows affected (0.01 sec)
Records: 512  Duplicates: 0  Warnings: 0

MySQL [test111] 09:26:14 > select count(*) from t222;
+----------+
| count(*) |
+----------+
|     1024 |
+----------+
1 row in set (0.00 sec)

MySQL [test111] 09:26:21 > exit
Bye
[root@iZuf6c08fdv8duubho2b0rZ percona-data-recovery-tool-for-innodb-0.5]# mysql -uroot -p123456 test111 -e "delete from t222;select count(*) from t222;" && cp /hwdata/data/percona/test111/t222.* /tmp/
Warning: Using a password on the command line interface can be insecure.
+----------+
| count(*) |
+----------+
|        0 |
+----------+

解析ibd文件

实战mysql安装在/usr/local/percona，数据目录在/hwdata/data/percona，注意保存数据文件。
此过程会将表的idb文件解析为很多的page，innodb的page分为两大部分，一部分一级索引部分（primary key），另一部分为二级索引部分（secondary key），所以解析出来的ibd包括了主键数据和索引数据两大部分（如果该表有多个二级索引，则会生成多个文件

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
[root@iZuf6c08fdv8duubho2b0rZ percona-data-recovery-tool-for-innodb-0.5]# ll -t /tmp/t222*
-rw-r----- 1 root root 163840 7月  17 09:26 /tmp/t222.ibd
-rw-r----- 1 root root   8614 7月  17 09:26 /tmp/t222.frm
[root@iZuf6c08fdv8duubho2b0rZ percona-data-recovery-tool-for-innodb-0.5]# ./page_parser -5 -f /tmp/t222.ibd 
Opening file: /tmp/t222.ibd:
64513           ID of device containing file
1597422         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)
163840          total size, in bytes
4096            blocksize for filesystem I/O
320             number of blocks allocated
1500254806      time of last access
1500254806      time of last modification
1500254806      time of last status change
163840  Size to process in bytes
104857600       Disk cache size in bytes
19.97% done. 2017-07-17 09:27:37 ETA(in 00:00 hours). Processing speed: 32723 B/sec
[root@iZuf6c08fdv8duubho2b0rZ percona-data-recovery-tool-for-innodb-0.5]# ll -t
总用量 3172
drwxr-xr-x  3 root root     4096 7月  17 09:27 pages-1500254852
-rwxr-xr-x  1 root root  1346155 7月  17 09:21 page_parser
-rwxr-xr-x  1 root root   748729 7月  17 09:21 constraints_parser
drwxr-xr-x  2 root root     4096 7月  17 09:21 lib
-rwxr-xr-x  1 root root   979051 7月  17 09:06 ibdconnect
-rwxr-xr-x  1 root root    14873 7月  17 09:06 innochecksum
drwxr-xr-x 40  510 wheel    4096 7月  17 09:06 mysql-source
drwxr-xr-x  2  510 wheel    4096 8月  28 2011 docs
drwxr-xr-x  2  510 wheel    4096 8月  28 2011 include
-rw-r--r--  1  510 wheel    6269 8月  28 2011 check_data.c
-rw-r--r--  1  510 wheel   22172 8月  28 2011 constraints_parser.c
-rwxr-xr-x  1  510 wheel   12051 8月  28 2011 create_defs.pl
-rwxr-xr-x  1  510 wheel    1978 8月  28 2011 fetch_data.sh
-rw-r--r--  1  510 wheel   12200 8月  28 2011 ibdconnect.c
-rw-r--r--  1  510 wheel    8262 8月  28 2011 incrementalupdate.c
-rw-r--r--  1  510 wheel    9117 8月  28 2011 innochecksum.c
-rw-r--r--  1  510 wheel      74 8月  28 2011 INSTALL
-rw-r--r--  1  510 wheel    2676 8月  28 2011 Makefile
-rw-r--r--  1  510 wheel   15239 8月  28 2011 page_parser.c
-rw-r--r--  1  510 wheel   10608 8月  28 2011 print_data.c
-rwxr-xr-x  1  510 wheel     302 8月  28 2011 split_dump.pl
-rw-r--r--  1  510 wheel    2046 8月  28 2011 tables_dict.c

参数解释：
-5：代表 row format为Compact mysql5.0后的版本，-4 代表mysql5.0前版本
-f：代表要解析的文件

结果分析

从下面的解析后的结果看到，当前目录多了pages-1500254852，其中39为主键索引的index_id,40为二级索引的index_id,该id可以通过开启innodb_table_monitor查看

1
2
3
4
5
6
7
8
9
10
11
[root@iZuf6c08fdv8duubho2b0rZ percona-data-recovery-tool-for-innodb-0.5]# ll pages-1500254852/FIL_PAGE_INDEX/0-*
pages-1500254852/FIL_PAGE_INDEX/0-39:
总用量 64
-rw-r--r-- 1 root root 16384 7月  17 09:27 0-00000003.page
-rw-r--r-- 1 root root 16384 7月  17 09:27 2-00000005.page
-rw-r--r-- 1 root root 16384 7月  17 09:27 3-00000006.page
-rw-r--r-- 1 root root 16384 7月  17 09:27 4-00000007.page

pages-1500254852/FIL_PAGE_INDEX/0-40:
总用量 16
-rw-r--r-- 1 root root 16384 7月  17 09:27 1-00000004.page

生成表定义

由于该工具在解析数据pages的时候，需要获得该table的表结构定义，所以需要执行如下命令。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
[root@iZuf6c08fdv8duubho2b0rZ percona-data-recovery-tool-for-innodb-0.5]# ./create_defs.pl --host localhost --user root --password 123456 --db test111 --table t222 > include/table_defs.h
[root@iZuf6c08fdv8duubho2b0rZ percona-data-recovery-tool-for-innodb-0.5]# cat include/table_defs.h
#ifndef table_defs_h
#define table_defs_h

// Table definitions
table_def_t table_definitions[] = {
        {
                name: "t222",
                {
                        { /* int(10) unsigned */
                                name: "id",
                                type: FT_UINT,
                                fixed_length: 4,

                                has_limits: FALSE,
                                limits: {
                                        can_be_null: FALSE,
                                        uint_min_val: 0,
                                        uint_max_val: 4294967295ULL
                                },

                                can_be_null: FALSE
                        },
                        { /*  */
                                name: "DB_TRX_ID",
                                type: FT_INTERNAL,
                                fixed_length: 6,

                                can_be_null: FALSE
                        },
                        { /*  */
                                name: "DB_ROLL_PTR",
                                type: FT_INTERNAL,
                                fixed_length: 7,

                                can_be_null: FALSE
                        },
                        { /* varchar(20) */
                                name: "name",
                                type: FT_CHAR,
                                min_length: 0,
                                max_length: 60,

                                has_limits: FALSE,
                                limits: {
                                        can_be_null: TRUE,
                                        char_min_len: 0,
                                        char_max_len: 60,
                                        char_ascii_only: TRUE
                                },

                                can_be_null: TRUE
                        },
                        { /* int(11) */
                                name: "age",
                                type: FT_INT,
                                fixed_length: 4,

                                has_limits: FALSE,
                                limits: {
                                        can_be_null: TRUE,
                                        int_min_val: -2147483648LL,
                                        int_max_val: 2147483647LL
                                },

                                can_be_null: TRUE
                        },
                        { type: FT_NONE }
                }
        },
};

#endif
[root@iZuf6c08fdv8duubho2b0rZ percona-data-recovery-tool-for-innodb-0.5]# 
[root@iZuf6c08fdv8duubho2b0rZ 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 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@iZuf6c08fdv8duubho2b0rZ 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 -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

上面的命令会将t222表的表定义传入到table_defs.h中，在生成了表结构定义后，重新make该恢复工具

注意：需要make 2次

开始提取page中删除的数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
[root@iZuf6c08fdv8duubho2b0rZ percona-data-recovery-tool-for-innodb-0.5]# ./constraints_parser -5 -f pages-1500254852/FIL_PAGE_INDEX/0-39/ > /tmp/t222.sql
LOAD DATA INFILE '/usr/src/percona-data-recovery-tool-for-innodb-0.5/dumps/default/t222' REPLACE INTO TABLE `t222` FIELDS TERMINATED BY '\t' OPTIONALLY ENCLOSED BY '"' LINES STARTING BY 't222\t' (id, name, age);
[root@iZuf6c08fdv8duubho2b0rZ percona-data-recovery-tool-for-innodb-0.5]# cat /tmp/t222.sql |wc -l
1024
[root@iZuf6c08fdv8duubho2b0rZ percona-data-recovery-tool-for-innodb-0.5]# head -10 /tmp/t222.sql 
t222    1       "jiessie"       18
t222    2       "jiessie"       18
t222    3       "jiessie"       18
t222    4       "jiessie"       18
t222    6       "jiessie"       18
t222    7       "jiessie"       18
t222    8       "jiessie"       18
t222    9       "jiessie"       18
t222    13      "jiessie"       18
t222    14      "jiessie"       18

参数解释：
-5 -f的参数和page_parser相同，代表 row format为Compact ；
-D:该参数的含义为代表恢复删除的数据页；

恢复数据

使用上面constraints_parser执行后的load data导入数据，需要注意目录

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
[root@iZuf6c08fdv8duubho2b0rZ percona-data-recovery-tool-for-innodb-0.5]# mysql -uroot -p123456 
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 35598
Server version: 5.6.36-82.0-log Source distribution

Copyright (c) 2009-2017 Percona LLC and/or its affiliates
Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MySQL [(none)] 09:32:33 > use test111;
Database changed
MySQL [test111] 09:32:35 > LOAD DATA INFILE '/tmp/t222.sql' REPLACE INTO TABLE `t222` FIELDS TERMINATED BY '\t' OPTIONALLY ENCLOSED BY '"' LINES STARTING BY 't222\t' (id, name, age);
Query OK, 1024 rows affected (0.00 sec)
Records: 1024  Deleted: 0  Skipped: 0  Warnings: 0

MySQL [test111] 09:32:53 > select count(*) from t222;
+----------+
| count(*) |
+----------+
|     1024 |
+----------+
1 row in set (0.00 sec)

MySQL [test111] 09:32:59 > select * from t222 limit 10;
+----+---------+------+
| id | name    | age  |
+----+---------+------+
|  1 | jiessie |   18 |
|  2 | jiessie |   18 |
|  3 | jiessie |   18 |
|  4 | jiessie |   18 |
|  6 | jiessie |   18 |
|  7 | jiessie |   18 |
|  8 | jiessie |   18 |
|  9 | jiessie |   18 |
| 13 | jiessie |   18 |
| 14 | jiessie |   18 |
+----+---------+------+
10 rows in set (0.00 sec)

查看数据，数据恢复成功。从实验来看，当delete误操作，第一时间要保护好ibd数据，拷贝到其他目录。

恢复脚本

当误操作的数据量大时，constraints_parser恢复是单线程，速度较慢，此处引用了2个恢复脚本
详情请参考：http://www.orczhou.com/index.php/2013/07/how-to-recover-data-from-mysql-innodb-data-file-ibd-file/

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
[root@iZuf6c08fdv8duubho2b0rZ percona-data-recovery-tool-for-innodb-0.5]# pwd
/usr/src/percona-data-recovery-tool-for-innodb-0.5
[root@iZuf6c08fdv8duubho2b0rZ percona-data-recovery-tool-for-innodb-0.5]# cat parallel_exec.sh 
#!/bin/bash
ws=/usr/src/percona-data-recovery-tool-for-innodb-0.5/
pagedir=/usr/src/percona-data-recovery-tool-for-innodb-0.5/pages-1500255306/FIL_PAGE_INDEX
logdir=/usr/src/percona-data-recovery-tool-for-innodb-0.5/log
rectool=/usr/src/percona-data-recovery-tool-for-innodb-0.5/constraints_parser
cd `dirname $rectool`
count=0
page_count=353894
page_done=0
startdate=`date +%s`
for d1 in `ls $pagedir`
do
  count=$(($count+1))
  echo "in page $d2 at dir $d1" > $logdir/$count.log
  thedate=`date +%s`
  echo "$page_done / $page_count at $thedate from $startdate"
  total=`ls -l $pagedir/$d1/|wc -l`
  page_done=$(($page_done+$total))
  threads=`ps axu|grep parser_jobs|grep -v grep|wc -l`
  echo $threads
  while [ $threads -gt 48 ];
  do
    sleep 1
    threads=`ps axu|grep parser_jobs|grep -v grep|wc -l`
  done
  $ws/parser_jobs.sh $pagedir/$d1 > $ws/job.log 2>&1 &
done
[root@iZuf6c08fdv8duubho2b0rZ percona-data-recovery-tool-for-innodb-0.5]# cat parser_jobs.sh 
#!/bin/bash
pagedir=/usr/src/percona-data-recovery-tool-for-innodb-0.5/pages-1500255306/FIL_PAGE_INDEX
logdir=/usr/src/percona-data-recovery-tool-for-innodb-0.5/log
rectool=/usr/src/percona-data-recovery-tool-for-innodb-0.5/constraints_parser
logfile="$logdir/`basename $1`.log"
echo "$1" > $logfile
if [ -d $1 ];then
  for d2 in `ls $1`
  do
    $rectool -5 -f $1/$d2 >> $logfile 2>/dev/null
  done
fi
[root@iZuf6c08fdv8duubho2b0rZ percona-data-recovery-tool-for-innodb-0.5]# ./parallel_exec.sh 
0 / 353894 at 1500255771 from 1500255771
0
[root@iZuf6c08fdv8duubho2b0rZ percona-data-recovery-tool-for-innodb-0.5]# ll log/
总用量 296
-rw-r--r-- 1 root root 296770 7月  17 09:42 0-41.log
-rw-r--r-- 1 root root     21 7月  17 09:42 1.log
[root@iZuf6c08fdv8duubho2b0rZ percona-data-recovery-tool-for-innodb-0.5]# cat log/0-41.log |wc -l
10001
[root@iZuf6c08fdv8duubho2b0rZ percona-data-recovery-tool-for-innodb-0.5]# head -10 log/0-41.log 
/usr/src/percona-data-recovery-tool-for-innodb-0.5/pages-1500255306/FIL_PAGE_INDEX/0-41
employee        3670    3670    "test3670"
employee        3671    3671    "test3671"
employee        3672    3672    "test3672"
employee        3673    3673    "test3673"
employee        3674    3674    "test3674"
employee        3675    3675    "test3675"
employee        3676    3676    "test3676"
employee        3677    3677    "test3677"
employee        3678    3678    "test3678"

用法：
1.修改脚本路
2.修改page_parser后的pages目录
3.创建log目录
4.执行parallel_exec脚本
5.注意，上面演示的示例，0-41.log中在第一行多了一句待恢复innodb页的路径，实际恢复中应删除

innodb_file_per_table=0

模拟数据被误删除

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
[root@iZuf6c08fdv8duubho2b0rZ percona-data-recovery-tool-for-innodb-0.5]# mysql -uroot -p123456 -e "show variables like 'innodb_file_per_table'"
Warning: Using a password on the command line interface can be insecure.
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_file_per_table | OFF   |
+-----------------------+-------+
[root@iZuf6c08fdv8duubho2b0rZ percona-data-recovery-tool-for-innodb-0.5]# mysql -uroot -p123456
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 5.6.36-82.0-log Source distribution

Copyright (c) 2009-2017 Percona LLC and/or its affiliates
Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MySQL [(none)] 09:58:22 > create database test111;
Query OK, 1 row affected (0.00 sec)

MySQL [(none)] 09:58:31 > use test111;
Database changed
MySQL [test111] 09:58:34 > create table t222(id int unsigned not null auto_increment,name varchar(20),age int,primary key(`id`),key idx_age(`age`)); 
Query OK, 0 rows affected (0.01 sec)

MySQL [test111] 09:58:52 > insert into t222 values(null,'jiessie',18);   
Query OK, 1 row affected (0.00 sec)

MySQL [test111] 09:58:57 > insert into t222 (name,age) select name,age from t222;   
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

MySQL [test111] 09:59:02 > insert into t222 (name,age) select name,age from t222;   
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

MySQL [test111] 09:59:03 > insert into t222 (name,age) select name,age from t222;   
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

MySQL [test111] 09:59:04 > insert into t222 (name,age) select name,age from t222;   
Query OK, 8 rows affected (0.00 sec)
Records: 8  Duplicates: 0  Warnings: 0

MySQL [test111] 09:59:04 > insert into t222 (name,age) select name,age from t222;                                                                                       
Query OK, 16 rows affected (0.00 sec)
Records: 16  Duplicates: 0  Warnings: 0

MySQL [test111] 09:59:06 > insert into t222 (name,age) select name,age from t222;   
Query OK, 32 rows affected (0.00 sec)
Records: 32  Duplicates: 0  Warnings: 0

MySQL [test111] 09:59:07 > insert into t222 (name,age) select name,age from t222;   
Query OK, 64 rows affected (0.00 sec)
Records: 64  Duplicates: 0  Warnings: 0

MySQL [test111] 09:59:07 > insert into t222 (name,age) select name,age from t222;   
Query OK, 128 rows affected (0.00 sec)
Records: 128  Duplicates: 0  Warnings: 0

MySQL [test111] 09:59:08 > insert into t222 (name,age) select name,age from t222;   
Query OK, 256 rows affected (0.00 sec)
Records: 256  Duplicates: 0  Warnings: 0

MySQL [test111] 09:59:08 > insert into t222 (name,age) select name,age from t222;   
Query OK, 512 rows affected (0.00 sec)
Records: 512  Duplicates: 0  Warnings: 0

MySQL [test111] 09:59:09 > select count(*) from t222;
+----------+
| count(*) |
+----------+
|     1024 |
+----------+
1 row in set (0.00 sec)

解析ibd文件

实战mysql安装在/usr/local/percona，数据目录在/hwdata/data/percona，注意保存数据文件。
当设置了使用共享表空间(innodb_file_per_table=0)时，所有数据文件存在在ibdata1文件里面，不再是存在在单独表中，此时需要从ibdata1提取出需要被恢复的表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
[root@iZuf6c08fdv8duubho2b0rZ percona-data-recovery-tool-for-innodb-0.5]# ./page_parser -5 -f /tmp/ibdata1 
Opening file: /tmp/ibdata1:
64513           ID of device containing file
1597426         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)
1073741824              total size, in bytes
4096            blocksize for filesystem I/O
2097160         number of blocks allocated
1500256783      time of last access
1500256790      time of last modification
1500256790      time of last status change
1073741824      Size to process in bytes
104857600       Disk cache size in bytes
1.65% done. 2017-07-17 10:03:48 ETA(in 00:00 hours). Processing speed: 17666552 B/sec
3.41% done. 2017-07-17 10:03:44 ETA(in 00:00 hours). Processing speed: 19000304 B/sec
5.18% done. 2017-07-17 10:03:44 ETA(in 00:00 hours). Processing speed: 19005354 B/sec
6.96% done. 2017-07-17 10:03:44 ETA(in 00:00 hours). Processing speed: 19008049 B/sec
8.73% done. 2017-07-17 10:03:44 ETA(in 00:00 hours). Processing speed: 19015298 B/sec
10.47% done. 2017-07-17 10:03:45 ETA(in 00:00 hours). Processing speed: 18719062 B/sec
12.24% done. 2017-07-17 10:03:44 ETA(in 00:00 hours). Processing speed: 19008805 B/sec
14.01% done. 2017-07-17 10:03:44 ETA(in 00:00 hours). Processing speed: 19009523 B/sec
15.78% done. 2017-07-17 10:03:44 ETA(in 00:00 hours). Processing speed: 19007004 B/sec
17.55% done. 2017-07-17 10:03:44 ETA(in 00:00 hours). Processing speed: 19008533 B/sec
19.32% done. 2017-07-17 10:03:44 ETA(in 00:00 hours). Processing speed: 19008676 B/sec
21.06% done. 2017-07-17 10:03:45 ETA(in 00:00 hours). Processing speed: 18715203 B/sec
22.83% done. 2017-07-17 10:03:44 ETA(in 00:00 hours). Processing speed: 19007750 B/sec
24.60% done. 2017-07-17 10:03:44 ETA(in 00:00 hours). Processing speed: 19002802 B/sec
26.37% done. 2017-07-17 10:03:44 ETA(in 00:00 hours). Processing speed: 18998409 B/sec
28.14% done. 2017-07-17 10:03:44 ETA(in 00:00 hours). Processing speed: 18991699 B/sec
29.88% done. 2017-07-17 10:03:45 ETA(in 00:00 hours). Processing speed: 18708371 B/sec
31.65% done. 2017-07-17 10:03:44 ETA(in 00:00 hours). Processing speed: 18999365 B/sec
33.42% done. 2017-07-17 10:03:44 ETA(in 00:00 hours). Processing speed: 18997104 B/sec
35.19% done. 2017-07-17 10:03:44 ETA(in 00:00 hours). Processing speed: 19001358 B/sec
36.96% done. 2017-07-17 10:03:44 ETA(in 00:00 hours). Processing speed: 18999823 B/sec
38.73% done. 2017-07-17 10:03:44 ETA(in 00:00 hours). Processing speed: 18997814 B/sec
40.47% done. 2017-07-17 10:03:45 ETA(in 00:00 hours). Processing speed: 18706585 B/sec
42.24% done. 2017-07-17 10:03:44 ETA(in 00:00 hours). Processing speed: 18996668 B/sec
44.01% done. 2017-07-17 10:03:44 ETA(in 00:00 hours). Processing speed: 18998094 B/sec
45.78% done. 2017-07-17 10:03:44 ETA(in 00:00 hours). Processing speed: 18998427 B/sec
47.55% done. 2017-07-17 10:03:44 ETA(in 00:00 hours). Processing speed: 19002403 B/sec
49.30% done. 2017-07-17 10:03:45 ETA(in 00:00 hours). Processing speed: 18724158 B/sec
51.06% done. 2017-07-17 10:03:44 ETA(in 00:00 hours). Processing speed: 18997507 B/sec
52.83% done. 2017-07-17 10:03:44 ETA(in 00:00 hours). Processing speed: 18999524 B/sec
54.60% done. 2017-07-17 10:03:44 ETA(in 00:00 hours). Processing speed: 18998796 B/sec
56.37% done. 2017-07-17 10:03:44 ETA(in 00:00 hours). Processing speed: 19000200 B/sec
58.14% done. 2017-07-17 10:03:44 ETA(in 00:00 hours). Processing speed: 18998670 B/sec
59.88% done. 2017-07-17 10:03:45 ETA(in 00:00 hours). Processing speed: 18699601 B/sec
61.65% done. 2017-07-17 10:03:44 ETA(in 00:00 hours). Processing speed: 19000413 B/sec
63.42% done. 2017-07-17 10:03:44 ETA(in 00:00 hours). Processing speed: 18997737 B/sec
65.19% done. 2017-07-17 10:03:44 ETA(in 00:00 hours). Processing speed: 18996538 B/sec
66.96% done. 2017-07-17 10:03:44 ETA(in 00:00 hours). Processing speed: 18999244 B/sec
68.70% done. 2017-07-17 10:03:44 ETA(in 00:00 hours). Processing speed: 18704824 B/sec
70.47% done. 2017-07-17 10:03:44 ETA(in 00:00 hours). Processing speed: 19002702 B/sec
72.24% done. 2017-07-17 10:03:44 ETA(in 00:00 hours). Processing speed: 19002732 B/sec
74.01% done. 2017-07-17 10:03:44 ETA(in 00:00 hours). Processing speed: 18999341 B/sec
75.78% done. 2017-07-17 10:03:44 ETA(in 00:00 hours). Processing speed: 19001510 B/sec
77.55% done. 2017-07-17 10:03:44 ETA(in 00:00 hours). Processing speed: 18998098 B/sec
79.29% done. 2017-07-17 10:03:44 ETA(in 00:00 hours). Processing speed: 18706109 B/sec
81.06% done. 2017-07-17 10:03:44 ETA(in 00:00 hours). Processing speed: 19007616 B/sec
82.83% done. 2017-07-17 10:03:44 ETA(in 00:00 hours). Processing speed: 19007630 B/sec
84.60% done. 2017-07-17 10:03:44 ETA(in 00:00 hours). Processing speed: 19004630 B/sec
86.37% done. 2017-07-17 10:03:44 ETA(in 00:00 hours). Processing speed: 19004716 B/sec
88.12% done. 2017-07-17 10:03:44 ETA(in 00:00 hours). Processing speed: 18717386 B/sec
89.89% done. 2017-07-17 10:03:44 ETA(in 00:00 hours). Processing speed: 19005803 B/sec
91.66% done. 2017-07-17 10:03:44 ETA(in 00:00 hours). Processing speed: 19007785 B/sec
93.43% done. 2017-07-17 10:03:44 ETA(in 00:00 hours). Processing speed: 19006419 B/sec
95.20% done. 2017-07-17 10:03:44 ETA(in 00:00 hours). Processing speed: 19004360 B/sec
96.97% done. 2017-07-17 10:03:44 ETA(in 00:00 hours). Processing speed: 19008201 B/sec
98.73% done. 2017-07-17 10:03:44 ETA(in 00:00 hours). Processing speed: 18940047 B/sec
[root@iZuf6c08fdv8duubho2b0rZ percona-data-recovery-tool-for-innodb-0.5]# ll -t
总用量 3172
drwxr-xr-x  4 root root     4096 7月  17 10:18 pages-1500257904
-rwxr-xr-x  1 root root   979051 7月  17 10:18 ibdconnect
-rwxr-xr-x  1 root root    14873 7月  17 10:18 innochecksum
-rwxr-xr-x  1 root root  1346155 7月  17 10:18 page_parser
-rwxr-xr-x  1 root root   748761 7月  17 10:18 constraints_parser
drwxr-xr-x  2 root root     4096 7月  17 10:18 lib
drwxr-xr-x 40  510 wheel    4096 7月  17 10:18 mysql-source
drwxr-xr-x  2  510 wheel    4096 8月  28 2011 docs
drwxr-xr-x  2  510 wheel    4096 8月  28 2011 include
-rw-r--r--  1  510 wheel    6269 8月  28 2011 check_data.c
-rw-r--r--  1  510 wheel   22172 8月  28 2011 constraints_parser.c
-rwxr-xr-x  1  510 wheel   12051 8月  28 2011 create_defs.pl
-rwxr-xr-x  1  510 wheel    1978 8月  28 2011 fetch_data.sh
-rw-r--r--  1  510 wheel   12200 8月  28 2011 ibdconnect.c
-rw-r--r--  1  510 wheel    8262 8月  28 2011 incrementalupdate.c
-rw-r--r--  1  510 wheel    9117 8月  28 2011 innochecksum.c
-rw-r--r--  1  510 wheel      74 8月  28 2011 INSTALL
-rw-r--r--  1  510 wheel    2676 8月  28 2011 Makefile
-rw-r--r--  1  510 wheel   15239 8月  28 2011 page_parser.c
-rw-r--r--  1  510 wheel   10608 8月  28 2011 print_data.c
-rwxr-xr-x  1  510 wheel     302 8月  28 2011 split_dump.pl
-rw-r--r--  1  510 wheel    2046 8月  28 2011 tables_dict.c

从ibdata1提取待恢复数据

由于ibdata1恢复的是所有数据，需要从这些数据找到误操作的表，通过查询元数据，得到表空间的id和name

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
[root@iZuf6c08fdv8duubho2b0rZ percona-data-recovery-tool-for-innodb-0.5]# mysql -uroot -p123456 
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 39
Server version: 5.6.36-82.0-log Source distribution

Copyright (c) 2009-2017 Percona LLC and/or its affiliates
Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MySQL [(none)] 10:06:55 > use information_schema;
Database changed
MySQL [information_schema] 10:06:58 > select i.INDEX_ID, i.NAME FROM INNODB_SYS_INDEXES as i INNER JOIN INNODB_SYS_TABLES as t USING(TABLE_ID) WHERE t.NAME='test111/t222';
+----------+---------+
| INDEX_ID | NAME    |
+----------+---------+
|       20 | PRIMARY |
|       21 | idx_age |
+----------+---------+
2 rows in set (0.00 sec)

MySQL [information_schema] 10:06:59 > exit
Bye
[root@iZuf6c08fdv8duubho2b0rZ percona-data-recovery-tool-for-innodb-0.5]# ll pages-1500256968/FIL_PAGE_INDEX/
总用量 68
drwxr-xr-x 2 root root 4096 7月  17 10:02 0-1
drwxr-xr-x 2 root root 4096 7月  17 10:02 0-11
drwxr-xr-x 2 root root 4096 7月  17 10:02 0-12
drwxr-xr-x 2 root root 4096 7月  17 10:02 0-13
drwxr-xr-x 2 root root 4096 7月  17 10:02 0-14
drwxr-xr-x 2 root root 4096 7月  17 10:02 0-15
drwxr-xr-x 2 root root 4096 7月  17 10:02 0-16
drwxr-xr-x 2 root root 4096 7月  17 10:02 0-17
drwxr-xr-x 2 root root 4096 7月  17 10:02 0-18
drwxr-xr-x 2 root root 4096 7月  17 10:02 0-19
drwxr-xr-x 2 root root 4096 7月  17 10:02 0-2
drwxr-xr-x 2 root root 4096 7月  17 10:02 0-20
drwxr-xr-x 2 root root 4096 7月  17 10:02 0-21
drwxr-xr-x 2 root root 4096 7月  17 10:02 0-3
drwxr-xr-x 2 root root 4096 7月  17 10:02 0-4
drwxr-xr-x 2 root root 4096 7月  17 10:02 0-5
drwxr-xr-x 2 root root 4096 7月  17 10:02 4294967295-0
[root@iZuf6c08fdv8duubho2b0rZ percona-data-recovery-tool-for-innodb-0.5]#

结果分析

从下面的解析后的结果看到，当前目录pages-1500257904，其中20为主键索引的index_id,21为二级索引的index_id,该id可以通过开启innodb_table_monitor查看

1
2
3
4
5
6
7
8
9
10
11
12
13
14
[root@iZuf6c08fdv8duubho2b0rZ percona-data-recovery-tool-for-innodb-0.5]# ll pages-1500256968/FIL_PAGE_INDEX/0-20/
总用量 128
-rw-r--r-- 1 root root 16384 7月  17 10:02 15-00000054.page
-rw-r--r-- 1 root root 16384 7月  17 10:02 17-00000056.page
-rw-r--r-- 1 root root 16384 7月  17 10:02 18-00000057.page
-rw-r--r-- 1 root root 16384 7月  17 10:02 19-00000058.page
-rw-r--r-- 1 root root 16384 7月  17 10:02 20-00000054.page
-rw-r--r-- 1 root root 16384 7月  17 10:02 22-00000057.page
-rw-r--r-- 1 root root 16384 7月  17 10:02 23-00000056.page
-rw-r--r-- 1 root root 16384 7月  17 10:02 24-00000058.page
[root@iZuf6c08fdv8duubho2b0rZ percona-data-recovery-tool-for-innodb-0.5]# ll pages-1500256968/FIL_PAGE_INDEX/0-21
总用量 32
-rw-r--r-- 1 root root 16384 7月  17 10:02 16-00000055.page
-rw-r--r-- 1 root root 16384 7月  17 10:02 21-00000055.page

生成表定义

由于该工具在解析数据pages的时候，需要获得该table的表结构定义，所以需要执行如下命令。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
[root@iZuf6c08fdv8duubho2b0rZ percona-data-recovery-tool-for-innodb-0.5]# ./create_defs.pl --host localhost --user root --password 123456 --db test111 --table t222 > include/table_defs.h
[root@iZuf6c08fdv8duubho2b0rZ percona-data-recovery-tool-for-innodb-0.5]# cat include/table_defs.h
#ifndef table_defs_h
#define table_defs_h

// Table definitions
table_def_t table_definitions[] = {
        {
                name: "t222",
                {
                        { /* int(10) unsigned */
                                name: "id",
                                type: FT_UINT,
                                fixed_length: 4,

                                has_limits: FALSE,
                                limits: {
                                        can_be_null: FALSE,
                                        uint_min_val: 0,
                                        uint_max_val: 4294967295ULL
                                },

                                can_be_null: FALSE
                        },
                        { /*  */
                                name: "DB_TRX_ID",
                                type: FT_INTERNAL,
                                fixed_length: 6,

                                can_be_null: FALSE
                        },
                        { /*  */
                                name: "DB_ROLL_PTR",
                                type: FT_INTERNAL,
                                fixed_length: 7,

                                can_be_null: FALSE
                        },
                        { /* varchar(20) */
                                name: "name",
                                type: FT_CHAR,
                                min_length: 0,
                                max_length: 60,

                                has_limits: FALSE,
                                limits: {
                                        can_be_null: TRUE,
                                        char_min_len: 0,
                                        char_max_len: 60,
                                        char_ascii_only: TRUE
                                },

                                can_be_null: TRUE
                        },
                        { /* int(11) */
                                name: "age",
                                type: FT_INT,
                                fixed_length: 4,

                                has_limits: FALSE,
                                limits: {
                                        can_be_null: TRUE,
                                        int_min_val: -2147483648LL,
                                        int_max_val: 2147483647LL
                                },

                                can_be_null: TRUE
                        },
                        { type: FT_NONE }
                }
        },
};

#endif
[root@iZuf6c08fdv8duubho2b0rZ percona-data-recovery-tool-for-innodb-0.5]# 
[root@iZuf6c08fdv8duubho2b0rZ 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 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@iZuf6c08fdv8duubho2b0rZ 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 -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

上面的命令会将t222表的表定义传入到table_defs.h中，在生成了表结构定义后，重新make该恢复工具

注意：需要make 2次

开始提取page中删除的数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
[root@iZuf6c08fdv8duubho2b0rZ percona-data-recovery-tool-for-innodb-0.5]# ./constraints_parser -5 -f pages-1500258331/FIL_PAGE_INDEX/0-20/ > /tmp/0_t222.sql            
LOAD DATA INFILE '/usr/src/percona-data-recovery-tool-for-innodb-0.5/dumps/default/t222' REPLACE INTO TABLE `t222` FIELDS TERMINATED BY '\t' OPTIONALLY ENCLOSED BY '"' LINES STARTING BY 't222\t' (id, name, age);
[root@iZuf6c08fdv8duubho2b0rZ percona-data-recovery-tool-for-innodb-0.5]# cat /tmp/0_t222.sql |wc -l
2048
[root@iZuf6c08fdv8duubho2b0rZ percona-data-recovery-tool-for-innodb-0.5]# head -10 /tmp/0_t222.sql 
t222    1145    "jiessie"       18
t222    1146    "jiessie"       18
t222    1147    "jiessie"       18
t222    1148    "jiessie"       18
t222    1149    "jiessie"       18
t222    1150    "jiessie"       18
t222    1151    "jiessie"       18
t222    1152    "jiessie"       18
t222    1153    "jiessie"       18
t222    1154    "jiessie"       18

从结果上来看，数据重复了一份，通过load data导入只导入一份正常的数据
参数解释：
-5 -f的参数和page_parser相同，代表 row format为Compact ；
-D:该参数的含义为代表恢复删除的数据页；

恢复数据

使用上面constraints_parser执行后的load data导入数据，需要注意目录

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
[root@iZuf6c08fdv8duubho2b0rZ percona-data-recovery-tool-for-innodb-0.5]# mysql -uroot -p123456
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 108
Server version: 5.6.36-82.0-log Source distribution

Copyright (c) 2009-2017 Percona LLC and/or its affiliates
Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MySQL [(none)] 10:28:16 > use test111;
Database changed
MySQL [test111] 10:28:20 > LOAD DATA INFILE '/tmp/0_t222.sql' REPLACE INTO TABLE `t222` FIELDS TERMINATED BY '\t' OPTIONALLY ENCLOSED BY '"' LINES STARTING BY 't222\t' (id, name, age);
Query OK, 2048 rows affected (0.01 sec)
Records: 2048  Deleted: 0  Skipped: 0  Warnings: 0

MySQL [test111] 10:28:39 > select count(*) from t222;
+----------+
| count(*) |
+----------+
|     1024 |
+----------+
1 row in set (0.00 sec)

MySQL [test111] 10:28:44 > select * from t222 limit 10;
+----+---------+------+
| id | name    | age  |
+----+---------+------+
|  1 | jiessie |   18 |
|  2 | jiessie |   18 |
|  3 | jiessie |   18 |
|  4 | jiessie |   18 |
|  6 | jiessie |   18 |
|  7 | jiessie |   18 |
|  8 | jiessie |   18 |
|  9 | jiessie |   18 |
| 13 | jiessie |   18 |
| 14 | jiessie |   18 |
+----+---------+------+
10 rows in set (0.00 sec)

从结果看，数据恢复完成

结束语

Percona Data Recovery Tool for InnoDB工具并不保证100%能够恢复数据，而且会存在丢失数据的现象，特别是当发生误删除后，数据文件并没有第一时间保存起来，而导致了被重写。truncate操作在此不做演示，建议还是做好备份恢复工作，并做日常的演练。

-------------本文结束感谢您的阅读-------------