處理MySQL 訪問異常故障

河南 劉建臣

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

故障分析

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

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

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

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

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

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

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

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

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

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

故障排查

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

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