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
$ ls ‐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
$ ls ‐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.opt
$ 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.opt
$ ./stop
$ cp data/dummy/a.frm data/test/
$ ./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‐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
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
现在通过新的表定义能访问初始表数据了。
Comment