Undelete MySQL如何从InnoDB表空间恢复被删除的行

Posted by PDSERVICE on Dec 08, 2015 In

在我以前的文章中，我解释了它如何在某些特定情况下，从一个完整备份恢复单个表，以节省时间，使恢复过程更简单。现在的情况更糟糕，因为我们没有备份或备份恢复过程不起作用。我如何恢复已删除的行？

我们将根据之前帖子中相同的例子，所以我们需要从表“salaries”中删除员工10008的记录。“意外”删除行之后，你应该停止MySQL，获取salaries.ibd的副本，并再次启动它。稍后，我们将从ibd文件提取这些被删除的行，并导入到数据库中。删除行和数据库停止之间的时间是至关重要的。如果页被重用，你无法恢复数据。

我要通过四个步骤解释全过程，以便更清晰明了：

1- 从表空间提取所有InnoDB页：

首先我们需要下载Percona数据恢复工具并使用“make”命令编译所有工具。在本例中，我要在/root/recovery-tool文件夹安装工具，数据如表空间和被恢复行在/root/recovery-tool/data。

编译后，我们需要将salaries.ibd表空间复制到恢复工具的数据目录。为了提取的所有页，我们将使用page_parser工具。该工具会找到并将表空间的所有页提取到输出目录。我们只需要指定行格式（-5），以及表空间位置（-f）

The row format can be -4 (REDUNDANT) or -5 (COMPACT). From 5.0.3 the default format is COMPACT. More information about row format on the following link: 行格式可以是-4（冗余）或-5（COMPACT）。从5.0.3起，默认格式是COMPACT。

你也能从Information Schema获取表行格式：

mysql (information_schema) > SELECT ROW_FORMAT from TABLES WHERE TABLE_SCHEMA=’employees’ AND TABLE_NAME=’salaries';
+————+
| ROW_FORMAT |
+————+
| Compact |
+————+

~/recovery-tool# ./page_parser -5 -f data/salaries.ibd
Opening file: data/salaries.ibd:
[…]
71.43% done. 2012-02-14 13:10:08 ETA(in 00:00 hours). Processing speed: 104857600 B/sec

所有页都储存在单个目录，其中有一些子目录，一页对应表中一个索引：

~/recovery-tool# ls pages-1329221407/FIL_PAGE_INDEX/
0-26 0-27

在这种情况下，ID 0-26和0-27有两个索引。 InnoDB有聚集主键，即数据与主键一起组织。因此，如果我们要提取行数据，我们需要确定哪两个索引是主键。这是我们的下一步。

2-标识主键

有不同的方法来找到正确索引，在这里我要解释其中三个：

A) INNODB_SYS_INDEXES

Percona Server 在INFORMATION_SCHEMA 有一些额外的表，可以帮助我们找到不同的索引和类型。

mysql (information_schema) > select i.INDEX_ID, i.NAME FROM INNODB_SYS_INDEXES as i INNER JOIN INNODB_SYS_TABLES as t USING(TABLE_ID) WHERE t.NAME=’salaries';
+———-+———+
| INDEX_ID | NAME |
+———-+———+
| 26 | PRIMARY |
| 27 | emp_no |
+———-+———+

B) InnoDB Table Monitor表监视器

索引信息也可以直接从MySQL获取，使用 InnoDB Tablespace Monitor。这个监视器将与表和索引（与它们的ID）相关所有信息写入错误日志中。

mysql (employees) > CREATE TABLE innodb_table_monitor (id int) ENGINE=InnoDB;

TABLE: name employees/salaries, id 18, flags 1, columns 7, indexes 2, appr.rows 2844513
[…]
INDEX: name PRIMARY, id 26, fields 2/6, uniq 2, type 3
root page 3, appr.key vals 2844513, leaf pages 6078, size pages 6120
FIELDS: emp_no from_date DB_TRX_ID DB_ROLL_PTR salary to_date
INDEX: name emp_no, id 27, fields 1/2, uniq 2, type 0
root page 4, appr.key vals 306195, leaf pages 2189, size pages 2212
FIELDS: emp_no from_date
[…]

第二个方法得到相同的结果， 0-26 是我们的主键。标识主键后，不要忘了删除innodb_table_monitor。

C) 检查磁盘上每个索引的大小

这非常依赖表模式，但通常主键在磁盘上更大，因为它也存储行本身的。

~/recovery-tool/pages-1329221407/FIL_PAGE_INDEX# du -hs 0-26/
96M 0-26/
~/recovery-tool/pages-1329221407/FIL_PAGE_INDEX# du -hs 0-27/
35M 0-27/

在我们的例子中，0-26 看上去是主键。

3- 提取行

我们知道了数据在哪个索引，所以下一步显然是从中提取行。要完成这个任务，我们要使用constraint_parser命令。为了使用它，工具需要知道表schema结构，即列的类型，名称和属性。这个信息需要在头文件 recovery-tools/include/table_defs.h可用。所以重新编译工具将是必要的。

要将schema定义转换为C头文件，可以使用名为create_defs.pl的工具。它会连接到数据库，以便检查表并创建table_defs.h内容。

~/recovery-tool# ./create_defs.pl –host 127.0.0.1 –port 5520 –user root –password msandbox –db employees –table salaries > include/table_defs.h

有关table_defs.h格式的详细信息在以下链接： https://www.percona.com/docs/wiki/innodb-data-recovery-tool:mysql-data-recovery:generating_a_table_definition

有了table_defs.h表定义，我们就要用“make”命令重新编译所有的工具。编译后，我们可以使用constraints_parser将行恢复为人类可读格式。

~/recovery-tool# ./constraints_parser -5 -D -f pages-1329221407/FIL_PAGE_INDEX/0-26/ > data/salaries.recovery

通过 -D 选项，我们请求 constraints_parser 只恢复被删除的页。-5 和 -f 是我们在page_parser之前使用的相同选项。

你能在salaries.recovery 中找到许多被删除的行，不仅是意外删除的行。你应当收到找出需要恢复的行并将它储存在其他文件中。这是例子的输出

~/data-recovery# cat data/salaries.recovery
salaries 10008 “1998-03-11″ 46671 “1999-03-11″
salaries 10008 “1999-03-11″ 48584 “2000-03-10″
salaries 10008 “2000-03-10″ 52668 “2000-07-31″

4- 导入行

有了这些数据，最后一步就是将它们导入到数据库：

mysql (employees) > LOAD DATA INFILE ‘/root/recovery-tool/data/salaries.recovery’ REPLACE INTO TABLE `salaries` FIELDS TERMINATED BY ‘\t’ OPTIONALLY ENCLOSED BY ‘”‘ LINES STARTING BY ‘salaries\t’ (emp_no, from_date, salary, to_date);
Query OK, 3 rows affected (0.01 sec)
Records: 3 Deleted: 0 Skipped: 0 Warnings: 0

mysql (employees) > select * from salaries where emp_no=10008;
+——–+——–+————+————+
| emp_no | salary | from_date | to_date |
+——–+——–+————+————+
| 10008 | 46671 | 1998-03-11 | 1999-03-11 |
| 10008 | 48584 | 1999-03-11 | 2000-03-10 |
| 10008 | 52668 | 2000-03-10 | 2000-07-31 |
+——–+——–+————+————+

数据恢复成功！10008 再次恢复了它的工资。

结论

通过InnoDB，被删除的行没有丢失。你可以从原始表空间恢复，或者如果你有二进制备份，也能从ibd文件中恢复它们。只需使用constraint_parser不加-D选项（被删除），你就能恢复所有在表空间中的数据。

You are here

Undelete MySQL如何从InnoDB表空间恢复被删除的行