ITPub博客

首页 > 数据库 > MySQL > 未删除的表结构从ibdata1文件进行恢复

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

原创 MySQL 作者:psufnxk2000 时间:2015-07-17 10:13:49 0 删除 编辑
上节说到对于删除的表结构进行恢复,这次讲对于未删除的表结构进行恢复:
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/,如需转载,请注明出处,否则将追究法律责任。

下一篇: mysql数据恢复
请登录后发表评论 登录
全部评论

注册时间:2011-05-31

  • 博文量
    215
  • 访问量
    619608