从ibdata1恢复mysql数据库

Question

我有一个客户机，它似乎从本地机器上丢失了所有mysql数据库。他们在Mac上，我有点不熟悉，我在Ubuntu上。数据库文件夹中没有.MYD或.MYI文件，只有.frm文件。我让他们将mysql和sight文件夹(我们需要的数据库)以及ibdata1、ib_logfile0和ib_logfile1文件拉链起来。我为mysql创建了第二个文件夹/var/lib/mysql 2，并将文件和文件夹移到其中。我将新文件夹和文件切换到mysql: mysql，编辑/etc/mysql/my.cnf指向新文件夹，编辑/etc/parmor.d/usr.sbin.mysqld，然后重新启动apparmor和mysql。但是，我在mysql错误日志中得到了以下错误：

130308 17:38:16 [Note] Plugin 'FEDERATED' is disabled.
130308 17:38:16  InnoDB: Initializing buffer pool, size = 8.0M
130308 17:38:16  InnoDB: Completed initialization of buffer pool
InnoDB: The log sequence number in ibdata files does not match
InnoDB: the log sequence number in the ib_logfiles!
130308 17:38:16  InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer...
130308 17:38:16  InnoDB: Error: space id and page n:o stored in the page
InnoDB: read in are 0:589824, should be 0:7!
130308 17:38:16  InnoDB: Error: page 589824 log sequence number 786432 0
InnoDB: is in the future! Current system log sequence number 0 63932940.
InnoDB: Your database may be corrupt or you may have copied the InnoDB
InnoDB: tablespace but not the InnoDB log files. See
InnoDB: http://dev.mysql.com/doc/refman/5.1/en/forcing-innodb-recovery.html
InnoDB: for more information.
InnoDB: Database page corruption on disk or a failed
InnoDB: file read of page 7.
InnoDB: You may have to recover from a backup.
130308 17:38:16  InnoDB: Page dump in ascii and hex (16384 bytes):
 len 16384; hex 0008000000090000000a0000000b0000000c00000000000000000000000202720000 (snipped because this goes on for a while)
                                                                                 Tg 9  <o q                      E                i F     /     D                ;InnoDB: End of page dump
130308 17:38:16  InnoDB: Page checksum 4146777650, prior-to-4.0.14-form checksum 1800374066
InnoDB: stored checksum 524288, prior-to-4.0.14-form stored checksum 0
InnoDB: Page lsn 786432 0, low 4 bytes of lsn at page end 0
InnoDB: Page number (if stored to page already) 589824,
InnoDB: space id (if created with >= MySQL-4.1.1 and stored already) 0
InnoDB: Page may be a freshly allocated page
InnoDB: Database page corruption on disk or a failed
InnoDB: file read of page 7.
InnoDB: You may have to recover from a backup.
InnoDB: It is also possible that your operating
InnoDB: system has corrupted its own file cache
InnoDB: and rebooting your computer removes the
InnoDB: error.
InnoDB: If the corrupt page is an index page
InnoDB: you can also try to fix the corruption
InnoDB: by dumping, dropping, and reimporting
InnoDB: the corrupt table. You can use CHECK
InnoDB: TABLE to scan your table for corruption.
InnoDB: See also http://dev.mysql.com/doc/refman/5.1/en/forcing-innodb-recovery.html
InnoDB: about forcing recovery.
InnoDB: Ending processing because of a corrupt database page.

我尝试将innodb_force_recovery =4添加到my.cnf文件中，该文件生成一系列不同的错误：

130308 17:48:30 [Note] Plugin 'FEDERATED' is disabled.
130308 17:48:30  InnoDB: Initializing buffer pool, size = 8.0M
130308 17:48:30  InnoDB: Completed initialization of buffer pool
InnoDB: The log sequence number in ibdata files does not match
InnoDB: the log sequence number in the ib_logfiles!
130308 17:48:30  InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer...
130308 17:48:30  InnoDB: Error: space id and page n:o stored in the page
InnoDB: read in are 0:589824, should be 0:7!
130308 17:48:30  InnoDB: Error: page 589824 log sequence number 786432 0
InnoDB: is in the future! Current system log sequence number 0 63932940.
InnoDB: Your database may be corrupt or you may have copied the InnoDB
InnoDB: tablespace but not the InnoDB log files. See
InnoDB: http://dev.mysql.com/doc/refman/5.1/en/forcing-innodb-recovery.html
InnoDB: for more information.
InnoDB: Database page corruption on disk or a failed
InnoDB: file read of page 7.
InnoDB: You may have to recover from a backup.

再来一堆，如果有帮助的话，我可以提供。任何关于从这里尝试什么的建议将不胜感激，谢谢。

编辑:我尝试遵循这里的步骤，但在让mysql使用他使用的命令行序列运行时遇到了问题：

http://blog.shiraj.com/2012/10/extract-data-from-mysql-ibdata1-data-file/

Answer 1

以下内容适用于我的：

• 在您的innodb_force_recovery中设置my.cnf =1
• 尝试重新启动mysqld。如果没有，请重复步骤1并将每个步骤的innodb_force_recovery递增，直到成功为止。使用指南帮助您理解每次递增时所发生的事情：http://dev.mysql.com/doc/refman/5.0/en/forcing-innodb-recovery.html
• 运行mysqld之后，尝试转储所有数据库。

mysqldump -u root -p --所有数据库> /tmp/mysqldump-all.sql

• 如果这不成功，您必须首先在数据库级别上尝试

mysqldump -u root -p --数据库db_name > mysqldump-db_name.sql

• 如果这不成功，您必须在表级别上尝试

从table_name选择*到OUTFILE '/tmp/table_name.sql‘

• 其中一个成功并导出所有db或所有表后，停止mysqld
• 移动您的ib_logfile* > ib_logfile*.bak。它们通常位于mysql数据目录中。
• 如果在第一步中增加了innodb_force_recovery => 4，则需要将其设置为4以下。从5.6.15开始，innodb_force_recovery设置为4或更高位置的InnoDB处于只读模式。
• 启动mysqld服务器
• 导入导出的数据库或表

mysql -u根-p < /tmp/mysqldump-all.sql

• 增加innodb_force_recovery => 1
• 重新启动mysqld服务器

Answer 2

这是为我工作的：

1. innodb_force_recovery => 2
2. 使用MySQL登录到HeidiSQL服务器并转储所有数据库
3. 重新安装MySQL服务器并从转储文件导入所有数据库。