这是一个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