mysql 還原表的定義語句

還原表的定義語句：
如果表被刪除，可以使用下面的方法 找到表的定義語句：


1 環境準備： 
mysql> create database db_song; 
Query OK, 1 row affected (0.24 sec) 


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


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


mysql> create table t3(id int ,name varchar(10),sex int); 
Query OK, 0 rows affected (0.01 sec) 




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


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


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


mysql> exit 
關掉庫， 把ibdata1檔案拷貝出來： 




2 parse ibdata1檔案： 
[root@10-4-1-104 ]# ./stream_parser -f /tmp/ibdata1 
Opening file: /tmp/ibdata1 
Opening file: /tmp/ibdata1 
File information: 


File information: 


ID of device containing file: 64513 
inode number: 655720 
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 
ID of device containing file: 64513 
inode number: 655720 
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: 


Opening file: /tmp/ibdata1 
File information: 


ID of device containing file: 64513 
inode number: 655720 
ID of device containing file: 64513 
protection: 100640 inode number: 655720 
(regular file) 
number of hard links: 1 
user ID of owner: 0 
group ID of owner: 0 
protection: 100640 device ID (if special file): 0 
(regular file) 
blocksize for filesystem I/O: 4096 
number of hard links: 1 
number of blocks allocated: 204800 
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: 1436945308 Wed Jul 15 15:28:28 2015 
time of last modification: 1436945309 Wed Jul 15 15:28:29 2015 
time of last status change: 1436945309 Wed Jul 15 15:28:29 2015 
total size, in bytes: 104857600 (100.000 MiB) 


time of last access: 1436945308 Wed Jul 15 15:28:28 2015 
time of last access: 1436945308 Wed Jul 15 15:28:28 2015 
time of last modification: 1436945309 Wed Jul 15 15:28:29 2015 
time of last access: 1436945308 Wed Jul 15 15:28:28 2015 
time of last status change: 1436945309 Wed Jul 15 15:28:29 2015 
time of last modification: 1436945309 Wed Jul 15 15:28:29 2015 
Size to process: 104857600 (100.000 MiB) 
time of last modification: 1436945309 Wed Jul 15 15:28:29 2015 
time of last status change: 1436945309 Wed Jul 15 15:28:29 2015 
time of last status change: 1436945309 Wed Jul 15 15:28:29 2015 
total size, in bytes: 104857600 (100.000 MiB) 


total size, in bytes: 104857600 (100.000 MiB) 


total size, in bytes: 104857600 (100.000 MiB) 


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


3 從innodb頁中抽取字典資訊 
mkdir -p dumps/default 


c_parser 這個命令要加上 -D選項，因為表是刪除過的 


SYS_TABLES 的資訊在 第一個頁中： 


./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 


SYS_INDEXES的資訊在第三個頁中： 
./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 


SYS_COLUMNS的資訊放在第二個頁中： 
./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 


SYS_FIELDS的資訊放在第4個頁中： 
./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 






cat dumps/default/*.sql | mysql -u root -ptest -S /data/mysqld.sock -D db_song_recover 


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 
./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 ]# export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/opt/udb/program/mysql/mysql-5.5.24/lib 
[root@10-4-1-104 ]# ./sys_parser 
sys_parser [-h ] [-u ] [-p ] [-d ] databases/table 






[root@10-4-1-104 ]# ./sys_parser -u root -ptest -h10.4.1.104 -d db_song_recover db_song/t1 
CREATE TABLE `t1`( 
`id` INT, 
Fields are not found for table 'db_song/t1' in SYS_FIELDS 
[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, 
`name` VARCHAR(10) CHARACTER SET 'utf8' COLLATE 'utf8_general_ci', 
Fields are not found for table 'db_song/t2' in SYS_FIELDS 
[root@10-4-1-104 ]# ./sys_parser -u root -ptest -h10.4.1.104 -d db_song_recover db_song/t3 
CREATE TABLE `t3`( 
`id` INT, 
`name` VARCHAR(10) CHARACTER SET 'utf8' COLLATE 'utf8_general_ci', 
`sex` INT, 
Fields are not found for table 'db_song/t3' in SYS_FIELDS


轉載請註明源出處
QQ 273002188  歡迎一起學習