MySQL 从ibdata1恢复mysql数据库
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15304708/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
recover mysql database from ibdata1
提问by Michael VanDeMar
I have a client who appears to have lost all of their mysql databases from their local machine. They are on a Mac, which I am somewhat unfamiliar with and I am on Ubuntu. There were no .MYD or .MYI files in the database folder, only .frm ones. I had them zip up the mysql and sight folders (with sight being the database we need), and the ibdata1, ib_logfile0, and ib_logfile1 files. I created a second folder for mysql, /var/lib/mysql2, and moved the files and folders into there. I chowned the new folder and files to mysql:mysql, edited /etc/mysql/my.cnf to point to the new folder, edited /etc/apparmor.d/usr.sbin.mysqld, and then restarted apparmor and mysql. However, I am getting the following errors in the mysql error log:
我有一个客户似乎已经从他们的本地机器上丢失了所有的 mysql 数据库。他们在 Mac 上，我有点不熟悉，我在 Ubuntu 上。数据库文件夹中没有 .MYD 或 .MYI 文件，只有 .frm 文件。我让他们压缩 mysql 和sight 文件夹（其中sight 是我们需要的数据库）以及ibdata1、ib_logfile0 和ib_logfile1 文件。我为 mysql 创建了第二个文件夹 /var/lib/mysql2，并将文件和文件夹移动到那里。我chown了新文件夹和文件到mysql:mysql，编辑/etc/mysql/my.cnf指向新文件夹，编辑/etc/apparmor.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.
I have tried adding innodb_force_recovery = 4 to the my.cnf file, which generates a whole slew of different errors:
我曾尝试将 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.
and a whole bunch more, which I can supply if it helps. Any suggestions on what to try from here would be greatly appreciated, thanks.
还有一大堆，如果有帮助，我可以提供。任何关于从这里尝试什么的建议将不胜感激，谢谢。
Edit: I tried following the steps here, but was having issues getting mysql to run using the command line sequence he used:
编辑：我尝试按照此处的步骤操作，但在使用他使用的命令行序列运行 mysql 时遇到问题：
http://blog.shiraj.com/2012/10/extract-data-from-mysql-ibdata1-data-file/
http://blog.shiraj.com/2012/10/extract-data-from-mysql-ibdata1-data-file/
回答by zmonteca
The following worked for my:
以下对我有用：
Set innodb_force_recovery = 1 in your my.cnf
Try and get your mysqld to restart. If not, repeat step #1 and increment innodb_force_recovery by each until successful. Use the guide to help you understand what's happening each time you increment it: http://dev.mysql.com/doc/refman/5.0/en/forcing-innodb-recovery.html
Once mysqld is running, try and dump all your databases
在 my.cnf 中设置 innodb_force_recovery = 1
尝试让您的 mysqld 重新启动。如果没有，请重复步骤 #1 并按每个递增 innodb_force_recovery 直到成功。使用指南帮助您了解每次增加时发生的情况：http: //dev.mysql.com/doc/refman/5.0/en/forcing-innodb-recovery.html
mysqld 运行后，尝试转储所有数据库
mysqldump -u root -p --all-databases > /tmp/mysqldump-all.sql
mysqldump -u root -p --all-databases > /tmp/mysqldump-all.sql
- If that isn't successful, you'll have to try it first on the database level
- 如果这不成功，您必须首先在数据库级别尝试
mysqldump -u root -p --databases db_name > mysqldump-db_name.sql
mysqldump -u root -p --databases db_name > mysqldump-db_name.sql
- If that isn't successful, you'll have to try it on the table level
- 如果这不成功，您将不得不在表级别上尝试
SELECT * FROM table_name INTO OUTFILE '/tmp/table_name.sql'
SELECT * FROM table_name INTO OUTFILE '/tmp/table_name.sql'
Once one of those is successful and either all your db's or all your tables are exported, stop the mysqld
Move your ib_logfile* > ib_logfile*.bak. These are typically in your mysql data directory.
If in the first step you incremented your innodb_force_recovery => 4, you need to set it below 4. As of 5.6.15, an innodb_force_recovery setting of 4 or greater places InnoDB in read-only mode.
Start the mysqld server
Import your exported databases or tables
一旦其中一个成功并且导出了所有数据库或所有表，请停止 mysqld
移动您的 ib_logfile* > ib_logfile*.bak。这些通常在您的 mysql 数据目录中。
如果在第一步中您增加了 innodb_force_recovery => 4，则需要将其设置为低于 4。从 5.6.15 开始，4 或更大的 innodb_force_recovery 设置将 InnoDB 置于只读模式。
启动mysqld服务器
导入导出的数据库或表
mysql -u root -p < /tmp/mysqldump-all.sql
mysql -u root -p < /tmp/mysqldump-all.sql
Incremented your innodb_force_recovery => 1
Restart the mysqld server
增加你的 innodb_force_recovery => 1
重启mysqld服务器
