完美解决mysql启动后随即关闭的问题(ibdata1文件损坏导致)
机房一台服务器上的mysql运行一段时间了,突然出现了一个很奇怪的现象:重启后无法恢复了!准确情况是:启动mysql后随即就又关闭了。
查看mysql错误日志如下:
16092022:41:41mysqld_safeStartingmysqlddaemonwithdatabasesfrom/home/MysqlData/
2016-09-2022:41:410[Note]/Data/app/mysql5.6.25/bin/mysqld(mysqld5.6.25-log)startingasprocess32372...
2016-09-2022:41:4232372[Note]Plugin'FEDERATED'isdisabled.
2016-09-2022:41:4232372[Warning]option'innodb-write-io-threads':unsignedvalue1000adjustedto64
2016-09-2022:41:4232372[Warning]option'innodb-read-io-threads':unsignedvalue1000adjustedto64
2016-09-2022:41:4232372[Note]InnoDB:Usingatomicstorefcountbufferpoolpages
2016-09-2022:41:4232372[Note]InnoDB:TheInnoDBmemoryheapisdisabled
2016-09-2022:41:4232372[Note]InnoDB:Mutexesandrw_locksuseGCCatomicbuiltins
2016-09-2022:41:4232372[Note]InnoDB:Memorybarrierisnotused
2016-09-2022:41:4232372[Note]InnoDB:Compressedtablesusezlib1.2.3
2016-09-2022:41:4232372[Note]InnoDB:UsingCPUcrc32instructions
2016-09-2022:41:4232372[Note]InnoDB:Initializingbufferpool,size=1.0G
2016-09-2022:41:4232372[Note]InnoDB:Completedinitializationofbufferpool
2016-09-2022:41:4232372[Note]InnoDB:HighestsupportedfileformatisBarracuda.
2016-09-2022:41:4232372[Note]InnoDB:Logscanprogressedpastthecheckpointlsn20293587957
2016-09-2022:41:4232372[Note]InnoDB:Databasewasnotshutdownnormally!
2016-09-2022:41:4232372[Note]InnoDB:Startingcrashrecovery.
2016-09-2022:41:4232372[Note]InnoDB:Readingtablespaceinformationfromthe.ibdfiles...
2016-09-2022:41:4232372[Note]InnoDB:Restoringpossiblehalf-writtendatapages
2016-09-2022:41:4232372[Note]InnoDB:fromthedoublewritebuffer...
InnoDB:Doingrecovery:scanneduptologsequencenumber20293596130
2016-09-2022:41:4232372[Note]InnoDB:Startinganapplybatchoflogrecordstothedatabase...
InnoDB:Progressinpercent:495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899
InnoDB:Applybatchcompleted
InnoDB:LastMySQLbinlogfileposition0136254,filenamemysql-bin.000013
2016-09-2022:41:4332372[Note]InnoDB:128rollbacksegment(s)areactive.
2016-09-2022:41:4332372[Note]InnoDB:Waitingforpurgetostart
2016-09-2022:41:437f77a9edd700InnoDB:Assertionfailureinthread140151928772352infiletrx0purge.ccline699
InnoDB:Failingassertion:purge_sys->iter.trx_no<=purge_sys->rseg->last_trx_no
InnoDB:Weintentionallygenerateamemorytrap.
InnoDB:Submitadetailedbugreporttohttp://bugs.mysql.com.
InnoDB:Ifyougetrepeatedassertionfailuresorcrashes,even
InnoDB:immediatelyafterthemysqldstartup,theremaybe
InnoDB:corruptionintheInnoDBtablespace.Pleasereferto
InnoDB:http://dev.mysql.com/doc/refman/5.6/en/forcing-innodb-recovery.html
InnoDB:aboutforcingrecovery.
02:41:43UTC-mysqldgotsignal6;
Thiscouldbebecauseyouhitabug.Itisalsopossiblethatthisbinary
oroneofthelibrariesitwaslinkedagainstiscorrupt,improperlybuilt,
ormisconfigured.Thiserrorcanalsobecausedbymalfunctioninghardware.
Wewilltryourbesttoscrapeupsomeinfothatwillhopefullyhelp
diagnosetheproblem,butsincewehavealreadycrashed,
somethingisdefinitelywrongandthismayfail.
分析日志后发现,数据库无法重启的原因是因为ibdata1文件损坏,重启后无法正常恢复。
解决办法:
需要跳过恢复步骤,修改my.cnf文件,在my.cnf中的[mysqld]中添加:
innodb_force_recovery=6
innodb_purge_threads=1
解释:
innodb_force_recovery可以设置为1-6,大的数字包含前面所有数字的影响。
具体数字对应的含义:
1-----(SRVFORCEIGNORECORRUPT):忽略检查到的corrupt页。
2-----(SRVFORCENOBACKGROUND):阻止主线程的运行,如主线程需要执行fullpurge操作,会导致crash。
3-----(SRVFORCENOTRXUNDO):不执行事务回滚操作。
4-----(SRVFORCENOIBUFMERGE):不执行插入缓冲的合并操作。
5-----(SRVFORCENOUNDOLOGSCAN):不查看重做日志,InnoDB存储引擎会将未提交的事务视为已提交。
6-----(SRVFORCENOLOG_REDO):不执行前滚的操作。
再次启动mysql就ok了~
如果还无法启动,则需要删除数据目录datafile下的ibdata1,ib_logfile*等文件。
启动后导出MySQL数据库,重新恢复即可。
以上这篇完美解决mysql启动后随即关闭的问题(ibdata1文件损坏导致)就是小编分享给大家的全部内容了,希望能给大家一个参考,也希望大家多多支持毛票票。