首页 > 代码库 > DROP TABLE 恢复【一】
DROP TABLE 恢复【一】
当DROP TABLE指令敲下的时候,你很爽,你有考虑过后果么?如果该表真的没用,你DROP到无所谓,如果还有用的,这时你肯定吓惨了吧,如果你有备份,那么恭喜你,逃过一劫,如果没有备份呢?这时就该绝望了?NO! 如果你的表是innodb表,那么还有希望挽救,如果是myisam表,那么真的没救了。前面文章介绍了 Recover InnoDB dictionary,这是恢复数据的前提。恢复innodb字典信息使用的是TwinDB recovery toolkit,我们恢复数据也是使用该工具。下面的案例是基于innodb_file_per_table=OFF的前提下,即使用共享表空间,所有的信息都保存在ibdata1中。使用独立表空间DROP TABLE后数据恢复将在后面的文章介绍。
错误的操作--删除表
用到的示例数据库还是sakila,关于下载地址前面的文章有地址。将模拟把sakila库中的actor表删除后进行恢复。
root@localhost : sakila 21:34:11> 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.01 sec)root@localhost : sakila 21:34:25>
root@localhost : sakila 21:34:25> CHECKSUM TABLE actor; +--------------+------------+| Table | Checksum |+--------------+------------+| sakila.actor | 2472295518 |+--------------+------------+1 row in set (0.07 sec)root@localhost : sakila 21:35:30> SET foreign_key_checks=OFF;Query OK, 0 rows affected (0.00 sec)root@localhost : sakila 21:35:46> DROP TABLE actor;Query OK, 0 rows affected (0.07 sec)root@localhost : sakila 21:35:57>
从ibdata1恢复数据
现在actor表已经删除,但表中的信息仍然存与ibdata1中。该数据保持不变,直到InnoDB的重用空闲的页。我们需要尽快停止mysqld进程。
对于恢复,我们将使用TwinDB恢复工具包。看看我前面的文章Recover InnoDB dictionary。
解析innodb表空间(ibdata1)
InnoDB将所有数据存储在B +树索引。 一个表有只有一个聚集索引,所有字段存储在这里。 如果表有辅助索引,由index_id标识每个索引。
如果我们要恢复一个表,我们必须找到属于特定index_id的所有页面。
stream_parser读取InnoDB表和排序按类型和每个index_id的InnoDB的页面。
[root@mysql-server-01 undrop-for-innodb]# ./stream_parser -f /data/mysql/user_3306/data/ibdata1 Opening file: /data/mysql/user_3306/data/ibdata1File information:ID of device containing file: 2055inode number: 77760163protection: 100660 (regular file)number of hard links: 1user ID of owner: 498group ID of owner: 500device ID (if special file): 0blocksize for filesystem I/O: 4096number of blocks allocated: 53248time of last access: 1407057329 Sun Aug 3 17:15:29 2014time of last modification: 1407072967 Sun Aug 3 21:36:07 2014time of last status change: 1407072967 Sun Aug 3 21:36:07 2014total size, in bytes: 27262976 (26.000 MiB)Size to process: 27262976 (26.000 MiB)All workers finished in 0 sec[root@mysql-server-01 undrop-for-innodb]#
使用stream_parser将把数据从page保存到pages-ibdata1
[root@mysql-server-01 FIL_PAGE_INDEX]# pwd/root/undrop-for-innodb/pages-ibdata1/FIL_PAGE_INDEX[root@mysql-server-01 FIL_PAGE_INDEX]# ll total 6976-rw-r--r-- 1 root root 32768 Aug 3 21:59 0000000000000001.page-rw-r--r-- 1 root root 49152 Aug 3 21:59 0000000000000002.page-rw-r--r-- 1 root root 49152 Aug 3 21:59 0000000000000003.page-rw-r--r-- 1 root root 49152 Aug 3 21:59 0000000000000004.page-rw-r--r-- 1 root root 32768 Aug 3 21:59 0000000000000005.page。。。。。。。。。。。。。。。。。。。。。。。。。-rw-r--r-- 1 root root 16384 Aug 3 21:59 0000000000000011.page-rw-r--r-- 1 root root 16384 Aug 3 21:59 0000000000000012.page-rw-r--r-- 1 root root 16384 Aug 3 21:59 0000000000000013.page-rw-r--r-- 1 root root 16384 Aug 3 21:59 0000000000000053.page-rw-r--r-- 1 root root 16384 Aug 3 21:59 0000000000000054.page-rw-r--r-- 1 root root 16384 Aug 3 21:59 0000000000000055.page-rw-r--r-- 1 root root 16384 Aug 3 21:59 18446744069414584320.page
现在InnoDB表空间的每个index_id被保存在一个单独的文件。我们可以用c_parser工具从page提取记录。但是,我们需要知道哪个index_id对应表Sakila/actor。这些信息,我们可以从字典中获取:SYS_TABLES和SYS_INDEXES。
SYS_TABLES始终存储在文件index_id为1的page,ibdata1/FIL_PAGE_INDEX/0000000000000001.page 这让我们找到Sakila/actor表的标识符。如果MySQL有足够的时间来刷新到磁盘的变化再加入D选项,意思是“寻找被删除的记录“,innodb字典信息永远是冗余格式,所以我们需要指定选项-4。
[root@mysql-server-01 undrop-for-innodb]# ./c_parser -4Df pages-ibdata1/FIL_PAGE_INDEX/0000000000000001.page -t dictionary/SYS_TABLES.sql | grep sakila/actor000000000344 45000002B902C8 SYS_TABLES "sakila/actor" 13 4 1 0 0 "" 0000000000344 45000002B902C8 SYS_TABLES "sakila/actor" 13 4 1 0 0 "" 0SET FOREIGN_KEY_CHECKS=0;LOAD DATA LOCAL INFILE ‘/root/undrop-for-innodb/dumps/default/SYS_TABLES‘ REPLACE INTO TABLE `SYS_TABLES` FIELDS TERMINATED BY ‘\t‘ OPTIONALLY ENCLOSED BY ‘"‘ LINES STARTING BY ‘SYS_TABLES\t‘ (`NAME`, `ID`, `N_COLS`, `TYPE`, `MIX_ID`, `MIX_LEN`, `CLUSTER_NAME`, `SPACE`);[root@mysql-server-01 undrop-for-innodb]#
注意表名之后的数13。这是表标识符。这和前面的文章不谋而合,Recover InnoDB dictionary
接下来的事情,需要做的是找到actor表的的主键ID。为此,我们将从SYS_INDEXES文件0000000000000003.page获取记录(该表将包含有关index_id和表标识符信息)。 SYS_INDEXES的结构需要通过-t选项解析。
[root@mysql-server-01 undrop-for-innodb]# ./c_parser -4Df pages-ibdata1/FIL_PAGE_INDEX/0000000000000003.page -t dictionary/SYS_INDEXES.sql | grep 13000000000344 45000002B90145 SYS_INDEXES 13 15 "PRIMARY" 1 3 0 4294967295000000000344 45000002B901B7 SYS_INDEXES 13 16 "idx\_actor\_last\_name" 1 0 0 4294967295000000000344 45000002B90145 SYS_INDEXES 13 15 "PRIMARY" 1 3 0 4294967295000000000344 45000002B901B7 SYS_INDEXES 13 16 "idx\_actor\_last\_name" 1 0 0 4294967295000000000344 45000002B90145 SYS_INDEXES 13 15 "PRIMARY" 1 3 0 4294967295000000000344 45000002B901B7 SYS_INDEXES 13 16 "idx\_actor\_last\_name" 1 0 0 4294967295SET FOREIGN_KEY_CHECKS=0;LOAD DATA LOCAL INFILE ‘/root/undrop-for-innodb/dumps/default/SYS_INDEXES‘ REPLACE INTO TABLE `SYS_INDEXES` FIELDS TERMINATED BY ‘\t‘ OPTIONALLY ENCLOSED BY ‘"‘ LINES STARTING BY ‘SYS_INDEXES\t‘ (`TABLE_ID`, `ID`, `NAME`, `N_FIELDS`, `TYPE`, `SPACE`, `PAGE_NO`);[root@mysql-server-01 undrop-for-innodb]#
我们可以从输出看到,PRIMARY index_id标示符是15。因此,我们的数据将从0000000000000015.page寻找。
[root@mysql-server-01 undrop-for-innodb]# ./c_parser -6f pages-ibdata1/FIL_PAGE_INDEX/0000000000000015.page -t sakila/actor.sql | head -10-- Page id: 307, Format: COMPACT, Records list: Valid, Expected records: (200 200)00000000032C AD000001750110 actor 1 "PENELOPE" "GUINESS" "2006-02-15 04:34:33"00000000032C AD00000175011A actor 2 "NICK" "WAHLBERG" "2006-02-15 04:34:33"00000000032C AD000001750124 actor 3 "ED" "CHASE" "2006-02-15 04:34:33"00000000032C AD00000175012E actor 4 "JENNIFER" "DAVIS" "2006-02-15 04:34:33"00000000032C AD000001750138 actor 5 "JOHNNY" "LOLLOBRIGIDA" "2006-02-15 04:34:33"00000000032C AD000001750142 actor 6 "BETTE" "NICHOLSON" "2006-02-15 04:34:33"00000000032C AD00000175014C actor 7 "GRACE" "MOSTEL" "2006-02-15 04:34:33"00000000032C AD000001750156 actor 8 "MATTHEW" "JOHANSSON" "2006-02-15 04:34:33"00000000032C AD000001750160 actor 9 "JOE" "SWANK" "2006-02-15 04:34:33"[root@mysql-server-01 undrop-for-innodb]#
看见上面的输出,是不是觉得希望来了?哈哈
上面的结果正是我们想要的,我们现在把数据存贮在文件中,然后倒入,创建dump/default目录存储数据。
[root@mysql-server-01 undrop-for-innodb]# mkdir -p dumps/default[root@mysql-server-01 undrop-for-innodb]#
[root@mysql-server-01 undrop-for-innodb]# ./c_parser -6f pages-ibdata1/FIL_PAGE_INDEX/0000000000000015.page -t sakila/actor.sql > dumps/default/actor 2> dumps/default/actor_load.sql [root@mysql-server-01 undrop-for-innodb]#
我们看看一个文件,其实是命令加载表而已
[root@mysql-server-01 undrop-for-innodb]# cat dumps/default/actor_load.sql SET FOREIGN_KEY_CHECKS=0;LOAD DATA LOCAL INFILE ‘/root/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@mysql-server-01 undrop-for-innodb]#
将数据load回数据库中
现在将数据恢复到数据库中。但是,在导入数据以前,我们需要创建表actor(前提我们要有表结构备份,如果没有只有使用另外的工具找到表结构Percona Data Recovery Tool)看来还是需要两个工具结合使用啊。
root@localhost : sakila 23:03:50> source sakila/actor.sql
root@localhost : sakila 23:03:50> show create table actor\G*************************** 1. row *************************** Table: actorCreate 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=utf81 row in set (0.00 sec)root@localhost : sakila 23:04:36>
现在我们导入数据,恢复actor表
[root@mysql-server-01 undrop-for-innodb]# mysql -uroot -p123456 -S /data/mysql/user_3306/mysql.sock --local-infileWelcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 18Server version: 5.5.37-log MySQL Community Server (GPL)Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type ‘help;‘ or ‘\h‘ for help. Type ‘\c‘ to clear the current input statement.root@localhost : (none) 23:17:16> use sakilaDatabase changedroot@localhost : sakila 23:17:19> source dumps/default/actor_load.sql;Query OK, 0 rows affected (0.00 sec)Query OK, 600 rows affected (0.08 sec)Records: 400 Deleted: 200 Skipped: 0 Warnings: 0root@localhost : sakila 23:17:22>
检查恢复的数据
root@localhost : sakila 23:19:00> SELECT COUNT(*) FROM actor; +----------+| COUNT(*) |+----------+| 200 |+----------+1 row in set (0.00 sec)root@localhost : sakila 23:19:34> 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)root@localhost : sakila 23:19:37> CHECKSUM TABLE actor; +--------------+------------+| Table | Checksum |+--------------+------------+| sakila.actor | 2472295518 |+--------------+------------+1 row in set (0.00 sec)root@localhost : sakila 23:19:40>
可以发现和drop table之前完全一致。到这里数据就恢复完成啦。希望小伙伴们永远不要使用到改工具。
参考资料
https://twindb.com/recover-innodb-table-after-drop-table-innodb/