MySQL InnoDB表的Table is Full错误

症状

4.x, 5.0.84 及以下, 5.1.37 及以下, 带 InnoDB 插件的5.1.40及以下

使用 InnoDB 表空间且不使用 innodb_file_per_table 的所有版本

 

重现问题:

使用自动扩展 InnoDB 表空间包括使用 innodb_file_per_table 的所有版本

 

原因

4.x, 5.0.84 及以下, 5.1.37 及以下, 及带 InnoDB 插件的5.1.40 及以下

使用 fixed size InnoDB 表空间和不使用 innodb_file_per_table的所有版本

使用自动扩展InnoDB 表空间包括使用 innodb_file_per_table的所有版本

 

解决方案

Versions 4.x, 5.0.84 及以下, 5.1.37 及以下, 带 InnoDB 插件的5.1.40 及以下

使用固定大小InnoDB 表空间和不使用 innodb_file_per_table 的所有版本

使用自动扩展 InnoDB 表空间包括使用 innodb_file_per_table 的所有版本

 

 

应用于:

MySQL服务器版本4.0 及以上

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

 

症状

这个错误的方式有两种可能:

4.x, 5.0.84 及以下, 5.1.37 及以下,以及带InnoDB插件的5.1.40及以下版本

当运行以下MySQL 服务器版本之一:

  • MySQL 4.0 和1的所有版本
  • 0.84 及以下
  • 不带InnoDB插件的MySQL 5.1:1.37 及以下
  • 带InnoDB插件的MySQL 5.1:1.40 及以下

 

当有多于1023个动态事务且错误信息”Table is Full”被返回到客户端。有时会发生插入错误。错误日志会包含一个更描述性的错误信息:

 

110912 14:52:14 InnoDB: Warning: cannot find a free slot for an undo log. Do you have too
InnoDB: many active transactions running concurrently?

 

所有版本 使用固定大小的 InnoDB 表空间且不使用 innodb_file_per_table

当 InnoDB 表空间设为固定大小且不使用 innodb_file_per_table 选项时,尝试将数据插入InnoDB 表时,会发生以下错误:

 

The table ‘<tablename>’ is full

 

其中<tablename> 是表名。相应的 MySQL 错误代码和SQL 状态是:

 

MySQL Error Code: 1114

SQLState: HY000

 

显示在MySQL错误日志中的错误取决于在使用的InnoDB存储引擎版本:

  • 对于带InnoDB插件的MySQL 5.1 和MySQL 5.5 及以上,错误日志包含如下错误:

 

InnoDB: Error: Data file(s) ran out of space.

Please add another data file or use ‘autoextend’ for the last data file.

110908 12:18:13 [ERROR] /usr/sbin/mysqld: The table ‘<tablename>’ is full

 

 

  • 对于MySQL/InnoDB 更早的版本(包括有InnoDB的buildin版本的MySQL 5.1 ),错误信息是:

110908 13:25:37 [ERROR] /usr/sbin/mysqld: The table ‘t1’ is full

 

即使 SHOW TABLE STATUS 或 SELECT * FROM information_schema.TABLES 显示InnoDB 表有大于0的Data_free 。

重现问题:

该问题能通过以下步骤重现:

  1. 以重新安装的MySQL启动
  2. 在首次启动MySQL之前,在你的my.cnf中有这样的设置

innodb_data_file_path = ibdata1:100M

且确保没有启动 innodb_file_per_table

  1. 启动MySQL并让InnoDB 创建数据文件
  2. 登录到MySQL并运行:

CREATE DATABASE innodbtest;

use innodbtest

CREATE TABLE t1 (id mediumint unsigned NOT NULL auto_increment PRIMARY KEY) ENGINE=InnoDB;

 

 

  1. 不断将行插入到表中,直到查询返回 The table ‘t1’ is full。
  2. 当空间即将耗尽,错误信息类似于:

 

SQLSTATE[HY000]: General error: 1114 The table ‘t1’ is full

 

 

  1. 运行SHOW TABLE STATUS:

 

mysql> SHOW TABLE STATUS LIKE 't1'\G

*************************** 1. row ***************************
Name: t1
Engine: InnoDB
Version: 10
Row_format: Compact
Rows: 3918789
Avg_row_length: 23
Data_length: 90800128
Max_data_length: 0
Index_length: 0
Data_free: 4194304
Auto_increment: 3920001
Create_time: 2011-09-08 12:14:35
Update_time: NULL
Check_time: NULL
Collation: latin1_swedish_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)

 

 

注意即使没有更多可用的数据,Data_free 仍是4194304 。

 

 

使用自动扩展的 InnoDB 表空间包括使用 innodb_file_per_table 的所有版本

当出现Table is full error ,在MySQL错误日志的常见症状为:

 

2014-09-15 08:33:38 7f28aeb8a700 InnoDB: Error: Write to file /mysql/data/ibdata1 failed at offset 20971520.
InnoDB: 1048576 bytes should have been written, only -1 were written.
InnoDB: Operating system error number 28.
InnoDB: Check that your OS and file system support files of this size.
InnoDB: Check also that the disk is not full or a disk quota exceeded.
InnoDB: Error number 28 means 'No space left on device'.
InnoDB: Some operating system error numbers are described at
InnoDB: http://dev.mysql.com/doc/refman/5.6/en/operating-system-error-codes.html
2014-09-15 08:33:38 7101 [ERROR] /usr/sbin/mysqld: The table 't1' is full
2014-09-15 08:33:39 7101 [ERROR] /usr/sbin/mysqld: The table 't1' is full

 

 

在Windows中,错误可能像这样:

 

 

2014-09-15 10:04:06 12AB InnoDB: Encountered a problem with file C:\ProgramData\MySQL\ibdata1
2014-09-15 10:04:06 12AB InnoDB: Disk is full. Try to clean the disk to free space.
2014-09-15 10:04:06 13D7 [ERROR] C:\Program Files\MySQL\bin\mysqld.exe: The table 't1' is full

 

 

以上是共享的表空间的错误。相似错误能在各个表空间发生。

 

原因

4.x, 5.0.84 及以下, 5.1.37 及以下, InnoDB插件的5.1.40 及以下版本

 

这是由于bug If InnoDB Runs Out of Undo Slots, It Returns Misleading ‘Table is Full’。在MySQL 5.1 及以下 InnoDB 支持最多1023个动态事务。要启动更多将会导致错误或在受该bug影响的版本中可能产生声明assertion。

 

使用固定大小的InnoDB 表空间和不使用 innodb_file_per_table 的所有版本

 

该问题是由于InnoDB 耗尽了在共享表空间中的可用空间。在每个参考手册中,Table is full 错误表示磁盘用尽或表达到最大。对于InnoDB,当InnoDB被设为有共享的表空间,InnoDB 表空间的大小 (数据文件) 决定了表的最大空间。

 

InnoDB 表空间包含默认大小为16KB的数据页。有可能一些页没有被完全使用,但是实际上不太可能对新的数据进行提示。因此,SHOW TABLE STATUS报告仍有空间而实际上无法再插入任何数据是有可能的。

 

参见MySQL参考手册中以下页:

  • The table is full
  • Limits on Table Size
  • Configuring InnoDB
  • InnoDB File Space Management

 

使用自动扩展的InnoDB 表空间包括使用 innodb_file_per_table 的所有版本

在这个情况下,起因可能是:

储存表空间的磁盘已满。

已达到设置的磁盘配额。

表空间文件达到文件系统支持的最大尺寸。

 

解决方案

4.x, 5.0.84 及以下, 5.1.37 及以下,和带InnoDB插件的5.1.40及以下版本

解决方案是升级到MySQL的补丁版本。以下版本被打过补丁:

  • MySQL 5.0: 5.0.85及以上
  • 不带InnoDB插件的MySQL 5.1:1.38及以上
  • 带InnoDB插件的MySQL 5.1:1.41及以上
  • MySQL 5.5 及以上:所有版本

 

建议升级到可用的最新GA版本。

 

在升级之前总是创建数据库部分!这包括schema,数据和数据库需要执行的程序(函数,触发器等)。安全总是最好的策略,所以如果在升级过程中有一些问题,很容易恢复到之前的版本。

 

升级教程参见:

  • Downloading MySQL Server
  • 升级或下载MySQL。检查表或索引是否必须重建的部分尤其重要。对下载的版本使用页: 5, 5.1, 5.0。参见 How to Upgrade your Existing MySQL Server to MySQL 5.6 Version 获得更多升级MySQL的帮助。

 

总是在对生产服务器执行升级之前开发或阶段系统中进行测试。

 

使用固定大小的InnoDB表空间和不使用 innodb_file_per_table的所有版本

解决方案使让InnoDB表空间增长,通过执行以下步骤完成:

 

总是在更改应用于生产之前,在开发或阶段系统中尝试步骤。

 

  1. 停止MySQL
  2. 在你喜欢的编辑器中打开MySQL配置文件。
  3. 找到配置innodb_data_file_path 选项的行
  4. 添加一个新表空间或让最近的表空间自动扩展:
  5. 将 innodb_data_file_path 设为

innodb_data_file_path = ibdata1:100M;ibdata2:100M

或类似的

  1. 将 innodb_data_file_path 设为

innodb_data_file_path = ibdata1:100M:autoextend

  1. 保存MySQL配置文件
  2. 再次启动MySQL
  3. 确认现在能够再次将数据插入InnoDB表
  4. 将解决方法迁移到合适的其他环境。

 

参见MySQL参考手册中以下页:

Configuring InnoDB

Adding, Removing, or Resizing InnoDB Data and Log Files

 

使用自动扩展的InnoDB 表空间包括使用 innodb_file_per_table 的所有版本

由于原因是无法进一步增加表空间文件的大小,解决方法包括:

  • 确保表空间文件储存的路径有多余的磁盘空间(参见以下建议,如何释放磁盘空间)。

不要为了释放空间而删除任何InnoDB文件,包括共享的表空间。没有它的这些文件,InnoDB将无法启动。

如何取回空间的教程,参见以下。

  • 确保没有预设的定量会阻止表空间文件的增长。
  • 确保文件系统支持的最大文件尺寸还未达到。如果这是Table is full错误的原因,一些解决方案如下:
    1. 通过你将所有表储存在共享的表空间 (innodb_file_per_table = 0):
      1. 另外创建一个共享的表空间文件。
      2. 或者,你能另外创建一个额外共享的表空间文件启用 innodb_file_per_table ,从而能将表移出共享的表空间。注意到这不会降低共享的表空间大小。参见MySQL Server Variable: innodb_file_per_table获得关于innodb_file_per_table的更多信息。
    2. 对于每个表的表空间的文件,对表进行分区。对已分区的表,每个分区在 自己的表空间中且innodb_file_per_table = 1,所以这是你能将总大小分割到多个文件。

 

释放磁盘空间的可能操作有:

 

  • 使用OPTIMIZE TABLE <table_name>取回每个表空间的空闲空间

这需要重建在表空间文件位置的表。所以只有当你已经释放了空间或将表迁移到另外的位置才有作用。(参见以下)

  • 要取回在共享表空间文件中的文件,参见:How to Reclaim Unused Space and Decrease InnoDB ibdata1 Data File Size.
  • 如果错误日志,慢查询日志,一般查询日志,审计日志等储存在与表空间相同的磁盘。考虑重定向停止并压缩旧日志或将旧日志移动到其他磁盘。参见:
    1. 服务器日志维护
    2. audit_log_rotate_on_size
  • 如果二进制日志位于与数据相同的磁盘上,考虑清楚旧的二进制日志。

确保被清楚二进制日志是不再需要的,例,用于复制或时间点恢复。如果二进制不再需要用于复制但仍要用于恢复,你能在清楚日志之前创建备份。

确保你使用PURGE BINARY LOGS 命令来删除旧的二进制日志。

  • 在MySQL 5.6 中,你能使用 InnoDB native links将在自己tablespace的表空间移到另一个磁盘。
  • 将日志移到另一个磁盘。参见:
    1. What is the General Query Log and How to Control Where the Log is Stored?
    2. What is the Slow Query Log and How to Control Where the Log is Stored and What is Logged?
    3. –log_ error
    4. –audit_ log_file

 

什么导致了MySQL服务器中的MyISAM 表损坏?

 

应用于:

MySQL 服务器-版本:4.0 到 5.5 –发行版:到5.5

MySQL 服务器-版本:4.0 以上–发行版:4.0 以上

MySQL 服务器-版本:4.0 到 5.5 –发行版:4.0 到 5.5

 

所有平台

 

目标

说明MyISAM 表损坏的具体原因

 

解决方案

 

MyISAM 存储引擎非常可靠。但如果时常遇到MyISAM 表损坏,你最好思考导致这个问题的原因。在查询数据时,以下错误信息通常意味着表损坏:

 

Error 1034 Incorrect key file for table: ‘…’. Try to repair it

 

或包含以下错误号码之一的信息:

 

Error 126 = Index file is crashed

Error 127 = Record-file is crashed

Error 134 = Record was already deleted (or record file crashed)

Error 144 / Error 1195 = Table is crashed and last repair failed

Error 145 / Error 1194 = Table was marked as crashed and should be repaired

 

当查询本该查找到在表中的行但没有找到,或当一个查询返回不完整的数据,也可以假定发生了损坏。你可以使用CHECK TABLE 语句来验证MyISAM 表是否损坏。

 

MyISAM的损坏可能由多种因素导致。以可能性从大到小排序,它们依次为:

  1. 由于服务器崩溃,意外关闭,或硬件错误引起的损坏
  • 如果在写的过程中,mysqld进程被杀掉或崩溃可能会导致损坏。
  • 由于电源故障导致运行MySQL的服务器关闭可能会导致损坏。
  • 还可能由于硬件错误导致,如服务器的硬盘问题,这比之前的问题引起损坏的可能性低。
  • RAM中的损坏;重启硬件可能解决这个罕见的问题;如果在操作系统缓存中有损坏的数据,有时只需重启,不需要通断电就能解决这个问题。这个情况比较罕见,但如果你的硬件故障,发生频率就会很高。

服务器重启时的自动修复通常会解决这个问题,但有时需要使用REPAIR TABLE SQL 语句以及更深入的检查选项。当你的表很大,而服务器离线并有强制更快修复的选项时,可以使用myisamchk 。如果你有许多CPU 内核且表中有许多索引,你应该尝试myisamchk 选项先来使用多个线程,因为这通常会更快;这个选项失败时而会有报告,所以不太可能尝试后发现只需要单个线程。

 

  1. 由于其他程序导致的损坏
  • 如果你在使用外部程序,如myisamchk,这会在服务器运行时对表进行更改,你就很可能遇到损坏。
  • 部分或反病毒软件也经常导致损坏,有时由于恢复表的旧版,或由于检验需要的文件。

这些情况的第一步是找出是什么更改了文件。修复表只是暂时的方法。

  1. 由于bug导致的损坏
  • 使用在2007年夏季之前版本的服务器建设。从2006年开始到2007年,MySQL执行了系统的测试来找出罕见且难以复制的MyISAM损坏bug。大约在2007年夏季,我们看到几乎所有bug相关的损坏问题来自旧版本,有广泛修复的较新版本很少受到影响。你能通过将版本升级到接近于2007夏季的版本,最好是最接近的,来消除遇到损坏的可能性,越接近的版本越好。
  • 如果bug仍导致损坏,你应该首先查看最近引入的服务器功能和情况,其中并发级别很高的区域是最可能有问题的。现今,新的损坏bug通常涉及具体客户的语句结合和高并发性,或新功能和高并发性。

你能查找在错误认知中最近重启的mysqld 来验证表是否由于服务器崩溃而损坏。如果没有错误信息表明问题是由于服务器故障,且损坏看上去发生在正常操作期间,这可能是bug。因此,你应该尝试创建一个可复制的测试以反映出问题并通过在My Oracle Support中开启一个Support Request 来报告问题。

再说一次,你能用REPAIR TABLE SQL 语句来修改损坏的MyISAM表。此外,当 mysqld不在运行时,你能用myisamchk 命令来检查或修复表。

 

 

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

目的

故障排除步骤

适用于:

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

 

A 第一反应

A.1 停止,备份,重启

  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数据库目录应该提供一些能回退的数据。

 

3.备份你的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: 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)
do
innochecksum $i
done

 

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

i

如果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主要只用于找出问题而不是解决问题。

 

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

  1. 相应的.ibd表空间文件的存在
  2. Primary索引的一致性
  3. 正确顺序(键的升序)
  4. 唯一约束的完整
  5. 索引条目的计数
  6. 对表中的其他索引重复步骤1-5。
  7. 最终,所有表经过一个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;

+-----------------+-------+-------- --+----------+

| Table | Op | Msg_type | Msg_text |
+-----------------+-------+----------+----------+
| roundcube.users | check | status | OK |
+-----------------+-------+----------+----------+
1 row in set (0.13 sec)



mysql> CHECK TABLE roundcube.dictionary;
+----------------------+-------+-------- --+------------------------------------------------------------
----+
| Tab le | Op | Msg_type |
Msg_text |
+----------------------+-------+----------+--------------------- ---------------------------------------
----+
| roundcube.dictionary | check | Warning | InnoDB: Tablespace is missing for table
'roundcube/dictionary' |
| roundcube.dictionary | check | Error | Table 'roundcube.dictionary' doesn't
| 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 {}

 

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 -u root -p

 

成功的话,你会得到这样的提示符:

mysql>

 

2. 运行以下 MySQL 语句,以你的表和数据库名称分别替换 tablename 和 dbname :

代码:

 

USE dbname;
CREATE TABL E tablename_recovered LIKE tablename;
INSERT INTO tablename_recovered SELECT * FROM ta blename;

 

3. 如果在这里没有遇到任何问题,那你真走运了。此时,你就能删除原始表,并将”_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开始的现有损坏的数据库,也可以使用此方法。

 

1.对所有数据库执行mysqldump

 

mysqldump  -AER > /root/recovery_dump.sql

 

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

 

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

 

mysql> SET FOREIGN_KEY_CHECKS=0;
mysql> DROP DATABASE db1;
mysql> DROP DATABASE db2;
...


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

 

mysql -e "SET GLOBAL innodb_fast_shutdown=0"
/etc/init.d/mysql stop

 

 

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

 

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

 

 

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

 

sed -i '/innodb_force_recovery/d' /etc/my.cnf

 

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

 

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

 

7.  一旦你确定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能被成功启动。

 

1. 将所有数据库中的表从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'"

 

2.停止 MySQL之后,将 ibdata* 和 ib_logfiles 取出:

 

/etc/init.d/mysql stop
mkdir -p /root/innodb.bak
mv ib* /root/innodb.bak/
/etc/init.d/mysql start

 

3.现在,你的 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/lib/mysql/test2/test.frm /var/lib/mysql/test/
# mysql
mysql> SET FOREIGN_KEY_CHECKS=0;
mysql> DROP TABLE test.test;

 

 

总结

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

 

恢复MySQL中损坏的Innodb表

假设你在使用MySQL中的InnoDB表,但是硬件出现问题,或者遇到了驱动程序错误,内核错误,电源故障或某些罕见的MySQL错误,而在InnoDB表空间的某些页被损坏。在这种情况下,Innodb通常打印出如下:

 

InnoDB: Database page corruption on disk or a failed
InnoDB: file read of page 7.
InnoDB: You may have to recover from a backup.
080703 23:46:16 InnoDB: Page dump in ascii and hex (16384 bytes):
… A LOT OF HEX AND BINARY DATA…
080703 23:46:16 InnoDB: Page checksum 587461377, prior-to-4.0.14-form checksum 772331632
InnoDB: stored checksum 2287785129, prior-to-4.0.14-form stored checksum 772331632
InnoDB: Page lsn 24 1487506025, low 4 bytes of lsn at page end 1487506025
InnoDB: Page number (if stored to page already) 7,
InnoDB: space id (if created with >= MySQL-4.1.1 and stored already) 6353
InnoDB: Page may be an index page where index id is 0 25556
InnoDB: (index “PRIMARY” of table “test”.”test”)
InnoDB: Database page corruption on disk or a failed

 

MySQL并且崩溃,日志声称故障。 所以你要如何恢复这样的表呢?

被损坏的可能有各种东西,我将详细查看这篇文章中的简单示例 – 在聚集键中索引页被损坏。这相比于在secondary索引的数据损坏更糟,该情况下简单的OPTIMIZE TABLE足以重建它,但它相对于表字典损坏又较好,因为该情况下表的恢复会更难。

在这个例子中,我其实手动编辑了test.ibd 文件,替换了几个字节使得损坏较轻。

首先,注意在INNODB中的CHECK TABLE 没什么用。从手动损坏的表中获得:

 

mysql> check table test;
ERROR 2013 (HY000): Lost connection to MySQL server during query




mysql> check table test;

+-----------+-------+----------+----------+
| Table     | Op    | Msg_type | Msg_text |
+-----------+-------+----------+----------+
| test.test | check | status   | OK       |
+-----------+-------+----------+----------+
1 row in set (0.69 sec)

 

 

在这个例子中,数据损坏只发生在页中,所以一旦你以innodb_force_recovery=1 启动Innodb,你能进行如下操作:首先在正常操作模式下运行check table – 在这样的情况下,如果有校验错误(即使我们运行CHECK操作),Innodb 就直接崩溃了。在第二种情况下,我设置innodb_force_recovery=1 ,你能看到即使在日志文件中获得校验故障的信息,CHECK TABLE 仍显示表没问题。这表示你不能相信Innodb中的CHECK TABLE 来确定表没问题。

 

 

mysql> CREATE TABLE `test2` (->   `c` char(255) DEFAULT NULL,
->   `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
->   PRIMARY KEY (`id`)
-> ) ENGINE=MYISAM;
Query OK, 0 rows affected (0.03 sec)




mysql> insert into test2 select * from test;
Query OK, 229376 rows affected (0.91 sec)
Records: 229376  Duplicates: 0  Warnings: 0

 

现在,在MyISAM表中有所有的数据,你只要删除旧表,并在在没有innodb_force_recovery选项下重新启动Innodb后将新表转移回Innodb。你还可以重命名旧表,以便以后能详细查看。另一种方法是用MySQLDump转储表并将它加载回来。这差不多是同样的。我正在使用MyISAM表,随后你会看到原因。

你可能会认为你为什么不干脆用OPTIMIZE TABLE重建表?这是因为在innodb_force_recovery模式下运行,Innodb就只对于数据操作可读,这样就不能插入或删除任何数据(虽然你可以创建或删除InnoDB表):

 

mysql> optimize table test;

+-----------+----------+----------+----------------------------------+
| Table     | Op       | Msg_type | Msg_text                         |
+-----------+----------+----------+----------------------------------+
| test.test | optimize | error    | Got error -1 from storage engine |
| test.test | optimize | status   | Operation failed                 |
+-----------+----------+----------+----------------------------------+
2 rows in set, 2 warnings (0.09 sec)

 

 

我也这样认为,所以我接下来对test.ibd进行了一些编辑,完全删除其中一个页头。现在CHECK TABLE 将会崩溃,即使innodb_force_recovery=1这很简单,对吧?

 

080704 0:22:53 InnoDB: Assertion failure in thread 1158060352 in file btr/btr0btr.c line 3235
InnoDB: Failing assertion: page_get_n_recs(page) > 0 || (level == 0 && page_get_page_no(page) == dict_index_get_page(index))
InnoDB: We intentionally generate a memory trap.
InnoDB: Submit a detailed bug report to http://bugs.mysql.com.
InnoDB: If you get repeated assertion failures or crashes, even

 

如果你得到这样的故障声明,很可能更高的innodb_force_recovery 值也没什么帮助 – 只有在多个系统区域有损坏的情况下有用,但不能以Innodb处理页数据的方式作什么改变。

接下来是试错的做法:

 

mysql> insert into test2 select * from test;
ERROR 2013 (HY000): Lost connection to MySQL server during query

你可能认为mysql将扫描表直到首先损坏的行并在MyISAM表中获得结果?

 

遗憾的是,test2 在运行后为空的。同时,我看到一些可能被选出的数据。问题是,有一些缓冲发生,而MySQL崩溃时不会将所有能够恢复的数据储存到MyISAM表。

 

手动恢复时,使用一些带有LIMIT的查询更简便:


mysql> insert ignore into test2 select * from test limit 10;
Query OK, 10 rows affected (0.00 sec)
Records: 10  Duplicates: 0  Warnings: 0




mysql> insert ignore into test2 select * from test limit 20;
Query OK, 10 rows affected (0.00 sec)
Records: 20  Duplicates: 10  Warnings: 0




mysql> insert ignore into test2 select * from test limit 100;
Query OK, 80 rows affected (0.00 sec)
Records: 100  Duplicates: 20  Warnings: 0




mysql> insert ignore into test2 select * from test limit 200;
Query OK, 100 rows affected (1.47 sec)
Records: 200  Duplicates: 100  Warnings: 0


mysql> insert ignore into test2 select * from test limit 300;
ERROR 2013 (HY000): Lost connection to MySQL server during query

 

 

注意,即使你不使用MyISAM表,而是获取脚本的数据,在MySQL崩溃时一定要使用LIMIT或PK Rangers,你不会得到所有在网络数据包的数据,由于缓冲你可能得到部分。你能看到,我可以从新的测试的表中获取行,直到最终遇到使MySQL崩溃的行。在这种情况下,我们可以预估在200和300之间有这样的行,我们可以执行一大堆类似的语句,进行“二进制搜索”来查找确切的行号。

所以,现在我们发现在表中损坏的数据,我们需要以某种方式跳过它。为此我们要找到能被恢复的最大PK,并尝试一些更高的值。

 

mysql> select max(id) from test2;

+---------+
| max(id) |
+---------+
|     220 |
+---------+
1 row in set (0.00 sec)




mysql> insert ignore into test2 select * from test where id>250;
ERROR 2013 (HY000): Lost connection to MySQL server during query




mysql> insert ignore into test2 select * from test where id>300;
Query OK, 573140 rows affected (7.79 sec)
Records: 573140  Duplicates: 0  Warnings: 0

 

因此,我们试图跳过30行,太少,跳过80行,还行。再次使用二进制搜索,获知你需要跳过多少行来恢复尽可能多的数据。行大小对你很有用。在这个例子中,每行约280个字节,所以每页有50行,所以30行不够也不令人吃惊 – 通常,如果页目录已损坏,你至少需要跳过整个页面。如果页面在较高层级在BTREE损坏,你可能需要跳过很多页(整个子树)来使用此恢复方法。

你可能需要跳过多个坏页,而不只示例中的一个。

另一个提示 – 你最好CHECK在MySQL崩溃之后用于恢复的MyISAM表,以确保索引没有被损坏。

我们探讨了如何从简单的InnoDB表获得恢复的数据。在更复杂的情况下,你可能需要使用较高的innodb_force_recovery模式来阻止数据清楚,插入缓冲合并或从事务日志进行恢复。虽然说恢复模式越低,恢复过程中越可能得到更好的数据。

在某些情况下,如数据字典或聚类索引的“root页”损坏,此方法将无法很好地运作。在这种情况下,您可能希望使用恢复软件包,它在需要恢复被删除的行或表时也很有帮助。

 

我还要说一下,我们提供MySQL恢复的援助,包括对Innodb损坏和被删除的数据进行恢复。

Undelete MySQL如何从InnoDB表空间恢复被删除的行

在我以前的文章中,我解释了它如何在某些特定情况下,从一个完整备份恢复单个表,以节省时间,使恢复过程更简单。现在的情况更糟糕,因为我们没有备份或备份恢复过程不起作用。我如何恢复已删除的行?

我们将根据之前帖子中相同的例子,所以我们需要从表“salaries”中删除员工10008的记录。“意外”删除行之后,你应该停止MySQL,获取salaries.ibd的副本,并再次启动它。稍后,我们将从ibd文件提取这些被删除的行,并导入到数据库中。删除行和数据库停止之间的时间是至关重要的。如果页被重用,你无法恢复数据。

我要通过四个步骤解释全过程,以便更清晰明了:

 

 

从表空间提取所有InnoDB页:

 

首先我们需要下载Percona数据恢复工具并使用“make”命令编译所有工具。在本例中,我要在/root/recovery-tool文件夹安装工具,数据如表空间和被恢复行在/root/recovery-tool/data。

编译后,我们需要将salaries.ibd表空间复制到恢复工具的数据目录。为了提取的所有页,我们将使用page_parser工具。该工具会找到并将表空间的所有页提取到输出目录。我们只需要指定行格式(-5),以及表空间位置(-f)

The row format can be -4 (REDUNDANT) or -5 (COMPACT). From 5.0.3 the default format is COMPACT. More information about row format on the following link: 行格式可以是-4(冗余)或-5(COMPACT)。从5.0.3起,默认格式是COMPACT。

 

 

 

你也能从Information Schema获取表行格式:

 

 

mysql (information_schema) > SELECT ROW_FORMAT from TABLES WHERE TABLE_SCHEMA='employees'    AND TABLE_NAME='salaries';
+------------+
| ROW_FORMAT |
+------------+
| Compact |
+------------+

~/recovery-tool# ./page_parser -5 -f data/salaries.ibd
Opening file: data/salaries.ibd:
[...]
71.43% done. 2012-02-14 13:10:08 ETA(in 00:00 hours). Processing speed: 104857600 B/sec

 

 

所有页都储存在单个目录,其中有一些子目录,一页对应表中一个索引:

 

 

~/recovery-tool# ls pages-1329221407/FIL_PAGE_INDEX/
0-26 0-27

 

 

在这种情况下,ID 0-26和0-27有两个索引。 InnoDB有聚集主键,即数据与主键一起组织。因此,如果我们要提取行数据,我们需要确定哪两个索引是主键。这是我们的下一步。

 

 

标识主键

 

 

有不同的方法来找到正确索引,在这里我要解释其中三个:

 

 INNODB_SYS_INDEXES

Percona Server 在INFORMATION_SCHEMA 有一些额外的表,可以帮助我们找到不同的索引和类型。

 

mysql (information_schema) > 
select i.INDEX_ID, i.NAME FROM INNODB_SYS_INDEXES as i INNER JOIN INNODB_SYS_TABLES 
as t USING(TABLE_ID) WHERE t.NAME='salaries';


+----------+---------+
| INDEX_ID | NAME |
+----------+---------+
| 26 | PRIMARY |
| 27 | emp_no |
+----------+---------+

 

 

InnoDB Table Monitor表监视器

索引信息也可以直接从MySQL获取,使用 InnoDB Tablespace Monitor。这个监视器将与表和索引(与它们的ID)相关所有信息写入错误日志中。

 

mysql (employees) > CREATE TABLE innodb_table_monitor (id int) ENGINE=InnoDB;




TABLE: name employees/salaries, id 18, flags 1, columns 7, indexes 2, appr.rows 2844513
[...]
INDEX: name PRIMARY, id 26, fields 2/6, uniq 2, type 3
root page 3, appr.key vals 2844513, leaf pages 6078, size pages 6120
FIELDS: emp_no from_date DB_TRX_ID DB_ROLL_PTR salary to_date
INDEX: name emp_no, id 27, fields 1/2, uniq 2, type 0
root page 4, appr.key vals 306195, leaf pages 2189, size pages 2212
FIELDS: emp_no from_date
[...]

 

 

第二个方法得到相同的结果, 0-26 是我们的主键。标识主键后,不要忘了删除innodb_table_monitor。

 

 

检查磁盘上每个索引的大小

这非常依赖表模式,但通常主键在磁盘上更大,因为它也存储行本身的。

 

 

~/recovery-tool/pages-1329221407/FIL_PAGE_INDEX# du -hs 0-26/
96M 0-26/
~/recovery-tool/pages-1329221407/FIL_PAGE_INDEX# du -hs 0-27/
35M 0-27/

 

 

在我们的例子中,0-26 看上去是主键。

 

提取行

 

 

我们知道了数据在哪个索引,所以下一步显然是从中提取行。要完成这个任务,我们要使用constraint_parser命令。为了使用它,工具需要知道表schema结构,即列的类型,名称和属性。这个信息需要在头文件 recovery-tools/include/table_defs.h可用。所以重新编译工具将是必要的。

 

要将schema定义转换为C头文件,可以使用名为create_defs.pl的工具。它会连接到数据库,以便检查表并创建table_defs.h内容。

 

~/recovery-tool# ./create_defs.pl –host 127.0.0.1 –port 5520 –user root –password msandbox –db employees –table salaries > include/table_defs.h

 

有关table_defs.h格式的详细信息在以下链接: https://www.percona.com/docs/wiki/innodb-data-recovery-tool:mysql-data-recovery:generating_a_table_definition

 

 

有了table_defs.h表定义,我们就要用“make”命令重新编译所有的工具。编译后,我们可以使用constraints_parser将行恢复为人类可读格式。

~/recovery-tool# ./constraints_parser -5 -D -f pages-1329221407/FIL_PAGE_INDEX/0-26/ > data/salaries.recovery

通过 -D 选项,我们请求 constraints_parser 只恢复被删除的页。-5 和 -f 是我们在page_parser之前使用的相同选项。

你能在salaries.recovery 中找到许多被删除的行,不仅是意外删除的行。你应当收到找出需要恢复的行并将它储存在其他文件中。这是例子的输出

 

 

~/data-recovery# cat data/salaries.recovery
salaries 10008 "1998-03-11" 46671 "1999-03-11"
salaries 10008 "1999-03-11" 48584 "2000-03-10"
salaries 10008 "2000-03-10" 52668 "2000-07-31"

 

导入行

有了这些数据,最后一步就是将它们导入到数据库:

 

mysql (employees) > LOAD DATA INFILE '/root/recovery-tool/data/salaries.recovery' REPLACE INTO TABLE `salaries` FIELDS TERMINATED BY '\t' OPTIONALLY ENCLOSED BY '"' LINES STARTING BY 'salaries\t' (emp_no, from_date, salary, to_date);
Query OK, 3 rows affected (0.01 sec)
Records: 3 Deleted: 0 Skipped: 0 Warnings: 0

mysql (employees) > select * from salaries where emp_no=10008;
+--------+--------+------------+------------+
| emp_no | salary | from_date | to_date |
+--------+--------+------------+------------+
| 10008 | 46671 | 1998-03-11 | 1999-03-11 |
| 10008 | 48584 | 1999-03-11 | 2000-03-10 |
| 10008 | 52668 | 2000-03-10 | 2000-07-31 |
+--------+--------+------------+------------+

 

数据恢复成功!10008 再次恢复了它的工资。

 

结论

通过InnoDB,被删除的行没有丢失。你可以从原始表空间恢复,或者如果你有二进制备份,也能从ibd文件中恢复它们。只需使用constraint_parser不加-D选项(被删除),你就能恢复所有在表空间中的数据。

 

 

如果自己搞不定可以找诗檀软件专业ORACLE/MySQL数据库修复团队成员帮您恢复!

诗檀软件专业数据库修复团队

服务热线 : 13764045638   QQ号:47079569    邮箱:service@parnassusdata.com

 

MySQL中恢复/修复InnoDB数据字典

innodb-dictionary

 

 

c_parser 是工具包中的一个命令行工具,它能读取 InnoDB 页面并从中获取记录。虽然它可以扫描任何字节流,但恢复质量比你将属于表的PRIMARY索引的页面提供给 c_parser 更高。所有InnoDB索引有自己的标识符,又名index_id。InnoDB字典储存表名和index_id之间的对应关系。这是第一个原因。

另一个原因是InnoDB字典能恢复表结构。当一个表被删除,MySQL删除相应的.frm文件。如果你既没有备份,又没有表schema,恢复该表结构就有相当大的困难。这个话题需要我哪天再写一篇单独的文章来讲。

假设你对以上足够确信,我们就能继续InnoDB字典的恢复了。

 

拆分 ibdata1

InnoDB 字段储存在 ibdata1中,所以我们需要分析它并获取存放字典记录的页面。使用 stream_parser 。

 

 

# ./stream_parser -f /var/lib/mysql/ibdata1
...
Size to process:                  79691776 (76.000 MiB)
All workers finished in 1 sec

stream_parser 找出在ibdata1 中的InnoDB 页面,将它们以页面类型(FIL_PAGE_INDEX 或FIL_PAGE_TYPE_BLOB) , index_id.的顺序储存。
索引如下:

 

 

 

SYS_TABLES
[root@twindb-dev undrop-for-innodb]# ll pages-ibdata1/FIL_PAGE_INDEX/0000000000000001.page
-rw-r--r-- 1 root root 16384 Jun 24 00:50 pages-ibdata1/FIL_PAGE_INDEX/0000000000000001.page
SYS_INDEXES
[root@twindb-dev undrop-for-innodb]# ll pages-ibdata1/FIL_PAGE_INDEX/0000000000000003.page
-rw-r--r-- 1 root root 16384 Jun 24 00:50 pages-ibdata1/FIL_PAGE_INDEX/0000000000000003.page
SYS_COLUMNS
[root@twindb-dev undrop-for-innodb]# ll pages-ibdata1/FIL_PAGE_INDEX/0000000000000002.page
-rw-r--r-- 1 root root 49152 Jun 24 00:50 pages-ibdata1/FIL_PAGE_INDEX/0000000000000002.page
和 SYS_FIELDS
[root@twindb-dev undrop-for-innodb]# ll pages-ibdata1/FIL_PAGE_INDEX/0000000000000004.page
-rw-r--r-- 1 root root 16384 Jun 24 00:50 pages-ibdata1/FIL_PAGE_INDEX/0000000000000004.page


可以看到字典较小,每个索引只有一页。

SYS_TABLES SYS_INDEXES转储(dump)记录

要从索引页提取记录,你需要使用 c_parser。但首先,我们来创建转储的目录:

 

 

[root@twindb-dev undrop-for-innodb]# mkdir -p dumps/default
[root@twindb-dev undrop-for-innodb]#


InnoDB 字典总是 REDUNDANT 格式,所以选项 -4 是强制的:

[root@twindb-dev undrop-for-innodb]# ./c_parser -4f pages-ibdata1/FIL_PAGE_INDEX/0000000000000001.page -t dictionary/SYS_TABLES.sql > dumps/default/SYS_TABLES 2> dumps/default/SYS_TABLES.sql


[root@twindb-dev undrop-for-innodb]#

这是我们的sakila 表:

[root@twindb-dev undrop-for-innodb]# grep sakila dumps/default/SYS_TABLES | head -5
0000000052D5    D9000002380110  SYS_TABLES  "sakila/actor"  753 4   1   0   80  ""  739
0000000052D8    DC0000014F0110  SYS_TABLES  "sakila/address"    754 8   1   0   80  ""  740
0000000052DB    DF000002CA0110  SYS_TABLES  "sakila/category"   755 3   1   0   80  ""  741
0000000052DE    E2000002F80110  SYS_TABLES  "sakila/city"   756 4   1   0   80  ""  742
0000000052E1    E5000002C50110  SYS_TABLES  "sakila/country"    757 3   1   0   80  ""  743
[root@twindb-dev undrop-for-innodb]#


dumps/default/SYS_TABLES 是符合 LOAD DATA INFILE命令的表转储。具体命令 c_parsers 打印到标准错误输出。我将它保存在dumps/default/SYS_TABLES.sql

 

[root@twindb-dev undrop-for-innodb]# cat dumps/default/SYS_TABLES.sql
SET FOREIGN_KEY_CHECKS=0;
LOAD DATA INFILE '/root/tmp/undrop-for-innodb/dumps/default/SYS_TABLES' REPLACE INTO TABLE `SYS_TABLES` FIELDS TERMINATED BY '\t' OPTIONALLY ENCLOSED BY '"' LINES STARTING BY 'SYS_TABLES\t' (`NAME`, `ID`, `N_COLS`, `TYPE`, `MIX_ID`, `MIX_LEN`, `CLUSTER_NAME`, `SPACE`);
[root@twindb-dev undrop-for-innodb]#

我们以相同方式转储 SYS_INDEXES:

[root@twindb-dev undrop-for-innodb]# ./c_parser -4f pages-ibdata1/FIL_PAGE_INDEX/0000000000000003.page -t dictionary/SYS_INDEXES.sql > dumps/default/SYS_INDEXES 2> dumps/default/SYS_INDEXES.sql
[root@twindb-dev undrop-for-innodb]# 

[root@twindb-dev undrop-for-innodb]# head -5 dumps/default/SYS_INDEXES
-- Page id: 11, Format: REDUNDANT, Records list: Valid, Expected records: (153 153)
000000000300    800000012D0177  SYS_INDEXES 11  11  "ID\_IND"   1   3   0   302
000000000300    800000012D01A5  SYS_INDEXES 11  12  "FOR\_IND"  1   0   0   303
000000000300    800000012D01D3  SYS_INDEXES 11  13  "REF\_IND"  1   0   0   304
000000000300    800000012D026D  SYS_INDEXES 12  14  "ID\_IND"   2   3   0   305
[root@twindb-dev undrop-for-innodb]# head -5 dumps/default/SYS_INDEXES.sql
SET FOREIGN_KEY_CHECKS=0;
LOAD DATA INFILE '/root/tmp/undrop-for-innodb/dumps/default/SYS_INDEXES' REPLACE INTO TABLE `SYS_INDEXES` FIELDS TERMINATED BY '\t' OPTIONALLY ENCLOSED BY '"' LINES STARTING BY 'SYS_INDEXES\t' (`TABLE_ID`, `ID`, `NAME`, `N_FIELDS`, `TYPE`, `SPACE`, `PAGE_NO`);
[root@twindb-dev undrop-for-innodb]#

 

现在用字典操作了,如果表在MySQL中会更方便。

 

将字典表加载到 MySQL

SYS_TABLES 和 SYS_INDEXES 的主要用途是根据表名获取 index_id。运行两个 greps是可能的。SYS_TABLES 和 SYS_INDEXES 在MySQL中会使操作更简便。

Before we can process let’s make sure mysql user can read from the root’s home directory. Maybe it’s not wise from security standpoint. If it’s your concern create whole recovery environment somewhere in /tmp. 在操作之前,我们要确保MySQL用户可以从root的主目录中读取。从安全角度来看,这也许不太明智。如果你对此有所顾虑,你可以在/ tmp目录某处创建整个恢复环境。

 

[root@twindb-dev undrop-for-innodb]# chmod 711 /root/
[root@twindb-dev undrop-for-innodb]#


在某些数据库中创建空字典表(例: test)
[root@twindb-dev undrop-for-innodb]# mysql test < dictionary/SYS_TABLES.sql

在某些数据库中创建空字典表(例: test)
[root@twindb-dev undrop-for-innodb]# mysql test < dictionary/SYS_TABLES.sql


并加载转储数据:
[root@twindb-dev undrop-for-innodb]# mysql test < dumps/default/SYS_TABLES.sql
[root@twindb-dev undrop-for-innodb]# mysql test < dumps/default/SYS_INDEXES.sql [root@twindb-dev undrop-for-innodb]# 现在InnoDB 字典在MySQL 中,我们能以在任何其他MySQL表中的方式查询它: mysql> SELECT * FROM SYS_TABLES WHERE NAME = 'sakila/actor';
+--------------+-----+--------+------+--------+---------+--------------+-------+
| NAME         | ID  | N_COLS | TYPE | MIX_ID | MIX_LEN | CLUSTER_NAME | SPACE |
+--------------+-----+--------+------+--------+---------+--------------+-------+
| sakila/actor | 753 |      4 |    1 |      0 |      80 |              |   739 |
+--------------+-----+--------+------+--------+---------+--------------+-------+
1 row in set (0.00 sec)
mysql> SELECT * FROM SYS_INDEXES WHERE TABLE_ID = 753;
+----------+------+---------------------+----------+------+-------+---------+
| TABLE_ID | ID   | NAME                | N_FIELDS | TYPE | SPACE | PAGE_NO |
+----------+------+---------------------+----------+------+-------+---------+
|      753 | 1828 | PRIMARY             |        1 |    3 |   739 |       3 |
|      753 | 1829 | idx_actor_last_name |        1 |    0 |   739 |       4 |
+----------+------+---------------------+----------+------+-------+---------+
2 rows in set (0.00 sec)


 

我们看到sakila.actor 有两个索引: PRIMARY 和idx_actor_last_name。index_id 分别是1828 和1829。

 

如果自己搞不定可以找诗檀软件专业ORACLE/MySQL数据库修复团队成员帮您恢复!

诗檀软件专业数据库修复团队

服务热线 : 13764045638   QQ号:47079569    邮箱:service@parnassusdata.com

Undrop MySQL InnoDB 中恢复被drop的表,当 innodb_file_per_table=off时

人为错误是不可避免的。错误的 “DROP DATABASE” 或 “DROP TABLE” 可能会破坏MySQL 服务器上的重要数据。备份是有帮助的,但不总是可用。这种情况是可怕的,但不至于没有希望的。在许多情况下,恢复几乎所有在数据库或表中的数据是有可能的。

我们来看看如何能做到这一点。恢复计划取决于InnoDB将所有数据储存在单个ibdata1还是每个表都有自己的表空间。在这篇文章中,我们考虑innodb_file_per_table= OFF的情况下。此参数假定所有表都保存在一个公共文件中,通常位于位于/var/lib/mysql/ibdata1。

 

错误操作表删除

在这个情况下,我们使用测试数据库sakila 以及附带的工具。
假设我们错误删除了表actor:

 

mysql> SELECT * FROM actor LIMIT 10;
+----------+------------+--------------+---------------------+
| actor_id | first_name | last_name    | last_update         |
+----------+------------+--------------+---------------------+
|        1 | PENELOPE   | GUINESS      | 2006-02-15 04:34:33 |
|        2 | NICK       | WAHLBERG     | 2006-02-15 04:34:33 |
|        3 | ED         | CHASE        | 2006-02-15 04:34:33 |
|        4 | JENNIFER   | DAVIS        | 2006-02-15 04:34:33 |
|        5 | JOHNNY     | LOLLOBRIGIDA | 2006-02-15 04:34:33 |
|        6 | BETTE      | NICHOLSON    | 2006-02-15 04:34:33 |
|        7 | GRACE      | MOSTEL       | 2006-02-15 04:34:33 |
|        8 | MATTHEW    | JOHANSSON    | 2006-02-15 04:34:33 |
|        9 | JOE        | SWANK        | 2006-02-15 04:34:33 |
|       10 | CHRISTIAN  | GABLE        | 2006-02-15 04:34:33 |
+----------+------------+--------------+---------------------+
10 rows in set (0.00 sec)
mysql> CHECKSUM TABLE actor;
+--------------+------------+
| Table        | Checksum   |
+--------------+------------+
| sakila.actor | 3596356558 |
+--------------+------------+
1 row in set (0.00 sec)

mysql> SET foreign_key_checks=OFF
mysql> DROP TABLE actor;
Query OK, 0 rows affected (0.00 sec)

mysql>


 

ibdata1DROP TABLE后进行恢复

 

解析 InnoDB 表空间

InnoDB 将所有数据储存在B+tree 索引。一个表有一个集群索引PRIMARY,所有键都储存在其中。如果表有secondary 键,那每个键都有一个索引。每个索引由index_id标识。

如果我们要恢复表,必须找到属于特定index_id的所有页。

stream_parser 读取 InnoDB 表空间并根据类型和index_id排序InnoDB 页。

 

root@test:~/undrop-for-innodb# ./stream_parser -f /var/lib/mysql/ibdata1
Opening file: /var/lib/mysql/ibdata1
File information:

ID of device containing file:        64768
inode number:                      1190268
protection:                         100660 (regular file)
number of hard links:                    1
user ID of owner:                      106
group ID of owner:                     114
device ID (if special file):             0
blocksize for filesystem I/O:         4096
number of blocks allocated:          69632
time of last access:            1404842312 Tue Jul  8 13:58:32 2014
time of last modification:      1404842478 Tue Jul  8 14:01:18 2014
time of last status change:     1404842478 Tue Jul  8 14:01:18 2014
total size, in bytes:             35651584 (34.000 MiB)

Size to process:                  35651584 (34.000 MiB)
All workers finished in 0 sec
root@test: ~/undrop-for-innodb#


数据库页的数据被stream_parser 储存在文件夹pages-ibdata1:

 

 

root@test:~/undrop-for-innodb/pages-ibdata1/FIL_PAGE_INDEX# ls
0000000000000001.page  0000000000000121.page  0000000000000382.page
0000000000000395.page  0000000000000408.page  0000000000000421.page
0000000000000434.page  0000000000000447.page  0000000000000002.page
...
0000000000000406.page  0000000000000419.page  0000000000000432.page
0000000000000445.page  0000000000000120.page  0000000000000381.page
0000000000000394.page  0000000000000407.page  0000000000000420.page
0000000000000433.page  0000000000000446.page
root@test: ~/undrop-for-innodb/pages-ibdata1/FIL_PAGE_INDEX

 

Now each index_id from InnoDB tablespace is saved in a separate file. We can use c_parser to fetch records from the pages. But we need to know what index_id corresponds to table sakila/actor. That information we can acquire from the dictionary – SYS_TABLES and SYS_INDEXES. 现在InnoDB表空间的每个index_id被保存在单独的文件中。我们可以使用c_parser从页中提取记录。但是,我们需要知道什么index_id对应表中的Sakila/actor。我们可以从字典- SYS_TABLES SYS_INDEXES中获得这些信息。

SYS_TABLES 总是储存在文件 index_id 1中,即文件页-ibdata1/FIL_PAGE_INDEX./0000000000000001.page
我们来查找sakila/actor的table_id。如果MySQL 有足够时间将更改刷到磁盘,那添加 -D 参数表示“查找已删除记录”。字典总是REDUNDANT 格式,所以我们指定参数-4:

 

 

root@test:~/undrop-for-innodb# ./c_parser -4Df pages-ibdata1/FIL_PAGE_INDEX/0000000000000001.page -t dictionary/SYS_TABLES.sql | grep sakila/actor
000000000B28  2A000001430D4D  SYS_TABLES  "sakila/actor"  158  4  1 0   0   ""  0
000000000B28  2A000001430D4D  SYS_TABLES  "sakila/actor"  158  4  1 0   0   ""  0


 

注意就在表名后的号码 158 。这就是table_id。
接下来是查找表actor的PRIMARY索引的索引id。为此,我们将从文件0000000000000003.page (该表包含index_id 和table_id的信息)获取SYS_INDEXES 的记录。SYS_INDEXES的结构由-t选项传递。 The structure of SYS_INDEXES is passed with -t option.

 

 

root@test:~/undrop-for-innodb$ ./c_parser -4Df pages-ibdata1/FIL_PAGE_INDEX/0000000000000003.page -t dictionary/SYS_INDEXES.sql | grep 158
000000000B28    2A000001430BCA  SYS_INDEXES     158     376     "PRIMARY"       1       3       0       4294967295
000000000B28    2A000001430C3C  SYS_INDEXES     158     377     "idx\_actor\_last\_name"        1       0       0       4294967295
000000000B28    2A000001430BCA  SYS_INDEXES     158     376     "PRIMARY"       1       3       0       4294967295
000000000B28    2A000001430C3C  SYS_INDEXES     158     377     "idx\_actor\_last\_name"        1       0       0       4294967295

 

你能从输出中发现,必要的index_id 是376。因此我们要查找文件 0000000000000376.page中的actor数据。

 

root@test:~/undrop-for-innodb# ./c_parser -6f pages-ibdata1/FIL_PAGE_INDEX/0000000000000376.page -t sakila/actor.sql |  head -5
-- Page id: 895, Format: COMPACT, Records list: Valid, Expected records: (200 200)
000000000AA0    B60000035D0110  actor   1       "PENELOPE"      "GUINESS"       "2006-02-15 04:34:33"
000000000AA0    B60000035D011B  actor   2       "NICK"  "WAHLBERG"      "2006-02-15 04:34:33"
000000000AA0    B60000035D0126  actor   3       "ED"    "CHASE" "2006-02-15 04:34:33"
000000000AA0    B60000035D0131  actor   4       "JENNIFER"      "DAVIS" "2006-02-15 04:34:33"
root@test:~/undrop-for-innodb#


结果输出看上去正确,我们将转储数据储存到一个文件。要简化加载,c_parser 输出LOAD DATA INFILE 命令到标准错误输出。

我们使用该文件的默认位置:dump/default

 

root@test:~/undrop-for-innodb# mkdir -p dumps/default
root@test:~/undrop-for-innodb# ./c_parser -6f pages-ibdata1/FIL_PAGE_INDEX/0000000000000376.page -t sakila/actor.sql > dumps/default/actor 2> dumps/default/actor_load.sql

这是加载表的命令。

root@test:~/undrop-for-innodb# cat dumps/default/actor_load.sql
SET FOREIGN_KEY_CHECKS=0;
LOAD DATA LOCAL INFILE '/home/asterix/undrop-for-innodb/dumps/default/actor' REPLACE INTO TABLE `actor` FIELDS TERMINATED BY '\t' OPTIONALLY ENCLOSED BY '"' LINES STARTING BY 'actor\t' (`actor_id`, `first_name`, `last_name`, `last_update`);
root@test:~/undrop-for-innodb#


将数据加载回数据库

现在我们要将数据恢复到数据库中了。在加载转储数据之前,我们需要创建表actor的空结构:

 

mysql> source sakila/actor.sql
mysql> show create table actor\G
*************************** 1. row ***************************
       Table: actor
Create Table: CREATE TABLE `actor` (
  `actor_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `first_name` varchar(45) NOT NULL,
  `last_name` varchar(45) NOT NULL,
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`actor_id`),
  KEY `idx_actor_last_name` (`last_name`)
) ENGINE=InnoDB AUTO_INCREMENT=201 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
mysql>


现在,表actor被创建了。我们能在恢复后载入数据。

root@test:~/undrop-for-innodb# mysql --local-infile -uroot -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
...
mysql> USE sakila;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> source dumps/default/actor_load.sql
Query OK, 0 rows affected (0.00 sec)

Query OK, 600 rows affected (0.01 sec)
Records: 400  Deleted: 200  Skipped: 0  Warnings: 0

mysql>



检查恢复的数据

最后的步骤是– 查看数据质量。我们会看到记录的总数,预览一些记录并计算校验。

 

mysql> SELECT COUNT(*) FROM actor;
+----------+
| COUNT(*) |
+----------+
|      200 |
+----------+
1 row in set (0.00 sec)

mysql> SELECT * FROM actor LIMIT 5;
+----------+------------+--------------+---------------------+
| actor_id | first_name | last_name    | last_update         |
+----------+------------+--------------+---------------------+
|        1 | PENELOPE   | GUINESS      | 2006-02-15 04:34:33 |
|        2 | NICK       | WAHLBERG     | 2006-02-15 04:34:33 |
|        3 | ED         | CHASE        | 2006-02-15 04:34:33 |
|        4 | JENNIFER   | DAVIS        | 2006-02-15 04:34:33 |
|        5 | JOHNNY     | LOLLOBRIGIDA | 2006-02-15 04:34:33 |
+----------+------------+--------------+---------------------+
5 rows in set (0.00 sec)

mysql> CHECKSUM TABLE actor;
+--------------+------------+
| Table        | Checksum   |
+--------------+------------+
| sakila.actor | 3596356558 |
+--------------+------------+
1 row in set (0.00 sec)

mysql>


你能看到恢复后的校验是3596356558,与意外删除表之前的检验相等。因此,我们能确认数据被正确恢复了。
在下一篇文章中会讲到其他恢复的情况。

 

如果自己搞不定可以找诗檀软件专业ORACLE/MySQL数据库修复团队成员帮您恢复!

诗檀软件专业数据库修复团队

服务热线 : 13764045638   QQ号:47079569    邮箱:service@parnassusdata.com

Undrop MySQL InnoDB 中恢复被drop的表,当 innodb_file_per_table=on时

我们介绍了在innodb_file_per_table 设为OFF,意外删除了表时,使用恢复工具包进行恢复的情况。
在这篇文章中,我们将展示在innodb_file_per_table 开启的情况下如何恢复 MySQL 表或数据库。假设mysql 服务器的设置为innodb_file_per_table=ON,这个参数告诉InnoDB 将用户表储存在单独的数据文件中。

在恢复测试中我们使用与之前文章中相同的数据库sakila。

 

 

root@test:/var/lib/mysql/sakila# ll
total 23468
drwx------ 2 mysql mysql     4096 Jul 15 04:26 ./
drwx------ 6 mysql mysql     4096 Jul 15 04:26 ../
-rw-rw---- 1 mysql mysql     8694 Jul 15 04:26 actor.frm
-rw-rw---- 1 mysql mysql   114688 Jul 15 04:26 actor.ibd
-rw-rw---- 1 mysql mysql     2871 Jul 15 04:26 actor_info.frm
-rw-rw---- 1 mysql mysql     8840 Jul 15 04:26 address.frm
-rw-rw---- 1 mysql mysql   163840 Jul 15 04:26 address.ibd
-rw-rw---- 1 mysql mysql     8648 Jul 15 04:26 category.frm
-rw-rw---- 1 mysql mysql    98304 Jul 15 04:26 category.ibd
-rw-rw---- 1 mysql mysql     8682 Jul 15 04:26 city.frm
-rw-rw---- 1 mysql mysql   114688 Jul 15 04:26 city.ibd
-rw-rw---- 1 mysql mysql     8652 Jul 15 04:26 country.frm
-rw-rw---- 1 mysql mysql    98304 Jul 15 04:26 country.ibd
...
-rw-rw---- 1 mysql mysql       36 Jul 15 04:26 upd_film.TRN
root@test:/var/lib/mysql/sakila#


 

注意与表country相关的两个文件: country.frm, country.ibd。
我们将删除这个表并尝试恢复它。首先,我们进行校验,预览在这个表中包含的记录:

 

 

Database changed
mysql> SELECT * FROM country LIMIT 10;
+------------+----------------+---------------------+
| country_id | country        | last_update         |
+------------+----------------+---------------------+
|          1 | Afghanistan    | 2006-02-15 04:44:00 |
|          2 | Algeria        | 2006-02-15 04:44:00 |
|          3 | American Samoa | 2006-02-15 04:44:00 |
|          4 | Angola         | 2006-02-15 04:44:00 |
|          5 | Anguilla       | 2006-02-15 04:44:00 |
|          6 | Argentina      | 2006-02-15 04:44:00 |
|          7 | Armenia        | 2006-02-15 04:44:00 |
|          8 | Australia      | 2006-02-15 04:44:00 |
|          9 | Austria        | 2006-02-15 04:44:00 |
|         10 | Azerbaijan     | 2006-02-15 04:44:00 |
+------------+----------------+---------------------+
10 rows in set (0.00 sec)

mysql> CHECKSUM TABLE country;
+----------------+------------+
| Table          | Checksum   |
+----------------+------------+
| sakila.country | 3658016321 |
+----------------+------------+
1 row in set (0.00 sec)

mysql> SELECT COUNT(*) FROM country;
+----------+
| COUNT(*) |
+----------+
|      109 |
+----------+
1 row in set (0.00 sec)

mysql>




意外删除
现在我们删除表并查找表相关的文件。你能从列表中看到,有 country 表数据的文件丢失了:

 

mysql> SET foreign_key_checks=OFF;
Query OK, 0 rows affected (0.00 sec)

mysql> DROP TABLE country;
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> exit
Bye
root@test:~# cd /var/lib/mysql/sakila/
root@test:/var/lib/mysql/sakila# ll
total 23360
drwx------ 2 mysql mysql     4096 Jul 15 04:33 ./
drwx------ 6 mysql mysql     4096 Jul 15 04:26 ../
-rw-rw---- 1 mysql mysql     8694 Jul 15 04:26 actor.frm
-rw-rw---- 1 mysql mysql   114688 Jul 15 04:26 actor.ibd
-rw-rw---- 1 mysql mysql     2871 Jul 15 04:26 actor_info.frm
-rw-rw---- 1 mysql mysql     8840 Jul 15 04:26 address.frm
-rw-rw---- 1 mysql mysql   163840 Jul 15 04:26 address.ibd
-rw-rw---- 1 mysql mysql     8648 Jul 15 04:26 category.frm
-rw-rw---- 1 mysql mysql    98304 Jul 15 04:26 category.ibd
-rw-rw---- 1 mysql mysql     8682 Jul 15 04:26 city.frm
-rw-rw---- 1 mysql mysql   114688 Jul 15 04:26 city.ibd
-rw-rw---- 1 mysql mysql       40 Jul 15 04:26 customer_create_date.TRN
-rw-rw---- 1 mysql mysql     8890 Jul 15 04:26 customer.frm
-rw-rw---- 1 mysql mysql   196608 Jul 15 04:26 customer.ibd
-rw-rw---- 1 mysql mysql     1900 Jul 15 04:26 customer_list.frm
-rw-rw---- 1 mysql mysql      297 Jul 15 04:26 customer.TRG
-rw-rw---- 1 mysql mysql       65 Jul 15 04:26 db.opt
...
-rw-rw---- 1 mysql mysql       36 Jul 15 04:26 upd_film.TRN
root@ALtestTwinDB:/var/lib/mysql/sakila#


DROP TABLE后的恢复

因为我们需要恢复已删除的文件。如果数据库服务器与磁盘上的数据再有写入,有可能被删除的文件会被其他数据重写。因此,停止服务器并挂载该分区只读是很重要的。但在测试中我们将只停止mysql服务,并继续恢复。

 

 

root@test:/var/lib/mysql/sakila# service mysql stop
mysql stop/waiting


尽管用户数据被存储各表独立的文件中,数据字典仍然存储在ibdata1文件中。这是我们要对/var/lib/mysql/ibdata1使用 stream_parser 的原因。
为了找到 country表的table_id 和 index_id ,我们要使用储存在SYS_TABLES 和 SYS_INDEXES 中的字典。我们将从ibdata1 文件中获取数据。数据字典总是REDUNDANT 格式,因此我们指定选项 -4。假设mysql服务器已将更改刷到磁盘,所以我们添加选项 -D ,表示“查找已删除的记录”。 SYS_TABLES 信息储存在index_id=1 的文件中,即文件页-ibdata1/FIL_PAGE_INDEX./0000000000000001.page:

 

 

root@test:~/undrop-for-innodb# ./c_parser -4Df ./pages-ibdata1/FIL_PAGE_INDEX/0000000000000001.page -t ./dictionary/SYS_TABLES.sql | grep country
000000000CDC  62000001960684  SYS_TABLES      "sakila/country"        228     3       1       0       0       ""      88
000000000CDC  62000001960684  SYS_TABLES      "sakila/country"        228     3       1       0       0       ""      88
SET FOREIGN_KEY_CHECKS=0;
LOAD DATA LOCAL INFILE '/home/asterix/undrop-for-innodb/dumps/default/SYS_TABLES' REPLACE INTO TABLE `SYS_TABLES` FIELDS TERMINATED BY '\t' OPTIONALLY ENCLOSED BY '"' LINES STARTING BY 'SYS_TABLES\t' (`NAME`, `ID`, `N_COLS`, `TYPE`, `MIX_ID`, `MIX_LEN`, `CLUSTER_NAME`, `SPACE`);

root@test:~/undrop-for-innodb#


我们能看到country table 表有table_id=228。下一步,我们将查找表country的PRIMARY索引。为此,我们从文件0000000000000003.page (SYS_INDEXES 表包含table_id 和 index_id之间的映射)获取SYS_INDEXES 表的记录。SYS_INDEXES 结构通过-t选项被添加到工具。

 

root@test:~/undrop-for-innodb# ./c_parser -4Df ./pages-ibdata1/FIL_PAGE_INDEX/0000000000000003.page -t ./dictionary/SYS_INDEXES.sql | grep 228
000000000CDC    620000019605A8  SYS_INDEXES     228     547     "PRIMARY"       1       3       88      4294967295
000000000CDC    620000019605A8  SYS_INDEXES     228     547     "PRIMARY"       1       3       88      4294967295
SET FOREIGN_KEY_CHECKS=0;
LOAD DATA LOCAL INFILE '/home/asterix/undrop-for-innodb/dumps/default/SYS_INDEXES' REPLACE INTO TABLE `SYS_INDEXES` FIELDS TERMINATED BY '\t' OPTIONALLY ENCLOSED BY '"' LINES STARTING BY 'SYS_INDEXES\t' (`TABLE_ID`, `ID`, `NAME`, `N_FIELDS`, `TYPE`, `SPACE`, `PAGE_NO`);

root@test:~/undrop-for-innodb#


 

我们能看到已删除表country的 index_id 是547。以下步骤与之前在 innodb_file_per_table=OFF的情况不同。由于没有可用数据的文件,我们要扫描所有存储设备作为裸设备并查找符合数据库页预期结构的数据。顺便收一下,这个方法能应用于数据文件损坏的情况。如果一些数据被损坏,恢复工具能进行部分数据恢复。在工具参数中,我们指定设备名称和设备尺寸(可以是大概的)。

 

 

 

root@test:~/undrop-for-innodb#./stream_parser -f /dev/vda -t 20000000k
Opening file: /dev/vda
File information:

ID of device containing file:            5
inode number:                         6411
protection:                          60660 (block device)
number of hard links:                    1
user ID of owner:                        0
group ID of owner:                       6
device ID (if special file):         64768
blocksize for filesystem I/O:         4096
number of blocks allocated:              0
time of last access:            1405411377 Tue Jul 15 04:02:57 2014
time of last modification:      1404625158 Sun Jul  6 01:39:18 2014
time of last status change:     1404625158 Sun Jul  6 01:39:18 2014
total size, in bytes:                    0 (0.000 exp(+0))

Size to process:               20480000000 (19.073 GiB)
Worker(0): 1.06% done. 2014-07-15 04:57:37 ETA(in 00:01:36). Processing speed: 199.848 MiB/sec
Worker(0): 2.09% done. 2014-07-15 04:57:37 ETA(in 00:01:35). Processing speed: 199.610 MiB/sec
Worker(0): 3.11% done. 2014-07-15 04:59:13 ETA(in 00:03:09). Processing speed: 99.805 MiB/sec
...
Worker(0): 97.33% done. 2014-07-15 04:57:15 ETA(in 00:00:05). Processing speed: 99.828 MiB/sec
Worker(0): 98.35% done. 2014-07-15 04:57:20 ETA(in 00:00:06). Processing speed: 49.941 MiB/sec
Worker(0): 99.38% done. 2014-07-15 04:57:17 ETA(in 00:00:01). Processing speed: 99.961 MiB/sec
All workers finished in 77 sec
root@test:~/undrop-for-innodb#


 

流解析器将结果文件储存在文件夹页 pages-vda (根据设备标题命名)。我们能看到必要索引显示在文件中。

 

 

root@test:~/undrop-for-innodb/pages-vda/FIL_PAGE_INDEX# ll | grep 547
-rw-r--r-- 1 root root    32768 Jul 15 04:57 0000000000000547.page
root@test:~/undrop-for-innodb/pages-vda/FIL_PAGE_INDEX#


我们来查找在文件0000000000000547.page中的数据。工具 c_parser 根据预期表结构,通过-t选项为我们提供信息。utility c parser provide us information according to expected table structure, supplied with -t option.

 

 

 

root@test:~/undrop-for-innodb# ./c_parser -6f pages-vda/FIL_PAGE_INDEX/0000000000000547.page -t sakila/country.sql |  head -5
-- Page id: 3, Format: COMPACT, Records list: Valid, Expected records: (109 109)
000000000C4B    F30000038C0110  country 1       "Afghanistan"   "2006-02-15 04:44:00"
000000000C4B    F30000038C011B  country 2       "Algeria"       "2006-02-15 04:44:00"
000000000C4B    F30000038C0126  country 3       "American Samoa"        "2006-02-15 04:44:00"
000000000C4B    F30000038C0131  country 4       "Angola"        "2006-02-15 04:44:00"
root@test:~/undrop-for-innodb#


结果看起来有效,所以我们要准备加载数据回数据库的文件。附带必要参数的LOAD DATA INFILE命令被发送到标准错误设备。

 

 

root@test:~/undrop-for-innodb# ./c_parser -6f pages-vda/FIL_PAGE_INDEX/0000000000000547.page -t sakila/country.sql > dumps/default/country 2> dumps/default/country_load.sql

root@test:


将数据加载回数据库

我们要将数据加载到数据库中。在加载数据之前,我们创建表country的空结构:

 

root@test:~/undrop-for-innodb# service mysql start
mysql start/running, process 31035
root@test:~/undrop-for-innodb# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 37
Server version: 5.5.37-0ubuntu0.14.04.1 (Ubuntu)

Copyright (c) 2000, 2014, 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> 

mysql> use sakila;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> source sakila/country.sql
Query OK, 0 rows affected (0.00 sec)
...

Query OK, 0 rows affected (0.00 sec)

mysql>

mysql> show create table country\G
*************************** 1. row ***************************
       Table: country
Create Table: CREATE TABLE `country` (
  `country_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `country` varchar(50) NOT NULL,
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`country_id`)
) ENGINE=InnoDB AUTO_INCREMENT=110 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql>

现在我们加载数据本身。

 

root@testB:~/undrop-for-innodb# mysql --local-infile -uroot -p
Enter password:
...
mysql> USE sakila;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> source dumps/default/country_load.sql
Query OK, 0 rows affected (0.00 sec)

Query OK, 327 rows affected (0.00 sec)
Records: 218  Deleted: 109  Skipped: 0  Warnings: 0
mysql>


 

检查数据质量

剩下的最后一件事就是检查被恢复数据的质量。我们将预览一些记录,计算出记录和校验的总数。

 

 

mysql> SELECT COUNT(*) FROM country;
+----------+
| COUNT(*) |
+----------+
|      109 |
+----------+
1 row in set (0.00 sec)

mysql> SELECT * FROM country LIMIT 5;
+------------+----------------+---------------------+
| country_id | country        | last_update         |
+------------+----------------+---------------------+
|          1 | Afghanistan    | 2006-02-15 04:44:00 |
|          2 | Algeria        | 2006-02-15 04:44:00 |
|          3 | American Samoa | 2006-02-15 04:44:00 |
|          4 | Angola         | 2006-02-15 04:44:00 |
|          5 | Anguilla       | 2006-02-15 04:44:00 |
+------------+----------------+---------------------+
5 rows in set (0.00 sec)

mysql> CHECKSUM TABLE country;
+----------------+------------+
| Table          | Checksum   |
+----------------+------------+
| sakila.country | 3658016321 |
+----------------+------------+
1 row in set (0.00 sec)

mysql>


 

 

我们很幸运。尽管我们对mysql数据了使用系统卷(不建议的操作),并且我们没有重新载入分区作为只读(其他操作继续写入磁盘),我们还是成功恢复了所有记录。计算出的恢复后检验 (3658016321) 等于删除前的检验(3658016321)。

 

 

如果自己搞不定可以找诗檀软件专业ORACLE/MySQL数据库修复团队成员帮您恢复!

诗檀软件专业数据库修复团队

服务热线 : 13764045638   QQ号:47079569    邮箱:service@parnassusdata.com

数据库管理员和开发人员MySQL指南

作 者:Diana Gray,甲骨文大学首席课程产品经理

本文地址:https://www.askmaclean.com/archives/mysql-ocp-z2.html

 

 

MySQL是当前市场中最值得信赖和依靠的开源数据库平台。全世界十个最受欢迎、访问量最大的网站中便有九个采用MySQL,其优势在于拥有跨异构平台和跨应用堆栈的广泛应用范围。

MySQL以高性能、可靠性和易用性著称。MySQL 5.6 现已得到全面的完善和改进,将帮助富有创新力的数据库管理员和开发人员在最新一代的开发框架和硬件平台基础上创建及部 署下一代网络、嵌入式应用和Cloud/SaaS/DaaS 应用。

构 建下一代网络应用程序和服务

毫 无疑问,MySQL 5.6是一款功能更强大的MySQL, 它 对数据库内核的每个功能区都进行了完善,包括:

  • 增强了 InnoDB存储引擎,提供更强的信息处理能力
  • 增强了Optimizer,提供更理想的查 询执行时间和诊断功能。
  • 通过在线DDL/模式变化提供更好的应用程序可用性。
  • 通过NoSQL Access、Memcached API至InnoDB提高了开发人员的灵活性。
  • 增强了复制功能,提供更高的性能和自 愈分布式部署。
  • 增强了安全性,提供无忧应用程序部署
  • 增强了性能模式,提供更好的性能测量

MySQL课 程和认证考试最新发布

“MySQL for Database Administrator” 和“ MySQL for Developers” 培 训课程的内容与最新发布的GA版本“MySQL 5.6 Developr”和“MySQL 5.6 Database Administrator OCP”认证考试内容一致。

参 加实践培训并成功通过认证考试,将充分验证您的专业知识。

用全球最受欢迎的开源数据库MySQL证明您的专业知识

本文地址:https://www.askmaclean.com/archives/mysql-ocp-z1.html

 

 

Oracle 认证现已发布新的MySQL 5.6版本认证考试—— MySQL 5.6 Developer(1Z0-882)和MySQL 5.6 Database Administrator (1Z0-883)。

5.6版本的MySQL对产品的几乎所有功能进行了提高 ,包括性能和可扩展性、InnoDB、Optimizer、 Replication和Performance Schema等。该版本也新增了一项NoSQL ACCESS TO INNODB功能,可大幅度提高开发员的灵活性。

Oracle Certified Professional, MySQL 5.6 Developer (OCP)和Oracle Certified Professional, MySQL 5.6 Database Administrator (OCP)认证专门为拥有丰富全面的技能和工具知识而能熟练使用MySQL数据库服务器编写应用程序的专业人士而设计。获得认证意味着您已熟练掌握全球最受欢迎的开源数据库的专业知识与技能,将帮助您在激烈的竞争中脱颖而出。

获取上述认证时培训虽然并非必要条件,但甲骨文大学为您提供的培训资源能够帮助您为认证考试做好更充分的准备。

  • MySQL for Developers 培训课程—— 强烈推荐参加本课程,有助于您成功通过Developer考试。您将在课程中学习所有与使用MySQL数据库服务器编写应用程序相关的知识、技能和工具等。
  • MySQL for Database Administrators培训课程——强烈推荐参加本课程,有助于您成功通过DBA认证考试。您将在课程中学习使用MySQL的所有功能,以发挥网页、云计算和嵌入式应用的最大优势。

立即注册考试1Z0-882或1Z0-883,请访问 pearsonvue.com/oracle

快速链接:

沪ICP备14014813号

沪公网安备 31010802001379号