Email: service@parnassusdata.com 7 x 24 online support!
Undrop MySQL InnoDB 中恢复被drop的表，当 innodb_file_per_table=on时
我们介绍了在innodb_file_per_table 设为OFF，意外删除了表时，使用恢复工具包进行恢复的情况。
在这篇文章中，我们将展示在innodb_file_per_table 开启的情况下如何恢复 MySQL 表或数据库。假设mysql 服务器的设置为innodb_file_per_table=ON，这个参数告诉InnoDB 将用户表储存在单独的数据文件中。
在恢复测试中我们使用与之前文章中相同的数据库sakila。
root@test:/var/lib/mysql/sakila# ll total 23468 drwx------ 2 mysql mysql 4096 Jul 15 04:26 ./ drwx------ 6 mysql mysql 4096 Jul 15 04:26 ../ -rw-rw---- 1 mysql mysql 8694 Jul 15 04:26 actor.frm -rw-rw---- 1 mysql mysql 114688 Jul 15 04:26 actor.ibd -rw-rw---- 1 mysql mysql 2871 Jul 15 04:26 actor_info.frm -rw-rw---- 1 mysql mysql 8840 Jul 15 04:26 address.frm -rw-rw---- 1 mysql mysql 163840 Jul 15 04:26 address.ibd -rw-rw---- 1 mysql mysql 8648 Jul 15 04:26 category.frm -rw-rw---- 1 mysql mysql 98304 Jul 15 04:26 category.ibd -rw-rw---- 1 mysql mysql 8682 Jul 15 04:26 city.frm -rw-rw---- 1 mysql mysql 114688 Jul 15 04:26 city.ibd -rw-rw---- 1 mysql mysql 8652 Jul 15 04:26 country.frm -rw-rw---- 1 mysql mysql 98304 Jul 15 04:26 country.ibd ... -rw-rw---- 1 mysql mysql 36 Jul 15 04:26 upd_film.TRN root@test:/var/lib/mysql/sakila#
注意与表country相关的两个文件： country.frm, country.ibd。
我们将删除这个表并尝试恢复它。首先，我们进行校验，预览在这个表中包含的记录：
Database changed mysql> SELECT * FROM country LIMIT 10; +------------+----------------+---------------------+ | country_id | country | last_update | +------------+----------------+---------------------+ | 1 | Afghanistan | 2006-02-15 04:44:00 | | 2 | Algeria | 2006-02-15 04:44:00 | | 3 | American Samoa | 2006-02-15 04:44:00 | | 4 | Angola | 2006-02-15 04:44:00 | | 5 | Anguilla | 2006-02-15 04:44:00 | | 6 | Argentina | 2006-02-15 04:44:00 | | 7 | Armenia | 2006-02-15 04:44:00 | | 8 | Australia | 2006-02-15 04:44:00 | | 9 | Austria | 2006-02-15 04:44:00 | | 10 | Azerbaijan | 2006-02-15 04:44:00 | +------------+----------------+---------------------+ 10 rows in set (0.00 sec) mysql> CHECKSUM TABLE country; +----------------+------------+ | Table | Checksum | +----------------+------------+ | sakila.country | 3658016321 | +----------------+------------+ 1 row in set (0.00 sec) mysql> SELECT COUNT(*) FROM country; +----------+ | COUNT(*) | +----------+ | 109 | +----------+ 1 row in set (0.00 sec) mysql>
意外删除
现在我们删除表并查找表相关的文件。你能从列表中看到，有 country 表数据的文件丢失了：
mysql> SET foreign_key_checks=OFF; Query OK, 0 rows affected (0.00 sec) mysql> DROP TABLE country; Query OK, 0 rows affected (0.00 sec) mysql> mysql> exit Bye root@test:~# cd /var/lib/mysql/sakila/ root@test:/var/lib/mysql/sakila# ll total 23360 drwx------ 2 mysql mysql 4096 Jul 15 04:33 ./ drwx------ 6 mysql mysql 4096 Jul 15 04:26 ../ -rw-rw---- 1 mysql mysql 8694 Jul 15 04:26 actor.frm -rw-rw---- 1 mysql mysql 114688 Jul 15 04:26 actor.ibd -rw-rw---- 1 mysql mysql 2871 Jul 15 04:26 actor_info.frm -rw-rw---- 1 mysql mysql 8840 Jul 15 04:26 address.frm -rw-rw---- 1 mysql mysql 163840 Jul 15 04:26 address.ibd -rw-rw---- 1 mysql mysql 8648 Jul 15 04:26 category.frm -rw-rw---- 1 mysql mysql 98304 Jul 15 04:26 category.ibd -rw-rw---- 1 mysql mysql 8682 Jul 15 04:26 city.frm -rw-rw---- 1 mysql mysql 114688 Jul 15 04:26 city.ibd -rw-rw---- 1 mysql mysql 40 Jul 15 04:26 customer_create_date.TRN -rw-rw---- 1 mysql mysql 8890 Jul 15 04:26 customer.frm -rw-rw---- 1 mysql mysql 196608 Jul 15 04:26 customer.ibd -rw-rw---- 1 mysql mysql 1900 Jul 15 04:26 customer_list.frm -rw-rw---- 1 mysql mysql 297 Jul 15 04:26 customer.TRG -rw-rw---- 1 mysql mysql 65 Jul 15 04:26 db.opt ... -rw-rw---- 1 mysql mysql 36 Jul 15 04:26 upd_film.TRN root@ALtestTwinDB:/var/lib/mysql/sakila#
DROP TABLE后的恢复
因为我们需要恢复已删除的文件。如果数据库服务器与磁盘上的数据再有写入，有可能被删除的文件会被其他数据重写。因此，停止服务器并挂载该分区只读是很重要的。但在测试中我们将只停止mysql服务，并继续恢复。
root@test:/var/lib/mysql/sakila# service mysql stop mysql stop/waiting
尽管用户数据被存储各表独立的文件中，数据字典仍然存储在ibdata1文件中。这是我们要对/var/lib/mysql/ibdata1使用 stream_parser 的原因。
为了找到 country表的table_id 和 index_id ，我们要使用储存在SYS_TABLES 和 SYS_INDEXES 中的字典。我们将从ibdata1 文件中获取数据。数据字典总是REDUNDANT 格式，因此我们指定选项 -4。假设mysql服务器已将更改刷到磁盘，所以我们添加选项 -D ，表示“查找已删除的记录”。 SYS_TABLES 信息储存在index_id=1 的文件中，即文件页-ibdata1/FIL_PAGE_INDEX./0000000000000001.page：
root@test:~/undrop-for-innodb# ./c_parser -4Df ./pages-ibdata1/FIL_PAGE_INDEX/0000000000000001.page -t ./dictionary/SYS_TABLES.sql | grep country 000000000CDC 62000001960684 SYS_TABLES "sakila/country" 228 3 1 0 0 "" 88 000000000CDC 62000001960684 SYS_TABLES "sakila/country" 228 3 1 0 0 "" 88 SET FOREIGN_KEY_CHECKS=0; LOAD DATA LOCAL INFILE '/home/asterix/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@test:~/undrop-for-innodb#
我们能看到country table 表有table_id=228。下一步，我们将查找表country的PRIMARY索引。为此，我们从文件0000000000000003.page (SYS_INDEXES 表包含table_id 和 index_id之间的映射)获取SYS_INDEXES 表的记录。SYS_INDEXES 结构通过-t选项被添加到工具。
root@test:~/undrop-for-innodb# ./c_parser -4Df ./pages-ibdata1/FIL_PAGE_INDEX/0000000000000003.page -t ./dictionary/SYS_INDEXES.sql | grep 228 000000000CDC 620000019605A8 SYS_INDEXES 228 547 "PRIMARY" 1 3 88 4294967295 000000000CDC 620000019605A8 SYS_INDEXES 228 547 "PRIMARY" 1 3 88 4294967295 SET FOREIGN_KEY_CHECKS=0; LOAD DATA LOCAL INFILE '/home/asterix/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@test:~/undrop-for-innodb#
我们能看到已删除表country的 index_id 是547。以下步骤与之前在 innodb_file_per_table=OFF的情况不同。由于没有可用数据的文件，我们要扫描所有存储设备作为裸设备并查找符合数据库页预期结构的数据。顺便收一下，这个方法能应用于数据文件损坏的情况。如果一些数据被损坏，恢复工具能进行部分数据恢复。在工具参数中，我们指定设备名称和设备尺寸（可以是大概的）。
root@test:~/undrop-for-innodb#./stream_parser -f /dev/vda -t 20000000k Opening file: /dev/vda File information: ID of device containing file: 5 inode number: 6411 protection: 60660 (block device) number of hard links: 1 user ID of owner: 0 group ID of owner: 6 device ID (if special file): 64768 blocksize for filesystem I/O: 4096 number of blocks allocated: 0 time of last access: 1405411377 Tue Jul 15 04:02:57 2014 time of last modification: 1404625158 Sun Jul 6 01:39:18 2014 time of last status change: 1404625158 Sun Jul 6 01:39:18 2014 total size, in bytes: 0 (0.000 exp(+0)) Size to process: 20480000000 (19.073 GiB) Worker(0): 1.06% done. 2014-07-15 04:57:37 ETA(in 00:01:36). Processing speed: 199.848 MiB/sec Worker(0): 2.09% done. 2014-07-15 04:57:37 ETA(in 00:01:35). Processing speed: 199.610 MiB/sec Worker(0): 3.11% done. 2014-07-15 04:59:13 ETA(in 00:03:09). Processing speed: 99.805 MiB/sec ... Worker(0): 97.33% done. 2014-07-15 04:57:15 ETA(in 00:00:05). Processing speed: 99.828 MiB/sec Worker(0): 98.35% done. 2014-07-15 04:57:20 ETA(in 00:00:06). Processing speed: 49.941 MiB/sec Worker(0): 99.38% done. 2014-07-15 04:57:17 ETA(in 00:00:01). Processing speed: 99.961 MiB/sec All workers finished in 77 sec root@test:~/undrop-for-innodb#
流解析器将结果文件储存在文件夹页 pages-vda (根据设备标题命名)。我们能看到必要索引显示在文件中。
root@test:~/undrop-for-innodb/pages-vda/FIL_PAGE_INDEX# ll | grep 547 -rw-r--r-- 1 root root 32768 Jul 15 04:57 0000000000000547.page root@test:~/undrop-for-innodb/pages-vda/FIL_PAGE_INDEX#
我们来查找在文件0000000000000547.page中的数据。工具 c_parser 根据预期表结构，通过-t选项为我们提供信息。utility c parser provide us information according to expected table structure, supplied with -t option.
root@test:~/undrop-for-innodb# ./c_parser -6f pages-vda/FIL_PAGE_INDEX/0000000000000547.page -t sakila/country.sql | head -5 -- Page id: 3, Format: COMPACT, Records list: Valid, Expected records: (109 109) 000000000C4B F30000038C0110 country 1 "Afghanistan" "2006-02-15 04:44:00" 000000000C4B F30000038C011B country 2 "Algeria" "2006-02-15 04:44:00" 000000000C4B F30000038C0126 country 3 "American Samoa" "2006-02-15 04:44:00" 000000000C4B F30000038C0131 country 4 "Angola" "2006-02-15 04:44:00" root@test:~/undrop-for-innodb#
结果看起来有效，所以我们要准备加载数据回数据库的文件。附带必要参数的LOAD DATA INFILE命令被发送到标准错误设备。
root@test:~/undrop-for-innodb# ./c_parser -6f pages-vda/FIL_PAGE_INDEX/0000000000000547.page -t sakila/country.sql > dumps/default/country 2> dumps/default/country_load.sql root@test:
将数据加载回数据库
我们要将数据加载到数据库中。在加载数据之前，我们创建表country的空结构：
root@test:~/undrop-for-innodb# service mysql start
mysql start/running, process 31035
root@test:~/undrop-for-innodb# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 37
Server version: 5.5.37-0ubuntu0.14.04.1 (Ubuntu)
Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
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 sakila/country.sql
Query OK, 0 rows affected (0.00 sec)
...
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> show create table country\G
*************************** 1. row ***************************
Table: country
Create Table: CREATE TABLE `country` (
`country_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
`country` varchar(50) NOT NULL,
`last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`country_id`)
) ENGINE=InnoDB AUTO_INCREMENT=110 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
mysql>
现在我们加载数据本身。
root@testB:~/undrop-for-innodb# mysql --local-infile -uroot -p Enter password: ... 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/country_load.sql Query OK, 0 rows affected (0.00 sec) Query OK, 327 rows affected (0.00 sec) Records: 218 Deleted: 109 Skipped: 0 Warnings: 0 mysql>
检查数据质量
剩下的最后一件事就是检查被恢复数据的质量。我们将预览一些记录，计算出记录和校验的总数。
mysql> SELECT COUNT(*) FROM country; +----------+ | COUNT(*) | +----------+ | 109 | +----------+ 1 row in set (0.00 sec) mysql> SELECT * FROM country LIMIT 5; +------------+----------------+---------------------+ | country_id | country | last_update | +------------+----------------+---------------------+ | 1 | Afghanistan | 2006-02-15 04:44:00 | | 2 | Algeria | 2006-02-15 04:44:00 | | 3 | American Samoa | 2006-02-15 04:44:00 | | 4 | Angola | 2006-02-15 04:44:00 | | 5 | Anguilla | 2006-02-15 04:44:00 | +------------+----------------+---------------------+ 5 rows in set (0.00 sec) mysql> CHECKSUM TABLE country; +----------------+------------+ | Table | Checksum | +----------------+------------+ | sakila.country | 3658016321 | +----------------+------------+ 1 row in set (0.00 sec) mysql>
我们很幸运。尽管我们对mysql数据了使用系统卷（不建议的操作），并且我们没有重新载入分区作为只读（其他操作继续写入磁盘），我们还是成功恢复了所有记录。计算出的恢复后检验 (3658016321) 等于删除前的检验(3658016321)。
Copyright © 2013, 2023, parnassusdata.com. 版权所有。dbrecover.com
沪公网安备 31010802001377号