对于innodb_file_per_table=off时，innodb引擎，误删除表的恢复

对于innodb_file_per_table=off时，innodb引擎，误删除表的恢复。


场景模拟：
mysql> show databases; 
+--------------------+ 
| Database | 
+--------------------+ 
| information_schema | 
| d1 | 
| mysql | 
| performance_schema | 
| song1 | 
| song1_recover | 
| t | 
| test | 
| ttt | 
+--------------------+ 
9 rows in set (0.00 sec) 


mysql> create database db_song; 
Query OK, 1 row affected (0.01 sec) 


mysql> use db_song; 
Database changed 
mysql> create table t1(id int); 
Query OK, 0 rows affected (0.13 sec) 


mysql> create table t2(id int primary key ,name varchar(10)); 
Query OK, 0 rows affected (0.04 sec) 


mysql> insert into t1 values (1),(2),(3); 
Query OK, 3 rows affected (0.00 sec) 
Records: 3 Duplicates: 0 Warnings: 0 


mysql> insert into t2 values (1,'song'),(2,'zhi'),(3,'qinag'); 
Query OK, 3 rows affected (0.01 sec) 
Records: 3 Duplicates: 0 Warnings: 0 


mysql> show variables like '%per%'; 
+---------------------------------------------------+---------+ 
| Variable_name | Value | 
+---------------------------------------------------+---------+ 
| innodb_file_per_table | OFF 


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


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




把ibdata1文件放到/tmp目录后
[root@10-4-1-104 ]# ./stream_parser -f /tmp/ibdata1 
Opening file: /tmp/ibdata1 
File information: 


ID of device containing file: 64513 
inode number: 655707 
protection: 100640 (regular file) 
number of hard links: 1 
user ID of owner: 0 
group ID of owner: 0 
device ID (if special file): 0 
blocksize for filesystem I/O: 4096 
number of blocks allocated: 204800 
Opening file: /tmp/ibdata1 
File information: 


ID of device containing file: 64513 
inode number: 655707 
protection: 100640 (regular file) 
number of hard links: 1 
user ID of owner: 0 
group ID of owner: 0 
device ID (if special file): 0 
blocksize for filesystem I/O: 4096 
number of blocks allocated: 204800 
time of last access: 1437100833 Fri Jul 17 10:40:33 2015 
time of last modification: 1437100833 Fri Jul 17 10:40:33 2015 
time of last status change: 1437100833 Fri Jul 17 10:40:33 2015 
Opening file: /tmp/ibdata1 
total size, in bytes: 104857600 (100.000 MiB) 


File information: 


time of last access: 1437100833 Fri Jul 17 10:40:33 2015 
Opening file: /tmp/ibdata1 
ID of device containing file: 64513 
Size to process: 104857600 (100.000 MiB) 
inode number: 655707 
File information: 


protection: 100640 (regular file) 
number of hard links: 1 
user ID of owner: 0 
ID of device containing file: 64513 
time of last modification: 1437100833 Fri Jul 17 10:40:33 2015 
inode number: 655707 
protection: 100640 group ID of owner: 0 
(regular file) 
device ID (if special file): 0 
number of hard links: 1 
blocksize for filesystem I/O: 4096 
user ID of owner: 0 
number of blocks allocated: 204800 
group ID of owner: 0 
device ID (if special file): 0 
blocksize for filesystem I/O: 4096 
time of last status change: 1437100833 Fri Jul 17 10:40:33 2015 
number of blocks allocated: 204800 
total size, in bytes: 104857600 (100.000 MiB) 


Size to process: 104857600 (100.000 MiB) 
time of last access: 1437100833 Fri Jul 17 10:40:33 2015 
time of last access: 1437100833 Fri Jul 17 10:40:33 2015 
time of last modification: 1437100833 Fri Jul 17 10:40:33 2015 
time of last modification: 1437100833 Fri Jul 17 10:40:33 2015 
time of last status change: 1437100833 Fri Jul 17 10:40:33 2015 
total size, in bytes: 104857600 (100.000 MiB) 


time of last status change: 1437100833 Fri Jul 17 10:40:33 2015 
Size to process: 104857600 (100.000 MiB) 
total size, in bytes: 104857600 (100.000 MiB) 


Size to process: 104857600 (100.000 MiB) 
All workers finished in 0 sec 




先获得表的定义语句： 
[root@10-4-1-104 ]# ./c_parser -4Df pages-ibdata1/FIL_PAGE_INDEX/0000000000000001.page \ 
> -t dictionary/SYS_TABLES.sql \ 
> > dumps/default/SYS_TABLES \ 
> 2> dumps/default/SYS_TABLES.sql 
[root@10-4-1-104 ]# ./c_parser -4Df pages-ibdata1/FIL_PAGE_INDEX/0000000000000003.page \ 
> -t dictionary/SYS_INDEXES.sql \ 
> > dumps/default/SYS_INDEXES \ 
> 2> dumps/default/SYS_INDEXES.sql 
[root@10-4-1-104 ]# ./c_parser -4Df pages-ibdata1/FIL_PAGE_INDEX/0000000000000002.page \ 
> -t dictionary/SYS_COLUMNS.sql \ 
> > dumps/default/SYS_COLUMNS \ 
> 2> dumps/default/SYS_COLUMNS.sql 
[root@10-4-1-104 ]# ./c_parser -4Df pages-ibdata1/FIL_PAGE_INDEX/0000000000000004.page \ 
> -t dictionary/SYS_FIELDS.sql \ 
> > dumps/default/SYS_FIELDS \ 
> 2> dumps/default/SYS_FIELDS.sql 
[root@10-4-1-104 ]# 
[root@10-4-1-104 ]# 




[root@10-4-1-104 ]# cat dictionary/SYS_* | mysql -u root -ptest -S /data/mysqld.sock -D db_song_recover 
[root@10-4-1-104 ]# cat dumps/default/*.sql | mysql -u root -ptest -S /data/mysqld.sock -D db_song_recover --local-infile=1 
[root@10-4-1-104 ]# make sys_parser 
/opt/udb/program/mysql/mysql-5.5.24/bin/mysql_config 
cc `mysql_config --cflags` `mysql_config --libs` -o sys_parser sys_parser.c 
[root@10-4-1-104 ]# ./sys_parser -u root -ptest -h10.4.1.104 -d db_song_recover db_song/t1 
CREATE TABLE `t1`( 
WARNING: Fields are not found for table 'db_song/t1' in SYS_FIELDS 
`id` INT) ENGINE=InnoDB; 
[root@10-4-1-104 ]# ./sys_parser -u root -ptest -h10.4.1.104 -d db_song_recover db_song/t2 
CREATE TABLE `t2`( 
`id` INT NOT NULL, 
`name` VARCHAR(10) CHARACTER SET 'utf8' COLLATE 'utf8_general_ci', 
PRIMARY KEY (`id`) 
) ENGINE=InnoDB; 


存放表定义： 
[root@10-4-1-104 ]# mkdir db_song 
[root@10-4-1-104 ]# cd db_song 
[root@10-4-1-104 db_song]# cat t1.sql 
CREATE TABLE `t1`( 
`id` INT) ENGINE=InnoDB; 
[root@10-4-1-104 db_song]# cat t2.sql 
CREATE TABLE `t2`( 
`id` INT NOT NULL, 
`name` VARCHAR(10) CHARACTER SET 'utf8' COLLATE 'utf8_general_ci', 
PRIMARY KEY (`id`) 
) ENGINE=InnoDB; 
[root@10-4-1-104 db_song]# pwd 
/tmp//db_song 




先还原 t1表数据： 


[root@10-4-1-104 ]# ./c_parser -4Df pages-ibdata1/FIL_PAGE_INDEX/0000000000000001.page -t dictionary/SYS_TABLES.sql | grep t1 
00000005553D 3800000D0E0CB3 SYS_TABLES "db\_song/t1" 236 1 1 0 0 "" 0 
00000005553D 3800000D0E0CB3 SYS_TABLES "db\_song/t1" 236 1 1 0 0 "" 0 
SET FOREIGN_KEY_CHECKS=0; 
LOAD DATA LOCAL INFILE '/tmp//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`); 
00000005553D 3800000D0E0CB3 SYS_TABLES "db\_song/t1" 236 1 1 0 0 "" 0 
[root@10-4-1-104 ]# ./c_parser -4Df pages-ibdata1/FIL_PAGE_INDEX/0000000000000003.page -t dictionary/SYS_INDEXES.sql | grep 236 
00000005553D 3800000D0E0C41 SYS_INDEXES 236 431 "GEN\_CLUST\_INDEX" 0 1 0 4294967295 
SET FOREIGN_KEY_CHECKS=0; 
LOAD DATA LOCAL INFILE '/tmp//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`); 
00000005553D 3800000D0E0C41 SYS_INDEXES 236 431 "GEN\_CLUST\_INDEX" 0 1 0 4294967295 
[root@10-4-1-104 ]# ./c_parser -6f pages-ibdata1/FIL_PAGE_INDEX/0000000000000431.page -t db_song/t1.sql 
-- Page id: 600, Format: COMPACT, Records list: Valid, Expected records: (3 3) 
000000000803 00000005553B B6000001DC0110 t1 1 
000000000804 00000005553B B6000001DC011F t1 2 
000000000805 00000005553B B6000001DC012E t1 3 
SET FOREIGN_KEY_CHECKS=0; 
LOAD DATA LOCAL INFILE '/tmp//dumps/default/t1' REPLACE INTO TABLE `t1` FIELDS TERMINATED BY '\t' OPTIONALLY ENCLOSED BY '"' LINES STARTING BY 't1\t' (`id`); 
-- Page id: 600, Found records: 3, Lost records: NO, Leaf page: YES 
[root@10-4-1-104 ]# ./c_parser -6f pages-ibdata1/FIL_PAGE_INDEX/0000000000000431.page -t db_song/t1.sql > dumps/default/t1 2> dumps/default/t1_load.sql 




mysql> use db_song_recover 
Database changed 
mysql> show tables; 
Empty set (0.00 sec) 


mysql> CREATE TABLE `t1`( 
-> `id` INT) ENGINE=InnoDB; 
Query OK, 0 rows affected (0.03 sec) 


mysql> CREATE TABLE `t2`( --顺手把t2也建上，一会就不会再建了 
-> `id` INT NOT NULL, 
-> `name` VARCHAR(10) CHARACTER SET 'utf8' COLLATE 'utf8_general_ci', 
-> PRIMARY KEY (`id`) 
-> ) ENGINE=InnoDB; 
Query OK, 0 rows affected (0.04 sec) 


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


ERROR 1148 (42000): The used command is not allowed with this MySQL version 
mysql> exit 
Bye 
[root@10-4-1-104 ~]# mysql -u root -ptest -S /data/mysqld.sock --local-infile=1 
Welcome to the MySQL monitor. Commands end with ; or \g. 
Your MySQL connection id is 10 
Server version: 5.5.24-ucloudrel1-log Source distribution 


Copyright (c) 2000, 2011, 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> source /tmp//dumps/default/t1_load.sql 
Query OK, 0 rows affected (0.00 sec) 


ERROR 1046 (3D000): No database selected 
mysql> use db_song_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 /tmp//dumps/default/t1_load.sql 
Query OK, 0 rows affected (0.00 sec) 


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


mysql> select * from t1; --t1表数据恢复了 
+------+ 
| id | 
+------+ 
| 1 | 
| 2 | 
| 3 | 
+------+ 
3 rows in set (0.00 sec) 




再恢复 t2： 


[root@10-4-1-104 ]# ./c_parser -4Df pages-ibdata1/FIL_PAGE_INDEX/0000000000000001.page -t dictionary/SYS_TABLES.sql | grep t2 
00000005553F 3900000D100D1D SYS_TABLES "db\_song/t2" 237 2 1 0 0 "" 0 
00000005553F 3900000D100D1D SYS_TABLES "db\_song/t2" 237 2 1 0 0 "" 0 
SET FOREIGN_KEY_CHECKS=0; 
LOAD DATA LOCAL INFILE '/tmp//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`); 
00000005553F 3900000D100D1D SYS_TABLES "db\_song/t2" 237 2 1 0 0 "" 0 
[root@10-4-1-104 ]# ./c_parser -4Df pages-ibdata1/FIL_PAGE_INDEX/0000000000000003.page -t dictionary/SYS_INDEXES.sql | grep 237 
00000005553F 3900000D100C76 SYS_INDEXES 237 432 "PRIMARY" 1 3 0 4294967295 
SET FOREIGN_KEY_CHECKS=0; 
LOAD DATA LOCAL INFILE '/tmp//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`); 
00000005553F 3900000D100C76 SYS_INDEXES 237 432 "PRIMARY" 1 3 0 4294967295 
[root@10-4-1-104 ]# ./c_parser -6f pages-ibdata1/FIL_PAGE_INDEX/0000000000000432.page -t db_song/t2.sql 
-- Page id: 615, Format: COMPACT, Records list: Valid, Expected records: (3 3) 
00000005553C B7000001DE0110 t2 1 "song" 
00000005553C B7000001DE011D t2 2 "zhi" 
00000005553C B7000001DE012A t2 3 "qinag" 
-- Page id: 615, Found records: 3, Lost records: NO, Leaf page: YES 
-- Page id: 615, Format: COMPACT, Records list: Valid, Expected records: (0 0) 
-- Page id: 615, Found records: 0, Lost records: NO, Leaf page: YES 
-- Page id: 615, Format: COMPACT, Records list: Valid, Expected records: (3 3) 
00000005553C B7000001DE0110 t2 1 "song" 
00000005553C B7000001DE011D t2 2 "zhi" 
00000005553C B7000001DE012A t2 3 "qinag" 
SET FOREIGN_KEY_CHECKS=0; 
LOAD DATA LOCAL INFILE '/tmp//dumps/default/t2' REPLACE INTO TABLE `t2` FIELDS TERMINATED BY '\t' OPTIONALLY ENCLOSED BY '"' LINES STARTING BY 't2\t' (`id`, `name`); 
-- Page id: 615, Found records: 3, Lost records: NO, Leaf page: YES 
[root@10-4-1-104 ]# ./c_parser -6f pages-ibdata1/FIL_PAGE_INDEX/0000000000000432.page -t db_song/t2.sql > dumps/default/t2 2> dumps/default/t2_load.sql 


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


Query OK, 6 rows affected (0.01 sec) 
Records: 6 Deleted: 0 Skipped: 0 Warnings: 0 


mysql> select * from t2; --t2表的数据也恢复了 
+----+-------+ 
| id | name | 
+----+-------+ 
| 1 | song | 
| 2 | zhi | 
| 3 | qinag | 
+----+-------+ 
3 rows in set (0.00 sec) 


转载请注明源出处
QQ 273002188  欢迎一起学习

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/25099483/viewspace-1736046/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/25099483/viewspace-1736046/