MySQL Innodb数据库误删ibdata1后MySQL数据库的恢复案例

上周，以前公司的同事朋友找我帮忙，看看能否帮忙恢复一个 MySQL 数据库，具体情况为：数据库版本为MySQL 5.6（具体版本不清楚），也不清楚具体的数据库引擎; 没有数据库备份，只剩下数据库下面的一些文件（frm、idb）,具体原因是因为出现问题的时候，重装了MySQL，最要命的是ibdata1等文件也没有了，当然这中间细节过程如何，不清楚也不用去纠结了。大概就是这么一个情况。

因为数据库不大，将对应的文件拷贝到自己一台测试服务器的MySQL数据文件目录下后（下面实验测试，对数据库名等敏感信息做了一下混淆），如下所示，数据库名为test，show tables可以看到相关的表。

其中有几张表的存储引擎为MyISAM，那么这些表的数据是完全可以恢复的，但是大部分表的存储引擎为InnoDB，访问表或查看表都会提示 “ ERROR 1146 (42S02): Table 'xxxx' doesn't exist 不存在。

mysql> desc think_cache;
ERROR 1146 (42S02): Table 'test.think_cache' doesn't exist
mysql> show create table think_cache;
ERROR 1146 (42S02): Table 'test.think_cache' doesn't exist
mysql> 

由于共享表空间的ibdata1数据文件不存在了，加之有没有备份，所以我武断的判断这个数据库真的无法恢复了，但是过后一天，这个朋友跟我说找了一家数据恢复公司将这个数据库恢复了 。 听到这个消息颇有点学艺不精的尴尬（其实谈不上尴尬吧，本来还在学习MySQL的路上，有些知识点不清楚也很正常。经验是需要慢慢积累的），不过更多的是好奇别人是如何恢复数据的，既然别人能够恢复，那么自己下一次遇到这种情况也要能搞定。下面就来复盘一下别人是如何恢复数据的（其实只要稍稍做点功课，发现这个其实挺简单的）

首先，我们来了解一下MySQL 表空间数据文件idbdat1文件相关概念和知识点:

    InnoDB采用按表空间（tablespace)的方式进行存储数据, 默认配置情况下会有一个初始大小为10MB， 名字为ibdata1的文件， 该文件就是默认的表空间文件（tablespce file），用户可以通过参数innodb_data_file_path对其进行设置，可以有多个数据文件，如果没有设置innodb_file_per_table的话， 那些Innodb存储类型的表的数据都放在这个共享表空间中，而系统变量innodb_file_per_table=1的话，那么InnoDB存储引擎类型的表就会产生一个独立表空间，独立表空间的命名规则为：表名.idb. 这些单独的表空间文件仅存储该表的数据、索引和插入缓冲BITMAP等信息，其它信息还是存放在共享表空间中。

    其实当时主要是对这个概念有点模糊了，以为这个系统变量innodb_file_per_table默认是关闭的，数据都会存储在共享表空间中，那么这些文件删除了，数据就无法恢复。所以武断的下结论，其实从MySQL 5.6.6开始， 系统变量innodb_file_per_table默认是启用的。只要再多了解一点或者说更深入了解一点的话，情况就会立马就会反转。也就是说如果开启了独立表空间，可从ibd文件中恢复数据。即使共享表空间的数据文件idbdata1丢失也不要紧，反之， 如果未开启独立表空间时，idbdat1被删除了，数据也会被删除，只能从备份中恢复，真的没有其他办法。

那么我们接下来看看，如何从idb文件中恢复数据吧，我们需要用到mysqlfrm工具, 需要安装MySQL Utilities，下面是安装MySQL Utilities 1.5.5

# tar -xvf mysql-utilities-1.5.5.tar.gz

# cd mysql-utilities-1.5.5

# python ./setup.py build

# python ./setup.py install

提取frm文件的表结构信息

mysqlfrm 是一个恢复性质的工具，用来读取.frm文件并从该文件中找到表定义数据生成CREATE语句。此处不对mysqlfrm工具做过多介绍，我们使用msqlfrm来生成该数据库的表的CREATE语句

[root@DB-Server ~]# service mysql stop
Shutting down MySQL.... SUCCESS! 
[root@DB-Server ~]# /usr/local/bin/mysqlfrm --basedir=/usr --port=3306 --user=root /data/mysql/test/ > test_frm.sql
[root@DB-Server ~]# 

检查导出的 SQL 语句 ， 发现都是 “ ERROR: The server version for this file is too low. It requires a server version 5.6.29 or higher but your server is version 5.6.20. Try using a newer server or use diagnostic mode ” 这类错误 ：

[root@DB-Server ~]# more test_frm.sql 
# Spawning server with --user=root.
# Starting the spawned server on port 3306 ... done.
# Reading .frm files
#
# Reading the think_cache.frm file.
ERROR: The server version for this file is too low. It requires a server version 5.6.29 or higher but your server is version 5.6.20. Try using a newer server or use diagnostic mode.
#
# Reading the think_session.frm file.
ERROR: The server version for this file is too low. It requires a server version 5.6.29 or higher but your server is version 5.6.20. Try using a newer server or use diagnostic mode.
#
# Reading the wx_activity_config.frm file.
ERROR: The server version for this file is too low. It requires a server version 5.6.29 or higher but your server is version 5.6.20. Try using a newer server or use diagnostic mode.
#
........................................................................................

从中可以看到这个数据库之前的版本为 MySQL 为 5.6.29 ， 而我这里的 MySQL 版本比这个低 （ MySQL 5.6.20 ） 。所以必须找一个跟这个版本相同或高的MySQL数据库操作才行。于是在另外一台测试服务器安装了MySQL

[root@gettestlnx02 ~]# service mysqld stop
 
Stopping mysqld:  [  OK  ]
 
[root@gettestlnx02 tmp]# mv test  /data/mysqldata/mysql/test
 
[root@gettestlnx02 tmp]# cd /data/mysqldata/mysql/

/usr/bin/mysqlfrm --basedir=/usr --port=3306 --user=root /data/mysqldata/mysql/test/ > test_frm.sql

如何要查看输出信息，可以使用参数-vvv

/usr/bin/mysqlfrm --basedir=/usr --port=3306 --user=root -vvv /data/mysqldata/mysql/test/ > test_frm.sql

生成的 SQL 脚本没有以分号结尾，本来想用sed命令给那些CREATE TABLE脚本加上分号结尾，但是发现其中大量CREATE TABLE的脚本结尾没有规律 ，都是以COMMNET='xxxxx'结尾，只能手工添加分号（如下所示）

导入frm文件的表结构信息

mysql> use test;
Database changed
mysql> source test_frm.sql
Query OK, 0 rows affected (0.02 sec)
 
Query OK, 0 rows affected (0.01 sec)
 
Query OK, 0 rows affected (0.01 sec)
 
Query OK, 0 rows affected (0.00 sec)
.................................

然后我们检查这个数据库的各类文件frm、ibd、MYI、MYD文件数量，后续做对比验证用途。

[root@gettestlnx02 test]# ls -lrt *.frm | wc -l

84

[root@gettestlnx02 test]# ls -lrt *.ibd | wc -l   

84

[root@gettestlnx02 test]# ls -lrt *.MYI | wc -l      

22

[root@gettestlnx02 test]# ls -lrt *.MYD | wc -l  

22

[root@gettestlnx02 test]#

删除新建表的独立表空间文件

使用下面脚本生成删除新建表的独立空间的脚本：

select concat(concat('alter table ',table_name), ' discard tablespace;')                                                  
from information_schema.tables                               
where table_schema='test' and engine ='InnoDB';

使用脚本就可以生成下面SQL，执行该命令后，对应数据库下面的ibd文件全部被删除。

alter table think_cache discard tablespace;       

alter table think_session discard tablespace;     

alter table wx_activity_config discard tablespace;

........................................

复制待恢复的表空间文件

将待恢复的ibd文件拷贝到对应数据库目录下面，并设置好权限属性

# cd /tmp/database
# ls -lrt *.ibd | wc -l
84
# cp *.ibd /var/lib/mysql/test
 
# chown  mysql:mysql *.ibd
# chmod 660 *.ibd

导入表空间

mysql> alter table think_cache import tablespace;
Query OK, 0 rows affected, 1 warning (0.21 sec)
 
mysql> alter table think_session import tablespace;
Query OK, 0 rows affected, 1 warning (0.18 sec)
 
mysql> select count(*) from think_cache;
+----------+
| count(*) |
+----------+
|    10919 |
+----------+
1 row in set (0.01 sec)
 
mysql> select * from think_cache limit 5;
+---------------------------------------+------------+----------------------------------------+---------+
| cachekey                              | expire     | data                                   | datacrc |
+---------------------------------------+------------+----------------------------------------+---------+
| 00OLH9JvIwX42R3mPygXYN3gWZp2rH_rebate | 1533050257 | s:30:"00OLH9JvIwX42R3mPygXYN3gWZp2rH"; |         |
| 00SCWX7cIgqnnzHRArAXoascr1gnlA_rebate | 1516937278 | s:30:"00SCWX7cIgqnnzHRArAXoascr1gnlA"; |         |
| 00uVkAbOMPGQc2z02PPxVMblGY7oj7_rebate | 1528708564 | s:30:"00uVkAbOMPGQc2z02PPxVMblGY7oj7"; |         |
| 01dB7czgCph7hgm1qGM7qA7haChXop_rebate | 1525740805 | s:30:"01dB7czgCph7hgm1qGM7qA7haChXop"; |         |
| 023oMqQAAwg4WWxWgJSLNgQhYlgtVi_rebate | 1531560804 | s:30:"023oMqQAAwg4WWxWgJSLNgQhYlgtVi"; |         |
+---------------------------------------+------------+----------------------------------------+---------+
5 rows in set (0.00 sec)
 
mysql> select count(*) from think_session;
+----------+
| count(*) |
+----------+
|     1347 |
+----------+
1 row in set (0.00 sec)
 
mysql> select * from think_session limit 5;
+----------------------------+----------------+--------------+
| session_id                 | session_expire | session_data |
+----------------------------+----------------+--------------+
| 00onr4u3jabvi6vrts3bfeaqt4 |     1533358643 |              |
| 00rs65ljphuhhughujfnk2bci6 |     1533350110 |              |
| 01ld93n8ac31o4uorqrebtjir5 |     1533418040 |              |
| 01u5tv79pp8jjssh1r3s7oj6d4 |     1533351181 |              |
| 0261rcndf0jmq9dccou5l23mn4 |     1533346621 |              |
+----------------------------+----------------+--------------+
5 rows in set (0.00 sec)

导出数据库

导入数据库

如果顺利的话，一切就正常了，数据正常恢复，是否也不是什么难事，难就难在你不知道而已，如果你认证学习了一下这方面的知识点，整个事情其实并不复杂。有些细节操作问题可以参考官方文档：

https://dev.mysql.com/doc/refman/8.0/en/innodb-troubleshooting-datadict.html

https://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting-datadict.html

那么在这个恢复过程中是否会遇到一些麻烦或问题呢，答案是肯定的，下面简单介绍一些在恢复过程中可能遇到的问题

1： 在实验测试过程，我一度使用版本为MySQL 5.7.21的数据库，在导入表空间是遇到下面错误：

mysql> alter table wx_sign_record import tablespace; 
 
ERROR 1808 (HY000): Schema mismatch (Table has ROW_TYPE_DYNAMIC row format, .ibd file has ROW_TYPE_COMPACT row format.)
 
mysql> alter table wx_sign_record  row_format=DYNAMIC;
 
ERROR 2013 (HY000): Lost connection to MySQL server during query
 
mysql> alter table wx_sign_record  row_format=DYNAMIC;
 
ERROR 2006 (HY000): MySQL server has gone away
 
No connection. Trying to reconnect...
 
Connection id:    2
 
Current database: test

后面在网上找到相关资料 ， 可能是因为数据库版本缘故 ， 我使用 MySQL 5.6.41 这个版本就没有遇到这个问题。所以如果遇到这种数据恢复，最好使用相同的数据库版本。

2：表空间 id 不一致问

    个人没有遇到这个问题，这里不做介绍。可以参考 MySQL 数据恢复案例

3：脚本自动化问题 

   对于研究问题，可以手工操作，但是最好通过脚本自动化操作， MySQL 数据恢复案例 里有自动化脚本放在github上，有兴趣可以参考！

原理介绍：

关于原理介绍，可以参考英文原文 The basics of InnoDB space file layout 或者 MySQL 数据恢复案例 ， 下面这部分内容完全摘抄自 MySQL 数据恢复案例 ：

恢复方案中，我们使用到了 DISCARD TABLESPACE、IMPORT TABLESPACE 和修改表空间 id。我们先说下 InnoDB 数据页的组成。InnoDB 数据页由 7 个部分组成，分别是 File Header、Page Header、Infimum 和 Supermum Records、User Records、Free Space 和 Page Directory。

接下来看看 ibdata 文件的组织结构，如下图：

From   blog.jcole.us, by Jeremy Cole.

然后看看 ibd 文件的组织结构，如下图：

From   blog.jcole.us, by Jeremy Cole.

我们要修改的表空间 id ， 位于 FSP_HEADER 。不同的 ibd 文件 ， 表空间 id 是不同的。ibdata 文件中有一个数据字典 data dictionary，记录的是实例中每个表在 ibdata 中的一个逻辑位置，而在 ibd 文件中也存储着同样的一个 tablespace id，两者必须一致，InnoDB 引擎才能正常加载到数据。所以，我们需要修改旧的表空间 id 为新的。

实际上，我们对于 ibdata 文件中的 undo、change buffer、double write buffer 数据可以不用关心。我们只需要利用一个全新的实例，以及一个干净的 ibdata 文件，通过卸载和加载表空间把 ibd 文件与 ibdata 文件关联。笔者使用了这么多脚本，目的就是如此。

参考资料：

https://serverfault.com/questions/698038/mysql-innodb-recovery-from-datafiles

https://dbarobin.com/2016/04/23/ibd-recovery/