SQL进阶理论篇（十九）：（无备份 + 无binlog）情况下的数据恢复

简介

实际生产环境中，会有很多种不同形式的数据备份方式，比如说对数据库做时间点备份或者是做延迟备份的从库。

延迟备份有个好处，就是如果开发人员在主库上做了误操作导致数据丢失的话，正常的主从架构会实时同步给从库，导致从库的数据也会丢失，但延迟备份不会，如果我们在延迟从库进行同步前发现了误操作的话，还是有希望根据延迟从库来恢复主库数据的。

但想象一个这样的情况，就是没有做数据库备份，也没有开启binlog，这时候如何做数据恢复？

本节将围绕这个场景，简单介绍以下几个部分，做简单了解就行：

• InnoDB中的表空间；
• 如果.ibd文件损坏了，数据该如何找回？
• 如何模拟InnoDB文件的损坏与数据恢复？

InnoDB的表空间

InnoDB的文件格式是.ibd文件，数据会按照表空间进行存储，有两种表空间：共享表空间和独立表空间。

我们可以使用以下命令来查看当前表空间的存储方式：

show variables like 'innodb_file_per_table';

on表示独立表空间，OFF则表示共享表空间。

在共享表空间的模式下，InnoDB存储的所有表的数据都会放在共享表空间中，就是说多个数据表共用一个表空间。

当然，表空间在物理上是呈现成多个文件，来存放在磁盘上。

这样的好处是，单个表的大小可以突破文件系统大小的限制，最大可以达到64TB，即InnoDB表空间的上限。

缺点也很明显，多个数据表放在一起，结构不清晰，不利用数据的恢复和迁移。而且大量表的数据和索引都会放在一个文件里，会导致共享空间下的文件大小都很大。

在独立表空间的模式下，每个数据表都有自己的物理文件，即table_name.ibd，每个文件里会保存这个表的所有数据、索引、内部元数据等所有信息。

优点在于每张表即一个文件，表之间是相互独立的，互相之间不会有影响，结构清晰，而且非常方便单个表的迁移和恢复。

如果.ibd文件损坏，该如何找回数据？

可以使用Percona Data Recovery Tool for InnoDB 工具来对ibd文件做恢复。

恢复的原理是因为我们在使用delete的时候是逻辑删除，并不是真的删除。

InnoDB的数据页里，每个数据行有一个删除位标记，即delete_mask属性，属性为1时表示该记录已经被逻辑删除。逻辑删除并不是真正的删除，只是说在新数据插入的时候，可以把新数据覆盖在这一行上，这时候才算是真正的删除。

因此，当我们发生了delete误删除的时候，一定要第一时间停止对该表的更新和写入，及时将ibd文件复制出来进行修复。

当然，如果你之前已经开启了 Binlog，就可以使用闪回工具，比如 mysqlbinlog 或者 binlog2sql。原理就是因为 Binlog 文件本身保存了数据库更新的事件（Event），通过这些事件可以帮我们重现数据库的所有更新变化，也就是 Binlog 回滚。

那如果没有开启binlog的话，在ibd文件受损的情况下（数据表无法正常被读取），还可以通过数据库自身的机制来进行数据恢复。

我们可以人工介入，调整innodb_force_recovery参数。

通过以下命令来查看当前参数的状态：

show variables like 'innodb_force_recovery';

默认是0，表示不进行强制恢复。这时候如果ibd文件中的数据页发生损坏的话，就会无法读取数据，甚至出现MySQL宕机的情况。

这个参数还有1-6的取值，分别代表不同的强制恢复措施。

innodb_force_recovery=1的时候，表示即使发现了损坏页也允许服务继续运行，数据表仍然可以正常读取，方便我们对当前损坏的数据表进行分析和备份。

通常我们会把这个参数设置为1。但如果设置成1还是无法读取到数据表的话，可以适当依次增加到2和3。一般不会设置到4及以上，因为可能会对数据文件造成永久破坏。

另外，当参数设置大于0时，相当于对InnoDB做了写保护，此时只能进行有限的select操作（比如说where条件和order by都无法使用）或者是drop。

当我们开启了强恢复之后，数据库的功能会受到极大的限制，我们需要尽快把错误的数据表备份出来，完成数据恢复工作。整体恢复步骤如下：

• 使用innodb_force_recovery启动服务器。

将该参数设置为1，然后启动数据库。如果仍然无法读取到数据表，调大参数到2或者3。

• 备份数据表

将损坏的InnoDB数据表备份到新的MyISAM数据表中。因为此时InnoDB引擎已经整个进入写保护了，无法再创建新表来写入数据了，只能通过MyISAM来中转。

• 删除旧表，改名新表

数据备份完成后，我们可以删除掉有问题的InnoDB数据表，将新建的MyISAM表改成这个名字，让新表替代旧表来履行服务职能。

• 关闭innodb_force_recovery，重启数据库

备份完成后，我们需要马上关闭innodb_force_recovery，因为强恢复模式下整个数据库近乎无法操作了。之后关闭数据库，将新表的引擎更新为InnoDB即可。

整个恢复流程结束。

InnoDB文件的损坏与恢复实例

接下来，我们使用一个例子来模拟一下InnoDB文件损坏及人工干预恢复的整个过程。

生成InnoDB数据表

我们简单创建一个只有一个字段的数据表t1，执行下面命令即可：

create table t1(id int);

然后调用下面的存储过程来生成一些数据：

BEGIN
-- 当前数据行
DECLARE i INT DEFAULT 0;
-- 最大数据行数
DECLARE max_num INT DEFAULT 100;
-- 关闭自动提交
SET autocommit=0;
REPEAT
SET i=i+1;
-- 向t1表中插入数据
INSERT INTO t1(id) VALUES(i);
UNTIL i = max_num
END REPEAT;
-- 提交事务
COMMIT;
END

接着我们运行call insert_t1()，这个存储过程会帮我们插入100条数据，这样我们就有了t1.ibd这个文件。

模拟损坏ibd文件

我们首先关闭掉MySQL的服务，然后用编辑器打开t1.ibd文件，可以看到类似下图的内容：

都是二进制编码。

接着我们破坏其中的一些内容，比如说找几行非0的数据，删掉后保存，这时候的t1.ibd就被破坏了。

如果我们没有打开innodb_force_recovery，那么数据文件将无法正常读取。因此我们修改MySQL的配置文件，找到[mysqld]的位置，然后再下面增加一行innodb_force_recovery=1。

备份数据表

我们在设置了innodb_force_recovery=1后再重启数据库，就可以读到数据表里的数据，但是其实没法读取到所有数据。

比如说我们使用select * from t1 limit 10;，发现可以正常读到10行数据。接着我们执行select * from t1 limit 100;，发现可能会报错：

这是为什么呢？

这说明我们要读取的部分包含了已损坏的数据页。

因此，我们需要通过二分查找，判断出数据页损坏的位置，从而筛选出有效的数据行位置，将有效的数据行备份出来。

假设我们发现出问题的行是第100行，那么前99行就是没问题的。我们在MyISAM里创建一个相同结构的新表t2：

create table t2(id int) engine=MyISAM;

然后将前99行复制给t2：

insert into t2 select * from t1 limit 99;

注意，这时候InnoDB整个开启了写保护，只支持有限的select查询，甚至带where条件的select都不行，会报错如下：

删除旧表，改名新表

drop table t1;
rename table t2 to t1;

这时候还不能将新t1的引擎改成InnoDB，仍然会报错

关闭innodb_force_recovery

接着我们将数据库配置文件里的innodb_force_recovery=1删掉，然后重启数据库。

最后将新的t1表的存储引擎修改回InnoDB：

alter table t1 engine = InnoDB;

至此，大功告成。我们成功恢复了所有正常的数据，虽然错误数据是没法恢复了，但至少我们把对业务的影响程度降到了最低。

参考文献

1. 36丨数据库没有备份，没有使用Binlog的情况下，如何恢复数据？