人为错误是不可避免的。错误的 “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，与意外删除表之前的检验相等。因此，我们能确认数据被正确恢复了。
在下一篇文章中会讲到其他恢复的情况。
Comment