Percona Data Recovery Tool for InnoDB

发表于 更新于 本文字数： 4.4k 阅读时长 ≈ 4 分钟

遇到这么一个问题，不小心删除关键数据（delete），MySQL 没有备份且没有开 binlog。网上搜寻了一遍，发现可以使用 percona 的恢复工具解决，本文做个恢复步骤记录。

下载

网站 或 直接下载工具

安装

1
2
3
4
5
tar -xvf percona-data-recovery-tool-for-innodb-0.5.tar.gz
cd percona-data-recovery-tool-for-innodb-0.5/mysql-source
./configure
cd ..
make

idb 处理

提取 ibd 物理文件，按照每页16K，存放。

1
2
3
4
5
6
7
8
# 参数
# -5: row format 为 Compact
# -f: 要解析的文件
./page_parser -5 -f t_trans.ibd

# 结果生成
pages-1610078166/FIL_PAGE_INDEX
pages-1610078166/FIL_PAGE_TYPE_BLOB

FIL_PAGE_INDEX 按照主键索引目录生成，要恢复的表索引id使用innodb_table_monitor查询获取

获取索引 id

1
2
# 创建innodb_table_monitor表，通知innodb存储引擎将数据输出到/etc/my.cnf中定义的log-error文件里
mysql> create table innodb_table_monitor(a int) engine=innodb;

log-error = /var/log/mysql/mysqld.log

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
===========================================
210108 13:18:22 INNODB TABLE MONITOR OUTPUT
===========================================
--------------------------------------
TABLE: name test/t_trans, id 51, flags 1, columns 28, indexes 2, appr.rows 942
  COLUMNS: id: DATA_INT DATA_UNSIGNED DATA_BINARY_TYPE DATA_NOT_NULL len 8; transdate: DATA_VARCHAR prtype 524559 len 8; mchid: DATA_VARCHAR prtype 524559 len 15; refno: DATA_VARCHAR prtype 524559 len 12; custacct: DATA_VARCHAR prtype 524303 len 20; wtrq: DATA_VARCHAR prtype 524303 len 8; djxh: DATA_VARCHAR prtype 524303 len 32; name: DATA_VARCHAR prtype 524303 len 32; zjlx: DATA_VARCHAR prtype 524303 len 32; idno: DATA_VARCHAR prtype 524303 len 32; rqrq: DATA_VARCHAR prtype 524303 len 8; nsrsbh: DATA_VARCHAR prtype 524303 len 20; swjylsh: DATA_VARCHAR prtype 524303 len 32; payamount: DATA_FIXBINARY DATA_BINARY_TYPE len 9; zoneno: DATA_VARCHAR prtype 524303 len 5; devno: DATA_VARCHAR prtype 524303 len 20; type: DATA_VARCHAR prtype 524303 len 2; channel: DATA_VARCHAR prtype 524303 len 2; posserno: DATA_VARCHAR prtype 524303 len 27; rzzh: DATA_VARCHAR prtype 524303 len 20; returnCode: DATA_VARCHAR prtype 524303 len 3; returnMessage: DATA_VARCHAR prtype 524303 len 100; jftype: DATA_VARCHAR prtype 524303 len 1; reqpack: DATA_VARCHAR prtype 528399 len 40960; respack: DATA_VARCHAR prtype 524303 len 255; DB_ROW_ID: DATA_SYS prtype 256 len 6; DB_TRX_ID: DATA_SYS prtype 257 len 6; DB_ROLL_PTR: DATA_SYS prtype 258 len 7;
  INDEX: name PRIMARY, id 77, fields 4/27, uniq 4, type 3
   root page 98364, appr.key vals 942, leaf pages 209, size pages 289
   FIELDS:  id transdate mchid refno DB_TRX_ID DB_ROLL_PTR custacct wtrq djxh name zjlx idno rqrq nsrsbh swjylsh payamount zoneno devno type channel posserno rzzh returnCode returnMessage jftype reqpack respack
  INDEX: name Index_trans_sw_id, id 78, fields 3/4, uniq 4, type 0
   root page 98365, appr.key vals 702, leaf pages 4, size pages 5
   FIELDS:  transdate mchid refno id
-----------------------------------
END OF INNODB TABLE MONITOR OUTPUT
==================================

关于上面一些特殊字段中的含义

1
2
3
DB_TRX_ID - this field is managed by InnoDBinternally and contains a ID of transaction which changed a recordlast time
DB_ROLL_PTR - one more internal InnoDB field (TODO: find out whatis it used for).
DB_ROW_ID - this internally used field should be the first field intables without primary keys (it is an auto-increment field used byInnoDB to identify rows in such tables)

上面信息可以知道，index_id 为 77，下面在恢复数据时需要用到

生成表结构

1
2
3
4
5
6
7
8
# percona-data-recovery-tool-for-innodb-0.5
# -- host 主机地址
# -- port 端口
# -- user 用户名
# -- password 密码
# -- db 数据库名
# -- table 表名
./create_defs.pl --host localhost --port 3306 --user root --password 123456 --db test --table t_trans > include/table_defs.h

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

1
2
# percona-data-recovery-tool-for-innodb-0.5
make

恢复数据

1
2
3
4
5
6
# 77 通过上面 innodb_table_monitor 查询得到
# -5 -f: 和page_parser相同
# -5: row format 为 Compact
# -f: 要解析的文件
# -D: 代表恢复删除的数据页
./constraints_parser -D -5 -f pages-1610078166/FIL_PAGE_INDEX/0-77/ > /tmp/t_trans.txt

恢复完成后生成如下语句和文件：

1
LOAD DATA INFILE '/mysql/percona-data-recovery-tool-for-innodb-0.5/dumps/default/t_trans' REPLACE INTO TABLE `t_trans` FIELDS TERMINATED BY '\t' OPTIONALLY ENCLOSED BY '"' LINES STARTING BY 't_trans\t' (id, transdate, mchid, refno, custacct, wtrq, djxh, name, zjlx, idno, rqrq, nsrsbh, swjylsh, payamount, zoneno, devno, type, channel, posserno, rzzh, returnCode, returnMessage, jftype, reqpack, respack);

/tmp/t_trans.txt 该文件就是我们需要 load data 的文本文件

导入数据

1
LOAD DATA LOCAL INFILE '/tmp/t_trans.txt' REPLACE INTO TABLE `t_trans` FIELDS TERMINATED BY '\t' OPTIONALLY ENCLOSED BY '"' LINES STARTING BY 't_trans\t' (id, transdate, mchid, refno, custacct, wtrq, djxh, name, zjlx, idno, rqrq, nsrsbh, swjylsh, payamount, zoneno, devno, type, channel, posserno, rzzh, returnCode, returnMessage, jftype, reqpack, respack);

总结

1）该恢复工具只支持innodb存储引擎，文件的格式需要为：Compact
2）数据被误删除后，需要尽快将保护现场，停止数据库，把idb文件拷贝出来，防止ibd文件写入数据被覆盖（笔者恢复的一个表中，由于数据删除后，表中仍有大量写入，导致大部分数据没有恢复出来）；
3）千叮嘱万嘱咐，数据库的备份重于泰山；

reference

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

无全量备份、未开启binlog日志，利用percona工具恢复delete的数据