MySQL数据库innodb启动失败无法重启的解决方法
问题介绍
电脑在使用过程中死机，重启后发现mysql没有启动成功，查看错误日志发现是innodb出现问题导致mysql启动失败。
错误日志
$mysql.serverstart StartingMySQL .ERROR!TheserverquitwithoutupdatingPIDfile(/usr/local/var/mysql/fdipzonedeMacBook-Air.local.pid). 22:08:37mysqld_safeStartingmysqlddaemonwithdatabasesfrom/usr/local/var/mysql 2016-04-2322:08:380[Warning]TIMESTAMPwithimplicitDEFAULTvalueisdeprecated.Pleaseuse--explicit_defaults_for_timestampserveroption(seedocumentationformoredetails). 2016-04-2322:08:380[Note]/usr/local/Cellar/mysql/5.6.24/bin/mysqld(mysqld5.6.24)startingasprocess3604... 2016-04-2322:08:383604[Warning]Settinglower_case_table_names=2becausefilesystemfor/usr/local/var/mysql/iscaseinsensitive 2016-04-2322:08:383604[Note]Plugin'FEDERATED'isdisabled. 2016-04-2322:08:383604[Note]InnoDB:Usingatomicstorefcountbufferpoolpages 2016-04-2322:08:383604[Note]InnoDB:TheInnoDBmemoryheapisdisabled 2016-04-2322:08:383604[Note]InnoDB:Mutexesandrw_locksuseGCCatomicbuiltins 2016-04-2322:08:383604[Note]InnoDB:Memorybarrierisnotused 2016-04-2322:08:383604[Note]InnoDB:Compressedtablesusezlib1.2.3 2016-04-2322:08:383604[Note]InnoDB:UsingCPUcrc32instructions 2016-04-2322:08:383604[Note]InnoDB:Initializingbufferpool,size=128.0M 2016-04-2322:08:383604[Note]InnoDB:Completedinitializationofbufferpool 2016-04-2322:08:383604[Note]InnoDB:HighestsupportedfileformatisBarracuda. 2016-04-2322:08:383604[Note]InnoDB:Logscanprogressedpastthecheckpointlsn68929933440 2016-04-2322:08:383604[Note]InnoDB:Databasewasnotshutdownnormally! 2016-04-2322:08:383604[Note]InnoDB:Startingcrashrecovery. 2016-04-2322:08:383604[Note]InnoDB:Readingtablespaceinformationfromthe.ibdfiles... 2016-04-2322:08:383604[ERROR]InnoDB:checksummismatchintablespace./test_user/user_recommend_code#P#pmax.ibd(tabletest_user/user_recommend_code#P#pmax) 2016-04-2322:08:383604[Note]InnoDB:Pagesize:1024Pagestoanalyze:64 2016-04-2322:08:383604[Note]InnoDB:Pagesize:1024,Possiblespace_idcount:0 2016-04-2322:08:383604[Note]InnoDB:Pagesize:2048Pagestoanalyze:48 2016-04-2322:08:383604[Note]InnoDB:Pagesize:2048,Possiblespace_idcount:0 2016-04-2322:08:383604[Note]InnoDB:Pagesize:4096Pagestoanalyze:24 2016-04-2322:08:383604[Note]InnoDB:Pagesize:4096,Possiblespace_idcount:0 2016-04-2322:08:383604[Note]InnoDB:Pagesize:8192Pagestoanalyze:12 2016-04-2322:08:383604[Note]InnoDB:Pagesize:8192,Possiblespace_idcount:0 2016-04-2322:08:383604[Note]InnoDB:Pagesize:16384Pagestoanalyze:6 2016-04-2322:08:383604[Note]InnoDB:VALID:space:2947354page_no:3page_size:16384 2016-04-2322:08:383604[Note]InnoDB:Pagesize:16384,Possiblespace_idcount:1 2016-04-2322:08:383604[Note]InnoDB:space_id:2947354,Numberofpagesmatched:1/1(16384) 2016-04-2322:08:383604[Note]InnoDB:Chosenspace:2947354 2016-04-2322:08:383604[Note]InnoDB:Restoringpage0oftablespace2947354 2016-04-2322:08:383604[Warning]InnoDB:Doublewritedoesnothavepage_no=0ofspace:2947354 2016-04-2322:08:387fff79b9e300InnoDB:Operatingsystemerrornumber2inafileoperation. InnoDB:Theerrormeansthesystemcannotfindthepathspecified. InnoDB:IfyouareinstallingInnoDB,rememberthatyoumustcreate InnoDB:directoriesyourself,InnoDBdoesnotcreatethem. InnoDB:Error:couldnotopensingle-tabletablespacefile./test_user/user_recommend_code#P#pmax.ibd InnoDB:Wedonotcontinuethecrashrecovery,becausethetablemaybecome InnoDB:corruptifwecannotapplythelogrecordsintheInnoDBlogtoit. InnoDB:Tofixtheproblemandstartmysqld: InnoDB:1)Ifthereisapermissionprobleminthefileandmysqldcannot InnoDB:openthefile,youshouldmodifythepermissions. InnoDB:2)Ifthetableisnotneeded,oryoucanrestoreitfromabackup, InnoDB:thenyoucanremovethe.ibdfile,andInnoDBwilldoanormal InnoDB:crashrecoveryandignorethattable. InnoDB:3)Ifthefilesystemorthediskisbroken,andyoucannotremove InnoDB:the.ibdfile,youcansetinnodb_force_recovery>0inmy.cnf InnoDB:andforceInnoDBtocontinuecrashrecoveryhere.
解决方法
1.如果数据不重要或已经有备份，只需要恢复mysql启动
进入mysql目录，一般是：/usr/local/var/mysql/
删除ib_logfile*
删除ibdata*
删除所有数据库物理目录（例如数据库为test_db,则执行rm-rftest_db）
重启动mysql
重新建立数据库或使用备份覆盖
2.如果数据很重要且没有备份
可以使用innodb_force_recovery参数，使mysqld跳过恢复步骤，启动mysqld，将数据导出然后重建数据库。
innodb_force_recovery可以设置为1-6，大的数字包含前面所有数字的影响
1、(SRV_FORCE_IGNORE_CORRUPT):忽略检查到的corrupt页。
2、(SRV_FORCE_NO_BACKGROUND):阻止主线程的运行，如主线程需要执行fullpurge操作，会导致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):不执行前滚的操作。
在my.cnf(windows是my.ini)中加入
innodb_force_recovery=6 innodb_purge_thread=0
重启mysql
这时只可以执行select,create,drop操作，但不能执行insert,update,delete操作
执行逻辑导出，完成后将innodb_force_recovery=0，innodb_purge_threads=1，然后重建数据库，最后把导出的数据重新导入
总结
以上就是这篇文章的全部内容，希望能对大家学习或者使用mysql的时候有所帮助，如果有疑问大家可以留言交流，谢谢大家对毛票票的支持。