1、mysql日志報(bào)錯(cuò)

innodb引擎提示數(shù)據(jù)庫(kù)沒(méi)有正常關(guān)閉，報(bào)innodb錯(cuò)誤180112??0:49:28??InnoDB:?Database?was?not?shut?down?normally!

InnoDB:?Starting?crash?recovery.

InnoDB:?Reading?tablespace?information?from?the?.ibd?files...

InnoDB:?Restoring?possible?half-written?data?pages?from?the?doublewrite

InnoDB:?buffer...

InnoDB:?Doing?recovery:?scanned?up?to?log?sequence?number?2580576839

180112??0:49:28??InnoDB:?Error:?page?1?log?sequence?number?2580582651

InnoDB:?is?in?the?future!?Current?system?log?sequence?number?2580576839.

InnoDB:?Your?database?may?be?corrupt?or?you?may?have?copied?the?InnoDB

InnoDB:?tablespace?but?not?the?InnoDB?log?files.?See

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

InnoDB:?for?more?information.

180112??0:49:28??InnoDB:?Error:?page?5?log?sequence?number?2580579963

InnoDB:?is?in?the?future!?Current?system?log?sequence?number?2580576839.

InnoDB:?Your?database?may?be?corrupt?or?you?may?have?copied?the?InnoDB

InnoDB:?tablespace?but?not?the?InnoDB?log?files.?See

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

InnoDB:?for?more?information.

180112??0:49:29??InnoDB:?Error:?page?65565?log?sequence?number?2580577006

InnoDB:?is?in?the?future!?Current?system?log?sequence?number?2580576839.

InnoDB:?Your?database?may?be?corrupt?or?you?may?have?copied?the?InnoDB

InnoDB:?tablespace?but?not?the?InnoDB?log?files.?See

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

InnoDB:?for?more?information.

180112??0:49:29??InnoDB:?Error:?page?65566?log?sequence?number?2580577176

InnoDB:?is?in?the?future!?Current?system?log?sequence?number?2580576839.

InnoDB:?Your?database?may?be?corrupt?or?you?may?have?copied?the?InnoDB

InnoDB:?tablespace?but?not?the?InnoDB?log?files.?See

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

InnoDB:?for?more?information.

180112??0:49:29??InnoDB:?Starting?an?apply?batch?of?log?records?to?the?database...

InnoDB:

Progress?in?percents:?41?42?43?44?45?46?47?48?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?180112

0:49:29??InnoDB:?Assertion?failure?in?thread?140330795001600?in?file

rem0rec.c?line?569

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

InnoDB:?about?forcing?recovery.

16:49:29?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.

2、查看mysql服務(wù)狀態(tài)，提示mysql沒(méi)在運(yùn)行，但是鎖定文件存在ERROR!?MySQL?is?not?running，but?lock?file?(/var/lock/subsys/mysql)?exists

3、重啟mysql服務(wù)，提示錯(cuò)誤[root@mail?subsys]#?/etc/init.d/umail_mysqld?restart

ERROR!?MySQL?server?PID?file?could?not?be?found!

Starting?MySQL.?ERROR!?The?server?quit?without?updating?PID?file?(/usr/local/u-mail/data/mysql/default.pid).

日志報(bào)下面的錯(cuò)誤：180112??9:12:44?InnoDB:?Cannot?initialize?created?log?files?because

180112??9:12:44?InnoDB:?data?files?are?corrupt,?or?new?data?files?were

180112??9:12:44?InnoDB:?created?when?the?database?was?started?previous

180112??9:12:44?InnoDB:?time?but?the?database?was?not?shut?down

180112??9:12:44?InnoDB:?normally?after?that.

180112??9:12:44?[ERROR]?Plugin?'InnoDB'?init?function?returned?error.

180112??9:12:44?[ERROR]?Plugin?'InnoDB'?registration?as?a?STORAGE?ENGINE?failed.

將數(shù)據(jù)庫(kù)下ib_logfile文件移動(dòng)走后(因?yàn)樽铋_(kāi)始看日志的時(shí)候提示了InnoDB: tablespace but not the InnoDB log files. See )，可以重啟mysql服務(wù)。

4、以為重啟mysql成功后就可以，但是沒(méi)想到innodb引擎表已經(jīng)損壞了。

日志提示：180112??9:37:40?[ERROR]?Cannot?find?or?open?table?umail/core_auth_log?from

the?internal?data?dictionary?of?InnoDB?though?the?.frm?file?for?the

table?exists.?Maybe?you?have?deleted?and?recreated?InnoDB?data

files?but?have?forgotten?to?delete?the?corresponding?.frm?files

of?InnoDB?tables,?or?you?have?moved?.frm?files?to?another?database?

or,?the?table?contains?indexes?that?this?version?of?the?engine

doesn't?support.

See?http://dev.mysql.com/doc/refman/5.5/en/innodb-troubleshooting.html

how?you?can?resolve?the?problem

(1)用phpmyadmin進(jìn)去看提示正在使用，修復(fù)提示Unknown storage engine 'InnoDB '錯(cuò)誤。

(2)mysql命令控制臺(tái)輸入show engines;查看，也沒(méi)有innodb引擎。

5、解決辦法(數(shù)據(jù)會(huì)丟失，除非有備份數(shù)據(jù))

(1)將ib開(kāi)頭的日志文件和數(shù)據(jù)文件移動(dòng)走(最好停止umail后移動(dòng)走，然后再開(kāi)啟umail)

(2)使用drop tables 表名;，刪除innodb表

(3)停止與mysql服務(wù)相關(guān)的服務(wù)

(4)使用show processlist;命令查看是否有鎖定

(5)將創(chuàng)建這幾個(gè)表的sql文件放在一個(gè)路徑下

(6)到mysql命令控制臺(tái)輸入source /usr/local/kx-mail/data/mysql/default/base_table.sql導(dǎo)入表結(jié)構(gòu)

(7)重啟mysql服務(wù)

6、總結(jié)(平時(shí)數(shù)據(jù)庫(kù)要用mysqldump最好備份，也可以單獨(dú)對(duì)表做備份，出現(xiàn)問(wèn)題的時(shí)候還原)

ibdata1文件很多，將近2GB(innodb_file_per_table參數(shù)可以給ibdata文件瘦身)。可能和這個(gè)文件太大，而且又突然斷電有關(guān)系。

分析日志后發(fā)現(xiàn)，數(shù)據(jù)庫(kù)無(wú)法重啟的原因是因?yàn)閕bdata1文件損壞，重啟后無(wú)法正常恢復(fù)。

解決辦法：

需要跳過(guò)恢復(fù)步驟，修改my.cnf文件，在my.cnf中的[mysqld]中添加：

innodb_force_recovery = 6

innodb_purge_threads = 1

解釋：

innodb_force_recovery可以設(shè)置為1-6,大的數(shù)字包含前面所有數(shù)字的影響。

具體數(shù)字對(duì)應(yīng)的含義：

1-----(SRVFORCEIGNORECORRUPT):忽略檢查到的corrupt頁(yè)。

2-----(SRVFORCENOBACKGROUND):阻止主線程的運(yùn)行，如主線程需要執(zhí)行full purge操作，會(huì)導(dǎo)致crash。

3-----(SRVFORCENOTRXUNDO):不執(zhí)行事務(wù)回滾操作。

4-----(SRVFORCENOIBUFMERGE):不執(zhí)行插入緩沖的合并操作。

5-----(SRVFORCENOUNDOLOGSCAN):不查看重做日志，InnoDB存儲(chǔ)引擎會(huì)將未提交的事務(wù)視為已提交。

6-----(SRVFORCENOLOG_REDO):不執(zhí)行前滾的操作。

再次啟動(dòng)mysql就ok了.

如果還無(wú)法啟動(dòng)，則需要?jiǎng)h除數(shù)據(jù)目錄datafile下的 ibdata1,ib_logfile*等文件。然后恢復(fù)數(shù)據(jù)庫(kù)信息(本次用的方法)

突然收到MySQL報(bào)警，從庫(kù)的數(shù)據(jù)庫(kù)掛了，一直在不停的重啟，打開(kāi)錯(cuò)誤日志，發(fā)現(xiàn)有張表壞了。innodb表?yè)p壞不能通過(guò)repair table 等修復(fù)myisam的命令操作。現(xiàn)在記錄下解決過(guò)程，下次遇到就不會(huì)這么手忙腳亂了。

處理過(guò)程：

一遇到報(bào)警之后，直接打開(kāi)錯(cuò)誤日志，里面的信息：InnoDB:?Database?page?corruption?on?disk?or?a?failed

InnoDB:?file?read?of?page?30506.

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

130509?20:33:48??InnoDB:?Page?dump?in?ascii?and?hex?(16384?bytes):

##很多十六進(jìn)制的代碼

……

……

InnoDB:?End?of?page?dump

130509?20:37:34??InnoDB:?Page?checksum?1958578898,?prior-to-4.0.14-form?checksum?3765017239

InnoDB:?stored?checksum?3904709694,?prior-to-4.0.14-form?stored?checksum?3765017239

InnoDB:?Page?lsn?5?614270220,?low?4?bytes?of?lsn?at?page?end?614270220

InnoDB:?Page?number?(if?stored?to?page?already)?30506,

InnoDB:?space?id?(if?created?with?>=?MySQL-4.1.1?and?stored?already)?19

InnoDB:?Page?may?be?an?index?page?where?index?id?is?54

InnoDB:?(index?"PRIMARY"?of?table?"maitem"."email_status")

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

InnoDB:?file?read?of?page?30506.

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

InnoDB:?about?forcing?recovery.

InnoDB:?A?new?raw?disk?partition?was?initialized?or

InnoDB:?innodb_force_recovery?is?on:?we?do?not?allow

InnoDB:?database?modifications?by?the?user.?Shut?down

InnoDB:?mysqld?and?edit?my.cnf?so?that?newraw?is?replaced

InnoDB:?with?raw,?and?innodb_force_...?is?removed.

130509?20:39:35?[Warning]?Invalid?(old?)?table?or?database?name?'#sql2-19c4-5'

從錯(cuò)誤日志里面很清楚的知道哪里出現(xiàn)了問(wèn)題，該怎么處理。這時(shí)候數(shù)據(jù)庫(kù)隔幾s就重啟，所以差不多可以說(shuō)你是訪問(wèn)不了數(shù)據(jù)庫(kù)的。所以馬上想到要修復(fù)innodb表了。

以前在Performance的blog上看過(guò)類似文章。

當(dāng)時(shí)想到的是在修復(fù)之前保證數(shù)據(jù)庫(kù)正常，不是這么異常的無(wú)休止的重啟。所以就修改了配置文件的一個(gè)參數(shù)：innodb_force_recoveryinnodb_force_recovery影響整個(gè)InnoDB存儲(chǔ)引擎的恢復(fù)狀況。默認(rèn)為0，表示當(dāng)需要恢復(fù)時(shí)執(zhí)行所有的

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í)行前滾的操作。

因?yàn)殄e(cuò)誤日志里面提示出現(xiàn)了壞頁(yè)，導(dǎo)致數(shù)據(jù)庫(kù)崩潰，所以這里把innodb_force_recovery 設(shè)置為1，忽略檢查到的壞頁(yè)。重啟數(shù)據(jù)庫(kù)之后，正常了，沒(méi)有出現(xiàn)上面的錯(cuò)誤信息。找到錯(cuò)誤信息出現(xiàn)的表：

(index?"PRIMARY"?of table?"maitem"."email_status")

數(shù)據(jù)頁(yè)面的主鍵索引(clustered key index)被損壞。這種情況和數(shù)據(jù)的二級(jí)索引(secondary

indexes)被損壞相比要糟很多，因?yàn)楹笳呖梢酝ㄟ^(guò)使用OPTIMIZE TABLE命令來(lái)修復(fù)，但這和更難以恢復(fù)的表格目錄(table

dictionary)被破壞的情況來(lái)說(shuō)要好一些。

操作步驟：

因?yàn)楸黄茐牡牡胤街辉谒饕牟糠?#xff0c;所以當(dāng)使用innodb_force_recovery = 1運(yùn)行InnoDB時(shí)，操作如下：執(zhí)行check，repair?table?都無(wú)效

alter?table?email_status?engine?=myisam;??#也報(bào)錯(cuò)了，因?yàn)槟Ｊ绞莍nnodb_force_recovery?=1。

ERROR?1025?(HY000):?Error?on?rename?of?'...'?to?'....'?(errno:?-1)建立一張表：create?table?email_status_bak???#和原表結(jié)構(gòu)一樣，只是把INNODB改成了MYISAM。把數(shù)據(jù)導(dǎo)進(jìn)去insert?into?email_status_bak?select?*?from?email_status;

刪除掉原表：

drop?table?email_status;

注釋掉innodb_force_recovery?之后，重啟。

重命名：

rename?table?edm_email_status_bak?to?email_status;

最后該回存儲(chǔ)引擎

alter?table?edm_email_status?engine?=?innodb

總結(jié)：

這里的一個(gè)重要知識(shí)點(diǎn)就是

對(duì)?innodb_force_recovery?參數(shù)的理解了，要是遇到數(shù)據(jù)損壞甚至是其他的損壞。可能上面的方法不行了，需要嘗試另一個(gè)方法：insert

into tb select * from ta limit X；甚至是dump出去，再load回來(lái)。

總結(jié)

以上是生活随笔為你收集整理的mysql断电不受影响db引擎_一次服务器断电，造成innodb引擎表（日志表）损坏的解决办法...的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問(wèn)題。

如果覺(jué)得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。