MySQL 启动后随即关闭或启动失败 [ innodb表空间损坏、innodb_force_recovery强制启动 ]

线上数据库服务器上mysql运行一段时间了,突然出现了异常:启动mysql后随即就又关闭了,mysql服务启动失败!!

查看mysql错误日志如下:

160920 22:41:41 mysqld_safe Starting mysqld daemon with databases from /home/MysqlData/
2016-09-20 22:41:41 0 [Note] /Data/app/mysql5.6.25/bin/mysqld (mysqld 5.6.25-log) starting as process 32372 ...
2016-09-20 22:41:42 32372 [Note] Plugin 'FEDERATED' is disabled.
2016-09-20 22:41:42 32372 [Warning] option 'innodb-write-io-threads': unsigned value 1000 adjusted to 64
2016-09-20 22:41:42 32372 [Warning] option 'innodb-read-io-threads': unsigned value 1000 adjusted to 64
2016-09-20 22:41:42 32372 [Note] InnoDB: Using atomics to ref count buffer pool pages
2016-09-20 22:41:42 32372 [Note] InnoDB: The InnoDB memory heap is disabled
2016-09-20 22:41:42 32372 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2016-09-20 22:41:42 32372 [Note] InnoDB: Memory barrier is not used
2016-09-20 22:41:42 32372 [Note] InnoDB: Compressed tables use zlib 1.2.3
2016-09-20 22:41:42 32372 [Note] InnoDB: Using CPU crc32 instructions
2016-09-20 22:41:42 32372 [Note] InnoDB: Initializing buffer pool, size = 1.0G
2016-09-20 22:41:42 32372 [Note] InnoDB: Completed initialization of buffer pool
2016-09-20 22:41:42 32372 [Note] InnoDB: Highest supported file format is Barracuda.
2016-09-20 22:41:42 32372 [Note] InnoDB: Log scan progressed past the checkpoint lsn 20293587957
2016-09-20 22:41:42 32372 [Note] InnoDB: Database was not shutdown normally!
2016-09-20 22:41:42 32372 [Note] InnoDB: Starting crash recovery.
2016-09-20 22:41:42 32372 [Note] InnoDB: Reading tablespace information from the .ibd files...
2016-09-20 22:41:42 32372 [Note] InnoDB: Restoring possible half-written data pages
2016-09-20 22:41:42 32372 [Note] InnoDB: from the doublewrite buffer...
InnoDB: Doing recovery: scanned up to log sequence number 20293596130
2016-09-20 22:41:42 32372 [Note] InnoDB: Starting an apply batch of log records to the database...
InnoDB: Progress in percent: 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99
InnoDB: Apply batch completed
InnoDB: Last MySQL binlog file position 0 136254, file name mysql-bin.000013
2016-09-20 22:41:43 32372 [Note] InnoDB: 128 rollback segment(s) are active.
2016-09-20 22:41:43 32372 [Note] InnoDB: Waiting for purge to start
2016-09-20 22:41:43 7f77a9edd700  InnoDB: Assertion failure in thread 140151928772352 in file trx0purge.cc line 699
InnoDB: Failing assertion: purge_sys->iter.trx_no <= purge_sys->rseg->last_trx_no
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.
02:41:43 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.

分析日志后发现,数据库无法重启的原因是因为ibdata1文件 (即共享表空间) 损坏,重启后无法正常恢复。

解决办法:
需要跳过恢复步骤,修改my.cnf文件,在my.cnf中的[mysqld]中添加:
innodb_force_recovery = 6
innodb_purge_threads = 1

修改后,接着重启Mysql服务,发现可正常启动。如果还是无法启动,则就需要删除mysql数据目录下的 "ibdata1、ib_logfile*" 等文件 (删除前,提前做好备份),然后再做Mysql服务启动操作!!

######################## innodb_force_recovery参数说明 ########################
MySQL数据库当innodb表空间损坏时(如ibdata1文件损坏),尝试启动Mysql服务失败。这个时候可以使用innodb_force_recovery参数进行强制启动!!下面说下这个参数的用法:

innodb_force_recovery参数解释:
-------------------------------------------------------------------------------------------------------------
innodb_force_recovery影响整个InnoDB存储引擎的恢复状况,默认值为0,表示当需要恢复时执行所有的恢复操作!!
当不能进行有效的恢复操作时,Mysql有可能无法启动,并记录下错误日志。

innodb_force_recovery可以设置为1-6,大的数字包含前面所有数字的影响。
当该参数的数值设置大于0后,可以对表进行select,create,drop操作,但insert,update或者delete这类操作是不允许的。

innodb_force_recovery=0   表示当需要恢复时执行所有的恢复操作;
innodb_force_recovery=1   表示忽略检查到的corrupt页;
innodb_force_recovery=2   表示阻止主线程的运行,如主线程需要执行full purge操作,会导致crash;
innodb_force_recovery=3   表示不执行事务回滚操作;
innodb_force_recovery=4   表示不执行插入缓冲的合并操作;
innodb_force_recovery=5   表示不查看重做日志,InnoDB存储引擎会将未提交的事务视为已提交;
innodb_force_recovery=6   表示不执行前滚的操作,强制重启!
-------------------------------------------------------------------------------------------------------------

记一次事故:
线上Mysql环境采用一主两从模式,突然一天上午发现主从库的Mysql服务都启动失败,最后排查是Mysql共享表空间ibdata1文件损坏了。

恢复记录:
1) 在主从库的Mysql配置文件my.cnf中添加
# vim /etc/my.cnf
innodb_force_recovery=6 

2) 启动mysql服务
# service mysqld start

3)如果启动成功后,再将my.cnf文件中的"innodb_force_recovery=6"配置去掉,然后再次尝试mysql服务的重启,应该OK。

在主从库出现这种情况时,如果配置文件里之前就有这个参数,则尝试将该参数值修改为0或6,依次尝试重启。
上一篇:Oracle中如何查找未使用绑定变量的SQL语句?


下一篇:innodb_force_recovery参数分析