恢复表结构

恢复表结构

此文档适用于.frm文件丢失,并且没有备份情况下恢复出建表语句

恢复InnoDB字典表

首先创建用于恢复的字典辅助表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
mysql> create database sakila_recovered;
Query OK, 1 row affected (0.01 sec)
[mysql@master undrop-for-innodb]$ cat dictionary/SYS_* | mysql -umysql -pmysql -S /data/mysqldata/3307/mysql.sock sakila_recovered
Warning: Using a password on the command line interface can be insecure.
mysql> show tables;
+----------------------------+
| Tables_in_sakila_recovered |
+----------------------------+
| SYS_COLUMNS |
| SYS_FIELDS |
| SYS_INDEXES |
| SYS_TABLES |
+----------------------------+
4 rows in set (0.00 sec)

字典表存储在ibdata1中,所以要解析它

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
[mysql@master undrop-for-innodb]$ ./stream_parser -f /data/mysqldata/3307/data/ibdata1
Opening file: /data/mysqldata/3307/data/ibdata1
File information:
ID of device containing file: 64768
inode number: 104900755
protection: 100660 (regular file)
number of hard links: 1
user ID of owner: 27
group ID of owner: 27
device ID (if special file): 0
blocksize for filesystem I/O: 4096
number of blocks allocated: 4194304
time of last access: 1482819237 Tue Dec 27 14:13:57 2016
time of last modification: 1482819237 Tue Dec 27 14:13:57 2016
time of last status change: 1482819237 Tue Dec 27 14:13:57 2016
total size, in bytes: 2147483648 (2.000 GiB)
Size to process: 2147483648 (2.000 GiB)
Worker(0): 5.47% done. 2016-12-27 14:24:21 ETA(in 00:00:18). Processing speed: 104.000 MiB/sec
Worker(0): 13.67% done. 2016-12-27 14:24:14 ETA(in 00:00:10). Processing speed: 168.000 MiB/sec
Worker(0): 22.66% done. 2016-12-27 14:24:13 ETA(in 00:00:08). Processing speed: 184.000 MiB/sec
Worker(0): 28.52% done. 2016-12-27 14:24:18 ETA(in 00:00:12). Processing speed: 120.000 MiB/sec
Worker(0): 35.94% done. 2016-12-27 14:24:15 ETA(in 00:00:08). Processing speed: 152.000 MiB/sec
Worker(0): 43.75% done. 2016-12-27 14:24:15 ETA(in 00:00:07). Processing speed: 160.000 MiB/sec
Worker(0): 51.17% done. 2016-12-27 14:24:15 ETA(in 00:00:06). Processing speed: 152.000 MiB/sec
Worker(0): 57.81% done. 2016-12-27 14:24:16 ETA(in 00:00:06). Processing speed: 136.000 MiB/sec
Worker(0): 64.45% done. 2016-12-27 14:24:16 ETA(in 00:00:05). Processing speed: 136.000 MiB/sec
Worker(0): 71.48% done. 2016-12-27 14:24:16 ETA(in 00:00:04). Processing speed: 144.000 MiB/sec
Worker(0): 78.91% done. 2016-12-27 14:24:15 ETA(in 00:00:02). Processing speed: 152.000 MiB/sec
Worker(0): 85.16% done. 2016-12-27 14:24:16 ETA(in 00:00:02). Processing speed: 128.000 MiB/sec
Worker(0): 92.19% done. 2016-12-27 14:24:16 ETA(in 00:00:01). Processing speed: 144.000 MiB/sec
All workers finished in 13 sec
Data from database pages is saved by the stream_parser to folder pages-ibdata1:
[mysql@master undrop-for-innodb]$ tree pages-ibdata1/
pages-ibdata1/
├── FIL_PAGE_INDEX
│ ├── 0000000000000001.page
│ ├── 0000000000000002.page
│ ├── 0000000000000003.page
│ ├── 0000000000000004.page
│ ├── 0000000000000005.page
│ ├── 0000000000000011.page
│ ├── 0000000000000012.page
│ ├── 0000000000000013.page
│ ├── 0000000000000014.page
│ ├── 0000000000000015.page
│ ├── 0000000000000016.page
│ ├── 0000000000000017.page
│ ├── 0000000000000018.page
│ ├── 0000000000000019.page
│ ├── 0000000000000020.page
│ ├── 0000000000000021.page
│ ├── 0000000000000022.page
│ ├── 0000000000000023.page
│ └── 18446744069414584320.page
└── FIL_PAGE_TYPE_BLOB
2 directories, 19 files

现在我们要从生成的page文件中抽取字典记录,我们县创建一个目录

1
mkdir -p dumps/default

And now we can generate table dumps and LOAD INFILE commands to load the dumps. We also need to specify -D option to c_parser because the records we need were deleted from the dictionary when the table was dropped.
现在开始恢复字典表
SYS_TABLES

1
2
3
4
./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

1
2
3
4
./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

1
2
3
4
./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

1
2
3
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

生成了包含数据的文件和包含load data infile语句的sql文件

1
2
3
[mysql@master undrop-for-innodb]$ ls dumps/default/
SYS_COLUMNS SYS_FIELDS SYS_INDEXES SYS_TABLES
SYS_COLUMNS.sql SYS_FIELDS.sql SYS_INDEXES.sql SYS_TABLES.sql

恢复字典表数据
cat dumps/default/*.sql | mysql -umysql -pmysql -S /data/mysqldata/3307/mysql.sock sakila_recovered

编译sys_parser

sys_parser is a tool that reads dictionary from tables stored in MySQL and generates CREATE TABLE structure for a table.

To compile it we will need MySQL libraries and development files. Depending on a distribution they may be in -devel or -dev package. On RedHat based system you can check it with command yum provides “*/mysql_config” . On my server it was package mysql-community-devel.

If all necessary packages are installed compilation boils down to simple command:

1
2
3
[mysql@master undrop-for-innodb]$ make sys_parser
/usr/local/mysql/bin/mysql_config
cc -o sys_parser sys_parser.c `mysql_config --cflags` `mysql_config --libs`

恢复表结构

1
2
3
4
5
6
7
8
[mysql@master undrop-for-innodb]$ ./sys_parser -u mysql -p mysql -d sakila_recovered sakila/actor
CREATE TABLE `actor`(
`actor_id` SMALLINT UNSIGNED NOT NULL,
`first_name` VARCHAR(45) CHARACTER SET 'utf8' COLLATE 'utf8_general_ci' NOT NULL,
`last_name` VARCHAR(45) CHARACTER SET 'utf8' COLLATE 'utf8_general_ci' NOT NULL,
`last_update` TIMESTAMP NOT NULL,
PRIMARY KEY (`actor_id`)
) ENGINE=InnoDB;

遗憾的是好像sys_parser不能指定sock,只能用默认端口号3306
还有一些注意事项:
1.InnoDB不存储您可以在frm文件中找到的所有信息。 例如,如果一个字段是AUTO_INCREMENT,InnoDB字典什么也不知道。因此,sys_parser将不会恢复该属性。 如果有任何字段或表级注释,他们将丢失
2.sys_parser生成适合进一步数据恢复的表结构。 它可以但不会恢复二级索引,外键。
3.InnoDB不存储DECIMAL类型作为二进制字符串。 它不存储DECIMAL字段的精度。 这样信息就会丢失。

For example, table payment uses DECIMAL to store money.

1
2
3
4
5
6
7
8
9
10
11
# ./sys_parser -u root -p qwerty -d sakila_recovered sakila/payment
CREATE TABLE `payment`(
`payment_id` SMALLINT UNSIGNED NOT NULL,
`customer_id` SMALLINT UNSIGNED NOT NULL,
`staff_id` TINYINT UNSIGNED NOT NULL,
`rental_id` INT,
`amount` DECIMAL(6,0) NOT NULL,
`payment_date` DATETIME NOT NULL,
`last_update` TIMESTAMP NOT NULL,
PRIMARY KEY (`payment_id`)
) ENGINE=InnoDB;

幸运的是,Oracle计划扩展InnoDB字典,最终摆脱了.frm文件。

Powered by Hexo and Hexo-theme-hiker

Copyright © 2013 - 2018 Fan() All Rights Reserved.

访客数 : | 访问量 :

#