1. 摘要

        MySQL是一種開放源碼的關(guān)聯(lián)數(shù)據(jù)庫(kù)管理系統(tǒng)，MySQL數(shù)據(jù)庫(kù)服務(wù)器具有快速、可靠和易于使用的特點(diǎn)，MySQL服務(wù)器工作在客戶端/服務(wù)器模式下，或嵌入式系統(tǒng)中。

        本文檔主要描述了MySQL數(shù)據(jù)庫(kù)的各種使用特點(diǎn)。

        2. 關(guān)鍵詞

        MySQL，HA，innoDB，mysql cluster，iscsi

        3. 名詞解釋

        縮略語 英文全名 中文解釋

        HA High availability 高可靠性

        4. 概述

        4.1. 該技術(shù)產(chǎn)生的背景

        由于網(wǎng)站通常都是由小做大，在初期很少投入的情況下使用免費(fèi)的mysql數(shù)據(jù)庫(kù)，隨著網(wǎng)站越來越大數(shù)據(jù)量、訪問量都對(duì)數(shù)據(jù)庫(kù)要求越來越高，單臺(tái)mysql數(shù)據(jù)庫(kù)服務(wù)器已經(jīng)難以滿足網(wǎng)站的需求，而且對(duì)數(shù)據(jù)庫(kù)的安全級(jí)別也提高了，就出現(xiàn)了mysql的HA技術(shù)以及mysql服務(wù)器群，甚至多種數(shù)據(jù)庫(kù)技術(shù)混合使用。

        4.2. 該技術(shù)目前實(shí)現(xiàn)的情況

        隨著各個(gè)網(wǎng)站對(duì)數(shù)據(jù)庫(kù)的要求理解不同，mysql的組網(wǎng)方式也是五花八門，本文檔也無法將所有mysql組網(wǎng)技術(shù)提到，只能做個(gè)拋磚引玉。

        5. 基礎(chǔ)知識(shí)

        5.1. MySQL數(shù)據(jù)庫(kù)

        MySQL是一種開放源代碼的關(guān)系型數(shù)據(jù)庫(kù)管理系統(tǒng)(RDBMS)，MySQL數(shù)據(jù)庫(kù)系統(tǒng)使用最常用的數(shù)據(jù)庫(kù)管理語言--結(jié)構(gòu)化查詢語言(SQL)進(jìn)行數(shù)據(jù)庫(kù)管理。

        由于MySQL是開放源代碼的，因此任何人都可以在General Public License的許可下下載并根據(jù)個(gè)性化的需要對(duì)其進(jìn)行修改。MySQL因?yàn)槠渌俣?、可靠性和適應(yīng)性而備受關(guān)注。大多數(shù)人都認(rèn)為在不需要事務(wù)化處理的情況下，MySQL是管理內(nèi)容最好的選擇。

        MySQL關(guān)系型數(shù)據(jù)庫(kù)于1998年1月發(fā)行第一個(gè)版本。它使用系統(tǒng)核心提供的多線程機(jī)制提供

        完全的多線程運(yùn)行模式，提供了面向C、C++、Eiffel、Java、Perl、PHP、Python以及Tcl等編程語言的編程接口(APIs)，支持多種字段類型并且提供了完整的操作符支持查詢中的SELECT和WHERE操作。

        時(shí)至今日 mysql 和 php 的結(jié)合絕對(duì)是完美.很多大型的網(wǎng)站也用到mysql數(shù)據(jù)庫(kù)。 5.2. 高可用性HA(High Availability)

        是通過盡量縮短因日常維護(hù)操作(計(jì)劃)和突發(fā)的系統(tǒng)崩潰(非計(jì)劃)所導(dǎo)致的停機(jī)時(shí)間，以提高系統(tǒng)和應(yīng)用的可用性。它與被認(rèn)為是不間斷操作的容錯(cuò)技術(shù)有所不同。HA系統(tǒng)是目前企業(yè)防止核心計(jì)算機(jī)系統(tǒng)因故障停機(jī)的最有效手段。

        6. 技術(shù)原理描述

        6.1. MySQL遵循標(biāo)準(zhǔn)的SQL規(guī)范

        SQL是用于訪問數(shù)據(jù)庫(kù)的最常用標(biāo)準(zhǔn)化語言，它是由ANSI/ISO SQL標(biāo)準(zhǔn)定義的。SQL標(biāo)準(zhǔn)自1986年以來不斷演化發(fā)展，有數(shù)種版本， 目前MySQL使用的是ANSI/ISO SQL組織2003年發(fā)布的版本。使得MySQL的應(yīng)用在往大型商用數(shù)據(jù)庫(kù)遷移減少成本。

        6.2. MySQL的組網(wǎng)方式

        6.2.1. 單臺(tái)MySQL服務(wù)器

        整個(gè)網(wǎng)站只有一臺(tái)MySQL的數(shù)據(jù)庫(kù)服務(wù)器提供數(shù)據(jù)服務(wù)。甚至連web服務(wù)也在一起。

        拓?fù)淙缦聢D:

        6.2.2. 多臺(tái)MySQL服務(wù)器，將不同的應(yīng)用數(shù)據(jù)放在不同的MySQL數(shù)據(jù)庫(kù)中 隨著數(shù)據(jù)訪問量的增大，數(shù)據(jù)庫(kù)寫入、更新、查詢引起的IO消耗越來越多，數(shù)據(jù)庫(kù)反應(yīng)越來越慢，導(dǎo)致了整個(gè)系統(tǒng)變慢，本著分散IO的目的，就按不同的應(yīng)用分配到不同的數(shù)據(jù)庫(kù)服務(wù)器中，使用戶的操作分散在各臺(tái)不同的服務(wù)器，減輕了單臺(tái)服務(wù)器的負(fù)擔(dān)提高了效率。 應(yīng)用邏輯圖如下:

        網(wǎng)絡(luò)拓?fù)淙缦?

        6.2.3. 多臺(tái)MySQL服務(wù)器，采用讀寫分離

        隨著網(wǎng)站的發(fā)展，查詢業(yè)務(wù)是整個(gè)數(shù)據(jù)庫(kù)提供服務(wù)乃至整個(gè)網(wǎng)站提供服務(wù)的核心，在寫操作不是非常大的情況下，提供mysql服務(wù)器做寫業(yè)務(wù)，剩下的mysql服務(wù)器都只提供查詢業(yè)務(wù)，保證了向用戶提供的查詢業(yè)務(wù)的順暢，這種方式就是讀寫分離。

        為實(shí)現(xiàn)讀寫分離，按mysql服務(wù)器的功能分為主從mysql服務(wù)器，主服務(wù)器提供源數(shù)據(jù)給從服務(wù)器，其中主MySQL服務(wù)器執(zhí)行所有的數(shù)據(jù)庫(kù)寫操作，將做過的操作寫入數(shù)據(jù)庫(kù)日志中，主服務(wù)器再將這些寫入日志的操作同步到從MySQL服務(wù)器中去執(zhí)行，所以從服務(wù)器得到主服務(wù)器數(shù)據(jù)有時(shí)間差，多臺(tái)從MySQL服務(wù)器執(zhí)行用戶查詢的操作，對(duì)主MySQL服務(wù)器采用HA方式，保證沒有單點(diǎn)故障。

        應(yīng)用邏輯圖如下:  

        網(wǎng)絡(luò)拓?fù)淙缦?

        主從服務(wù)器之間沒有心跳數(shù)據(jù)，主服務(wù)器將sql操作語句(insert,update,delete這些對(duì)數(shù)據(jù)產(chǎn)生影響的操作)傳送給從服務(wù)器，他們之間通信是通過tcp/ip協(xié)議，3306端口傳送sql命令的ascii碼，沒有固定長(zhǎng)度，由于操作是用戶發(fā)起，主從服務(wù)器之間沒有固定流量，主服務(wù)器上執(zhí)行的sql語句在從服務(wù)器上也會(huì)執(zhí)行一遍。

        傳送的數(shù)據(jù)報(bào)文如下:  

        也是由于主從服務(wù)器之間通過3306端口同步數(shù)據(jù)，從服務(wù)器數(shù)量不能太多，否則會(huì)嚴(yán)重影響到主服務(wù)器的性能。

        6.2.4. 多臺(tái)MySQL服務(wù)器，采用MySQL推出的mysql cluster技術(shù)

        Mysql cluster是mysql公司推出的應(yīng)用，將數(shù)據(jù)存儲(chǔ)在數(shù)據(jù)節(jié)點(diǎn)服務(wù)器的內(nèi)存中，通過mysql節(jié)點(diǎn)查詢數(shù)據(jù)，由于該技術(shù)還不夠完善，目前沒有見到其商用案例。

        Mysql cluster中數(shù)據(jù)節(jié)點(diǎn)服務(wù)器與管理服務(wù)器之間通過tcp端口35595發(fā)心跳包，，由管理節(jié)點(diǎn)發(fā)出28字節(jié)詢問包，數(shù)據(jù)節(jié)點(diǎn)回應(yīng)100字節(jié)心跳包，再由管理節(jié)點(diǎn)回0字節(jié)的ack包。 mySql節(jié)點(diǎn)向數(shù)據(jù)節(jié)點(diǎn)端口35595發(fā)送36字節(jié)tcp心跳包，數(shù)據(jù)節(jié)點(diǎn)回ack。mySql節(jié)點(diǎn)查詢返回的數(shù)據(jù)也通過35595 tcp端口發(fā)送返回?cái)?shù)據(jù)，返回二進(jìn)制數(shù)據(jù)。

        應(yīng)用邏輯圖如下:   

        網(wǎng)絡(luò)拓?fù)淙缦?  

        6.2.5. 多臺(tái)mysql服務(wù)器采用不同技術(shù)混合使用

        讀寫分離，應(yīng)用分離，用戶分離，根據(jù)網(wǎng)站的不同訪問情況將不同的mysql數(shù)據(jù)庫(kù)分成不同的數(shù)據(jù)庫(kù)群，形成復(fù)雜的數(shù)據(jù)庫(kù)群應(yīng)用。

        例如以下拓?fù)鋱D:  

        7. 典型應(yīng)用描述

        YouTube網(wǎng)站

         維基百科

        淘寶、阿里巴巴都有部分采用

        新浪等

        8. MySQL的存儲(chǔ)引擎

        8.1. innoDB存儲(chǔ)引擎

        隨著oracle收購(gòu)了mysql后推出了mysql5.5的版本，mysql默認(rèn)數(shù)據(jù)庫(kù)引擎就修改為innodb，InnoDB給MySQL提供 了具有提交，回滾和崩潰恢復(fù)能力的事務(wù)安全(ACID兼容)存儲(chǔ)引擎。InnoDB鎖定在行級(jí)并且也在SELECT語句提供一個(gè)Oracle風(fēng)格一致的非 鎖定讀。這些特色增加 了多用戶部署和性能。沒有在InnoDB中擴(kuò)大鎖定的需要，因?yàn)樵贗nnoDB中行級(jí)鎖定適合非常小的空間。InnoDB也支持FOREIGN KEY強(qiáng)制。在SQL查詢中，你可以自由地將InnoDB類型的表與其它MySQL的表的類型混合起來，甚至在同一個(gè)查詢中也可以混合。

        InnoDB是為處理巨大數(shù)據(jù)量時(shí)的最大性能設(shè)計(jì)。它的CPU效率可能是任何其它基于磁盤的關(guān)系數(shù)據(jù)庫(kù)引擎所不能匹敵的innodb已經(jīng)初步具備了oracle的一些特征，使得mysql數(shù)據(jù)庫(kù)的性能越來越好。Innodb具備了數(shù)據(jù)文件、回滾段，分區(qū)表，存儲(chǔ)過程，支持?jǐn)?shù)據(jù)文件建立在裸設(shè)備中(原來都是oracle才具有的功能)，對(duì)事務(wù)支持越來越好，數(shù)據(jù)讀取速度更快，支持大型數(shù)據(jù)庫(kù)表。

        8.2. MyISAM存儲(chǔ)引擎

        MyISAM基于更老的ISAM代碼，但有很多有用的擴(kuò)展，MyISAM管理非事務(wù)表。它提供高速存儲(chǔ)和檢索，以及全文搜索能力。MyISAM在所有MySQL配置里被支持。

        9. MySQL的HA技術(shù)

        9.1. 采用共享存儲(chǔ)方式

        Mysql數(shù)據(jù)庫(kù)數(shù)據(jù)文件存放在共享存儲(chǔ)中(如網(wǎng)絡(luò)存儲(chǔ)、磁盤陣列，NFS網(wǎng)絡(luò)文件系統(tǒng))，兩臺(tái)Mysql服務(wù)器做主備，服務(wù)器之間通過心跳軟件發(fā)送網(wǎng)絡(luò)心跳信號(hào)，當(dāng)偵測(cè)到主服務(wù)器宕掉時(shí)，備用服務(wù)器接管所有的資源，取得vip成為主數(shù)據(jù)庫(kù)服務(wù)器。

        其拓?fù)鋱D如下:

        9.2. DRBD方式

        將mysql的數(shù)據(jù)庫(kù)數(shù)據(jù)通過drbd軟件復(fù)制成本地遠(yuǎn)程兩份，當(dāng)數(shù)據(jù)庫(kù)出現(xiàn)問題時(shí)，其數(shù)據(jù)可以從遠(yuǎn)程路徑中找到并成為新的數(shù)據(jù)庫(kù)，從而達(dá)到數(shù)據(jù)的高可用性。

        拓?fù)淙缦?

        9.3. 主從方式(異步方式)

        一臺(tái)mysql主服務(wù)器負(fù)責(zé)寫操作，將數(shù)據(jù)庫(kù)操作寫成日志文件，再將寫入日志文件的內(nèi)容通過網(wǎng)絡(luò)傳送給從mysql數(shù)據(jù)庫(kù)服務(wù)器，從而達(dá)到數(shù)據(jù)的高可用性(主從服務(wù)器數(shù)據(jù)有時(shí)間差)。

        9.4. 主從方式(半同步方式)

        一臺(tái)mysql主服務(wù)器負(fù)責(zé)寫操作，同步將操作通過網(wǎng)絡(luò)直接通知從mysql數(shù)據(jù)庫(kù)服務(wù)器，當(dāng)網(wǎng)絡(luò)出現(xiàn)問題時(shí)，這種同步方式自動(dòng)轉(zhuǎn)為復(fù)制日志文件的異步操作，從而達(dá)到數(shù)據(jù)的高可用性。

        9.5. 多主服務(wù)器方式

        多臺(tái)mysql服務(wù)器互為主從服務(wù)器，向其中任何一臺(tái)數(shù)據(jù)庫(kù)寫數(shù)據(jù)，該數(shù)據(jù)庫(kù)的所有數(shù)據(jù)操作都將通過網(wǎng)絡(luò)傳送給給其他mysql數(shù)據(jù)庫(kù)服務(wù)器，從而達(dá)到數(shù)據(jù)的高可用性。

        拓?fù)淙缦?

        9.6. Mysql cluster實(shí)現(xiàn)HA

        只要將Mysql cluster的復(fù)制數(shù)據(jù)配置改為2個(gè)以上就能達(dá)到數(shù)據(jù)備份在不同節(jié)點(diǎn)的作用，保證不會(huì)單點(diǎn)故障。

        10. MySQL數(shù)據(jù)庫(kù)實(shí)現(xiàn)過程舉例

        普通中型網(wǎng)站最常用的方式

        這個(gè)拓?fù)渲袃膳_(tái)mysql服務(wù)器互做主從關(guān)系，它們之間運(yùn)行ha軟件，vip在其中一臺(tái)服務(wù)器上，當(dāng)ha軟件檢測(cè)到master服務(wù)器出問題的時(shí)候就會(huì)將vip挪給salve的服務(wù)器，讓它成為master，剩下的服務(wù)器都是mysql的從服務(wù)器，只從vip的服務(wù)器上取日志將數(shù)據(jù)同步到本機(jī)中，對(duì)外提供查詢服務(wù)。

        11. Mysql數(shù)據(jù)包特點(diǎn)

        Mysql是基于tcp/ip進(jìn)行通信的，默認(rèn)tcp通信端口為3306，通信內(nèi)容基本是ascii碼。以下是mysql通信包解析圖:

        11.1. 用戶大量查詢數(shù)據(jù)包的特點(diǎn)

        Web服務(wù)器與mysql服務(wù)器之間，主要的包分布比例如下:

        其中包長(zhǎng)再80-319之間主要sql的查詢語句，包長(zhǎng)640以上主要是返回?cái)?shù)據(jù)，40-79之間的包主要是通訊包。

        當(dāng)用戶在web端發(fā)送select請(qǐng)求時(shí):

        mysql服務(wù)器與網(wǎng)絡(luò)存儲(chǔ)netapp之間的數(shù)據(jù)包統(tǒng)計(jì)信息如下:

        11.2. 純粹的數(shù)據(jù)庫(kù)操作語句(insert)，相關(guān)數(shù)據(jù)包特點(diǎn)

        Web服務(wù)器與mysql服務(wù)器之間數(shù)據(jù)包統(tǒng)計(jì)如下:

        在主mysql數(shù)據(jù)庫(kù)服務(wù)器與從mysql數(shù)據(jù)庫(kù)服務(wù)器之間的包統(tǒng)計(jì)結(jié)果如下:

        12. 測(cè)試不同交換機(jī)對(duì)mysql的影響

        根據(jù)mysql的各種組網(wǎng)方式對(duì)網(wǎng)絡(luò)時(shí)延的敏感性分析，選擇以下兩種情形檢查mysql查詢對(duì)交換機(jī)時(shí)延的敏感性。

        12.1. 測(cè)試交換機(jī)時(shí)延對(duì)mysql cluster的影響

        網(wǎng)絡(luò)拓?fù)淙缦聢D:

        根據(jù)mysql cluster的組網(wǎng)特點(diǎn)，一共5臺(tái)服務(wù)器，其中一臺(tái)做管理節(jié)點(diǎn)兼做lvs服務(wù)器(mgm node+lvs)，在剩下的4臺(tái)服務(wù)器上部署ndb node(mysql cluster的數(shù)據(jù)節(jié)點(diǎn)，mysql cluster所有的數(shù)據(jù)都放在ndb節(jié)點(diǎn)中，做兩份數(shù)據(jù)拷貝，保證任何一臺(tái)服務(wù)器損壞的情況下不影響整個(gè)系統(tǒng)運(yùn)行，就是沒有單點(diǎn)故障)，在其中的3臺(tái)服務(wù)器上部署Mysql node(提供用戶查詢?nèi)肟?，通過lvs將用戶請(qǐng)求平均分配在這三臺(tái)mysql node中)，在剩下的一臺(tái)中部署web服務(wù)器。用戶通過調(diào)用web服務(wù)器，將數(shù)據(jù)庫(kù)請(qǐng)求送往lvs節(jié)點(diǎn)，由lvs將數(shù)據(jù)庫(kù)請(qǐng)求平均分配給3臺(tái)mysql接入節(jié)點(diǎn)上，由3臺(tái)mysql接入節(jié)點(diǎn)到4臺(tái)數(shù)據(jù)節(jié)點(diǎn)中查詢數(shù)據(jù)。

        通過在web端發(fā)起并發(fā)的數(shù)據(jù)庫(kù)查詢請(qǐng)求，兩種交換機(jī)的時(shí)延對(duì)查詢的時(shí)延沒有造成任何影響。

        12.2. 測(cè)試交換機(jī)時(shí)延對(duì)mysql網(wǎng)絡(luò)存儲(chǔ)的影響

        網(wǎng)絡(luò)拓?fù)淙缦?

        在一臺(tái)服務(wù)器上安裝mysql server，將其innodb的數(shù)據(jù)文件放在通過iscsi連接的netapp網(wǎng)絡(luò)存儲(chǔ)中，在mysql server中建innodb數(shù)據(jù)表。在web端發(fā)起對(duì)mysql innodb數(shù)據(jù)庫(kù)表的查詢，這樣mysql的查詢就通過iscsi訪問到達(dá)骨干交換機(jī)6080或者6210直到netapp網(wǎng)絡(luò)存儲(chǔ)。

        通過對(duì)innodb的全表掃描、并發(fā)全表掃描、并發(fā)多次查詢數(shù)據(jù)，沒有發(fā)現(xiàn)兩款交換機(jī)時(shí)延對(duì)查詢速度有影響。

        通過測(cè)試軟件sysbench測(cè)試mysq innodb性能(測(cè)試數(shù)據(jù)5000000條記錄，32個(gè)線程，采用innodb引擎，確保測(cè)試數(shù)據(jù)通過iscsi訪問網(wǎng)絡(luò)存儲(chǔ))，沒發(fā)現(xiàn)兩款交換機(jī)的時(shí)延對(duì)mysql的讀寫性能造成任何影響。