IT博客汇 | 利用undrop-for-innodb恢复MySQL数据

利用undrop-for-innodb恢复MySQL数据

夜行人发表于 2017-12-07 00:21:50

这是一个MySQL数据恢复工具，我们尝试下恢复MySQL数据

1 环境说明

VMware 8H2G
CentOS 6.2 64bit
MySQL version: 5.7.17-11 Percona Server
一个没有任何读写的测试数据库实例
innodb_file_per_table on

2 安装

比较简单

cd /dist/src
git clone https://github.com/twindb/undrop-for-innodb.git
cd undrop-for-innodb/
make
gcc `$basedir/bin/mysql_config --cflags` `$basedir/bin/mysql_config --libs` -o sys_parser sys_parser.c

3 环境准备

create database recover;
use recover;
CREATE TABLE `actor` (
  `actor_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `first_name` varchar(45) NOT NULL,
  `last_name` varchar(45) NOT NULL,
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`actor_id`),
  KEY `idx_actor_last_name` (`last_name`)
) ENGINE=InnoDB AUTO_INCREMENT=201 DEFAULT CHARSET=utf8;

insert into actor(first_name, last_name) values('zhang', 'jian');
insert into actor(first_name, last_name) values('zhan', 'jian');
insert into actor(first_name, last_name) values('zha', 'jian');
insert into actor(first_name, last_name) values('zh', 'jian');
insert into actor(first_name, last_name) values('z', 'jian');

checksum table actor;
+-----------+------------+
| Table     | Checksum   |
+-----------+------------+
| per.actor | 2184463059 |
+-----------+------------+
1 row in set (0.00 sec)

# 此处模拟误删除表
DROP TABLE actor;

4 开始恢复

由于我们有表结构SQL了，所以本例没有恢复表结构，参考资料中有恢复表结构的操作

避免磁盘被覆盖写

由于是innodb_file_per_table on，就是drop操作会直接删除文件，此时比较稳妥的处理是关掉所有服务，将文件系统挂载为只读，由于本例是测试环境，没有任何写入，所以没有做以下处理

/etc/init.d/mysql stop
# 或者加-f参数，不过好像比较暴力
mount -o remount,ro /data

开始在磁盘上查找InnoDB页文件

本操作会在/dist/src/undrop-for-innodb/生成pages-sda5

cd /dist/src/undrop-for-innodb/

# 73G为df -h显示的sda5的总大小
# 以下指令本例大概会消耗20+小时
./stream_parser -f /dev/sda5 -s 1G -t 73G

将ibdata1分页

此操作不会影响ibdata1，会在/dist/src/undrop-for-innodb/生成pages-ibdata1

./stream_parser -f /data/database/mysql/ibdata1

获取TABLE ID

21616为TABLE ID

./c_parser -4Df pages-ibdata1/FIL_PAGE_INDEX/0000000000000001.page -t dictionary/SYS_TABLES.sql  | grep 'recover/actor'  
00000001E113    2F0000018C08A0  SYS_TABLES      "recover/actor" 21616   4       33      0       80      ""      21619

获取INDEX ID

其中36728和36729为NDEX ID

./c_parser -4Df pages-ibdata1/FIL_PAGE_INDEX/0000000000000003.page -t dictionary/SYS_INDEXES.sql  | grep '21616'
00000001E113    2F0000018C071D  SYS_INDEXES     21616   36728   "PRIMARY"       1       3       21619   4294967295
00000001E113    2F0000018C078F  SYS_INDEXES     21616   36729   "idx\_actor\_last\_name"        1       0       21619   4294967295

恢复数据

根据上面找到的INDEX ID找到对应的pages，sakila/actor.sql为建表SQL文件，好像只需要用到36728这个INDEX ID

从输出可以看到，数据找回了

./c_parser -6f pages-sda5/FIL_PAGE_INDEX/0000000000036728.page -t sakila/actor.sql
-- Page id: 3, Format: COMPACT, Records list: Valid, Expected records: (5 5)
00000001E106    A6000001D60110  actor   201     "zhang" "jian"  "2017-12-04 15:58:38"
00000001E107    A7000002D30110  actor   202     "zhan"  "jian"  "2017-12-04 15:58:38"
00000001E109    A9000002D50110  actor   203     "zha"   "jian"  "2017-12-04 15:58:38"
00000001E10B    AA000002D60110  actor   204     "zh"    "jian"  "2017-12-04 15:58:38"
00000001E10E    AC000002D80110  actor   205     "z"     "jian"  "2017-12-04 15:58:38"

5 其他

大内存和读写性能好的磁盘将有效加快恢复进度

6 参考资料

MySQL · 数据恢复 · undrop-for-innodb

github-undrop-for-innodb