无备份情况下恢复MySQL drop table

love wife & love life --Roger 2017-08-01 96 阅读

drop MySQL table

本站文章除注明转载外，均为本站原创： 转载自 love wife & love life —Roger的Oracle&MySQL技术博客

本文链接地址: 无备份情况下恢复MySQL drop table

这里我们首先来测试innodb_file_per_table为off的情况，即表结构和数据存在同一个文件中。这里我分别测试了表存在主键和不存在主键的情况，供参考。

innodb_file_per_table参数为off(有主键的情况）

1、创建测试表

mysql> use recover;
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> create table test_drop0801(id int);
Query OK, 0 rows affected (0.02 sec)

mysql> insert into test_drop0801 values(100);
Query OK, 1 row affected (0.01 sec)

mysql> insert into test_drop0801 values(101);
Query OK, 1 row affected (0.00 sec)

mysql> insert into test_drop0801 values(102);
Query OK, 1 row affected (0.00 sec)

mysql> alter table test_drop0801 add primary key(id);
Query OK, 3 rows affected (0.02 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> explain select * from test_drop0801 where id=102;
+----+-------------+---------------+-------+---------------+---------+---------+-------+------+-------------+
| id | select_type | table        | type  | possible_keys | key    | key_len | ref  | rows | Extra      |
+----+-------------+---------------+-------+---------------+---------+---------+-------+------+-------------+
|  1 | SIMPLE      | test_drop0801 | const | PRIMARY      | PRIMARY | 4      | const |    1 | Using index |
+----+-------------+---------------+-------+---------------+---------+---------+-------+------+-------------+
 row in set (0.00 sec)


mysql> show global variables like '%file_per%';
+-----------------------+-------+
| Variable_name        | Value |
+-----------------------+-------+
| innodb_file_per_table | OFF  |
+-----------------------+-------+
 row in set (0.00 sec)

mysql> show create table test_drop0801 \G;
*************************** 1. row ***************************
      Table: test_drop0801
Create Table: CREATE TABLE `test_drop0801` (
  `id` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
 row in set (0.00 sec)

ERROR:
No query specified

2、备份表结构

[root@killdb ~]# mysqldump --opt -d -uroot -proger recover test_drop0801 > /tmp/innodb_recovery/recover/test_drop0801.sql
[root@killdb ~]#

3、删除表

mysql> drop table test_drop0801;
Query OK, 0 rows affected (0.00 sec)

4、扫描数据文件

[root@killdb innodb_recovery]# ./stream_parser -f /var/lib/mysql/ibdata1
Opening file: /var/lib/mysql/ibdata1
File information:

ID of device containing file:        64768
inode number:                      924765
protection:                        100660 (regular file)
number of hard links:                    1
user ID of owner:                      496
group ID of owner:                    491
device ID (if special file):            0
blocksize for filesystem I/O:        4096
number of blocks allocated:          69632
time of last access:            1496441095 Sat Jun  3 06:04:55 2017
time of last modification:      1496464241 Sat Jun  3 12:30:41 2017
time of last status change:    1496464241 Sat Jun  3 12:30:41 2017
total size, in bytes:            35651584 (34.000 MiB)

Size to process:                  35651584 (34.000 MiB)
All workers finished in 1 sec

5、创建用于恢复的数据字典

[root@killdb innodb_recovery]# ./recover_dictionary.sh
Generating dictionary tables dumps... OK
Creating test database ... OK
Creating dictionary tables in database test:
SYS_TABLES ... OK
SYS_COLUMNS ... OK
SYS_INDEXES ... OK
SYS_FIELDS ... OK
All OK
Loading dictionary tables data:
SYS_TABLES ... 234 recs OK
SYS_COLUMNS ... 324 recs OK
SYS_INDEXES ... 123 recs OK
SYS_FIELDS ... 248 recs OK
All OK

6、查询需要恢复表的信息

mysql> use test;
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>
mysql> select * from SYS_TABLES where name like 'recover/test_drop0801%';
+-----------------------+-----+--------+------+--------+---------+--------------+-------+
| NAME                  | ID  | N_COLS | TYPE | MIX_ID | MIX_LEN | CLUSTER_NAME | SPACE |
+-----------------------+-----+--------+------+--------+---------+--------------+-------+
| recover/test_drop0801 | 187 |      1 |    1 |      0 |      0 |              |    0 |
+-----------------------+-----+--------+------+--------+---------+--------------+-------+
 row in set (0.00 sec)

mysql> select * from SYS_INDEXES where table_id=187;
+----------+-----+---------+----------+------+-------+------------+
| TABLE_ID | ID  | NAME    | N_FIELDS | TYPE | SPACE | PAGE_NO    |
+----------+-----+---------+----------+------+-------+------------+
|      187 | 184 | PRIMARY |        1 |    3 |    0 | 4294967295 |
+----------+-----+---------+----------+------+-------+------------+
 row in set (0.00 sec)

7、确认数据page中数据是否存在

[root@killdb innodb_recovery]#  ./c_parser -6f pages-ibdata1/FIL_PAGE_INDEX/0000000000000184.page -t recover/test_drop0801.sql |head -5
Line 22: syntax error at 'DROP'
:
: DROP TABLE IF EXISTS `test_drop0801`;
Failed to parse table structure
[root@killdb innodb_recovery]#

这里的报错是因为脚本的问题，需要修改备份脚本(mysqldump产生的）.

[root@killdb innodb_recovery]#  ./c_parser -6f pages-ibdata1/FIL_PAGE_INDEX/0000000000000184.page -t recover/test_drop0801.sql |head -5
-- Page id: 562, Format: COMPACT, Records list: Valid, Expected records: (3 3)
00001517    94000001800110test_drop0801    100
00001517    9400000180011Dtest_drop0801    101
00001517    9400000180012Atest_drop0801    102
SET FOREIGN_KEY_CHECKS=0;
LOAD DATA LOCAL INFILE '/tmp/innodb_recovery/dumps/default/test_drop0801' REPLACE INTO TABLE `test_drop0801` FIELDS TERMINATED BY '\t' OPTIONALLY ENCLOSED BY '"' LINES STARTING BY 'test_drop0801\t' (`id`);
-- Page id: 562, Found records: 3, Lost records: NO, Leaf page: YES
[root@killdb innodb_recovery]#

8、抽取page中的数据

[root@killdb innodb_recovery]#  ./c_parser -6f pages-ibdata1/FIL_PAGE_INDEX/0000000000000184.page -t recover/test_drop0801.sql > dumps/default/test_drop0801 2> dumps/default/test_drop0801_load.sql          
[root@killdb innodb_recovery]#
[root@killdb innodb_recovery]# ls -ltr dumps/default/test_drop0801*
-rw-r--r--. 1 root root 232 Jun  3 12:34 dumps/default/test_drop0801_load.sql
-rw-r--r--. 1 root root 285 Jun  3 12:34 dumps/default/test_drop0801

9、加载数据到数据库

mysql> use recover
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 recover/test_drop0801.sql
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.02 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)


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

Query OK, 3 rows affected (0.00 sec)
Records: 3  Deleted: 0  Skipped: 0  Warnings: 0

mysql> select * from test_drop0801;
+-----+
| id  |
+-----+
| 100 |
| 101 |
| 102 |
+-----+
 rows in set (0.00 sec)

mysql>

我们可以看到，顺利完成了drop table的恢复，而且数据完好无损。实际上我这里还同时测试了无主键的情况，经过测试都类似，可以进行完美的恢复。这里不再累述。

drop MySQL table

作者：love wife & love life --Roger