阮胜昌的技术记录站-LinuxMySQL数据库运维

undrop是一款针对mysql innodb的数据恢复工具，通过扫描文件或磁盘设备，然后解析innodb数据页进而恢复丢失的数据，对于drop、truncate以及文件损坏都很有帮助。本文介绍drop操作后表结构的恢复过程。

部署
软件包下载
shell> git clone https://github.com/twindb/undrop-for-innodb.git
依赖包安装
#此处安装必须的编译环境
shell> yum install make gcc flex bison

编译
shell> make
shell> ls
check_data.c  c_parser.o     innochecksum.c        Makefile      recover_dictionary.sh  sql_parser.o     stream_parser.o  test.sh
check_data.o  dictionary     innochecksum_changer  print_data.c sakila                sql_parser.y     sys_parser.c     vagrant
c_parser      fetch_data.sh  lex.yy.c              print_data.o  sql_parser.c           stream_parser    tables_dict.c
c_parser.c    include        LICENSE               README.md     sql_parser.l           stream_parser.c  tables_dict.o
到这里部署过程就结束了，这里介绍下几个重要的文件及目录：

dictionary目录。存放字典sql脚本，用于恢复表结构的几张核心字典表的DDL语句
sakila目录。测试schema
stream_parser。可执行文件，用于扫描文件或者磁盘设备，目的是找出符合innodb格式的数据页，按照index_id进行组织
c_parser。可执行文件，用于解析innodb数据页，获取行记录
sys_parser。可执行文件，通过字典表记录恢复目标表的表结构
环境准备
这款工具支持主流linux的4/5/6/7版本，mysql支持5.6/5.7。本文中测试的环境是centos7+MySQL5.7
[root@mydocker-test1 undrop-for-innodb]# cat /etc/redhat-release
CentOS Linux release 7.5.1804 (Core)
 mysql> select version();
+------------+
| version()  |
 +------------+
 | 5.7.20-log |
 +------------+
1 row in set (0.00 sec)
接下来准备测试表
mysql> show create table t1\G
*************************** 1. row ***************************
         Table: t1
 Create Table: CREATE TABLE `t1` (
    `id` int(11) NOT NULL,
    `name` varchar(20) DEFAULT NULL,
    `age` int(11) DEFAULT NULL,
    PRIMARY KEY (`id`),
    KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
 row in set (0.00 sec)

mysql> select * from t1;
+----+----------+------+
| id | name     | age  |
+----+----------+------+
|  1 | zhangsan |   18 |
|  2 | lisi     |   21 |
|  3 | jack     |  17 |
|  4 | alei     |  25 |
+----+----------+------+
4 rows in set (0.00 sec)


然后进行删除操作
 mysql> drop table t1;
 Query OK, 0 rows affected (0.32 sec)
innodb数据字典介绍
在做恢复演示之前，首先插播一段innodb数据字典的介绍。
innodb数据字典存储在系统表空间，主要是用于记录innodb核心的对象信息，比如表、索引、字段等。字典的本质是REDUNDANT行格式的innodb表，并且对用户不可见。
为了便于用户查看，innodb提供了一系列的字典视图，视图提供的信息和字典表完全相同，这一部分内容我们可以在information_schema中找到。如下，
mysql> use information_schema -A;
Database changed
mysql> show tables where Tables_in_information_schema like '%innodb_sys%';
 +------------------------------+
 | Tables_in_information_schema |
+------------------------------+
| INNODB_SYS_DATAFILES         |
| INNODB_SYS_VIRTUAL           |
 | INNODB_SYS_INDEXES           |
| INNODB_SYS_TABLES            |
| INNODB_SYS_FIELDS            |
| INNODB_SYS_TABLESPACES       |
| INNODB_SYS_FOREIGN_COLS      |
| INNODB_SYS_COLUMNS           |
| INNODB_SYS_FOREIGN           |
| INNODB_SYS_TABLESTATS        |
+------------------------------+
10 rows in set (0.00 sec)
接下来介绍几张对于数据恢复较为关键的几张字典表。
1、SYS_TABLES。对应视图INNODB_SYS_TABLES，提供innodb表的元数据信息，其中几个关键字段如下，
TABLE_ID。innodb表的标识id，这是组织字典信息的关键
NAME。表名
N_COLS。字段数量，包含innodb的三个隐藏字段，以及虚拟字段
FILE_FORMAT。文件格式
ROW_FORMAT。行格式


2、SYS_COLUMNS。对应视图INNODB_SYS_COLUMNS，提供innodb表中字段的元数据信息，其中关键字段如下，
TABLE_ID。表id，和sys_tables关联
NAME。字段名
POS。0开始的序号，表示字段在表中的位置
MTYPE/PRTYPE。字段类型，这是innodb的表示方法，前者是主体类型，后者是精确类型
LEN。字段长度
MTYPE
Stands for “main type”. A numeric identifier for the column type. 1 = VARCHAR, 2 = CHAR, 3 = FIXBINARY, 4 = BINARY, 5 = BLOB, 6 = INT, 7 = SYS_CHILD, 8 = SYS, 9 = FLOAT, 10 = DOUBLE, 11 = DECIMAL, 12 = VARMYSQL, 13 = MYSQL, 14 = GEOMETRY.
PRTYPE
The InnoDB “precise type”, a binary value with bits representing MySQL data type, character set code, and nullability.

3、SYS_INDEXES。对应视图INNODB_SYS_INDEXES，提供索引定义信息，关键字段如下，
INDEX_ID。索引id，这是组织数据的关键。innodb所有的数据都是基于B+tree进行组织，因此B+tree叶节点存储表数据以及二级索引数据，每个叶节点数据页存储index_id信息以标识所属的index。undrop-for-innodb工具扫描innodb数据页后通过index_id对数据页进行重组
NAME。索引名
TABLE_ID。表id，与sys_tables关联
PAGE_NO。B+tree索引根节点的页号

4、SYS_FIELDS。对应视图INNODB_SYS_FIELDS，提供索引定义中的字段信息。此信息可用于恢复二级索引
INDEX_ID。索引id，和SYS_INDEXES关联
NAME。字段名
POS。0开始的序号，表示字段在索引定义中的位置

有关字典表的描述，详细可参阅官方文档：
https://dev.mysql.com/doc/refman/5.7/en/innodb-information-schema-system-tables.html
表结构恢复
接下来进行误删除操作后的数据恢复过程，本文介绍第一步，表结构的恢复，主要理解几张字典表的逻辑关系。
恢复表结构的实质是恢复被删除的字典数据，因此是需要在ibdata文件中字典数据不被重写的前提下进行，咱们这里在发生删除操作后首先关闭数据库实例。
[root@mydocker-test1 undrop-for-innodb]#
[root@mydocker-test1 undrop-for-innodb]# mysqladmin -h127.0.0.1 -uroot -P3310 -p shutdown
 Enter password:
然后解析ibdata文件，扫描出所有符合innodb格式的数据页，结果会按照index_id进行重新组织，
shell> ./stream_parser  -f /home/my3310/data/ibdata1
此时会生成一个ibdata文件解析后的目录结构，接下来使用c_parser解析数据页，获取丢失的元数据行记录

#解析四张关键字典表的数据，获取已删表的数据结构。
#所有字典表的index_id是硬编码的，这里01是SYS_TABLES，02是SYS_COLUMNS，03是SYS_INDEXES，04是SYS_FIELDS。
shell> mkdir -p dumps/default
shell> ./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
shell> ./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
shell> ./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
shell> ./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
这里有几个选项：
-4Df 。最前面的数字4/5表示行格式REDUNDANT/COMPACT，D表示获取被删除的记录，f后面指定文件
-t 。指定建表语句，生成的结果会根据表结构进行组织
注意：
这里有个地方需要注意，就是这个输出的文件路径./dumps/default，这是工具里代码固定的，因此在此步骤之前我们必须要创建这个目录 $BASEDIR/dumps/default
接下来查看下解析结果，
shell> cd dumps/default/
shell> ls
SYS_COLUMNS  SYS_COLUMNS.sql  SYS_FIELDS SYS_FIELDS.sql  SYS_INDEXES  SYS_INDEXES.sql  SYS_TABLES SYS_TABLES.sql

现在我们看到的就是恢复出来的元数据记录，每张字典表有两个文件，和表名同名的文件已文本形式保存行记录，另一个SQL文件是根据表结构生成的loda data语句。
接下来在本地库上将字典数据恢复出来，注意这里需要是另外一个mysql实例，因为为了避免数据文件被复写，我们此前已经将事故实例停止了服务。
#根据工具安装文件下的dictionary目录中的SQL脚本创建字典表

mysql> create database dictionary;
mysql> use dictionary;
mysql> source  /root/undrop-for-innodb-develop/dictionary/SYS_TABLES.sql
mysql> source  /root/undrop-for-innodb-develop/dictionary/SYS_INDEXES.sql
mysql> source  /root/undrop-for-innodb-develop/dictionary/SYS_FIELDS.sql
mysql> source  /root/undrop-for-innodb-develop/dictionary/SYS_COLUMNS.sql

#执行前面生成的LOAD DATA语句导入恢复的记录
shell> mysql -h127.0.0.1 -uroot  -p dictionary </root/undrop-for-innodb-develop/dumps/default/SYS_TABLES.sql
shell> mysql -h127.0.0.1 -uroot  -p dictionary </root/undrop-for-innodb-develop/dumps/default/SYS_COLUMNS.sql
shell> mysql -h127.0.0.1 -uroot  -p dictionary </root/undrop-for-innodb-develop/dumps/default/SYS_INDEXES.sql
shell> mysql -h127.0.0.1 -uroot  -p dictionary </root/undrop-for-innodb-develop/dumps/default/SYS_FIELDS.sql

到这里工作已经基本完成，因为恢复出来的元数据信息，我们已经可以在本地实例的表中查看了。接下来就是最后一步，使用sys_parser读取表中的元数据记录，并生成DDL语句。
不过这里sys_parser需要单独编译，
shell> make sys_parser
/opt/mysql/bin/mysql_config
 cc -o sys_parser sys_parser.c `mysql_config --cflags` `mysql_config --libs`
最好联合mysql的安装路径进行编译，如果已经编译完成并出现如下报错的话，可以修改ld配置解决
[root@mydocker-test1 undrop-for-innodb]# ./sys_parser -h127.0.0.1  -uroot -p****   -d dictionary  test_1/t1
 ./sys_parser: error while loading shared libraries: libmysqlclient.so.20: cannot open shared object file: No such file or directory
以上错误解析方法：
[root@node232 undrop-for-innodb-develop]# ldconfig -v | grep mysql
/opt/mysql/lib:
/usr/lib64/mysql:
        libmysqlclient_r.so.16 -> libmysqlclient_r.so.16.0.0
        libmysqlclient.so.16 -> libmysqlclient.so.16.0.0

[root@node232 undrop-for-innodb-develop]# find / -name 'libmysqlclient.so.20'
/usr/local/mysql-5.7.9-linux-glibc2.5-x86_64/lib/libmysqlclient.so.20
/usr/local/mysql-5.7.18-linux-glibc2.5-x86_64/lib/libmysqlclient.so.20
[root@node232 undrop-for-innodb-develop]#cp /usr/local/mysql-5.7.18-linux-glibc2.5-x86_64/lib/libmysqlclient.so.20 /usr/lib64/mysql/
[root@node232 undrop-for-innodb-develop]# ldconfig


[root@node232 undrop-for-innodb-develop]# ldconfig -v | grep mysql
/opt/mysql/lib:
/usr/lib64/mysql:
        libmysqlclient.so.20 -> libmysqlclient.so.20
        libmysqlclient_r.so.16 -> libmysqlclient_r.so.16.0.0
        libmysqlclient.so.16 -> libmysqlclient.so.16.0.0

执行sys_parser，需要注意的是，用作表结构恢复的实例端口必须为3306，这里没有提供端口选项。

  [root@mydocker-test1 undrop-for-innodb]# ./sys_parser -h127.0.0.1  -uroot -p*** -d dictionary  test_1/t1

  CREATE TABLE `t1`(
      `id` INT NOT NULL,
      `name` VARCHAR(20) CHARACTER SET 'utf8' COLLATE 'utf8_general_ci',
      `age` INT,
     PRIMARY KEY (`id`)
  ) ENGINE=InnoDB;

大功告成，但是好像有一点不对？
是的，对比前面环境准备中原始的表结构，这里少了一些信息，就是二级索引。undrop-for-innodb的表结构恢复功能，只会恢复表的主干信息，不包含自增、二级索引以及外键等信息。但是这些信息我们可以通过字典数据获取。

这里我们首先通过SYS_INDEXES表找到该表的索引信息，发现除了primary key之外还有一个二级索引name，
root@localhost[dictionary]>select * from SYS_TABLES where NAME='test/t1'
    -> ;
+---------+-----+--------+------+--------+---------+--------------+-------+
| NAME    | ID  | N_COLS | TYPE | MIX_ID | MIX_LEN | CLUSTER_NAME | SPACE |
+---------+-----+--------+------+--------+---------+--------------+-------+
| test/t1 | 205 |      3 |   33 |      0 |      80 |              |   286 |
+---------+-----+--------+------+--------+---------+--------------+-------+
1 row in set (0.00 sec)

通过表的ID来查找索引的ID:
root@localhost[dictionary]>select * from SYS_INDEXES where TABLE_ID=205;
+----------+-----+---------+----------+------+-------+------------+
| TABLE_ID | ID  | NAME    | N_FIELDS | TYPE | SPACE | PAGE_NO    |
+----------+-----+---------+----------+------+-------+------------+
|      205 | 265 | PRIMARY |        1 |    3 |   286 | 4294967295 |
|      205 | 266 | name    |        1 |    0 |   286 | 4294967295 |
+----------+-----+---------+----------+------+-------+------------+
2 rows in set (0.00 sec)

然后通过SYS_FIELDS表查找这个索引的字段定义。
root@localhost[dictionary]>select * from SYS_FIELDS  where index_id=266;
+----------+-----+----------+
| INDEX_ID | POS | COL_NAME |
+----------+-----+----------+
|      266 |   0 | name     |
+----------+-----+----------+
1 row in set (0.00 sec)
然后我们就可以通过alter table语句恢复二级索引了。索引名为'name'，并且定义中只有name这一个字段

mysql> alter table t1 add key `name`(name);

其他的表结构信息也可以通过类似方式进行手工恢复。

结语

本文基于undrop-for-innodb工具，在发生innodb的drop操作后进行表结构恢复的操作，主要是通过这个过程理解innodb字典的逻辑关系。

以上介绍了如何在删除表的情况下，把表结构找出来，如果想知道如果找回表中具体的数据，请联系博主领免死金牌，哈哈。。。