MySQL InnoDB 问题 - InnoDB 注册失败

Question

我最近尝试将 MySQL 5.1 服务器升级到 5.7。当服务器无法启动时，我发现您必须先导出数据，然后再进行大量升级(二进制文件不再可用)，所以我回滚到 5.1 进行导出。

问题是，回到 5.1，InnoDB 将不再注册。在错误日志中我得到:

InnoDB: Error: log file ./ib_logfile0 is of different size 0 50331648 bytes
InnoDB: than specified in the .cnf file 0 5242880 bytes!
160822 17:05:12 [ERROR] Plugin 'InnoDB' init function returned error.
160822 17:05:12 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.

作为解决方法，我在 my.cnf 中设置了 innodb_log_file_size=50331648。再次重启并:

InnoDB: No valid checkpoint found.
InnoDB: If this error appears when you are creating an InnoDB database,
InnoDB: the problem may be that during an earlier attempt you managed
InnoDB: to create the InnoDB data files, but log file creation failed.
InnoDB: If that is the case, please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.1/en/error-creating-innodb.html

阅读该链接建议删除 InnoDB 创建的所有文件:所有 ibdata 文件和所有 ib_logfile 文件。我有一个 120 GB 的 ibdata 文件，我肯定不打算删除它，所以这是一个非启动器。我确实尝试过重命名日志文件，但服务器仍然无法启动。

这里有什么建议吗？

编辑:我还尝试重命名 iblog 文件。这会导致另一个错误，其中 mysql 建议设置 innodb_force_recovery=6。这样做会导致这些错误:

160823 12:17:32  InnoDB: Page checksum 271832187, prior-to-4.0.14-form checksum 315921779
InnoDB: stored checksum 401867329, prior-to-4.0.14-form stored checksum 401867329
InnoDB: Page lsn 96 3891045697, low 4 bytes of lsn at page end 3891045697
InnoDB: Page number (if stored to page already) 966706,
InnoDB: space id (if created with >= MySQL-4.1.1 and stored already) 0
InnoDB: Page may be a system page
InnoDB: Database page corruption on disk or a failed
InnoDB: file read of page 966706.
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: Database page corruption on disk or a failed
InnoDB: file read of page 966707.
InnoDB: You may have to recover from a backup.
160823 12:17:32  InnoDB: Page dump in ascii and hex (16384 bytes):

Answer 1

我刚才遇到了类似的问题。我在同一台服务器上同时运行 MySQL 5.1 和 5.7。

对于 MySQL 5.1，默认的 innodb_log_file_size 是 5m。

mysql> show variables like 'innodb_log_file_size';

    +----------------------+---------+
    | Variable_name        | Value   |
    +----------------------+---------+
    | innodb_log_file_size | 5242880 |
    +----------------------+---------+
    1 row in set (0.00 sec)

而 MySQL 5.7 默认的 innodb_log_file_size 是 48m。

mysql> show variables like 'innodb_log_file_size';
+----------------------+----------+
| Variable_name        | Value    |
+----------------------+----------+
| innodb_log_file_size | 50331648 |
+----------------------+----------+
1 row in set (0.01 sec)

当将 MySQL 5.1 服务器升级到 5.7，然后降级到 5.1 时，ib_logfile0/1 文件发生了神奇的变化。无法识别日志文件的检查点。因此 innodb 无法使用日志文件执行正常恢复。我没有做深入的研究，所以我不能给出详细的解释。

解决方案

备份数据文件并重启MySQL

mv ibdata1 ibdata1.bak
mv ib_logfile0 ib_logfile0.bak
mv ib_logfile1 ib_logfile1.bak
service mysqld restart

修改my.cnf文件，添加如下行

innodb_force_recovery=6

恢复数据文件并重新启动

mv ibdata1.bak ibdata1

service mysqld restart

现在你会看到MySQL启动成功。 Ibdata1 可能无法正常工作。您可以使用 mysqldump 备份您的应用程序数据。然后删除 my.cnf 文件中的恢复行。同时删除 ibdata 和 logfile。重新启动 MySQL 并导入您的备份。

有点复杂，但这些对我有帮助。

故障

由于重新创建了重做日志文件，所以有一小段日志无法重做。