undrop-for-innodb实测（一）-- 表结构恢复

2022-02-10 17:57:10


介绍

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

到这里部署过程就结束了，这里介绍下几个重要的文件及目录：

  1. dictionary目录。存放字典sql脚本，用于恢复表结构的几张核心字典表的DDL语句
  2. sakila目录。测试schema
  3. stream_parser。可执行文件，用于扫描文件或者磁盘设备，目的是找出符合innodb格式的数据页，按照index_id进行组织
  4. c_parser。可执行文件，用于解析innodb数据页，获取行记录
  5. 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
1 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  ../dictionary/SYS_TABLES.sql
mysql> source  ../dictionary/SYS_INDEXES.sql
mysql> source  ../dictionary/SYS_FIELDS.sql
mysql> source  ../dictionary/SYS_COLUMNS.sql

#执行前面生成的LOAD DATA语句导入恢复的记录
shell> mysql -h127.0.0.1 -uroot  -p dictionary <SYS_TABLES.sql
shell> mysql -h127.0.0.1 -uroot  -p dictionary <SYS_COLUMNS.sql
shell> mysql -h127.0.0.1 -uroot  -p dictionary <SYS_INDEXES.sql
shell> mysql -h127.0.0.1 -uroot  -p dictionary <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@mydocker-test1 undrop-for-innodb]# vi /etc/ld.so.conf.d/mariadb-x86_64.conf 
/opt/mysql/lib
[root@mydocker-test1 undrop-for-innodb]# ldconfig

执行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，

mysql> select * from SYS_INDEXES where TABLE_ID=41;
+----------+----+---------+----------+------+-------+------------+
| TABLE_ID | ID | NAME    | N_FIELDS | TYPE | SPACE | PAGE_NO    |
+----------+----+---------+----------+------+-------+------------+
|       41 | 42 | PRIMARY |        1 |    3 |    26 | 4294967295 |
|       41 | 43 | name    |        1 |    0 |    26 | 4294967295 |
+----------+----+---------+----------+------+-------+------------+
2 rows in set (0.00 sec)

然后通过SYS_FIELDS表查找这个索引的字段定义。

mysql> select * from SYS_FIELDS  where index_id=43;
+----------+-----+----------+
| INDEX_ID | POS | COL_NAME |
+----------+-----+----------+
|       43 |   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字典的逻辑关系。

上一篇：Docker容器启动报WARNING: IPv4 forwarding is disabled. Networking will not work


下一篇：mysql 跨年按周分组