mysql idb 恢复_MYSQL_误删innodb数据库，进行恢复独立表空间数据

参考链接：http://blog.sina.com.cn/s/blog_8317516b0102xfkb.html

--误删innodb数据库，进行恢复数据(使用innodb_per_file参数，所有数据存在独立表空间中)

[root@localhost][(none)]> show databases;

+--------------------+

| Database  |

+--------------------+

| information_schema |

| mysql  |

| performance_schema |

| sun  |

| sys  |

+--------------------+

5 rows in set (0.00 sec)

[root@localhost][(none)]> drop database sun;

Query OK, 1 row affected (0.06 sec)

--停止应用，备份DATADIR下文件。

1.

由于使用独立表空间模式，删除数据库后，对应的sun目录及里面的文件也会别删除，所以第一步做的就是进行ibd文件的恢复工作

参考http://blog.sina.com.cn/s/blog_8317516b0101d0rn.html

# umount /u01

[root@gridone extundelete-0.2.4]# yum install

e2fsprogs-devel

[root@gridone src]# ./extundelete --inode 2

/dev/sdb1

NOTICE: Extended attributes are not restored.

Loading filesystem metadata ... 240 groups loaded.

Group: 0

File name  | Inode number | Deleted

status

.  2

..  2

lost+found  11

app  524289

soft  1179649

mysql  131073

xtrabackup  393217

ogg  1835288  Deleted

mysqlhome_renamed  1050246  Deleted

./extundelete  /dev/sdb1

--restore-directory

'/u01/mysql/sun'  --未成功

./extundelete  /dev/sdb1

--restore-files

'/u01/mysql/sun/tab1.idb'

--未成功

./extundelete  /dev/sdb1

--restore-all  --成功

[root@gridone sun]#  pwd

/ogg/recovery/extundelete-0.2.4/src/RECOVERED_FILES/mysql/data57/sun

[root@gridone sun]# ls -l

total 208

-rw-r--r-- 1 root root  61

Mar 21 23:45 db.opt

-rw-r--r-- 1 root root 98304 Mar 21 23:45

#sql-ib44-3363294689.ibd

-rw-r--r-- 1 root root  8586 Mar 21 23:45

tab1.frm

-rw-r--r-- 1 root root 98304 Mar 21 23:45 tab1.ibd

2.Splitting ibdata1

分离ibd文件，创建目录pages-ibdata1，按照pages的类型分离出FIL_PAGE_INDEX or

FIL_PAGE_TYPE_BLOB)(解析数据字典)

INNODB数据字典存在在IBDATA1中，所以我们需要先进行编译然后从字典中获取存储信息的页，使用程序stream_parser

(The InnoDB dictionary is stored in ibdata1. So we need to

parse it and get pages that store records of the dictionary.

stream_parser does it.)

[root@gridone undrop-for-innodb-master]# ./stream_parser -f

/ogg/recovery/extundelete-0.2.4/src/RECOVERED_FILES/mysql/data57/sun/tab1.ibd

Opening file:

/ogg/recovery/extundelete-0.2.4/src/RECOVERED_FILES/mysql/data57/sun/tab1.ibd

File information:

ID of device containing file:  2145

inode number:  289

protection:  100644 (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:  192

time of last access:  1521647107 Wed Mar 21 23:45:07 2018

time of last modification:  1521647107 Wed Mar 21 23:45:07

2018

time of last status change:  1521647107 Wed Mar 21 23:45:07 2018

total size, in bytes:  98304

(96.000 kiB)

Size to process:  98304 (96.000 kiB)

All workers finished in 0 sec

3.

想办法找到建表的结构，并通过程序恢复数据，直接加载到数据库

[root@gridone undrop-for-innodb-master]# vi tab482.sql

PRIMARY KEY (`id`)

CREATE TABLE `tab1` (

`id` int(11) NOT NULL,

`name` varchar(32) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

[root@gridone undrop-for-innodb-master]# ./c_parser -6f

pages-tab1.ibd/FIL_PAGE_INDEX/0000000000000048.page -t tab482.sql

> dumps/default/tab482 2> load_cmd482.sql

[root@gridone undrop-for-innodb-master]# cat

load_cmd482.sql

SET FOREIGN_KEY_CHECKS=0;

LOAD DATA LOCAL INFILE

'/u01/soft/undrop-for-innodb-master/dumps/default/tab482' REPLACE

INTO TABLE `tab1` FIELDS TERMINATED BY '\t' OPTIONALLY ENCLOSED BY

'"' LINES STARTING BY 'tab1\t' (`id`, `name`);

[root@gridone undrop-for-innodb-master]# cat

/u01/soft/undrop-for-innodb-master/dumps/default/tab482

-- Page id: 3, Format: COMPACT, Records list: Valid, Expected

records: (3 3)

000000000400  00000000150F

AC000001200110

tab1  1

"sun"

000000000401  00000000150F

AC00000120011E

tab1  2

"qi"

000000000402  00000000150F

AC00000120012C

tab1  3

"cheng"

-- Page id: 3, Found records: 3, Lost records: NO, Leaf page:

YES

--恢复完成

--恢复工具安装(https://github.com/chhabhaiya/undrop-for-innodb)

unzip undrop-for-innodb-master.zip

yum -y install flex

yum -y install bison

/u01/soft/undrop-for-innodb-master/make