未删除的表结构从ibdata1文件进行恢复

上节说到对于删除的表结构进行恢复，这次讲对于未删除的表结构进行恢复：
http://blog.sina.com.cn/s/blog_5037eacb0102vq2n.html 




如果因为一些原因，库打不开了，使用recover参数为6同样也打不开。
这时只有.ibdata文件可以用了，可以在Ibdata文件中抽出表的定义语句


场景：
create database song1; 
create table song1(id int,name varchar(10)); 
insert into song1 values (8,'song'); 


关闭数据库，把ibdata1文件拷贝到安装了undrop-for-innodb机器的/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: 658133 
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 
Opening file: /tmp/ibdata1 
number of blocks allocated: 204800 
File information: 


ID of device containing file: 64513 
inode number: 658133 
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: 1436975508 Wed Jul 15 23:51:48 2015 
time of last access: 1436975508 Wed Jul 15 23:51:48 2015 
time of last modification: 1436975508 Wed Jul 15 23:51:48 2015 
Opening file: /tmp/ibdata1 
time of last status change: 1436975508 Wed Jul 15 23:51:48 2015 
total size, in bytes: 104857600 (100.000 MiB) 


File information: 


time of last modification: 1436975508 Wed Jul 15 23:51:48 2015 
Size to process: 104857600 (100.000 MiB) 
time of last status change: 1436975508 Wed Jul 15 23:51:48 2015 
ID of device containing file: 64513 
inode number: 658133 
protection: 100640 (regular file) 
number of hard links: 1 
user ID of owner: 0 
total size, in bytes: 104857600 (100.000 MiB) 


group ID of owner: 0 
device ID (if special file): 0 
blocksize for filesystem I/O: 4096 
number of blocks allocated: 204800 
Size to process: 104857600 (100.000 MiB) 
Opening file: /tmp/ibdata1 
File information: 


ID of device containing file: 64513 
inode number: 658133 
protection: 100640 (regular file) 
time of last access: 1436975508 Wed Jul 15 23:51:48 2015 
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 modification: 1436975508 Wed Jul 15 23:51:48 2015 
time of last status change: 1436975508 Wed Jul 15 23:51:48 2015 
total size, in bytes: 104857600 (100.000 MiB) 


Size to process: 104857600 (100.000 MiB) 
time of last access: 1436975508 Wed Jul 15 23:51:48 2015 
time of last modification: 1436975508 Wed Jul 15 23:51:48 2015 
time of last status change: 1436975508 Wed Jul 15 23:51:48 2015 
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 ]# mkdir -p dumps/default 
这里没有加上 -D 是因为这个表没有删除。 
[root@10-4-1-104 ]# ./c_parser -4f 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 -4f 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 -4f 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 -4f pages-ibdata1/FIL_PAGE_INDEX/0000000000000004.page \ 
> -t dictionary/SYS_FIELDS.sql \ 
> > dumps/default/SYS_FIELDS \ 
> 2> dumps/default/SYS_FIELDS.sql 


在当前机的库中加上 song1_recover数据库： 
然后： 
[root@10-4-1-104 ]# cat dictionary/SYS_* | mysql -u sla -h 10.4.1.104 -psla -D song1_recover 


[root@10-4-1-104 ]# cat dumps/default/*.sql | mysql -u sla -h 10.4.1.104 -psla -D song1_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 sla -p sla -h 10.4.1.104 -d song_recover song1/song1 
./sys_parser: error while loading shared libraries: libmysqlclient.so.18: cannot open shared object file: No such file or directory 
[root@10-4-1-104 ]# source /etc/pfrofile 
-bash: /etc/pfrofile: No such file or directory 
[root@10-4-1-104 ]# source /etc/profile 
[root@10-4-1-104 ]# ./sys_parser -u sla -p sla -h 10.4.1.104 -d song_recover song1/song1 
Error: Unknown database 'song_recover' 
[root@10-4-1-104 ]# ./sys_parser -u sla -p sla -h 10.4.1.104 -d song1_recover song1/song1 
CREATE TABLE `song1`( 
`id` INT, 
WARNING: Fields are not found for table 'song1/song1' in SYS_FIELDS 
`name` VARCHAR(10) CHARACTER SET 'utf8' COLLATE 'utf8_general_ci') ENGINE=InnoDB; 
[root@10-4-1-104 ]#




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

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

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