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

郑重声明:本站内容如果来自互联网及其他传播媒体,其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享,并不代表本站赞同其观点和对其真实性负责,也不构成任何其他建议。