mysql restore语句_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

欢迎一起学习