mysql - percona-data-recovery-tool-for-innodb 不能恢复被删除的行 - SegmentFault 思否

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

我用的是percona-data-recovery-tool-for-innodb-0.5。
表结构

CREATE TABLE `member_info` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '用户id',
  `name` varchar(20) NOT NULL COMMENT '用户名称',
  `sex` varchar(3) NOT NULL COMMENT '用户性别',
  `add_time` int(11) NOT NULL COMMENT '注册时间',
  `sub_desc` varchar(100) NOT NULL COMMENT '用户简介',
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`),
  KEY `add_time` (`add_time`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8

表数据

mysql> select * from member_info;
+----+--------------+-----+------------+----------+
| id | name | sex | add_time | sub_desc |
+----+--------------+-----+------------+----------+
| 1 | gaoxiaosong | 男 | 1481384310 | |
| 2 | zhanzhaozhong | nan  | 1476002753 | |
| 3 | tuyouyou | 女 | 1476005256 | |
+----+--------------+-----+------------+----------+
3 rows in set (0.00 sec)

先删除和修改数据

UPDATE member_info SET sex='女' WHERE id=1;

DELETE FROM member_info WHERE id=2;

使用recovery工具步骤

root@pz:/usr/local/src/percona-data-recovery-tool-for-innodb-0.5# ./page_parser -5 -f /usr/local/mysql5.5/data/test/member_info.ibd
root@pz:/usr/local/src/percona-data-recovery-tool-for-innodb-0.5# ./create_defs.pl --host 127.0.0.1 -port 3307 --user root --password 123456 --db test > ./include/table_defs.h
root@pz:/usr/local/src/percona-data-recovery-tool-for-innodb-0.5# make
root@pz:/usr/local/src/percona-data-recovery-tool-for-innodb-0.5# ./constraints_parser -5 -f ./pages-1484015318/FIL_PAGE_INDEX/0-486/0-00000003.page
member_info 1 "gaoxiaosong" "女" 1481384310 ""
member_info 3 "tuyouyou" "女" 1476005256 ""
LOAD DATA INFILE '/usr/local/src/percona-data-recovery-tool-for-innodb-0.5/dumps/default/member_info' REPLACE INTO TABLE `member_info` FIELDS TERMINATED BY '\t' OPTIONALLY ENCLOSED BY '"' LINES STARTING BY 'member_info\t' (id, name, sex, add_time, sub_desc);


root@pz:/usr/local/src/percona-data-recovery-tool-for-innodb-0.5#./constraints_parser -5 -f ./pages-1484015318/FIL_PAGE_INDEX/0-486/0-00000003.page -V
Opening file: pages-1483957112/FIL_PAGE_INDEX/0-477/0-00000003.page
Initializing table definitions...
Processing table: member_info
 - total fields: 7
 - nullable fields: 0
 - minimum header size: 8
 - minimum rec size: 21
 - maximum rec size: 390

Read data from fn=3...
Page id: 3
Checking a page
Infimum offset: 0x63
Supremum offset: 0x70
Next record at offset: 0x80 (128)
Next record at offset: 0xAB (171)
Next record at offset: 0x70 (112)
Page is good
Starting offset: 128 (80). Checking 1 table definitions.

Checking offset: 0x80: (member_info) ORIGIN=OK DELETED=0x0 OFFSETS=OK DATA_SIZE=OK
Checking field lengths for a row (member_info): OFFSETS: 0 4 10 17 28 31 35
 - field id(4):
 - field DB_TRX_ID(6):
 - field DB_ROLL_PTR(7):
 - field name(11):OK!
 - field sex(3):OK!
 - field add_time(4):
 - field sub_desc(0):OK!
FIELD_SIZES=OK
Checking constraints for a row (member_info) at 0x2069090: len 100; hex 00000001000000188cad210000066d083d67616f7869616f736f6e67e5a5b3d84c2176000308000018ffc500000003000000188ca89d0000016701107475796f75796f75e5a5b3d7fa0d880000030c200020000000000002000000188cab200000066c05; asc ! m =gaoxiaosong L!v g tuyouyou l ;
 - field id(addr = 0x2069090, len = 4):
 - field DB_TRX_ID(addr = 0x2069094, len = 6):
 - field DB_ROLL_PTR(addr = 0x206909a, len = 7):
 - field name(addr = 0x20690a1, len = 11):
 - field sex(addr = 0x20690ac, len = 3):
 - field add_time(addr = 0x20690af, len = 4):
 - field sub_desc(addr = 0x20690b3, len = 0):
Row looks OK!

---------------------------------------------------
PAGE3: Found a table member_info record: 0x2069090 (offset = 128)
Processing record 0x2069090 from table 'member_info'
PHYSICAL RECORD: n_fields 7; compact format; info bits 0
 0: len 4; hex 00000001; asc ;; 1: len 6; hex 000000188cad; asc ;; 2: len 7; hex 210000066d083d; asc ! m =;; 3: len 11; hex 67616f7869616f736f6e67; asc gaoxiaosong;; 4: len 3; hex e5a5b3; asc ;; 5: len 4; hex d84c2176; asc L!v;; 6: len 0; hex ; asc ;;
Field #0 @ 0x2069090: length 4, value: 1
Field #3 @ 0x20690a1: length 11, value: "gaoxiaosong"
Field #4 @ 0x20690ac: length 3, value: "女"
Field #5 @ 0x20690af: length 4, value: 1481384310
Field #6 @ 0x20690b3: length 0, value: ""

Next offset: 0xAB
Checking offset: 0xAB: (member_info) ORIGIN=OK DELETED=0x0 OFFSETS=OK DATA_SIZE=OK
Checking field lengths for a row (member_info): OFFSETS: 0 4 10 17 25 28 32
 - field id(4):
 - field DB_TRX_ID(6):
 - field DB_ROLL_PTR(7):
 - field name(8):OK!
 - field sex(3):OK!
 - field add_time(4):
 - field sub_desc(0):OK!
FIELD_SIZES=OK
Checking constraints for a row (member_info) at 0x20690bb: len 100; hex 00000003000000188ca89d0000016701107475796f75796f75e5a5b3d7fa0d880000030c200020000000000002000000188cab200000066c05c97a68616e7a68616f7a6f6e67e794b7d7fa03c10000000000000000000000000000000000000000000000; asc g tuyouyou l zhanzhaozong ;
 - field id(addr = 0x20690bb, len = 4):
 - field DB_TRX_ID(addr = 0x20690bf, len = 6):
 - field DB_ROLL_PTR(addr = 0x20690c5, len = 7):
 - field name(addr = 0x20690cc, len = 8):
 - field sex(addr = 0x20690d4, len = 3):
 - field add_time(addr = 0x20690d7, len = 4):
 - field sub_desc(addr = 0x20690db, len = 0):
Row looks OK!

---------------------------------------------------
PAGE3: Found a table member_info record: 0x20690bb (offset = 171)
Processing record 0x20690bb from table 'member_info'
PHYSICAL RECORD: n_fields 7; compact format; info bits 0
 0: len 4; hex 00000003; asc ;; 1: len 6; hex 000000188ca8; asc ;; 2: len 7; hex 9d000001670110; asc g ;; 3: len 8; hex 7475796f75796f75; asc tuyouyou;; 4: len 3; hex e5a5b3; asc ;; 5: len 4; hex d7fa0d88; asc ;; 6: len 0; hex ; asc ;;
Field #0 @ 0x20690bb: length 4, value: 3
Field #3 @ 0x20690cc: length 8, value: "tuyouyou"
Field #4 @ 0x20690d4: length 3, value: "女"
Field #5 @ 0x20690d7: length 4, value: 1476005256
Field #6 @ 0x20690db: length 0, value: ""

Next offset: 0x70LOAD DATA INFILE '/usr/local/src/percona-data-recovery-tool-for-innodb-0.5/dumps/default/member_info' REPLACE INTO TABLE `member_info` FIELDS TERMINATED BY '\t' OPTIONALLY ENCLOSED BY '"' LINES STARTING BY 'member_info\t' (id, name, sex, add_time, sub_desc);

直接grep pages文件，是可以找到被删除的数据的，在网上查了很久，都没有找到解决的方法

Original url: Access
Created at: 2018-10-23 18:36:46
Category: default
Tags: none

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

请先后发表评论