InnoDB存储引擎 - 常见问题修复

错误1：InnoDB: Error: page 19 log sequence number 2363194248

InnoDB: is in the future! Current system log sequence number 78250719.

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

InnoDB: tablespace but not the InnoDB log files.

解决方法: 在server没有任何备份的情况下，只能强迫innodb自己恢复

you can add the following line to the [mysqld] section of your option file before restarting the server:

[mysqld]
innodb_force_recovery = 1

 innodb_force_recovery 有 0-6 六个值，不同的值有不同的功能

  0  by default (normal startup without forced recovery).

• 1 (SRV_FORCE_IGNORE_CORRUPT)

  Lets the server run even if it detects a corrupt page. Tries to make SELECT * FROM tbl_name jump over corrupt index records and pages, which helps in dumping tables.

• 2 (SRV_FORCE_NO_BACKGROUND)

  Prevents the master thread and any purge threads from running. If a crash would occur during the purgeoperation, this recovery value prevents it.

• 3 (SRV_FORCE_NO_TRX_UNDO)

  Does not run transaction rollbacks after crash recovery.

• 4 (SRV_FORCE_NO_IBUF_MERGE)

  Prevents insert buffer merge operations. If they would cause a crash, does not do them. Does not calculate tablestatistics. This value can permanently corrupt data files. After using this value, be prepared to drop and recreate all secondary indexes.

• 5 (SRV_FORCE_NO_UNDO_LOG_SCAN)

  Does not look at undo logs when starting the database: InnoDB treats even incomplete transactions as committed. This value can permanently corrupt data files.

• 6 (SRV_FORCE_NO_LOG_REDO)

  Does not do the redo log roll-forward in connection with recovery. This value can permanently corrupt data files. Leaves database pages in an obsolete state, which in turn may introduce more corruption into B-trees and other database structures.

这些是从mysql官方的解释. http://dev.mysql.com/doc/refman/5.5/en/forcing-innodb-recovery.html

据说mysql第三方Percona有一个更有效率的恢复工具

Percona has a Data Recovery Toolkit that would do all this way more efficiently than I am saying it.

错误2: How to safely change MySQL innodb variable ‘innodb_log_file_size‘?

 编辑/etc/my.cnf 内容如下

     innodb_buffer_pool_size=2G

     innodb_log_file_size=100M

然后重启mysql，错误如下：

110216 9:48:41 InnoDB: Initializing buffer pool, size = 128.0M
110216 9:48:41 InnoDB: Completed initialization of buffer pool
InnoDB: Error: log file ./ib_logfile0 is of different size 0 5242880 bytes
InnoDB: than specified in the .cnf file 0 33554432 bytes!
110216 9:48:41 [ERROR] Plugin ‘InnoDB‘ init function returned error.
110216 9:48:41 [ERROR] Plugin ‘InnoDB‘ registration as a STORAGE ENGINE failed.

解决方法：          

1. mysql -uroot -p... -e"SET GLOBAL innodb_fast_shutdown = 0"

2. service mysql stop

3. rm -f /var/lib/mysql/ib_logfile[01]

4. service mysql start

看到网上一些文章，说最好把这个选项也加上

SET GLOBAL innodb_max_dirty_pages_pct = 0; （dirty page听我同事解释说是内存数据）

By default, innodb_max_dirty_pages_pct is 75 (MySQL 5.5+) or 90 (prior to MySQL 5.5). Setting this to zero keeps the number of dirty pages under 1% of the InnoDB Buffer Pool. Performing service mysql stop, does this anyway. In addition, a shutdown will finish up any remaining items in the redo log. 

我在用google搜索，发现下面这个链接，有很多mysql问题的汇总 http://dba.stackexchange.com/search?q=ib_logfile0

本文出自 “the-way-to-cloud” 博客，请务必保留此出处http://iceyao.blog.51cto.com/9426658/1568712