使用percona recover工具恢复被delete的数据-独享innodb表空间

最新推荐文章于 2024-06-28 15:40:04 发布

cnktbkanb141909486

最新推荐文章于 2024-06-28 15:40:04 发布

阅读量232

点赞数

文章标签：数据库

MySQL 5.6版本，独享innodb表空间模式
测试表：
mysql> select count(*) from live_users;
+----------+
| count(*) |
+----------+
| 34258 |
+----------+
1 row in set (0.02 sec)

mysql> delete from live_users;
Query OK, 34258 rows affected (0.25 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> select count(*) from live_users;
+----------+
| count(*) |
+----------+
| 0 |
+----------+
1 row in set (0.00 sec)

mysql> select database();
+------------+
| database() |
+------------+
| livekoo |
+------------+
1 row in set (0.00 sec)

解析ibd文件：
此过程会将表的idb文件解析为很多的page
[root@test11g percona-data-recovery-tool]# ./page_parser -5 -f /tol/mysql/data/livekoo/live_users.ibd
参数解释：
-5：指定row format为Compact
-f：指定要解析的文件
Opening file: /tol/mysql/data/livekoo/live_users.ibd:
2065 ID of device containing file
252192878 inode number
33200 protection
1 number of hard links
27 user ID of owner
27 group ID of owner
0 device ID (if special file)
10485760 total size, in bytes
4096 blocksize for filesystem I/O
20488 number of blocks allocated
1420082763 time of last access
1420082832 time of last modification
1420082832 time of last status change
10485760 Size to process in bytes
104857600 Disk cache size in bytes
75.04% done. 2015-01-01 11:28:43 ETA(in 00:00 hours). Processing speed: 7869035 B/sec

结果如下：
[root@test11g 0-59]# pwd
/root/percona-data-recovery-tool/pages-1420082922/FIL_PAGE_INDEX/0-59
[root@test11g 0-59]# ls
0-00000003.page 115-00000146.page 132-00000163.page 3-00000006.page 48-00000079.page 65-00000096.page 82-00000113.page
100-00000131.page 116-00000147.page 14-00000017.page 31-00000034.page 49-00000080.page 66-00000097.page 83-00000114.page
10-00000013.page 117-00000148.page 15-00000018.page 32-00000035.page 50-00000081.page 67-00000098.page 84-00000115.page
1-00000004.page 118-00000149.page 16-00000019.page 33-00000064.page 5-00000008.page 68-00000099.page 85-00000116.page
101-00000132.page 119-00000150.page 17-00000020.page 34-00000065.page 51-00000082.page 69-00000100.page 86-00000117.page
102-00000133.page 120-00000151.page 18-00000021.page 35-00000066.page 52-00000083.page 70-00000101.page 87-00000118.page
103-00000134.page 12-00000015.page 19-00000022.page 36-00000067.page 53-00000084.page 7-00000010.page 88-00000119.page
104-00000135.page 121-00000152.page 20-00000023.page 37-00000068.page 54-00000085.page 71-00000102.page 89-00000120.page
105-00000136.page 122-00000153.page 2-00000005.page 38-00000069.page 55-00000086.page 72-00000103.page 90-00000121.page
106-00000137.page 123-00000154.page 21-00000024.page 39-00000070.page 56-00000087.page 73-00000104.page 9-00000012.page
107-00000138.page 124-00000155.page 22-00000025.page 40-00000071.page 57-00000088.page 74-00000105.page 91-00000122.page
108-00000139.page 125-00000156.page 23-00000026.page 4-00000007.page 58-00000089.page 75-00000106.page 92-00000123.page
109-00000140.page 126-00000157.page 24-00000027.page 41-00000072.page 59-00000090.page 76-00000107.page 93-00000124.page
110-00000141.page 127-00000158.page 25-00000028.page 42-00000073.page 60-00000091.page 77-00000108.page 94-00000125.page
11-00000014.page 128-00000159.page 26-00000029.page 43-00000074.page 6-00000009.page 78-00000109.page 95-00000126.page
111-00000142.page 129-00000160.page 27-00000030.page 44-00000075.page 61-00000092.page 79-00000110.page 96-00000127.page
112-00000143.page 130-00000161.page 28-00000031.page 45-00000076.page 62-00000093.page 80-00000111.page 97-00000128.page
113-00000144.page 13-00000016.page 29-00000032.page 46-00000077.page 63-00000094.page 8-00000011.page 98-00000129.page
114-00000145.page 131-00000162.page 30-00000033.page 47-00000078.page 64-00000095.page 81-00000112.page 99-00000130.page

生成表定义：
该工具在解析数据pages的时候，需要获得该table的表结构定义
[root@test11g percona-data-recovery-tool]# ./create_defs.pl -host 192.168.100.141 -user root -password root -db livekoo -table live_users >include/table_defs.h

[root@test11g percona-data-recovery-tool]# more include/table_defs.h
#ifndef table_defs_h
#define table_defs_h

// Table definitions
table_def_t table_definitions[] = {
{
name: "live_users",
{
{ /* int(11) */
name: "id",
type: FT_INT,
fixed_length: 4,

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

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(90) */
name: "user_name",
type: FT_CHAR,
min_length: 0,
max_length: 270,

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

can_be_null: TRUE
},
{ /* varchar(50) */
name: "channel",
type: FT_CHAR,
min_length: 0,
max_length: 150,

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

can_be_null: TRUE
},
{ /* varchar(50) */
name: "sub_channel",
type: FT_CHAR,
min_length: 0,
max_length: 150,

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

can_be_null: TRUE
},
{ /* varchar(250) */
name: "email",
type: FT_CHAR,
min_length: 0,
max_length: 750,

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

can_be_null: TRUE
},
{ /* varchar(20) */
name: "mobile",
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
},
{ type: FT_NONE }
}
},
};

#endif

重新make：
[root@test11g percona-data-recovery-tool]# 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 -o page_parser page_parser.c lib/tables_dict.o lib/libut.a

开始恢复删除的数据
[root@test11g percona-data-recovery-tool]# ./constraints_parser -5 -D -f pages-1420082922/FIL_PAGE_INDEX/0-59 >/tmp/live_users.sql
LOAD DATA INFILE '/root/percona-data-recovery-tool/dumps/default/live_users' REPLACE INTO TABLE `live_users` FIELDS TERMINATED BY '\t' OPTIONALLY ENCLOSED BY '"' LINES STARTING BY 'live_users\t' (id, user_name, channel, sub_channel, email, mobile);

参数：
-5 -f的参数和page_parser相同；
-D:该参数的意思是恢复删除的数据页；

加载数据：
mysql> LOAD DATA INFILE '/tmp/live_users.sql' REPLACE INTO TABLE live_users FIELDS TERMINATED BY '\t' OPTIONALLY ENCLOSED BY '"' LINES STARTING BY 'live_users\t' (id,user_name,channel,sub_channel,email,mobile);
Query OK, 2709 rows affected (0.04 sec)
Records: 2709 Deleted: 0 Skipped: 0 Warnings: 0

mysql> select count(*) from live_users;
+----------+
| count(*) |
+----------+
| 2565 |
+----------+
1 row in set (0.00 sec)

恢复完以后，数据少了很多，感觉这工具也不靠谱，或许是我的操作过程有误，以后再慢慢研究了，所以说做好数据备份才是最重要的

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/23249684/viewspace-1386808/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/23249684/viewspace-1386808/