MySQL之——崩溃-修复损坏的innodb：innodb_force_recovery

r囧r小猫 2022-05-16 11:06 182阅读 0赞

# MySQL之——崩溃-修复损坏的innodb：innodb\_force\_recovery #

[转载：http://blog.csdn.net/l1028386804/article/details/77199194][http_blog.csdn.net_l1028386804_article_details_77199194]

### 一、问题描述 ###

今天在线运行的一个mysql崩溃了。  
查看错误日志，如下：

`-----------------------------------------`

`161108 11:36:45 mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/var`

`2017-08-15 11:36:46 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).`

`2017-08-15 11:36:46 5497 [Note] Plugin 'FEDERATED' is disabled.`

`2017-08-15 11:36:46 7f11c48e1720 InnoDB: Warning: Using innodb_additional_mem_pool_size is DEPRECATED. This option may be removed in future releases, together with the option innodb_use_sys_malloc and with the InnoDB's internal memory allocator.`

`2017-08-15 11:36:46 5497 [Note] InnoDB: Using atomics to ref count buffer pool pages`

`2017-08-15 11:36:46 5497 [Note] InnoDB: The InnoDB memory heap is disabled`

`2017-08-15 11:36:46 5497 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins`

`2017-08-15 11:36:46 5497 [Note] InnoDB: Memory barrier is not used`

`2017-08-15 11:36:46 5497 [Note] InnoDB: Compressed tables use zlib 1.2.3`

`2017-08-15 11:36:46 5497 [Note] InnoDB: Using CPU crc32 instructions`

`2017-08-15 11:36:46 5497 [Note] InnoDB: Initializing buffer pool, size = 16.0M`

`2017-08-15 11:36:46 5497 [Note] InnoDB: Completed initialization of buffer pool`

`InnoDB: Database page corruption on disk or a failed`

`InnoDB: file read of page 5.`

`InnoDB: You may have to recover from a backup.`

`2017-08-15 11:36:46 7f11c48e1720 InnoDB: Page dump in ascii and hex (16384 bytes):`

`len 16384; hex 7478d078000000050000000000000000000000000f271f4d000700000000000000000000000000000000001b4000000000000000000200f20000000000000006000000000000002d000000000000002e000000000000002f0000000000000030000000000（省略很多类似代码）`

`InnoDB: End of page dump`

`2017-08-15 11:36:46 7f11c48e1720 InnoDB: uncompressed page, stored checksum in field1 1954074744, calculated checksums for field1: crc32 993334256, innodb 2046145943, none 3735928559, stored checksum in field2 1139795846, calculated checksums for field2: crc32 993334256, innodb 1606613742, none 3735928559, page LSN 0 254222157, low 4 bytes of LSN at page end 254221236, page number (if stored to page already) 5, space id (if created with >= MySQL-4.1.1 and stored already) 0`

`InnoDB: Page may be a transaction system page`

`InnoDB: Database page corruption on disk or a failed`

`InnoDB: file read of page 5.`

`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.6/en/forcing-innodb-recovery.html`

`InnoDB: about forcing recovery.`

`InnoDB: Ending processing because of a corrupt database page.`

`2017-08-15 11:36:46 7f11c48e1720 InnoDB: Assertion failure in thread 139714288817952 in file buf0buf.cc line 4201`

`InnoDB: We intentionally generate a memory trap.`

`InnoDB: Submit a detailed bug report to http://bugs.mysql.com.`

`InnoDB: If you get repeated assertion failures or crashes, even`

`InnoDB: immediately after the mysqld startup, there may be`

`InnoDB: corruption in the InnoDB tablespace. Please refer to`

`InnoDB: http://dev.mysql.com/doc/refman/5.6/en/forcing-innodb-recovery.html`

`InnoDB: about forcing recovery.`

`03:36:46 UTC - mysqld got signal 6 ;`

`This could be because you hit a bug. It is also possible that this binary`

`or one of the libraries it was linked against is corrupt, improperly built,`

`or misconfigured. This error can also be caused by malfunctioning hardware.`

`We will try our best to scrape up some info that will hopefully help`

`diagnose the problem, but since we have already crashed,`

`something is definitely wrong and this may fail.`

`key_buffer_size=16777216`

`read_buffer_size=262144`

`max_used_connections=0`

`max_threads=1000`

`thread_count=0`

`connection_count=0`

`It is possible that mysqld could use up to`

`key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 798063 K bytes of memory`

`Hope that's ok; if not, decrease some variables in the equation.`

`Thread pointer: 0x0`

`Attempting backtrace. You can use the following information to find out`

`where mysqld died. If you see no messages after this, something went`

`terribly wrong...`

`stack_bottom = 0 thread_stack 0x40000`

`/usr/local/mysql/bin/mysqld(my_print_stacktrace+0x35)[0x8e64b5]`

`/usr/local/mysql/bin/mysqld(handle_fatal_signal+0x41b)[0x652fbb]`

`/lib64/libpthread.so.0(+0xf7e0)[0x7f11c44c77e0]`

`/lib64/libc.so.6(gsignal+0x35)[0x7f11c315d625]`

`/lib64/libc.so.6(abort+0x175)[0x7f11c315ee05]`

`/usr/local/mysql/bin/mysqld[0xa585c5]`

`/usr/local/mysql/bin/mysqld[0xa6c7b4]`

`/usr/local/mysql/bin/mysqld[0xa6cbc7]`

`/usr/local/mysql/bin/mysqld[0xa5bce2]`

`/usr/local/mysql/bin/mysqld[0xa1e2ba]`

`/usr/local/mysql/bin/mysqld[0xa0bf60]`

`/usr/local/mysql/bin/mysqld[0x95a427]`

`/usr/local/mysql/bin/mysqld(_Z24ha_initialize_handlertonP13st_plugin_int+0x48)[0x58f788]`

`/usr/local/mysql/bin/mysqld[0x6e4a36]`

`/usr/local/mysql/bin/mysqld(_Z11plugin_initPiPPci+0xb3e)[0x6e826e]`

`/usr/local/mysql/bin/mysqld[0x582d85]`

`/usr/local/mysql/bin/mysqld(_Z11mysqld_mainiPPc+0x4d8)[0x587d18]`

`/lib64/libc.so.6(__libc_start_main+0xfd)[0x7f11c3149d5d]`

`/usr/local/mysql/bin/mysqld[0x57a019]`

`The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains`

`information that should help you find out what is causing the crash.`

`161108 11:36:46 mysqld_safe mysqld from pid file /usr/local/mysql/var/VM_241_49_centos.pid ended`

`------------------------------------------------------------------------------`

### 二、问题分析 ###

从日志中可以看出是innodb引擎出了问题。日志里提示到[ http://dev.mysql.com/doc/refman/5.6/en/forcing-innodb-recovery.html][http_dev.mysql.com_doc_refman_5.6_en_forcing-innodb-recovery.html]查看强制恢复的方法。在mysql的配置文件my.cnf里找到 \[mysqld\]字段下，添加 innodb\_force\_recovery=1：

1.  `[mysqld]`
2.  `innodb_force_recovery = 1`

如果innodb\_force\_recovery = 1不生效，则可尝试2——6几个数字  
然后重启mysql，重启成功。然后使用mysqldump或 pma 导出数据，执行修复操作等。修复完成后，把该参数注释掉，还原默认值0。  
配置文件的参数：innodb\_force\_recovery  
innodb\_force\_recovery影响整个InnoDB存储引擎的恢复状况。默认为0，表示当需要恢复时执行所有的恢复操作（即校验数据页/purge undo/insert buffer merge/rolling back&forward），当不能进行有效的恢复操作时，mysql有可能无法启动，并记录错误日志；  
innodb\_force\_recovery可以设置为1-6,大的数字包含前面所有数字的影响。当设置参数值大于0后，可以对表进行select,create,drop操作,但insert,update或者delete这类操作是不允许的。

*  1(SRV\_FORCE\_IGNORE\_CORRUPT):忽略检查到的corrupt页。
 *  2(SRV\_FORCE\_NO\_BACKGROUND):阻止主线程的运行，如主线程需要执行full purge操作，会导致crash。
 *  3(SRV\_FORCE\_NO\_TRX\_UNDO):不执行事务回滚操作。
 *  4(SRV\_FORCE\_NO\_IBUF\_MERGE):不执行插入缓冲的合并操作。
 *  5(SRV\_FORCE\_NO\_UNDO\_LOG\_SCAN):不查看重做日志，InnoDB存储引擎会将未提交的事务视为已提交。
 *  6(SRV\_FORCE\_NO\_LOG\_REDO):不执行前滚的操作。

### 三、解析方案 ###

一般修复方法参考：

第一种方法

建立一张新表：  
create table demo\_bak  \#和原表结构一样，只是把INNODB改成了MYISAM。  
把数据导进去  
insert into demo\_bak select \* from demo;  
删除掉原表：  
drop table demo;  
注释掉 innodb\_force\_recovery 之后，重启。  
重命名：  
rename table demo\_bak to demo;  
最后改回存储引擎：  
alter table demo engine = innodb

第二种方法

另一个方法是使用mysqldump将表格导出，然后再导回到InnoDB表中。这两种方法的结果是相同的。  
备份导出(包括结构和数据)：  
mysqldump -uroot -p123 test > test.sql  
还原方法1：  
use test;  
source test.sql  
还原方法2（系统命令行）：  
mysql -uroot -p123 test < test.sql;  
注意，CHECK TABLE命令在InnoDB数据库中基本上是没有用的。

第三种方法

1、配置my.cnf

配置innodb\_force\_recovery = 1或2——6几个数字，重启MySQL

2、导出数据脚本

mysqldump -uroot -p123 test > test.sql  
导出SQL脚本。或者用Navicat将所有数据库/表导入到其他服务器的数据库中。  
注意：这里的数据一定要备份成功。然后删除原数据库中的数据。

3、删除ib\_logfile0、ib\_logfile1、ibdata1

备份MySQL数据目录下的ib\_logfile0、ib\_logfile1、ibdata1三个文件，然后将这三个文件删除

4、配置my.cnf

将my.cnf中innodb\_force\_recovery = 1或2——6几个数字这行配置删除或者配置为innodb\_force\_recovery = 0，重启MySQL服务

5、将数据导入MySQL数据库

mysql -uroot -p123 test < test.sql; 或者用Navicat将备份的数据导入到数据库中。  
此种方法下要注意的问题：  
  1、ib\_logfile0、ib\_logfile1、ibdata1这三个文件一定要先备份后删除；  
  2、一定要确认原数据导出成功了  
  3、当数据导出成功后，删除原数据库中的数据时，如果提示不能删除，可在命令行进入MySQL的数据目录，手动删除相关数据库的文件夹或者数据库文件夹下的数据表文件，前提是数据一定导出或备份成功。

[http_blog.csdn.net_l1028386804_article_details_77199194]: http://blog.csdn.net/l1028386804/article/details/77199194
[http_dev.mysql.com_doc_refman_5.6_en_forcing-innodb-recovery.html]: http://dev.mysql.com/doc/refman/5.6/en/forcing-innodb-recovery.html