恢复表结构

阅读量

恢复表结构

此文档适用于.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 - 2022 Fan() All Rights Reserved.

访客数 : | 访问量 :