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

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

我们将根据之前帖子中相同的例子,所以我们需要从表“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-标识主键

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

  1. 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 |
+———-+———+

  1. 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。

  1. 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选项(被删除),你就能恢复所有在表空间中的数据。

 

 

如果自己搞不定可以找诗檀软件专业ORACLE/MySQL数据库修复团队成员帮您恢复!

诗檀软件专业数据库修复团队

服务热线 : 13764045638   QQ号:47079569    邮箱:service@parnassusdata.com

 

关注刘相兵的新浪微博

扫码加入微信Oracle小密圈,了解Oracle最新技术下载分享资源

Speak Your Mind

沪公网安备 31010802001379号

TEL/電話+86 13764045638
Email service@parnassusdata.com
QQ 47079569