.jpg)
一、問(wèn)題日志
2017-08-31?14:18:05?4122?[Note]?InnoDB:?Database?was?not?shutdown?normally! 2017-08-31?14:18:05?4122?[Note]?InnoDB:?Starting?crash?recovery. 2017-08-31?14:18:05?4122?[Note]?InnoDB:?Reading?tablespace?information?from?the?.ibd?files... 2017-08-31?14:18:05?4122?[ERROR]?InnoDB:?Attempted?to?open?a?previously?opened?tablespace.?Previous?tablespace?dev/tb_test?uses?spac e?ID:?1?at?filepath:?./dev/tb_test.ibd.?Cannot?open?tablespace?mysql/innodb_table_stats?which?uses?space?ID:?1?at?filepath:?./mysql/ innodb_table_stats.ibd 2017-08-31?14:18:05?2ad861898590??InnoDB:?Operating?system?error?number?2?in?a?file?operation. InnoDB:?The?error?means?the?system?cannot?find?the?path?specified. InnoDB:?If?you?are?installing?InnoDB,?remember?that?you?must?create InnoDB:?directories?yourself,?InnoDB?does?not?create?them. InnoDB:?Error:?could?not?open?single-table?tablespace?file?./mysql/innodb_table_stats.ibd InnoDB:?We?do?not?continue?the?crash?recovery,?because?the?table?may?becomeInnoDB:?corrupt?if?we?cannot?apply?the?log?records?in?the?InnoDB?log?to?it. InnoDB:?To?fix?the?problem?and?start?mysqld: InnoDB:?1)?If?there?is?a?permission?problem?in?the?file?and?mysqld?cannot InnoDB:?open?the?file,?you?should?modify?the?permissions. InnoDB:?2)?If?the?table?is?not?needed,?or?you?can?restore?it?from?a?backup, InnoDB:?then?you?can?remove?the?.ibd?file,?and?InnoDB?will?do?a?normal InnoDB:?crash?recovery?and?ignore?that?table. InnoDB:?3)?If?the?file?system?or?the?disk?is?broken,?and?you?cannot?remove InnoDB:?the?.ibd?file,?you?can?set?innodb_force_recovery?>?0?in?my.cnf InnoDB:?and?force?InnoDB?to?continue?crash?recovery?here. 150126?14:18:06?mysqld_safe?mysqld?from?pid?file?/home/mysql/mysql_app/dbdata/liuyazhuang136.pid?ended
二、解決方案
1.在my.cnf中添加如下參數(shù)
在[mysqld]組中加入：
innodb_force_recovery=6
innodb_force_recovery參數(shù)解釋：
innodb_force_recovery影響整個(gè)InnoDB存儲(chǔ)引擎的恢復(fù)狀況，默認(rèn)值為0，表示當(dāng)需要恢復(fù)時(shí)執(zhí)行所有的恢復(fù)操作。
當(dāng)不能進(jìn)行有效的恢復(fù)操作時(shí)，mysql有可能無(wú)法啟動(dòng)，并記錄下錯(cuò)誤日志。
innodb_force_recovery可以設(shè)置為1-6,大的數(shù)字包含前面所有數(shù)字的影響。
當(dāng)設(shè)置參數(shù)值大于0后，可以對(duì)表進(jìn)行select,create,drop操作,但insert,update或者delete這類操作是不允許的。
1(SRV_FORCE_IGNORE_CORRUPT):忽略檢查到的corrupt頁(yè)
2(SRV_FORCE_NO_BACKGROUND):阻止主線程的運(yùn)行，如主線程需要執(zhí)行full purge操作，會(huì)導(dǎo)致crash
?3(SRV_FORCE_NO_TRX_UNDO):不執(zhí)行事務(wù)回滾操作。
4(SRV_FORCE_NO_IBUF_MERGE):不執(zhí)行插入緩沖的合并操作。
5(SRV_FORCE_NO_UNDO_LOG_SCAN):不查看重做日志，InnoDB存儲(chǔ)引擎會(huì)將未提交的事務(wù)視為已提交。
6(SRV_FORCE_NO_LOG_REDO):不執(zhí)行前滾的操作。
2.備份數(shù)據(jù)庫(kù)
$mysqldump?-h?192.168.209.136?-uroot?-p?dev?>?/home/mysql/dev.sql
3.刪除數(shù)據(jù)庫(kù)
$mysql?-h?192.168.209.136?-uroot?-p mysql>?drop?database?dev; ERROR?1051?(42S02):?Unknown?table?'dev.tb_test'
物理刪除tb_test對(duì)應(yīng)的frm和ibd文件
mysql>?drop?database?dev; Query?OK,?0?rows?affected?(0.00?sec)
4.創(chuàng)建數(shù)據(jù)庫(kù)
mysql>?create?database?dev; Query?OK,?1?row?affected?(0.03?sec)
5.去掉參數(shù)innodb_force_recovery
將之前設(shè)置的參數(shù)去掉后，重新啟動(dòng)數(shù)據(jù)庫(kù)
##innodb_force_recovery=6
6.導(dǎo)入數(shù)據(jù)
[mysql@liuyazhuang136?dev]$?mysql?-h?192.168.209.136?-uroot?-pmysql?dev/mysql/dev.sql Warning:?Using?a?password?on?the?command?line?interface?can?be?insecure. ERROR?1050?(42S01)?at?line?25:?Table?'`dev`.`tb_test`'?already?exists
提示表已經(jīng)存在，這是因?yàn)閷nnodb_force_recovery參數(shù)去掉后，數(shù)據(jù)庫(kù)會(huì)進(jìn)行回滾操作，會(huì)生成相應(yīng)的ibd文件，所有需要將該文件刪除掉.
刪除后重新導(dǎo)入
[mysql@liuyazhuang136?dev]$?mysql?-h?192.168.209.136?-uroot?-pmysql?dev/mysql/dev.sql
.png)
