无备份情况下恢复MySQL drop table
本站文章除注明转载外，均为本站原创： 转载自love wife love life —Roger的Oracle/MySQL/PostgreSQL数据恢复博客
本文链接地址: 无备份情况下恢复MySQL drop table
这里我们首先来测试innodb_file_per_table为off的情况，即表结构和数据存在同一个文件中。这里我分别测试了表存在主键和不存在主键的情况，供参考。
innodb_file_per_table参数为off(有主键的情况）
1、创建测试表
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 |
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 | +----+-------------+---------------+-------+---------------+---------+---------+-------+------+-------------+ 1 row in set (0.00 sec) mysql> show global variables like '%file_per%'; +-----------------------+-------+ | Variable_name | Value | +-----------------------+-------+ | innodb_file_per_table | OFF | +-----------------------+-------+ 1 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 1 row in set (0.00 sec) ERROR: No query specified |
2、备份表结构
|
1 2 |
[root@killdb ~]# mysqldump --opt -d -uroot -proger recover test_drop0801 > /tmp/innodb_recovery/recover/test_drop0801.sql [root@killdb ~]# |
3、删除表
|
1 2 |
mysql> drop table test_drop0801; Query OK, 0 rows affected (0.00 sec) |
4、扫描数据文件
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
[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、创建用于恢复的数据字典
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
[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、查询需要恢复表的信息
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
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 | +-----------------------+-----+--------+------+--------+---------+--------------+-------+ 1 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 | +----------+-----+---------+----------+------+-------+------------+ 1 row in set (0.00 sec) |
7、确认数据page中数据是否存在
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
[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' 21: 22: 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) 000000001517 94000001800110test_drop0801 100 000000001517 9400000180011Dtest_drop0801 101 000000001517 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中的数据
|
1 2 3 4 5 |
[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、加载数据到数据库
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 |
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 | +-----+ 3 rows in set (0.00 sec) mysql> |
我们可以看到，顺利完成了drop table的恢复，而且数据完好无损。实际上我这里还同时测试了无主键的情况，经过测试都类似，可以进行完美的恢复。这里不再累述。
One Response to “无备份情况下恢复MySQL drop table”
感受学习的力量！
Leave a Reply
You must be logged in to post a comment.