如何恢复MySQL中受损坏的InnoDB表定义文件

How to Recover a Corrupted InnoDB Table Definition File

 

适用于：

MySQL服务器版本5.0及以上

本文信息适用于所有平台。

你会想要完整的show create table的表输出来重建定义文件。

目标

如何在没有可用备份的情况下重建损坏的Innodb表定义文件。

 

修复

以下文档显示如何恢复InnoDB 中的损坏的表定义文件而无需通过创建一个相同的虚拟表并将它的定义复制到损坏的表中来从备份恢复。

mysql [localhost] {msandbox} ((none)) > drop database test;

Query OK, 5 rows affected (0.44 sec)

mysql [localhost] {msandbox} ((none)) > create database test;

Query OK, 1 row affected (1.38 sec)

mysql [localhost] {msandbox} ((none)) > create table test.a (b int);

Query OK, 0 rows affected (0.09 sec)

mysql [localhost] {msandbox} (test) > insert into test.a values (1),(2);

Query OK, 2 rows affected (0.53 sec)

Records: 2 Duplicates: 0 Warnings: 0

mysql [localhost] {msandbox} (test) > select * from a;

+‐‐‐‐‐‐+

| b |

+‐‐‐‐‐‐+

| 1 |

| 2 |

+‐‐‐‐‐‐+

2 rows i n set (0.00 sec)

mysql [localhost] {msandbox} ((none)) > \q

Bye

$ l s ‐l data/test/

total 16

‐rw‐rw‐‐ ‐‐ 1 ronan ronan 8554 Oct 30 13:44 a.frm

‐rw‐rw‐‐‐‐ 1 ronan ronan 65 Oct 30 13:44 db.op t

$ file data/test/a.frm

data/test/a.frm: MySQL table definition file Version 9

 

编辑frm文件来模拟损坏。

$ hexedit data/test/a.frm

00000000 FE 01 09 0C 03 00 00 10 01 00 00 30 00 00 10 00 05 00 00 00 00 00 00 00 00

00 00 02 08 00 08 00 00 05 00 00 00 00 08 00 00 00 00 00 00 00 00 10 00 00 00 5D

………..0…………………………………]

 

然后重启数据库以清空文件缓存，现在我们发现文件被损坏且不可访问。

$ ./stop ; ./start

…. sandbox server started

$ ./use ‐Dtest

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with ‐A

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 1

Server version: 5.5.25‐enterprise‐commercial‐advanced‐log MySQL Enterprise Server ‐

Advanced Edition (Commercial)

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.

mysql [localhost] {msandbox} (test) > select * from a;

ERROR 1033 (HY000): Incorrect information in file: ‘./test/a.frm’

mysql [localhost] {msandbox} (test) > \q

Bye

[ronan@hydra msb_5_5_25]$ tail data/msandbox.err

121030 13:52:27 InnoDB: Waiting for the background threads to start

121030 13:52:28 InnoDB: 1.1.8 started; log sequence number 1574983512

121030 13:52:28 [Note] Server hostname (bind‐address): ‘0.0.0.0’; port: 5525

121030 13:52:28 [Note] ‐ ‘0.0.0.0’ resolves to ‘0.0.0.0’;

121030 13:52:28 [Note] Server socket created on IP: ‘0.0.0.0’.

121030 13:52:28 [Note] Event Scheduler: Loaded 0 events

121030 13:52:28 [Note] /1/mysql/tgz/5.5.25/bin/mysqld: ready for connections.

Version: ‘5.5.25‐enterprise‐commercial‐advanced‐log’ socket:

‘/tmp/mysql_sandbox5525.sock’ port: 5525 MySQL Enterprise Server ‐ Advanced Edition

(Commercial)

121030 13:52:31 [ERROR] /1/mysql/tgz/5.5.25/bin/mysqld: Incorrect information in file:

‘./test/a.frm’

121030 13:52:32 [ERROR] /1/mysql/tgz/5.5.25/bin/mysqld: Incorrect information in file:

‘./test/a.frm’

 

现在，使用相同的create语句，在另一数据库创建同一名称的相同表。现在我们能将该新的定义覆盖旧的损坏表定义( 当数据库下线时)。然后重启，发现我们现在能访问初始表定义和相关的数据了。

mysql [localhost] {msandbox} (test) > create database dummy;

Query OK, 1 row affected (0.15 sec)

mysql [localhost] {msandbox} (test) > create table dummy.a (b int);

Query OK, 0 rows affected (0.09 sec)

mysql [localhost] {msandbox} (test) > \q

Bye

$ l s ‐l data/dummy/

total 16

‐rw‐rw‐‐ ‐‐ 1 ronan ronan 8554 Oct 30 13:53 a.frm

‐rw‐rw‐‐‐‐ 1 ronan ronan 65 Oct 30 13:52 db.op t

$ ls ‐l data/test/

total 16

‐rw‐rw‐‐ ‐‐ 1 ronan ronan 8554 Oct 30 13:51 a.frm

‐rw‐rw‐‐‐‐ 1 ronan ronan 65 Oct 30 13:44 db.op t

$ ./stop

$ cp dat a/dummy/a.frm data/test/

$ ./start

… sandb ox server started

$ ./use ‐Dtest

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with ‐A

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 1

Server version: 5.5.25‐enterp rise‐commercial‐advanced‐log MySQL Enterprise Server ‐

Advanced Edition (Commercial)

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its

2015/12/14 Document 1502290.1

https://support.oracle.com/epmos/faces/DocumentDisplay?_afrLoop=16663231...… 4/4

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.

mysql [localhost] {msandbox} (test) > select * from a;

+‐‐‐‐‐‐+

| b |

+‐‐‐‐‐‐+

| 1 |

| 2 |

+‐‐‐‐‐‐+

2 rows i n set (0.00 sec)

mysql [localhost] {msandbox} (test) > \q

 

现在通过新的表定义能访问初始表数据了。