處理MySQL 訪問異常故障

河南 劉建臣

某單位使用Java 開發(fā)的數(shù)據(jù)分析系統(tǒng)，后臺使用的是MySQL 數(shù)據(jù)庫，最近出現(xiàn)了無法查詢和寫入數(shù)據(jù)的情況，MySQL 服務(wù)器使用的是CentOS 6.X 系統(tǒng)，使用了八核的CPU，內(nèi)存為64GB，按照這樣的配置，運行MySQL 是沒有任何問題的。筆者進入MySQL 安裝路徑，發(fā)現(xiàn)似乎缺少了某些文件，果然其中的“ibdata1”“ib_logfile0”“ib_logfile1”等文件“不翼而飛”了。

故障分析

MySQL 默認采用InnoDB引擎，上述文件是MySQL 的表空間文件和日志文件，Innodb 存儲類型的表的數(shù)據(jù)都放在該共享表空間中?？磥硎怯腥苏`刪除了這些文件，是不是這些文件丟失的原因呢？因為MySQL服務(wù)并沒有重啟，是可以從內(nèi)存文件系統(tǒng)中找回這些文件的。執(zhí)行“netstat-nltp|grep mysqld”命令，可以查到MySQL 的進程號，這里為“6831”。執(zhí)行“l(fā)l/proc/6831/fd|grep -e ibdata -e ib_”命令，果然在內(nèi)存文件系統(tǒng)中找到了這些文件，狀態(tài)為“deleted”，說明實際文件已被刪除。因為數(shù)據(jù)庫處于運行狀態(tài)，在InnoDB 引擎緩存池中有很多數(shù)據(jù)已發(fā)生變動，但沒有寫入到磁盤文件中，如果直接恢復肯定會造成數(shù)據(jù)丟失。

在MySQL 中執(zhí)行“flush tables with read lock;”命令，禁止針對數(shù)據(jù)庫的修改操作。執(zhí)行“show engine innodb statusG;”命令，在返回信息中的“Pages flushed up to”行顯示當前最舊的臟頁數(shù)據(jù)對應(yīng)的位置，在“Log sequence number”行中顯示當前最新數(shù)據(jù)產(chǎn)生的日志序列號信息。這里兩者數(shù)據(jù)一致，說明內(nèi)存中的Dirty Page 數(shù)據(jù)已寫入磁盤。在“Modified db pages”欄中顯示臟數(shù)據(jù)庫頁數(shù)，該值應(yīng)為0，說明Dirty Page 已刷新到磁盤。

否則，執(zhí)行“set global innodb_max_dirty_pages_pct=0;”命令，設(shè)置Dirty Page 刷新比例。當確定Dirty Page 已全部寫入磁盤后，執(zhí)行：

執(zhí)行文件復制操作，之后執(zhí)行“chown mysql:mysql/data1/mysql/ib*”命令，為上述文件指定用戶權(quán)限，因為運行MySQL 的用戶名為“mysql”。執(zhí)行“/etc/init.d/mysqld restart”命令，重啟MySQL 服務(wù)。

進入MySQL 運行路徑，發(fā)現(xiàn)上述文件已找回，原本以為問題解決了，但在對某個數(shù)據(jù)表進行查詢和寫入操作時，依然出現(xiàn)失敗。

在MySQL 中執(zhí)行“show processlist;”命令，發(fā)現(xiàn)有線程正對“tb_anlydat”的表進行檢測操作，而其他線程在進行插入等操作時，出現(xiàn)“Waiting for table level lock”提示，說明MySQL 正在對該表進行檢查，因此其他的寫入操作處于阻塞狀態(tài)。

執(zhí)行“show create table tb_anlydat;”命令，發(fā)現(xiàn)該表使用的是MyISAM引擎，因為同InnoDB 引擎相比，MyISAM 引擎具有較快的查詢速度，在實際中經(jīng)常會出現(xiàn)混合使用上述引擎的現(xiàn)象，即大部分表使用InnoDB引擎，少量對查詢性能要求較高的表使用MyISAM 引擎。MyISAM 引擎只支持表級鎖，如果當數(shù)據(jù)庫出現(xiàn)執(zhí)行時間較長的查詢或檢測時，對目標數(shù)據(jù)表進行更新操作，就很容易出現(xiàn)“Waiting for table level lock”之類的錯誤提示，造成無法插入數(shù)據(jù)的問題。

前端程序就會因連接失效造成訪問請求失敗，連續(xù)執(zhí) 行“show processlist;”命令，可以發(fā)現(xiàn)相關(guān)線程正在對該表進行修復，說明MySQL 已發(fā)現(xiàn)該表存在問題，在進行修復操作。

根據(jù)以上分析，可以發(fā)現(xiàn)上述問題的原因，就在于某些數(shù)據(jù)表存儲問題。既然數(shù)據(jù)表無法插入數(shù)據(jù)，那么就需要進行修復。在修復之前需要將內(nèi)容導出來。例如執(zhí)行“select * into outfile '/tmp/export.txt' from tb_anlydat;”命令，將該表中數(shù)據(jù)導出，在備用MySQL服務(wù)器上執(zhí)行“LOAD DATA local INFILE '/tmp/export.txt' IGNORE INTO TABLE tb_anlydat;”之類的命令，將數(shù)據(jù)導入進來，便于對該數(shù)據(jù)表進行修復。

MyISAM 引擎提供了Check Table、Repair Table和Myisanchk 等工具，可以對數(shù)據(jù)表進行檢測和修復操作。例如在MySQL 中執(zhí)行“check table tb_anlydat;”和“repair table tb_anlydat;”命令，對該表進行修復操作，但很花時間卻沒有什么效果。打開MySQL 配置文件“my.cnf”，發(fā)現(xiàn)其中存在“myisamrecover=BACKUP,FORCE”行，這表明如果重啟MySQL，就會自動針對使用MyISAM 的表進行備份和檢測修復操作。因為該表體積達到幾百兆，讀寫比較頻繁，往往會因為各種原因（例如斷電、硬件故障等），造成使用MyISAM 引擎的數(shù)據(jù)表出現(xiàn)故障。

故障排查

筆者注意到該MySQL 數(shù)據(jù)庫使用了獨立的磁盤來存儲數(shù)據(jù)，執(zhí)行“df -lh”命令，顯示還有約30%的可用空間，不存在磁盤空間不足問題。執(zhí)行“dmesg”命令，在返回信息中發(fā)現(xiàn)和MySQL 對應(yīng)的磁盤出現(xiàn)“Medium Error”、“uncovered read erroe”、“critical medium error”等錯誤信息，表明該硬盤分區(qū)出現(xiàn)了壞道等才引發(fā)了以上問題。

表面看來，可以在硬盤上進行讀寫操作，似乎硬盤沒有問題，其實該盤的某些扇區(qū)已經(jīng)損壞，且該數(shù)據(jù)表恰恰使用了這些問題扇區(qū)。問題找到了，只能更換新的硬盤，當然，需要將所有MySQL數(shù)據(jù)導出保存到備用機。因為MyISAM 引擎容易出問題，最好還是使用InnoDB 引擎操作數(shù)據(jù)表。