對于mysql數(shù)據(jù)庫架構(gòu)為雙主復(fù)制模式的不少技術(shù)朋友都非常困惑，如何準確判斷mysqld服務(wù)是否能正常提供服務(wù)，以及能否自動判斷并且進行主機的切換?同時，對mysqld服務(wù)的檢測機制要求消耗資源少、判斷簡單且準確、開發(fā)和維護成本低等。我們在實際的生產(chǎn)環(huán)境檢測過程中，也曾經(jīng)犯過錯誤，為此寫一篇短小的文章，把相關(guān)經(jīng)驗、思路、做法分享給大家，為更多的技術(shù)朋友起到答疑解惑。

要想做到自動切換提供數(shù)據(jù)庫服務(wù)請求的主備服務(wù)器關(guān)鍵，就是要確定雙主復(fù)制架構(gòu)中的mysql數(shù)據(jù)庫實例是否能正常提供服務(wù)請求，最讓人頭疼的就是mysqld服務(wù)出現(xiàn)hang住的情況。那么mysqld服務(wù)hang住的時候，會有哪些表象呢?先列出本人及圈內(nèi)朋友們出現(xiàn)過的情況：

● 不能對數(shù)據(jù)庫中的對象或數(shù)據(jù)執(zhí)行修改性操作，但能正常執(zhí)行查詢操作;

● 能對系統(tǒng)數(shù)據(jù)庫(備注：mysql、information_schema)的對象或數(shù)據(jù)進行查詢操作，不能對非系統(tǒng)數(shù)據(jù)庫的對象和數(shù)據(jù);

● 只能對虛擬數(shù)據(jù)庫(備注： information_schema)的對象及數(shù)據(jù)進行查詢操作，不能對其他數(shù)據(jù)庫的對象和數(shù)據(jù);

● 不能對對任何數(shù)據(jù)庫的對象或數(shù)據(jù)進行查詢操作，但是能執(zhí)行SHOW PROCESSLIST;

● 不能對對任何數(shù)據(jù)庫的對象或數(shù)據(jù)進行查詢操作，也不能執(zhí)行SHOW PROCESSLIST，但是可以執(zhí)行部分SHOW操作，例如：SHOW STATUS;

● 其他，還未發(fā)現(xiàn)的狀態(tài)信息;

針對上述mysqld服務(wù)hang住的情況做一個分析及匯總，可以發(fā)現(xiàn)其有一些共同特征，總結(jié)如下：

● mysqld服務(wù)存在，且能ping或telnet;

● 能接受客戶端發(fā)送過來的請求，但是不繼續(xù)處理，而是停留在其發(fā)生hang住的當下SQL執(zhí)行的狀態(tài);

● 若能執(zhí)行SHOW PROCESSLIST的話，能看到所有的SQL執(zhí)行狀態(tài)停留不變;

● 數(shù)據(jù)庫服務(wù)器的LOAD會突然下降，甚至LOAD下降為0，CPU、IO等都會接近沒負荷狀態(tài);

● 若mysqld服務(wù)發(fā)生hang住的時候，一般都無法對數(shù)據(jù)庫的對象或數(shù)據(jù)執(zhí)行修改性質(zhì)的操作;

文章開篇描述了mysqld服務(wù)hang住的時候，mysqld接受、處理服務(wù)請求的情況，以及數(shù)據(jù)庫服務(wù)器的狀態(tài)信息，既然可以發(fā)現(xiàn)這些特征，那么對于常用檢測mysqld服務(wù)是否還活著或者網(wǎng)絡(luò)是否通的辦法：

● ping或telnet mysqld服務(wù)的端口;

● 通過執(zhí)行SHOW 命令;

● 通過執(zhí)行SELECT查詢操作;

上述三類檢測辦法是否能真正做到準確檢測呢?答案是：NO，只能準確監(jiān)測到mysqld進程是否活著、程序與數(shù)據(jù)庫服務(wù)器之間的網(wǎng)絡(luò)是否暢通，對于mysqld服務(wù)能否正常接收和完成處理請求，就無法做到或者部分做到，綜合上述分析信息，以及從目前我們將近三年實施效果看，對數(shù)據(jù)庫中的數(shù)據(jù)進行修改操作，再配合程序?qū)?shù)據(jù)修改操作的判斷邏輯是最穩(wěn)妥的方法，詳細步驟：

● 檢測頻率為：每隔10S，對當前提供服務(wù)的mysqld數(shù)據(jù)庫實例上的檢測表，做一次UPDATE操作，探測數(shù)據(jù)庫實例是否正常提供服務(wù);

● 若上一次數(shù)據(jù)庫實例服務(wù)檢測操作，沒有正常返回更新信息，則每隔1S做一次數(shù)據(jù)庫檢測表的UPDATE操作，總共做2次探測;

● 若前兩個步驟的數(shù)據(jù)庫實例服務(wù)探測結(jié)束，當前提供服務(wù)的數(shù)據(jù)庫實例服務(wù)都沒恢復(fù)正常，則每隔5MS對數(shù)據(jù)庫檢測表再做一次UPDATE操作，總共檢測三次，若還是沒有正常返回信息，則認定此數(shù)據(jù)庫實例服務(wù)不能正常接收服務(wù)請求;

用于執(zhí)行數(shù)據(jù)庫實例服務(wù)檢測的表結(jié)構(gòu)和UPDATE操作SQL為：

CREATE TABLE monitor_db(  
      ID SMALLINT UNSIGNED NOT NULL AUTO_INCREMNET,  
      CreateDate TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00',  
      PRIMARY KEY(ID)  
      )ENGINE=InnoDB CHARACTER SET 'utf8' COLLATE 'utf8_general_ci';  
INSERT INTO monitor_db VALUES(1,NOW()),(2,DATE_ADD(NOW(),INTERVAL -1 DAY)) 

● MySQL5.0及以下版本的UPDATE操作SQL

UPDATE monitor_db SET CreateDate =NOW() WHERE ID=1; 

● MySQL5.1及以上版本的UPDATE操作SQL

SET SESSION sql_log_bin=0;  
UPDATE monitor_db SET CreateDate=NOW() WHERE ID=1; 

備注：

對于支持MIXED、ROW復(fù)制模式的版本，必須規(guī)避MySQL雙主復(fù)制過程中，可能出現(xiàn)主從執(zhí)行更新操作SQL語句的被修改數(shù)據(jù)不一致的問題，從而導致復(fù)制中斷，為此我們對數(shù)據(jù)庫實例服務(wù)檢測的更新操作不記錄到二進制日志文件中，也即不會復(fù)制到其各自的從服務(wù)器。

另外，建議大家把monitor_db表創(chuàng)建到test數(shù)據(jù)庫，或者類似test功能的數(shù)據(jù)庫中，存儲引擎建議一定要是：InnoDB，對于檢測頻率可以根據(jù)自己對數(shù)據(jù)安全性要求，而調(diào)整為自己能接受的。

若mysqld服務(wù)出現(xiàn)hang住的時候，正常關(guān)閉mysqld服務(wù)的辦法都無效，只有對mysqld服務(wù)進程進行操作系統(tǒng)級別的kill -9 操作，然后再啟動mysqld服務(wù)實例，等待其自動進行回滾操作結(jié)束，才算啟動成功，建議大家別用mysql5.0.82及前后版本，存在一些BUG，很容易導致出現(xiàn)hang的情況。

【編輯推薦】

1. SQL與NoSQL——MySQL與NoSQL的融合
2. MySQL中的NoSQL插件
3. HandlerSocket是神馬
4. 主流NoSQL數(shù)據(jù)庫評測之HandlerSocket
5. 如何抓住蝴蝶效應(yīng)中的那只蝴蝶