未刪除的表結構從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/，如需轉載，請註明出處，否則將追究法律責任。