咨询微信： dbservice1234 7 x 24 在线支持！

首页

下载 DBRECOVER

Undrop MySQL InnoDB 中恢复被drop的表，当 innodb_file_per_table=off时

Undrop MySQL InnoDB 中恢复被drop的表，当 innodb_file_per_table=off时

Posted by PDSERVICE on 12月 08, 2015 In

人为错误是不可避免的。错误的 “DROP DATABASE” 或 “DROP TABLE” 可能会破坏MySQL 服务器上的重要数据。备份是有帮助的，但不总是可用。这种情况是可怕的，但不至于没有希望的。在许多情况下，恢复几乎所有在数据库或表中的数据是有可能的。

我们来看看如何能做到这一点。恢复计划取决于InnoDB将所有数据储存在单个ibdata1还是每个表都有自己的表空间。在这篇文章中，我们考虑innodb_file_per_table= OFF的情况下。此参数假定所有表都保存在一个公共文件中，通常位于位于/var/lib/mysql/ibdata1。

 

错误操作 – 表删除

在这个情况下，我们使用测试数据库sakila 以及附带的工具。
假设我们错误删除了表actor：

 

mysql> SELECT * FROM actor LIMIT 10;
+----------+------------+--------------+---------------------+
| actor_id | first_name | last_name    | last_update         |
+----------+------------+--------------+---------------------+
|        1 | PENELOPE   | GUINESS      | 2006-02-15 04:34:33 |
|        2 | NICK       | WAHLBERG     | 2006-02-15 04:34:33 |
|        3 | ED         | CHASE        | 2006-02-15 04:34:33 |
|        4 | JENNIFER   | DAVIS        | 2006-02-15 04:34:33 |
|        5 | JOHNNY     | LOLLOBRIGIDA | 2006-02-15 04:34:33 |
|        6 | BETTE      | NICHOLSON    | 2006-02-15 04:34:33 |
|        7 | GRACE      | MOSTEL       | 2006-02-15 04:34:33 |
|        8 | MATTHEW    | JOHANSSON    | 2006-02-15 04:34:33 |
|        9 | JOE        | SWANK        | 2006-02-15 04:34:33 |
|       10 | CHRISTIAN  | GABLE        | 2006-02-15 04:34:33 |
+----------+------------+--------------+---------------------+
10 rows in set (0.00 sec)
mysql> CHECKSUM TABLE actor;
+--------------+------------+
| Table        | Checksum   |
+--------------+------------+
| sakila.actor | 3596356558 |
+--------------+------------+
1 row in set (0.00 sec)

mysql> SET foreign_key_checks=OFF
mysql> DROP TABLE actor;
Query OK, 0 rows affected (0.00 sec)

mysql>

 

从ibdata1中DROP TABLE后进行恢复

 

解析 InnoDB 表空间

InnoDB 将所有数据储存在B+tree 索引。一个表有一个集群索引PRIMARY，所有键都储存在其中。如果表有secondary 键，那每个键都有一个索引。每个索引由index_id标识。

如果我们要恢复表，必须找到属于特定index_id的所有页。

stream_parser 读取 InnoDB 表空间并根据类型和index_id排序InnoDB 页。

 

root@test:~/undrop-for-innodb# ./stream_parser -f /var/lib/mysql/ibdata1
Opening file: /var/lib/mysql/ibdata1
File information:

ID of device containing file:        64768
inode number:                      1190268
protection:                         100660 (regular file)
number of hard links:                    1
user ID of owner:                      106
group ID of owner:                     114
device ID (if special file):             0
blocksize for filesystem I/O:         4096
number of blocks allocated:          69632
time of last access:            1404842312 Tue Jul  8 13:58:32 2014
time of last modification:      1404842478 Tue Jul  8 14:01:18 2014
time of last status change:     1404842478 Tue Jul  8 14:01:18 2014
total size, in bytes:             35651584 (34.000 MiB)

Size to process:                  35651584 (34.000 MiB)
All workers finished in 0 sec
root@test: ~/undrop-for-innodb#

数据库页的数据被stream_parser 储存在文件夹pages-ibdata1：

 

 

root@test:~/undrop-for-innodb/pages-ibdata1/FIL_PAGE_INDEX# ls
0000000000000001.page  0000000000000121.page  0000000000000382.page
0000000000000395.page  0000000000000408.page  0000000000000421.page
0000000000000434.page  0000000000000447.page  0000000000000002.page
...
0000000000000406.page  0000000000000419.page  0000000000000432.page
0000000000000445.page  0000000000000120.page  0000000000000381.page
0000000000000394.page  0000000000000407.page  0000000000000420.page
0000000000000433.page  0000000000000446.page
root@test: ~/undrop-for-innodb/pages-ibdata1/FIL_PAGE_INDEX

 

Now each index_id from InnoDB tablespace is saved in a separate file. We can use c_parser to fetch records from the pages. But we need to know what index_id corresponds to table sakila/actor. That information we can acquire from the dictionary – SYS_TABLES and SYS_INDEXES. 现在InnoDB表空间的每个index_id被保存在单独的文件中。我们可以使用c_parser从页中提取记录。但是，我们需要知道什么index_id对应表中的Sakila/actor。我们可以从字典- SYS_TABLES SYS_INDEXES中获得这些信息。

SYS_TABLES 总是储存在文件 index_id 1中，即文件页-ibdata1/FIL_PAGE_INDEX./0000000000000001.page
我们来查找sakila/actor的table_id。如果MySQL 有足够时间将更改刷到磁盘，那添加 -D 参数表示“查找已删除记录”。字典总是REDUNDANT 格式，所以我们指定参数-4：

 

 

root@test:~/undrop-for-innodb# ./c_parser -4Df pages-ibdata1/FIL_PAGE_INDEX/0000000000000001.page -t dictionary/SYS_TABLES.sql | grep sakila/actor
000000000B28  2A000001430D4D  SYS_TABLES  "sakila/actor"  158  4  1 0   0   ""  0
000000000B28  2A000001430D4D  SYS_TABLES  "sakila/actor"  158  4  1 0   0   ""  0

 

注意就在表名后的号码 158 。这就是table_id。
接下来是查找表actor的PRIMARY索引的索引id。为此，我们将从文件0000000000000003.page (该表包含index_id 和table_id的信息)获取SYS_INDEXES 的记录。SYS_INDEXES的结构由-t选项传递。 The structure of SYS_INDEXES is passed with -t option.

 

 

root@test:~/undrop-for-innodb$ ./c_parser -4Df pages-ibdata1/FIL_PAGE_INDEX/0000000000000003.page -t dictionary/SYS_INDEXES.sql | grep 158
000000000B28    2A000001430BCA  SYS_INDEXES     158     376     "PRIMARY"       1       3       0       4294967295
000000000B28    2A000001430C3C  SYS_INDEXES     158     377     "idx\_actor\_last\_name"        1       0       0       4294967295
000000000B28    2A000001430BCA  SYS_INDEXES     158     376     "PRIMARY"       1       3       0       4294967295
000000000B28    2A000001430C3C  SYS_INDEXES     158     377     "idx\_actor\_last\_name"        1       0       0       4294967295

 

你能从输出中发现，必要的index_id 是376。因此我们要查找文件 0000000000000376.page中的actor数据。

 

root@test:~/undrop-for-innodb# ./c_parser -6f pages-ibdata1/FIL_PAGE_INDEX/0000000000000376.page -t sakila/actor.sql |  head -5
-- Page id: 895, Format: COMPACT, Records list: Valid, Expected records: (200 200)
000000000AA0    B60000035D0110  actor   1       "PENELOPE"      "GUINESS"       "2006-02-15 04:34:33"
000000000AA0    B60000035D011B  actor   2       "NICK"  "WAHLBERG"      "2006-02-15 04:34:33"
000000000AA0    B60000035D0126  actor   3       "ED"    "CHASE" "2006-02-15 04:34:33"
000000000AA0    B60000035D0131  actor   4       "JENNIFER"      "DAVIS" "2006-02-15 04:34:33"
root@test:~/undrop-for-innodb#

结果输出看上去正确，我们将转储数据储存到一个文件。要简化加载，c_parser 输出LOAD DATA INFILE 命令到标准错误输出。

我们使用该文件的默认位置：dump/default

 

root@test:~/undrop-for-innodb# mkdir -p dumps/default
root@test:~/undrop-for-innodb# ./c_parser -6f pages-ibdata1/FIL_PAGE_INDEX/0000000000000376.page -t sakila/actor.sql > dumps/default/actor 2> dumps/default/actor_load.sql

这是加载表的命令。

root@test:~/undrop-for-innodb# cat dumps/default/actor_load.sql
SET FOREIGN_KEY_CHECKS=0;
LOAD DATA LOCAL INFILE '/home/asterix/undrop-for-innodb/dumps/default/actor' REPLACE INTO TABLE `actor` FIELDS TERMINATED BY '\t' OPTIONALLY ENCLOSED BY '"' LINES STARTING BY 'actor\t' (`actor_id`, `first_name`, `last_name`, `last_update`);
root@test:~/undrop-for-innodb#

将数据加载回数据库

现在我们要将数据恢复到数据库中了。在加载转储数据之前，我们需要创建表actor的空结构：

 

mysql> source sakila/actor.sql
mysql> show create table actor\G
*************************** 1. row ***************************
       Table: actor
Create Table: CREATE TABLE `actor` (
  `actor_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `first_name` varchar(45) NOT NULL,
  `last_name` varchar(45) NOT NULL,
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`actor_id`),
  KEY `idx_actor_last_name` (`last_name`)
) ENGINE=InnoDB AUTO_INCREMENT=201 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
mysql>

现在，表actor被创建了。我们能在恢复后载入数据。

root@test:~/undrop-for-innodb# mysql --local-infile -uroot -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
...
mysql> USE sakila;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> source dumps/default/actor_load.sql
Query OK, 0 rows affected (0.00 sec)

Query OK, 600 rows affected (0.01 sec)
Records: 400  Deleted: 200  Skipped: 0  Warnings: 0

mysql>

检查恢复的数据

最后的步骤是– 查看数据质量。我们会看到记录的总数，预览一些记录并计算校验。

 

mysql> SELECT COUNT(*) FROM actor;
+----------+
| COUNT(*) |
+----------+
|      200 |
+----------+
1 row in set (0.00 sec)

mysql> SELECT * FROM actor LIMIT 5;
+----------+------------+--------------+---------------------+
| actor_id | first_name | last_name    | last_update         |
+----------+------------+--------------+---------------------+
|        1 | PENELOPE   | GUINESS      | 2006-02-15 04:34:33 |
|        2 | NICK       | WAHLBERG     | 2006-02-15 04:34:33 |
|        3 | ED         | CHASE        | 2006-02-15 04:34:33 |
|        4 | JENNIFER   | DAVIS        | 2006-02-15 04:34:33 |
|        5 | JOHNNY     | LOLLOBRIGIDA | 2006-02-15 04:34:33 |
+----------+------------+--------------+---------------------+
5 rows in set (0.00 sec)

mysql> CHECKSUM TABLE actor;
+--------------+------------+
| Table        | Checksum   |
+--------------+------------+
| sakila.actor | 3596356558 |
+--------------+------------+
1 row in set (0.00 sec)

mysql>

你能看到恢复后的校验是3596356558，与意外删除表之前的检验相等。因此，我们能确认数据被正确恢复了。
在下一篇文章中会讲到其他恢复的情况。

 

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

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

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