MySQL InnoDB存储引擎表损坏恢复指南

Posted by PDSERVICE on Dec 13, 2015 In

目的

故障排除步骤

适用于：

MySQL服务器版本
5.0至5.7[5.0版到5.7]

A 第一反应

A.1 停止，备份，重启

停止MySQL 服务器。如果它已经下线或崩溃，跳到步骤 2。

代码：

/etc/init.d/mysqld stop

这里的目的是要冻结数据和表文件的当前状态，这样就没有新的写入发生，并且我们能创建文件副本，而无需注意文件更改会导致数据不一致，或存储信息的丢失。

2.如果不是整个MySQL数据目录，备份您的数据和日志文件。

代码：

mkdir /root/innodb.bak (or backup path of your choice)

cd /var/lib/mysql (or alternate data directory, if con figured)

dd if=ibdata1 of=ibdata1.bak conv=noerror

cp -p ./ibdata* /root/innodb.bak/

cp -p ./ib_log* /root/innodb.bak/

首先，你创建了存放任何文件副本的目录，然后你在/var/lib/mysql（或你的数据目录）中创建了ibdata1 文件的本地备份，还有ibdata和ib_logfiles的备份到你的备份目录。我喜欢同时使用dd和cp创建（多个）ibdata文件的副本，因为两个工具的性质不同。 Dd工具复制原始文件，而CP复制文件内容到一个新的文件。我没有遇到过任何情况中这是恢复成功的关键，但这仍是我的习惯，我认为这绝不是一个坏习惯。

理想情况下，特别当你还没有备份时，如果可能的话，你最好立即尝试创建你的数据目录的完整副本。

代码:

cp -Rp /var/lib/mysql{,.orig}

我知道这可能过于费时或对一些的紧急情况不太实际，因此，如果这不可行，至少数据文件和InnoDB数据库目录应该提供一些能回退的数据。

备份你的InnoDB数据库文件夹

假设你没有备份完整的MySQL数据目录，你最好还是确保包含InnoDB表的任何数据库都有各自的备份的文件夹。如果你不知道哪个数据库包含InnoDB表，可以使用像这样的命令检查包含的.ibd文件，并将它们复制到备份文件夹（在这个例子中/root/innodb.bak是额外的目录，如果你的DATADIR不是默认的，则需要在一开始更新变量）：

代码：

DATADIR=/var/lib/mysql; find $DATADIR -type f -name *.ibd | awk -F/ ‘{print $(NF-1)}’ | sort | uniq |

xargs -I {} cp -Rp $DATADIR/{} /root/innodb.bak

4.启动MySQL服务器（如果可以的话）
此时将MySQL重新联网是安全的，如果你能这样做而不导致崩溃。如果你能使其联网，接下来就启动MySQL服务，然后执行mysqldump，我建议如下（你可以将这些转储至/root以外的其他路径，如果你愿意记得你的选择）：

Code:

/etc/init.d/mysql start

mysqldump –single-tran saction -AER > /root/dump_wtrans.sql

mysqldump -AER > /root/dump.sql

Dumping it with singletransaction flag creates the dump in, go figure, a single transaction, which prevents locking on the database, and may help if you’re running a 100% InnoDB environment so to be safe, particularly if you’re not sure, I recommend running both.以singletransaction标识转储它在单个事务转储数据，go figure，这能防止在数据库上的锁，如果你在运行100% InnoDB环境也会安全，特别是你不能确定的情况下，我推荐两个都运行。

一定要检查你的SQL转储内容，以确保数据实际存在。有一些情况中，如果数据由于任何原因无法访问，那只有表结构会存在。尤其当在使用singletransaction，你操作的数据库经常运行ALTER TABLE命令的时候。如果在一个表中mysqldump与ALTER TABLE一致，可能只有结构。（详细讨论在MySQL的错误报告＃71017（BUG17862905））

注意：如果你在处理文件系统损坏，尝试并将这些文件备份到另一个可用的磁盘驱动（如果可以，甚至备份到一个安全的远程主机上）

A.2 如果MySQL崩溃
如果MySQL崩溃并拒绝重启，那这很可能是你此时最关心的问题。当然你在想让它在线用于生产，但最重要的是，MySQL在线可以让你得到真正的MySQL数据转储，这样可以最大限度地减少永久丢失数据的机会，并有助于修复可能损坏的表。

由于InnoDB的ACID合规性（MySQL的:: MySQL 5.6参考手册:: 14.2.1 MySQL和ACID模型），它坚持严格的数据一致性标准。这实际上意味着，如果它遇到数据的任何问题，它遵循严格的数据一致性标准。这实质上意味着，如果遇到数据的任何问题，它几乎总是使MySQL崩溃以防止进一步的一致性问题。从理论上讲，这是一件好事，但实际上，非计划的停机时间从来都不是一件好事。

不过使用innodb_force_recovery选项通常可以帮助至少让MySQL回到可访问状态。也就是说，了解它的运行原因，以及如何小心使用它是个好主意。

使用 innodb_force_recovery

当 InnoDB遇到问题时，它已经尝试默认下的基本恢复步骤，但更多的时候，你需要在你的/etc/my.cnf文件这添加innodb_force_recovery设置来帮助它。这指示InnoDB在恢复模式下启动，告诉它跳过InnoDB启动过程中，通常是崩溃发生的各种部分。你最好在一开始设置最低值，1，并且只有在需要时增加，最高值是6。此设置在你的my.cnf文件的[mysqld]部分输入，在示例中显示：

代码：

[mysqld]

innodb_force_recovery = 1

你还可以运行以下单行命令来将其自动添加到你的/etc/my.cnf文件的正确部分（在一开始时，将“mode=”变量更改为任何你想用的模式）：

代码：

mode=1; sed -i “/^\[mysqld\]/{N;s/$/\ninnodb_force_recovery=$mode/}” /etc/my.cnf

然后，一旦你准备把你的服务器返回到默认模式，你可以通过以下命令删除innodb_force_recovery行：

代码：

sed -i ‘/innodb_force_recovery/d’ /etc/my.cnf

此配置选项不应被用作使你的服务器联网的长期，或甚至中期的解决方案。如果你的服务器只能在innodb_force_recovery启用时联网，那在你的服务器上还是有需要处理的重要问题。如果innodb_force_recovery被闲置的活动时间过长，在服务器上可能会造成更多的问题，特别当选项设置为高值（将innodb_force_recovery长时间设为6没什么很好的理由）。这种模式完全是暂时的，仅用于恢复的目的。

以下是对每种模式的简短概要（每一种模式还复合自身，这意味着更高的值包括所有的低值的功能）：

Mode 1当遇到损坏页时，不使 MySQL 崩溃

Mode 2不运行后台操作

Mode 3不会尝试回滚事务

Mode 4不计算统计数据或应用存储/缓冲的变化

Mode 5在启动过程中不查看撤消日志

Mode 6在启动时不从重做日志（ib_logfiles）前滚

因此，如果你的MySQL服务器以模式3而不是模式2启动，崩溃与事务回滚过程有关是安全的假设。另外，要注意在MySQL5.6.15中，模式4和6将会把MySQL设为只读模式。

如果您已经试过所有innodb_force_recovery模式，但仍然由于InnoDB错误崩溃，下一步最好就是尝试并收集有关导致崩溃的原因的其他信息。

B 识别问题

InnoDB的问题多种原因，虽然通常用于概括大部分问题的“corruption”术语了常不准确，但试图找出你所面对的具体问题总是一个好主意。

B.1 检查日志

如果你怀疑InnoDB表或数据库被损坏，很可能是因为你发现受到损坏的数据，不存在数据，或者MySQL的服务拒绝启动。对于任何一种情况，你要首先查看的是MySQL错误日志。在通常的设置中，这是在/ var/ lib/mysql/中，而文件是你的主机名与.err后缀。

这里是拉出日志最后200行的快速命令，如果你不知道主机名，或不想完整输出（如果不是默认的，将数据目录替换为你自己的）：

代码：

tail -200 /var/lib/mysql/`hostname`.err

这执行hostname命令，并使用返回的字符串代替`hostname`，这是在命令行中的反引号的功能。
在这里你可能还会看到几件事情，可以帮助你pin下你遇到的损坏类型，如果有的话。在本指南中，我会涵盖在页损坏，日志序列号问题和数据字典的问题中最常见的三种损坏问题的类型。下面是几个例子，以及它们可能表示什么的说明：

B.1.1 页损坏

代码：

InnoDB: Database page corruption on disk or a failed

InnoDB: file read of page 515891.

通常在这之前还有一些信息，你应该注意，因为它可能包含这种损坏发生在哪里的有用信息，但最终这会告诉你，InnoDB看上去认为在引用的页ID上存在页损坏，或者可能只是无法读取文件。

这并不一定表示实际损坏，事实上，在某些情况下，这可能只是操作系统损坏它自己的文件缓存的结果。正因为如此，建议创建备份后，在无任何进一步操作之前，重启你的计算机。也就是说，如果重新启动解决了你的问题，你最好确保你的RAM没有问题或者在它的出门途中没问题，因为这是操作系统损坏它自己的文件高速缓存的常见原因之一。这可能是要在尝试任何恢复之前解决的问题，以避免陷入相同问题。

如果你不确定，或重启后，你仍怀疑存在损坏，可以运行下面的脚本在所有的.ibd文件执行innochecksum以找出损坏。这对MySQL仍不能成功启动特别有用，因为它在文件上直接运行，而无需MySQL（事实上，如果表空间的检查在服务器上启动，它无法工作）：

代码：

#!/bin/bash

for i in $( ls /var/lib/mysql/*/*.ibd)

in nochecksum $i

done

Innochecksum工具查看在表空间文件中的页，并且计算每页的校验。然后，将这些与存储的校验相比，如果有错配就告诉你。如果有，那通常表明页已被损坏。如果没有发现错配，它不会显示任何输出（除非包含v，即详细输出）。

如果MySQL是在线且可访问的，你可以使用CHECK TABLE语句，如下所述：

MySQL :: MySQL 5.5 Reference Manual :: 13.7.2.2 CHECK TABLE Syntax

B.1.2 InnoDB 的TimeTraveling 和日志序列号错误

代码：

mysql: 120901 9:43:55 InnoDB: Error: page 70944 log sequence number 8 1483471899

mysql: InnoDB: is in the future! Current system log sequence number 5 612394935.

mysql: InnoDB: Your database may be corrupt or you may have copied the InnoDB

mysql: InnoDB: tablespace but not the InnoDB log files. See

mysql: InnoDB: [url=http://dev.mysql.com/doc/refman/5.5/en/ forcing-innodb-recovery.html]MySQL ::

MySQL 5.5 Reference Manual :: 14.21.2 Starting InnoDB on a Corrupted Database[/url]

首先，解释一下日志序列号（LSN）是什么。在InnoDB引擎上每发生一个行为，记录被写入“redo”日志文件，通常默认为MySQL数据目录中的ib_logfile0和ib_logfile1。这两个文件有一组大小，（MySQL中5.6.8+中，默认每个48M），记录按顺序被写入这些文件，从第一个日志文件直到结束，然后继续在第二日志文件中进行。一旦它到达第二日志文件（假定只有2默认日志文件被配置在innodb_log_files_in_group可见）的尾部，它重新开始，再次在第一日志文件的开头写入。所有这些记录都有一个相关的LSN。

此外，当一个数据库被修改，在该数据库中的特定页面也得到一个相关LSN。两者之间，这些LSN被一起检查，确保操作以正确的顺序执行。LSN本身基本上是一个到日志文件的偏移，且存储在数据库页头中的LSN告诉InnoDB有多少日志需要被刷。

在过程中，无论是意外重启，内存问题，文件系统损坏，复制问题，手动更改为InnoDB的文件或其他，这些LSN不再“同步”。无论是否使你的服务器崩溃，这应该被当作合理损坏，通常你需要解决它。

B.1.3 数据字典错误

代码：

[ERROR] Table ./database/table has no primary key in InnoDB data dictionary, but has one in MySQL!

InnoDB: Error: table ‘database/table’

InnoDB: in InnoDB data dictionary has tablespace id 423,

InnoDB: but tablespace with that id or name does not exi st. Have

InnoDB: you deleted or moved .ibd files?

[ERROR] Cannot find or open table database/table from

the internal data dictionary of InnoDB though the .fr m file for the

table exists. Maybe you have deleted and recreated InnoDB data

files but have forgotten to delete the corresponding .frm file s

of InnoDB tables, or you have moved .frm files to another datab ase?

or, the table contains indexes that this version of the engine

doesn’t support.

稍微解释一下InnoDB数据字典，它存在于系统表空间，本身作为页的特殊集存储在ibdata1文件中（系统表空间总是被引用为“space 0”），它储存InnoDB显式处理的任何表，列，或索引的元信息。这不是结构元件的主要位置，那些是每个InnoDB表的.frm文件中的，然而，它确实含有许多相同信息。

在这里，你通常能看到导致这些错误的差异。如果由于某种原因，ibdata1文件被修改，移动，手动更改或替换，你会突然得到一个数据字典，它并不能反映你的文件或数据库结构中有什么。

如果你看过之前的错误描述，你应该知道在ibdata1中（或以其他方式命名）文件中的数据与在单个表空间/.ibd / .frm文件的数据之间有明显的关联。当该关联丢失或损坏，可能会发生不好的情况。所以这像这样的数据字典的错误出现，最常见的原因是有些文件被手动移动或修改。它通常归结为：“数据字典预计这一文件或表空间在这里，但它不在！”，或“.ibd / .frm文件预计此项目在数据字典中，但它不在！ “。再次记住，数据字典存储在ibdata文件中，在大多数环境中，就是MySQL数据目录中的ibdata1。

B.2 检查错误的表

当问题发生时，日志通常会立即显示，但它们有时会有点模糊。你通常只会被告知有损坏，但不知道哪个表，页或数据库具体受到影响。两个InnoDBrelevant检查表的方法是CHECK TABLE SQL语句，以及innochecksum工具。你要使用的方法取决于一个因素：你的MySQL服务器是在线还是离线。
MySQL正在运行并且能访问？

使用 CHECK TABLE。 innochecksum 不检查当前由服务器启动的表空间。

MySQL 崩溃或离线？

innochecksum 是你的选择。它查看在表空间文件的页，计算每个的校验，并将之与储存的校验值相比。如果不匹配，显然MySQL 会崩溃，损坏或数据更改，所以这是确认表空间中有问题的可靠方法。

B.2.1 使用 CHECK TABLE / mysqlcheck

CHECK TABLE 命令，也由mysqlcheck的运用（特别是c标志，然而mysqlcheck默认为这种行为），在许多不同的确认和比较检查中执行，以尝试识别损坏的迹象。CHECK TABLE和mysqlcheck都在MyISAM和InnoDB表执行，但在本文中，我将着重于在InnoDB表的执行。

注意mysqlcheck r的REPAIR功能和“REPAIR TABLE”MySQL命令不会在InnoDB表执行；在这里，mysqlcheck主要只用于找出问题而不是解决问题。

这是它在内部具体查找的详细内容：

相应的.ibd表空间文件的存在
Primary索引的一致性
正确顺序（键的升序）
唯一约束的完整
索引条目的计数
对表中的其他索引重复步骤1-5。
最终，所有表经过一个Adaptive Hash Index 检查。

如果其中任何一个返回不正确或不一致的值，该表可以被标记为损坏。一旦表被标记为损坏，表不能再被使用，直到问题得到解决，或者直到后续表检查确认该问题已不存在。

在某些情况下，如果在MySQL遇到问题之前，CHECK TABLE 在 InnoDB 表中发现了这个问题，这可能导致MySQL服务器被关闭，以避免引起其他错误。尽管这可能是件好事，因为它可以帮助你停止任何进一步的损害的发生，但当你决定在InnoDB表运行CHECK TABLE或mysqlcheck时最好要注意到这一点。

这不是发现问题是简单损坏或错误的情况。被发现的损坏/错误只会导致索引/表中相应地被标记。

运行CHECK TABLE

CHECK TABLE命令必须早MySQL shell中运行，或通过MySQL的其它地方执行。例如，这里是我创建的一个情况，我将现有的dictionary.ibd文件用来自同一个数据库另一个表的.idb文件替换，你可以看到一个普通表，与一个被损坏或有遇到错误表进行CHEACK TABLE的对比：
代码：

mysql> CHECK TABLE roundcube.users;

+—————–+——-+——– –+———-+

+—————–+——-+———-+———-+

+—————–+——-+———-+———-+

1 row in set (0.13 sec)

mysql> CHECK TABLE roundcube.dictionary;

+———————-+——-+——– –+————————————————————

—-+

| Tab le | Op | Msg_type |

Msg_text |

+———————-+——-+———-+——————— —————————————

—-+

| rou ndcube.dictionary | check | Warning | InnoDB: Tablespace is missing for table

’roundcube/dictionary’ |

| roundcube.dictionary | check | Error | Table ’roundcube.dictionary’ doesn’t

exist |

| roundcube.dictionary | ch eck | status | Operation

failed |

+———————-+——-+———-+———– ————————————————-

—-+

3 row s in set (0.00 sec)

In this situation, the error experienced actually warrants the server being automatically shut down. The instant I ran the CHECK TABLE on roundcube.dictionary the first time, the server crashed. This is because I “introduced” the active MySQL instance to the problem’s existence. 在这种情况下，经历的错误实际保证了服务器被自动关闭。我第一次在roundcube.dictionary上运行CHECK TABLE，服务器崩溃了。这是因为我将主动MySQL实例“带入”到这个问题的存在。

InnoDB的数据一致性坚持一旦这样的问题出现，它应尽快被停止。根据引发崩溃的源头，需要不同级别的innodb_force_recovery使MySQL服务器恢复。在缺少表空间的情况下，最低值为1就行了。

请记住MySQL服务器被强制崩溃是有原因的。不要通过快速启用innodb_force_recovery立即回到MySQL！这有时会造成比它解决的问题更多的问题。

运行 mysqlcheck

通常，你要一次检查许多表或数据库。CHECK TABLE对通配符没有任何支持，因此当要检查所有数据库中的表，或检查服务器上的所有数据库时，它就不实用了。 mysqlcheck默认在命令行中执行CHECK TABLE以弥补这点，允许你轻松地检查整个数据库或所有数据库。对数据库执行CHECK TABLE的默认语法是（用你的数据库名替换db_name）：
代码：

mysqlcheck db_name

然后它将输出对数据库中每个表执行CHECK TABLE的结果。如果你只想在一些选出的表上执行命令，你也能在数据库名后指定表 (mysqlcheck db_name tbl1_name tbl2_name…)。

下面是我在之前的情况中对roundcube数据库执行命令的例子：

代码：

-bash-4.1# mysqlcheck roundcube

roundcube.cache OK

roundcube.cache_index OK

roundcube.cache_messages OK

roundcube.cache_thread OK

roundcube.contactgroupmembers OK

roundcube.contactgroups OK

roundcube.contacts OK

roundcube.cp_schema_version OK

roundcube.dictionary

Warning : InnoDB: T ablespace is missing for table ’roundcube/dictionary’

Error : Table ’roundcube.dictionary’ doesn’t exist

status : Operation failed

roundcube.identities

Warning : InnoDB: T ablespace is missing for table ’roundcube/identities’

Error : Table ’roundcube.identities’ doesn’t exist

status : Operation failed

roundcube.searches OK

roundcube.session OK

roundcube.system OK

roundcube.users OK

Additionally, you can use the A flag (or alldatabases) to perform a CHECK TABLE on all tables in all databases on your server. 此外，还可以使用一个标志（或alldatabases）在服务器上的所有数据库中的所有表上执行CHECK TABLE。

B.2.2 使用innochecksum

像在之前提到的，InnoDB需要有一致的数据，当它遇到对本身校验的不匹配，将立即停止活动的服务器。考虑到这一点，innochecksum不仅可以帮助识别损坏，而且能监控校验状态。这里唯一的缺点，在预防方面，即它不能在任何启动的表空间文件运行。因此，要得到表的校验状态的描述，服务器需要下线。

不过，又有我们处理的是在崩溃的MySQL服务器的损坏，这可能你是最不用担心的，innochecksum在追踪不匹配的校验方面很强大，特别是它不需要服务器在线。

从innochecksum得到的输出将由发生的情况变化而变化，一般除非使用-v 指定详细的输出，你不会看到任何输出，除非确实有发现的问题。下面是发现在数据文件中的校验失败的例子：

代码：

page 8 invalid (fails old style checksum)

page 8: old style: calculated = 0x 819564 6B; recorded = 0x DA79A2EE

innochecksum工具目前只能在专门引用的表空间文件（.ibd）运行，但你可以简单使用find命令如下，在所有.ibd文件（调整合适的DATADIR）执行innochecksum：

代码：

DATADIR=/var/lib/mysql; find $DATADIR -type f -name *.ibd -o -name ibdata* | xargs -I{} innochecksum {}

12/9/2015 Document 2017846.1

https://support.oracle.com/epmos/faces/DocumentDisplay?_afrLoop=38120646...… 8/12

C 恢复数据
一旦你确定了问题并准备好你的服务器，下一步将是获得以运作排序的数据。MySQL此时应在线且至少部分响应，无论是通过innodb_force_recovery或其他方式。

C.1 MySQL工具/从.frm文件中提取CREATE TABLE语句
MySQL提供可下载的实用工具，包括对特定的恢复过程有帮助的一些工具，其中有个工具叫做“mysqlfrm”。该工具可以简单地从.frm文件中提取表的CREATE TABLE语句。该语句非常有用，因为几乎所有有用的恢复方法能重建你试图修复原始表的结构，而且往往不能有任何MySQL直接访问原始表本身成。

C.1.1 下载并安装 MySQL 工具：

此处下载包。

在服务某处提取它。

代码：

tar xvzf mysql-utilities*

更改到解压目录，将执行权限给setup.py，然后运行它的编译和安装操作

代码：

cd mysql-utilities-1.4.3

chmod +x setup.py

./setup.py build

./setup.py insta ll

C.1.2 要从.frm文件提取CREATE TABLE语句：
mysqlfrm将根据您现有的安装创建其临时的MySQL守护进程，这意味着如果你现有的MySQL安装已在运行，你需要指定一个备用端口。这里我运行的示例是从我的“staff.frm”文件中提取CREATE TABLE：

代码：

mysqlfrm –basedir=/usr –user=mysql –port=3308 /var/lib/mysql/testdb/staff.frm

这是之后的输出：

代码：

# Spawning server with –user=mysql.

# Starting the spawned server on por t 3308 … done.

# Reading .frm files

# Reading the staff.frm file.

# CREATE statement for staff.frm:

CREATE TABLE `staff` (

`staff_id` tinyint(3 ) unsigned NOT NULL AUTO_INCREMENT,

`first_name` varchar(45) NOT NULL,

`last_name` varchar(45) NOT NULL,

`address_id` smallint(5) unsigned NOT NULL,

`picture` blob,

`email` varchar (50) DEFAULT NULL,

`store_id` tinyint(3) unsigned NO T NULL,

`active` tinyint(1) NOT NULL DEFAULT ‘1’ ,

`username` varchar(16) NOT NULL,

`password` varchar(40) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,

`last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CUR RENT_TIMESTAMP,

PRIMARY KEY (`staff_id`),

KEY `idx_fk_store_id` (`s tore_id`),

KEY `idx_fk_address_id` (`address_i d`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8

#…done.

以上输出从“CREATE TABLE”部分到“CHARSET= UTF8”都是完整的，需要可执行的CREATE TABLE语句来重建“staff”表和正确的结构。要在MySQL shell中执行，我可以简单地粘贴该完整语句，并用分号（;）结尾。在某些情况下，你也可能需要禁用外键检查使之成功：

代码：

SET FOREIGN_KEY_CHECKS=0;

C.2 损坏的表
如果您已经确定了当前在服务器的损坏的表，根据情节严重程度，你可以有几种方法处理。在几乎所有表损坏的情况下，你至少需要以innodb_force_recovery为1运行的InnoDB，使MySQL在你操作时保持在线。

C.2.1 以CREATE..LIKE恢复表
这种方法的目标是尝试使用表的现有结构和数据，由于它们是可访问的，能用来简单地创建一个具有相同的结构和数据的新表来替代原始表。基本步骤如下：

1.访问MySQL shell通过运行：

代码：

mysql

此步骤引用 /root/.my.cnf 文件作为证书。如果它们是正确的，你就不需要提供root shell中的证书。否则你要手动提供它：

代码：

mysql -u root -p

成功的话，你会得到这样的提示：

代码：

mysql>

运行以下 MySQL 语句，以你的表和数据库名称分别替换 tablename 和 dbname ：

代码：

USE dbname;

CREATE TABL E tablename_recovered LIKE tablename;

INSERT INTO tablename_recovered SELECT * FROM ta blename;

如果在这里没有遇到任何问题，那你真走运了。此时，你就能删除原始表，并将”_recovered” 表名改回原来的：

代码：

DROP dbname.tablename;

RENAME TABLE dbname.ta blename_recovered TO dbname.tablename;

当遇到页损坏的情况，这种方法是最简单的，但可能成功率最低，因为它取决于你能否从表中选出所有的数据，并创建一个基于它结构的恢复表。如果没有可访问或可读的，这种方法可能会失败。

但是，如果它在“SELECT *”部分失败了，你还有另一个选择，涉及增量插入。因此，选择不执行以上的“INSERT INTO… SELECT*……”，你要执行以下操作：

代码：

insert ignore into tablename_recovered select * from tablename limit 10;

insert ignore into tablename_recovered select * from tablename limit 50;

insert ignore into tablename_recovered select * from tablename limit 100 ;

insert ignore into tablename_recovered select * from tablename limit 200;

…

With this method, you can piece through the data that’s accessible until you reach the point of failure, at which point you’ll likely lose connection from the MySQL server.

使用这种方法，你可以拼凑可访问的数据，直到到达故障点，此时你可能会失去MySQL服务器的连接。

C.2.2 恢复多个/所有的InnoDB数据库并重建ibdata / ib_log文件

这种方法的成功又取决于mysqldump从每个问题表中生成功能数据的能力，但它也是更全面的方法，因为它涉及到初始化新ibdata和ib_log文件。正因为如此，如不谨慎处理，这种方法也很有可能失败。所以确保你已经运行了第一反应的步骤，且在进一步操作之前有另外的备份。

如果你已有备份转储想要恢复从步骤2开始的现有损坏的数据库，也可以使用此方法。

对所有数据库执行mysqldump

代码：

mysqldump -AER > /root/recovery_dump.sql

如果你在此处遇到任何错误，停下来仔细看一下错误。如果它们表明任何重要数据都被损坏到无法正确转储的地步，最好就不要继续使用该方法了。此外，一定要查看生成的转储文件，以确保它包含预期的数据。

删除所有受影响的 InnoDB 数据库。

代码：

mysql> SET FOREIGN_KEY_CHECKS=0;

mysql> DROP DATABASE db1;

mysql> DROP DATABASE db2;

…

在禁用 innodb_fast_shutdown 以确保干净，完整的关闭执行后，停止 mysqld。

代码：

mysql -e “SET GLOBAL innodb_fast_shutdown = 0″

/etc/init.d/mysql stop

重新定位 InnoDB 数据并重做日志文件

代码：

mv /var/lib/mysql/ibdata* /tmp/

mv /var/lib/mysql/ib_log* /tmp/

注释或删除在当前 /etc/my.cnf 中的 innodb_force_recovery 条目：

代码：

sed -i ‘/innodb_force_recovery/d’ /etc/my.cnf

启动 mysqld 并监控日志，以确保其联网，初始化数据及正确重做日志文件

代码：

nohup /etc/init.d/mysql start & tail -f /var/lib/mysql/`hostname`.err

一旦你确定MySQL仍在线上，并准备将数据导入就恢复转储。

代码：

mysql < /root/recovery_dump.sql

C.3 日志序列号不匹配/在未来（In Future）

为了确保你的数据保持一致，并且具有“撤销/重做”的功能，InnoDB在日志文件和表空间文件中保存了日志序列号。每当在InnoDB表中的数据有所变化，它会导致日志序列号更新。这相当于一个偏移，指示InnoDB在文件中要向前或向后查看多少以找到引用该数据的具体状态。

在任何时间内，如果发生了什么事导致一个序列号被更新，而其他没有，最终你会在MySQL日志中看到有关“不匹配的日志序列号”或“日志序列号是在未来”的错误。为了你的数据库服务器再次正常工作，使这些序号回到正轨是很重要的。

C.3.1 删除并重建数据
这是最有效和唯一的 “实际”的解决方案，但遗憾的是，对许多人来说，这是不适用的，因为在实际情况中，不是每个人得到的数据可用（不过，如果你正在经历，你是该考虑建立一个有效的备份解决方案）。但是，如果你的MySQL实例没有崩溃，而你能创建mysqldump，那它值得一试，并将它重新导入回。你可以按照在损坏的表章节中“还原多个/所有的InnoDB数据库并重建ibdata/ ib_log文件” 方法中的详细步骤，尝试恢复现有数据库的转储。

再次声明，在进行任何更改之前，要确保你已经创建了所有重要ibdata，ib_logfile，.ibd，和.frm文件的副本。

C.3.2重建ib_logfiles

如果你只是处理单个MySQL实例，而不是一个主- >从或其他集群的情况，这可能是一种有效的方法。这里的目的是把现有的ib_logfiles从等式中取出，让MySQL来重新初始化它们来重启。说实话这种方法的成功率有限，但它的使用历史很长，值得一提：

代码：

mysql -e “SET GLOBAL innodb_fast_shutdown = 0″

/etc/init.d/mysql stop

cd /var/lib/mysql

mv ib_logfile0 ib _logfile0.bak

mv ib_logfile1 ib_logfile1.bak

/etc/init.d/mysql start

第一个命令确保InnoDB执行干净的关闭，这偶尔会对情况有帮助，值得写在这里。

C.3.3 执行引擎交换

这又是一个较激进的方法，虽然操作难度大，但根据我个人的经验，它似乎有一个非常稳定的成功率，但它肯定也取决于你的操作环境。这种方法还需要MySQL能被成功启动。

将所有数据库中的表从InnoDB转换为MyISAM，在MySQL运行以下命令，将有管数据库名替换db_name：

代码：

mysql -e “SELECT concat(‘ALTER TABLE ‘, TABLE_NAME,’ ENGINE=MYISAM;’) FROM

Information_schema.TABLES WHERE TABLE_SCHEMA = ‘db_name’ AND ENGINE = ‘InnoDB’ AND TABLE_TYPE = ‘BASE

TABLE'”

停止 MySQL之后，将 ibdata* 和 ib_logfiles 取出：

代码：

/etc/init.d/mysql stop

mkdir -p /root/innodb. bak

mv ib* /root/innodb.bak/

/etc/init.d/mysql start

现在，你的 MySQL 启动且其中的表使用 MyISAM，是时候将它们转换回 InnoDB了，祈祷把（再次用你的数据库名替换db_name ）：

代码：

mysql -e “SELECT concat(‘ALTER TABLE ‘, TABLE_NAME,’ ENGINE=InnoDB;’) FROM

Information_schema.TABLES WHERE TABLE_SCHEMA = ‘db_name’ AND ENGINE = ‘MyISAM'”

C.4 数据字典问题

在这些错误中，最常见的数据字典问题是有关表或表格文件不在InnoDB期望它们的状态，很多时候是由于不当删除InnoDB的.ibd或.frm文件，或者由于删除/移动ibdata文件。了解这些之后，要注意以下几点：

C.4.1 如何正确删除.ibd文件
数据库各子目录中的“.ibd文件代表在这些数据库中表的表空间。
删除文件本身会导致许多与InnoDB保持数据一致性的记录相关的问题。如果你由于一些原因（如尝试导入新的表空间/ idb文件）只要删除表空间，正确的方法是使用ALTER TABLE … DISCARD TABLESPACE语句，如：

代码：

mysql -e “ALTER TABLE roundcube.staff DISCARD TABLESPACE”

在上面的例子中，’roundcube’是数据库，’staff’是表。如果你这样做后检查数据库目录，你会发现虽然.frm文件仍然存在，但.ibd文件没了。记住，表条目本身仍然显示于服务器上。

注：通常在这样操作之前，你需要先停用foreign_key_checks，通过以下操作执行：

代码：

SET FOREIGN_KEY_CHECKS=0;

C.4.2 ibd文件丢失后表的重建

如果你已删除或更改表空间（.ibd）文件，你很有可能遇到像这样一个错误：

代码：

[ERROR] MySQL is trying to open a table handle but the .ibd file for table dbname/tblname does not

exist.
这个情况是它仍然认为表存在，这表示在该问题被解决之前，你将无法正常重建表。幸运的是，尽管执行失败，InnoDB能聪明地意识到发生了什么，并进行一些实用进程，如果运行以下命令（在适当处替换dbname和tblname）：

第一步是尝试删除任何在表空间遗留的一切：

代码：

ALTER TABLE dbname.tblname DISCARD TABLESPACE;

它可能会或不会在MySQL shell报错，但如果你查看错误日志，它仍然继续清除缓冲：

代码：

InnoDB: Warning: cannot delete tablespace 251 in DISCARD TABLESPACE.

InnoDB: But let us remove the insert buffer entries for this tablesp ace.

然后，尝试删除实际表记录（如果你需要保存.frm文件来再次获得CREATE TABLE语句，确保在执行此步骤之前进行复制）：

DROP TABLE dbname.tblname;

你更可能遇到类似的错误，但以下会在日志中再次出现

代码：

InnoDB: We removed now the InnoDB internal data dictionary entry

InnoDB: of table `dbname/tblname`.

之后你就能使用备份或.frm文件副本来重建表（使用之前描述的mysqlfrm方法）。

C.4.3 .frm文件存在，但表不存在

这是与之前问题的相同的行，但情况更简单，也更容易解决：

代码：

InnoDB: Cannot find table test/child2 from the internal data dictionary

InnoDB: of InnoDB though the .frm file for the table exists.

这里最常见的问题是，部分运行的删除或修改语句没有导致所有表中的文件被正确删除。在这些情况下，.frm文件可以简单地被安全删除，且InnoDB应该相应调整。我的建议是不要删除它，而是将其复制到备份文件夹，至少是暂时的，这样你就有CREATE TABLE语句，你可以以任何原因访问它。

If you did not intend to drop this table, or if the .ibd file was deleted by some other means, then aside from attempting some deep data recovery by digging through the ibdata file your only option in regards to restoring this table would be to restore from a backup, because InnoDB is essentially indicating in the error that this .frm file is absolutely the only thing left of the table in question. Definitely do not delete the .frm file if this is the case simply relocate it so that you can perhaps have a chance of recreating the table and rebuilding your data. 如果你不打算删除该表，或者如果.ibd文件通过其他方式被删除，那么除了通过挖掘ibdata文件尝试一些深层次的数据恢复，你唯一恢复表的方法就是从备份进行恢复，因为InnoDB基本上指明了错误，.frm文件是所剩表中唯一有问题的。如果是这样的话，绝对不要删除.frm文件，而是对它重新定位，这样或许有机会重建表，并重建你数据。

C.4.4 孤表或丢失的.frm文件

如果.frm文件因为某些原因丢失，你可能会看到如下所示的错误：

代码：

InnoDB: Error: table dbname/tblname already exists in InnoDB internal

InnoDB: data dictionary. Have you deleted the .frm file

InnoDB: and not used DROP TABLE? …

在这种情况下，这个错误的说明通常描述了处理该问题最有效的方法：

代码：

InnoDB: You can drop the orphaned table inside InnoDB by

InnoDB: creating an InnoDB table with the same name in a nother

InnoDB: database and moving the .frm file to the current datab ase.

InnoDB: Then MySQL thinks the table exists, and DROP TABLE will

InnoDB: succeed.

这表示，如果你有孤表的CREATE TABLE语句，不论通过备份或其他方式，你可以创建一个测试数据库，并在其中创建表的副本（仅结构）。这将创建一个.frm文件，它可以用来复制到原始数据库，并替换丢失的.frm，使你能删除表。这里是一个简单的例子，假设原来的数据库和表名都是“test”：

代码：

# mysql

mysql> CREATE DATABASE test2;

mysql> CREATE TABLE … CHARS ET=utf8;

mysql> quit

# cp /var/l ib/mysql/test2/test.frm /var/lib/mysql/test/

# mysql

mysql> SET FOREIGN_KEY_CHECKS=0;

mysql> DROP TABLE test.test;

总结

InnoDB的数据一致性标准是一把双刃剑。当小心运作并充分理解它是如何操作时，它就是梦幻般的引擎，但很遗憾，当涉及对它的环境改变时，它就不是最宽容的了。它有一些很好的方法来处理自己的情况，还有一些出色的错误日志记录，但当要确保一个稳定的环境时，它也绝对是需要谨慎操作的。

You are here

MySQL InnoDB存储引擎表损坏恢复指南

MySQL InnoDB存储引擎表损坏恢复指南

目的

适用于：