MySQL没有备份情况下误删除表恢复

一、下载安装

1.1 下载地址

git clone https://github.com/twindb/undrop-for-innodb.git

1.2 安装

安装依赖包

yum install -y make gcc flex bison

编译安装

cd undrop-for-innodb
make

增加用于恢复表结构的工具sys_parse

gcc `$basedir/bin/mysql_config --cflags` `$basedir/bin/mysql_config --libs` -o sys_parser sys_parser.c

$basedir 是 MySQL的安装路径

二、开始测试

2.1 测试数据

undrop-for-innodb中带了一个 sakila 库，不过我在测试的时候，用的是官方的，address 表有一个字段类型是 geometry。恢复的时候报错，有兴趣的朋友可以试一下

下载地址 https://dev.mysql.com/doc/index-other.html

2.2 删除表

set foreign_key_checks = 0;
checksum table customer;
+-----------------+-----------+
| Table           | Checksum  |
+-----------------+-----------+
| sakila.customer | 399782750 |
+-----------------+-----------+

drop table customer;

checksum table 用来做恢复后的校验

2.3 数据恢复

2.3.1 表结构恢复

使用工具 stream_parser 解析文件内容。

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

执行完毕后会在当前目录下生成文件夹 pages-ibdata1 , 目录下按照每个页为一个文件，分为索引页和数据较大的 BLOB 页。系统表的话，是存在索引页中的。使用另外一个重要的工具 c_parser 来解析页的内容。

./c_parser -4Df pages-ibdata1/FIL_PAGE_INDEX/0000000000000001.page -t dictionary/SYS_TABLES.sql > dumps/default/SYS_TABLES 2> dumps/default/SYS_TABLES.sql
./c_parser -4Df pages-ibdata1/FIL_PAGE_INDEX/0000000000000003.page -t dictionary/SYS_INDEXES.sql > dumps/default/SYS_INDEXES 2> dumps/default/SYS_INDEXES.sql 
./c_parser -4Df pages-ibdata1/FIL_PAGE_INDEX/0000000000000002.page -t dictionary/SYS_COLUMNS.sql > dumps/default/SYS_COLUMNS 2> dumps/default/SYS_COLUMNS.sql
./c_parser -4Df pages-ibdata1/FIL_PAGE_INDEX/0000000000000004.page -t dictionary/SYS_FIELDS.sql > dumps/default/SYS_FIELDS 2> dumps/default/SYS_FIELDS.sql

参数解析：

4 表示文件格式是 REDUNDANT，系统表的格式默认值。另外可以取值 5 表示 COMPACT 格式，6 表示 MySQL 5.6 格式。
D 表示只恢复被删除的记录。
f 后面跟着文件。
t 后面跟着 CREATE TABLE 语句，需要根据表的格式来解析文件。

得到的结果 ‘SYS_TABLES’ 字段后面的就是系统表 SYS_TABLE 中对应存的记录。

创建恢复数据库 recover，用来存放恢复的系统表

create database recover

导入系统表

mysql recover < dictionary/SYS_TABLES.sql 
mysql recover < dictionary/SYS_INDEXES.sql
mysql recover < dictionary/SYS_FIELDS.sql 
mysql recover < dictionary/SYS_COLUMNS.sql

mysql recover < dumps/default/SYS_TABLES.sql 
mysql recover < dumps/default/SYS_INDEXES.sql
mysql recover < dumps/default/SYS_FIELDS.sql 
mysql recover < dumps/default/SYS_COLUMNS.sql

dictionary目录下是建表SQL，dumps/default/是刚才解析 page 得到的sql

解析表结构

./sys_parser -h'127.0.0.1' -uroot -p'abc_1234' -d recover sakila/customer
CREATE TABLE `customer`(
    `customer_id` SMALLINT UNSIGNED NOT NULL,
    `store_id` TINYINT UNSIGNED NOT NULL,
    `first_name` VARCHAR(45) CHARACTER SET 'utf8' COLLATE 'utf8_general_ci' NOT NULL,
    `last_name` VARCHAR(45) CHARACTER SET 'utf8' COLLATE 'utf8_general_ci' NOT NULL,
    `email` VARCHAR(50) CHARACTER SET 'utf8' COLLATE 'utf8_general_ci',
    `address_id` SMALLINT UNSIGNED NOT NULL,
    `active` TINYINT NOT NULL,
    `create_date` DATETIME NOT NULL,
    `last_update` TIMESTAMP,
    PRIMARY KEY (`customer_id`)
) ENGINE=InnoDB;

对比发现，恢复出来的 CREATE TABLE 语句相比原来创建的语句信息量有点缺少，因为 innodb 系统表里面存的数据相比 frm 文件是不足的，比如 AUTO_INCREMENT, DECIMAL 类型的精度信息都会缺失，也不会恢复二级索引，外建等。

2.3.2 表数据恢复

找到表 customer 的 table_id

grep customer dumps/default/SYS_TABLES
00000018501F    3C00000188063B    SYS_TABLES    "sakila/customer"    480    9    33    0    80    ""    475
00000018501F    3C00000188063B    SYS_TABLES    "sakila/customer"    480    9    33    0    80    ""    475

再根据 table_id 找到 index_id

grep 480 dumps/default/SYS_INDEXES
00000018501F    3C0000018802CB    SYS_INDEXES    480    848    "PRIMARY"    1    3    475    4294967295
00000018501F    3C00000188033D    SYS_INDEXES    480    849    "idx\_fk\_store\_id"    1    0    475    4294967295
00000018501F    3C0000018803AF    SYS_INDEXES    480    850    "idx\_fk\_address\_id"    1    0    475    4294967295
00000018501F    3C000001880421    SYS_INDEXES    480    851    "idx\_last\_name"    1    0    475    4294967295
00000018501F    3C0000018802CB    SYS_INDEXES    480    848    "PRIMARY"    1    3    475    4294967295
00000018501F    3C00000188033D    SYS_INDEXES    480    849    "idx\_fk\_store\_id"    1    0    475    4294967295
00000018501F    3C0000018803AF    SYS_INDEXES    480    850    "idx\_fk\_address\_id"    1    0    475    4294967295
00000018501F    3C000001880421    SYS_INDEXES    480    851    "idx\_last\_name"    1    0    475    4294967295

grep 480 是对应 SYS_TABLE 的 TALE ID，848对应的 INDEX_ID

MySQL5.6之后，默认 innodb_file_per_table = on 这种情况下每个表是保存在各自的 ibd 文件中的，当 drop table 之后，ibd 文件会被删除，此时最好能够设置磁盘整体只读，避免有其它进程重写文件块。stream_parser 这个工具不但可以读文件，还可以读磁盘，会根据 innodb 数据格式把数据页读出来。

找到被删除的 ibd 文件的挂载磁盘

df -h
Filesystem               Size  Used Avail Use% Mounted on
/dev/mapper/centos-root   46G   13G   33G  28% /
devtmpfs                 1.9G     0  1.9G   0% /dev
tmpfs                    1.9G     0  1.9G   0% /dev/shm
tmpfs                    1.9G   89M  1.8G   5% /run
tmpfs                    1.9G     0  1.9G   0% /sys/fs/cgroup
/dev/vda1               1014M  142M  873M  14% /boot
tmpfs                    379M     0  379M   0% /run/user/0

我的实验环境是 /dev/mapper/centos-root

./stream_parser -f /dev/mapper/centos-root -t 50G

磁盘大小执行 stream_parser，-t 表示磁盘的大小。执行的时候需要注意磁盘空间。跑完之后，在 undrop-for-innodb目录下会有一个 pages-centos-root 目录，其他环境不知道叫什么。类似 pages-ibdata1目录，下面依然是 FIL_PAGE_INDEX 跟 FIL_PAGE_TYPE_BLOB。我们要找的页在 FIL_PAGE_INDEX 目录下。

pwd
/root/undrop-for-innodb/pages-centos-root/FIL_PAGE_INDEX
ll 0000000000000848.page 
-rw-r--r--. 1 root root 81920 Oct 22 01:25 0000000000000848.page

接下来解析 0000000000000848.page

./c_parser -6f pages-centos-root/FIL_PAGE_INDEX/0000000000000848.page -t customer.sql > dumps/default/customer 2> dumps/default/customer.sql

参数解析：

6 表示 MySQL 5.6 格式，4 表示文件格式是 REDUNDANT，系统表的格式默认值。另外可以取值 5 表示 COMPACT 格式。
D 表示只恢复被删除的记录。
f 后面跟着文件。
t 后面跟着 CREATE TABLE 语句，需要根据表的格式来解析文件。customer.sql 是我们解析表结构的文件

有兴趣的朋友可以看一下 dumps/default/customer.sql，是 load data 语法SQL

2.3.3 导入恢复的数据

mysql sakila < customer.sql

mysql sakila < dumps/default/customer.sql

没有报错就是导入成功了，接下来就是校验数据了

checksum table customer;
+-----------------+-----------+
| Table           | Checksum  |
+-----------------+-----------+
| sakila.customer | 399782750 |
+-----------------+-----------+

跟删除表前的校验值是一样的。恢复成功。