基於MySQL的資料庫叢集系統的實現(來源：IBM DW)

您的WebApp系統是否正在使用一個MySQL的資料庫系統？您的客戶是不是總是抱怨頁面結果反饋的非常慢？您的MySQL系統的負載是不是總是維持在一個非常高的狀態下？本文將為您提供一個分擔MySQL系統的負載的方法，以及由此派生出來的一個MySQL-HA-Proxy的開發項目。使用本文提供的方法，您將以最小的原始碼改動，獲得MySQL系統的高效運轉。

第一節 資料庫叢集技術的現狀

目前資料庫叢集系統應用得比較成功，應用範圍比較廣泛的是：Oracle公司的Oracle9與IBM公司DB2。Oracle9採用Shared-storage的技術，DB2選擇了Shared-nothing的技術，二者各有長短。

最新的資料庫叢集系統的理論基礎是分散式運算，將資料分布到每個節點，所有的計算節點平行處理資料，將結果匯總。這樣的方式無疑是最完美的。但是目前仍然不能實現全部的功能。

對於Shared-storage以及Shared-nothing的技術請參考Oracle以及IBM網站上的相關資料。

第二節 目前資料庫應用狀況

目前資料庫應用狀況大致分為兩類，第一類是資料量在100G以下，資料庫訪問頻繁，請求密集。主要是Web APP類型的應用，例如：網站，論壇等。這些Web APP類型的應用訪問資料庫的特點是：訪問頻繁，資料庫每秒鐘要接受幾千次以上的查詢，需要經常追加資料，同時對資料的響應速度要求比較高。另一類是用於科學計算、儲存曆史資料的應用，資料量往往達到幾百G。這些應用訪問資料庫的特點是：多為查詢操作，資料都是分批、定時、集中倒入資料庫，資料庫的記錄非常多，積累了大量的資料，對資料庫的響應速度沒有太高要求。

第三節 暴露出來的問題

第一類應用，由於訪問比較頻繁，而且為了支援更多的訪問，Web Server一般都使用了負載平衡的叢集，但是對於資料庫來說，由於無法實現叢集操作，每秒鐘的請求不斷增加，隨著伺服器負載的增加，響應單個請求的速度越來越慢，如果庫檔案比較大，出現寫操作的時候還會出現鎖表時間過長等影響訪問效率的事情。

第二類應用，主要是資料檔案太大，每次處理資料都需要大量的時間，如果寫錯一個語句就需要花幾個小時來重做查詢。

第四節 如何解決

首先應當從硬體、軟體、程式、索引、SQL語句這幾個方面進行最佳化，如果仍然不能解決問題，我們就要考慮資料庫系統的叢集（平行處理）了。

對於第一類的應用，在資料庫伺服器正常運行，負載不高的情況下，應用對資料庫系統的狀況還是滿意的。但是資料庫系統負載過高之後，就會出現完成請求的時間加長，達不到系統的要求時間。既然負載是由於過多的請求造成的，我們就採取分擔請求的方式，讓一部分的請求去訪問另外一台伺服器，讓單台伺服器的負載降低，從而解決問題。

對於第二類的應用，就需要分散式運算的系統來解決了，一般的系統是無能為力了。

第五節 針對於"Linux+Apache+PHP+MySQL"的第一類應用問題的解決方式

一個實際案例的解決：

我在工作當中遇到了這樣的問題，我們的Web Server是Linux+Apache+Php的三台機器組成的叢集，MySQL運行在SUN450，2G記憶體的平台上。由於WEB的訪問量在高峰的時候幾乎滿負荷運轉，LoadAvg(就是一分鐘之內處於Running狀態的進程數量)都在10-20之間，反映出來就是大量的請求都在訪問資料庫的時候被掛住了，導致一個請求沒有完成，下一個請求又進來，最後惡性迴圈。LoadAvg會在瞬間飆升至800以上。資料庫那邊就更糟糕了，LoadAvg達到300多，資料庫的線程非常多，CPU忙於切換線程狀態，這個時候除非Restart MySQL，否則怎麼都不會好。在對SQL語句最佳化完成後還是不能很好的解決問題，我們增加了一台資料庫伺服器，通過MySQL的資料同步機制，讓兩台資料庫上的資料保持同步，修改了一部分只會發生讀取操作的php程式，讓這些程式串連另外一台資料庫，算是把負載分離出去一部分，問題得到了初步的解決。但是後來業務做大，我們又增加了多台伺服器，修改了很多程式，分離他們對資料庫的讀取操作，訪問不同的伺服器。

第六節 MySQL-HA-Proxy方案的提出

通過修改程式的方式實現將系統的負載分離，是件很痛苦的事情，工程浩大，而且不能弄錯，因為除了主伺服器可以寫入、修改資料，而其它的伺服器只能通過資料同步更新自身的資料，所以如果你對那些資料庫進行了寫操作，結果將是災難性的。

如果我們能夠有一個程式分揀SQL語句，根據他的類型（讀取/寫入），分別傳送給不同的伺服器，然後再將結果返回。採用一種類似HTTP的PROXY的方式，這樣我們就不需要通過修改來源程式的方式來分擔負載了，如果再能夠根據伺服器的負載狀況，或者是表的狀態（可用/鎖定），來判斷應該將這個請求分配到哪台伺服器，那就比我們修改來源程式所能達到的效果還要好。

第七節 MySQL Client與Server之間如何通訊

四處尋找，也沒有找到一篇關於Mysql通訊協議的文章，看來只有分析Mysql的來源程式了。於是找來mysql 3.23.49的代碼，開啟sniffer工具。MySQL的通訊協議可能變更過多次，在3.23.49的版本裡面，通訊協議的版本竟然是10。

簡單的分析了一下通訊協議，現在規整如下，有些地方還不是很完善，由於我實在沒有太多的時間仔細研讀mysql的代碼，目前我只瞭解到了這些。

位移	地區	類型	長度(byte)	說明
0	HEAD	Data Length	3
1
2
3		FLAG	1	=0普通訊息 =1多段資訊 =2認證返回 >2段結束字
4	DATA	CMD Code	1
5		Message	DataLength - 1

當FLAG=0 , 2的時候 CMD Code 與 Message 的定義

CMDCode	類型	Message的結構
00	狀態代碼	位移	類型	Length(byte)
		0	Affect rows	2
0A	伺服器版本號碼	位移	類型	Length(byte)
	只有在剛剛串連上Server的時候有效，Server會馬上返回一個資料節段的資訊	0	VersionString	8	end of '/0'
		8	Session ID	4	32bits
		12	UnKnown	11
FF	當出現錯誤的時候返回資訊	位移	類型	Length(byte)
		0	ErrCode	2
		2	ErrMsg	END
FE	多段資訊傳輸的結束	空

Client對Server提交資料的格式：

位移	地區	類型	Length(byte)
0	HEAD	Data Length	3
1
2
3		Compressed	1
4	DATA	Command ID	1
5		Command Data	Data Length - 1

Command ID與Command Data的說明：

ID	類型	資料格式
0	COM_SLEEP
1	COM_QUIT	NULL
2	COM_INIT_DB	Database name
3	COM_QUERY	stand query string
4	COM_FIELD_LIST	table name [128] wildcard[128]
5	COM_CREATE_DB	Database name
6	COM_DROP_DB	Database name
7	COM_REFRESH	options(bits)
8	COM_SHUTDOWN	NULL
9	COM_STATISTICS	NULL
10	COM_PROCESS_INFO	NULL
11	COM_CONNECT
12	COM_PROCESS_KILL	sid[4]
13	COM_DEBUG	NULL
14	COM_PING	NULL
15	COM_TIME
16	COM_DELAYED_INSERT
17	COM_CHANGE_USER	[user][passwd][db]
18	COM_BINLOG_DUMP
19	COM_TABLE_DUMP
20	COM_CONNECT_OUT

第八節 Client如何通過Server的使用者認證

協議分析完成了，我嘗試著讓它工作起來，可是認證這個部分遇到了麻煩，Mysql Server在Client串連上它的時候，會首先返回給Client一個資料包，包含協議的版本號碼，版本資訊，SessionID，一個8位元組的Key，就是這個Key的原因。Client會使用這個Key來加密密碼，然後將使用者名稱，密碼，需要開啟的資料庫等資訊發送給Server，這樣就完成認證了。我不知道Client是如何利用這個Key來加密的，所以我打算跳過密碼，我將Client的資料包重組，去掉Password的資訊之後，我成功了，但是叢集裡面的Mysql使用者都是沒有密碼的，安全性多多少少有些問題，不過這些伺服器都是放在HA後面的，沒有外部的IP地址，應該問題不大，不過多多少少是個缺憾。

但是我總要知道使用者的密碼是否正確吧？怎麼辦呢？使用一個專用的Mysql來完成密碼認證。安裝一個最小化資源的Mysql Server用來做MysqlAuth（專用證明伺服器），當Client串連後，就將MysqlAuth的第一個資料包返回給Client，這裡面當然就包含著Key，然後Client會使用這個Key，加密密碼之後，將認證資訊發回來，這個時候，MysqlHA系統就會將這個資訊轉寄給MysqlAuth，並且自己保留一份，如果認證通過了，就把保留的那一份進行重組，去掉密碼資訊，然後用重組後的認證資訊去串連叢集中的伺服器。

第九節 系統的結構與流程

圖中HA就是使用HeartBeat方式建立的高可靠性系統（具體實現方法請參考 http://www.linuxvirtualserver.org/ ）。Proxy為Mysql-Proxy系統，MysqlAuth是專用的證明伺服器。紅色的RealServer為主要伺服器，可以進行資料更新操作，同時將資料同步到其它的RealServer。

描述的就是Client認證過程

描述的是認證不通過以及認證通過後與RealServer建立聯結的過程

講述了串連建立後，系統處理SQL Query請求的過程

第十節 結束語

我現在已經基本完成了mysql-proxy的程式的開發，但是目前仍然處於測試階段，最新的版本是0.0.4，下一個版本仍然還在修訂中。從0.0.3版本開始，mysql-proxy已經可以完整的跑完mysql自身提供的sql-bench了，但是這個sql-bench只能提供單點的效能，沒有對叢集的mysql系統提供測試功能。

系統提供了動態採集RealServer上的LoadAvg然後反饋給Mysql Proxy的程式，但是由於這部分我沒有進行測試，所以我在前面的測試中採用的請求分配方式是輪詢方式，如果出現兩個負載一樣的RealServer系統會自動的在它們之間輪換選擇。

Mysql-proxy的原始碼您可以到我的網站下載：http://netsock.org/bbs/Mysql-HA-Cluster項目。還有一部分測試的資料我也會在那裡公布。

如何進行系統測試？

既然是專門為Linux+Apache+Php+Mysql這樣的系統做的叢集，就應該找一個實際的應用來跑跑看，然後類比大量的訪問，來進行測試。

選擇一個論壇系統也許不錯，VBB吧，用的比較多，也比較流行。類比訪問就用Apache自身提供的AB來做。

測試系統的最小環境就是：（五台機器）

1 x Apache + PHP

1 x AB

1 x Mysql Proxy + Mysql Auth Server

2 x Mysql Real Server

參考資料：

第九節的投影片可以在 http://www.netsock.org/mysqlha/mysql-ha.ppt 得到

最新版本的原始碼可以在 http://www.netsock.org/mysqlha/mysql-proxy_0.0.4.zip 得到

安裝說明可以參考 http://netsock.org/bbs/showthread.php?threadid=5

一個sql-bench的運行結果可以在http://netsock.org/bbs/showthread.php?threadid=9 得到

作者簡介：

徐超，任職於TOM.COM北京公司，從事網路系統支援人員及系統維護工作。業餘時間致力於以NetSocket技術為基礎的網路應用的開發。開發網站：http://netsock.org/bbs/ 目前正在開發的項目包括：SocketChat, MySQL-HA-Proxy, Php Session Server