MYSQL集群解決實施方案

./MYSQL集群解決方案--高可用性、負(fù)載均衡一、mysql的市場占有率二、mysql為什么受到如此的歡迎三、mysql數(shù)據(jù)庫系統(tǒng)的優(yōu)缺點四、網(wǎng)絡(luò)服務(wù)器的需求五、什么是mysql的集群六、什么是負(fù)載均衡七、mysql集群部署和實現(xiàn)方法八、負(fù)載均衡的配置和測試九、Mysql集群系統(tǒng)的測試〔測試方案+測試腳本+測試結(jié)果分析mysql的市場占有率MySQL是世界上最流行的開源數(shù)據(jù)庫,已有1100多萬的擊活安裝,每天超過五萬的下載。MySQL為全球開發(fā)者、DBA和IT管理者在可靠性、性能、易用性方面提供了選擇。第三方市場調(diào)查機(jī)構(gòu)EvansDataCorporation調(diào)查顯示,過去兩年內(nèi)在開發(fā)者使用的所有數(shù)據(jù)庫中,MySQL已經(jīng)擁有了25%的市場占有率。開源已經(jīng)成為當(dāng)今IT結(jié)構(gòu)中不可或缺的重要部分,而且開源的市場占有率將繼續(xù)增加。如下圖所示：mysql為什么受到如此的歡迎Sun公司今天1月份花了10億美元將mysql收購,準(zhǔn)備進(jìn)軍開源和數(shù)據(jù)庫。數(shù)據(jù)庫系統(tǒng)OracleSQL

ServerMySQLDB2是否免費收費收費免費收費存儲過程支持支持支持支持視圖支持支持支持支持快照支持支持不支持支持觸發(fā)器支持支持支持支持安全強中中強復(fù)雜查詢強中弱中索引豐富一般弱中數(shù)據(jù)類型多多多多事務(wù)處理強強弱中mysql數(shù)據(jù)庫系統(tǒng)的優(yōu)缺點每個系統(tǒng)都有自身的不足和發(fā)展歷程,mysql也一樣。優(yōu)點缺點1.

源碼公開,免費2.

跨平臺3.

為多種開發(fā)語言和包提供了API4.

支持多線程5.

小巧、靈活、速度較快6.

支持各種字符集7.

提供各種連接、優(yōu)化的工具包1.

不完善,很多數(shù)據(jù)庫特性不支持2.

只適合中小型應(yīng)用,對于大型應(yīng)用,可以跟其他數(shù)據(jù)庫互補；3.

數(shù)據(jù)庫系統(tǒng)數(shù)據(jù)量只能達(dá)到千萬級別；網(wǎng)絡(luò)服務(wù)的需求隨著Internet的飛速發(fā)展和對我們生活的深入影響,越來越多的個人在互聯(lián)網(wǎng)上購物、娛樂、休閑、與人溝通、獲取信息；越來越多的企業(yè)把他們與顧客和業(yè)務(wù)伙伴之間的聯(lián)絡(luò)搬到互聯(lián)網(wǎng)上,通過網(wǎng)絡(luò)來完成交易,建立與客戶之間的聯(lián)系?；ヂ?lián)網(wǎng)的用戶數(shù)和網(wǎng)絡(luò)流量正以幾何級數(shù)增長,這對網(wǎng)絡(luò)服務(wù)的可伸縮性提出很高的要求。例如,比較熱門的Web站點會因為被訪問次數(shù)急劇增長而不能及時處理用戶的請求,導(dǎo)致用戶進(jìn)行長時間的等待,大大降低了服務(wù)質(zhì)量。另外,隨著電子商務(wù)等關(guān)鍵性應(yīng)用在網(wǎng)上運行,任何例外的服務(wù)中斷都將造成不可估量的損失,服務(wù)的高可用性也越來越重要。所以,對用硬件和軟件方法實現(xiàn)高可伸縮、高可用網(wǎng)絡(luò)服務(wù)的需求不斷增長,這種需求可以歸結(jié)以下幾點：1>

可伸縮性〔Scalability,當(dāng)服務(wù)的負(fù)載增長時,系統(tǒng)能被擴(kuò)展來滿足需求,且不降低服務(wù)質(zhì)量。2>

高可用性〔Availability,盡管部分硬件和軟件會發(fā)生故障,整個系統(tǒng)的服務(wù)必須是每天24小時每星期7天可用的。3>

可管理性〔Manageability,整個系統(tǒng)可能在物理上很大,但應(yīng)該容易管理。4>

價格有效性〔Cost-effectiveness,整個系統(tǒng)實現(xiàn)是經(jīng)濟(jì)的、易支付的。單服務(wù)器顯然不能處理不斷增長的負(fù)載。這種服務(wù)器升級方法有下列不足：一是升級過程繁瑣,機(jī)器切換會使服務(wù)暫時中斷,并造成原有計算資源的浪費；二是越往高端的服務(wù)器,所花費的代價越大；三是一旦該服務(wù)器或應(yīng)用軟件失效,會導(dǎo)致整個服務(wù)的中斷。通過高性能網(wǎng)絡(luò)或局域網(wǎng)互聯(lián)的服務(wù)器集群正成為實現(xiàn)高可伸縮的、高可用網(wǎng)絡(luò)服務(wù)的有效結(jié)構(gòu)。這種松耦合結(jié)構(gòu)比緊耦合的多處理器系統(tǒng)具有更好的伸縮性和性能價格比,組成集群的PC服務(wù)器或RISC服務(wù)器和標(biāo)準(zhǔn)網(wǎng)絡(luò)設(shè)備因為大規(guī)模生產(chǎn),價格低,具有很高的性能價格比。但是,這里有很多挑戰(zhàn)性的工作,如何在集群系統(tǒng)實現(xiàn)并行網(wǎng)絡(luò)服務(wù),它對外是透明的,它具有良好的可伸縮性和可用性。針對上述需求,我們給出了基于IP層和基于內(nèi)容請求分發(fā)的負(fù)載平衡調(diào)度解決方法,并在Linux內(nèi)核中實現(xiàn)了這些方法,將一組服務(wù)器構(gòu)成一個實現(xiàn)可伸縮的、高可用網(wǎng)絡(luò)服務(wù)的服務(wù)器集群,我們稱之為Linux虛擬服務(wù)器〔LinuxVirtualServer。在LVS集群中,使得服務(wù)器集群的結(jié)構(gòu)對客戶是透明的,客戶訪問集群提供的網(wǎng)絡(luò)服務(wù)就像訪問一臺高性能、高可用的服務(wù)器一樣。客戶程序不受服務(wù)器集群的影響不需作任何修改。系統(tǒng)的伸縮性通過在服務(wù)機(jī)群中透明地加入和刪除一個節(jié)點來達(dá)到,通過檢測節(jié)點或服務(wù)進(jìn)程故障和正確地重置系統(tǒng)達(dá)到高可用性。什么是mysql集群分為同步集群和異步集群。同步集群〔mysqlcluster結(jié)構(gòu)：〔data+sql+mgm節(jié)點特點：1>

內(nèi)存級別的,對硬件要求較低,但是對內(nèi)存要求較大。換算比例為：1：1.1；2>

數(shù)據(jù)同時放在幾臺服務(wù)器上,冗余較好；3>

速度一般；4>

建表需要聲明為engine=ndbcluster5>

擴(kuò)展性強；6>

可以實現(xiàn)高可用性和負(fù)載均衡,實現(xiàn)對大型應(yīng)用的支持；7>

必須是特定的mysql版本,如：已經(jīng)編譯好的max版本；8>

配置和管理方便,不會丟失數(shù)據(jù)；異步集群〔mysqlreplication結(jié)構(gòu)：〔master+slave特點：1>

主從數(shù)據(jù)庫異步數(shù)據(jù)；2>

數(shù)據(jù)放在幾臺服務(wù)器上,冗余一般；3>

速度較快；4>

擴(kuò)展性差；5>

無法實現(xiàn)高可用性和負(fù)載均衡〔只能在程序級別實現(xiàn)讀寫分離,減輕對主數(shù)據(jù)庫的壓力；6>

配置和管理較差,可能會丟失數(shù)據(jù)；什么是負(fù)載均衡通過director,將用戶的請求分發(fā)到realserver服務(wù)器上,然后返回給用戶。負(fù)載均衡部署靈活、能夠滿足各種需求。實現(xiàn)方式：硬件：BIG/IP、Cisco、IBM〔昂貴軟件：LVS〔免費LVS系統(tǒng)將用戶的請求的數(shù)據(jù)包在數(shù)據(jù)層和網(wǎng)絡(luò)層進(jìn)行了封裝和轉(zhuǎn)發(fā),由三種方式滿足各種需求。1>

DR：直接路由2>

Tuning：tcp/ip隧道3>

NAT：網(wǎng)絡(luò)地址轉(zhuǎn)換需求：免費的軟件包1>

2臺低端的director〔active和standby2>

心跳線：連接2臺director,檢測活動情況3>

2臺以上的realservers通用結(jié)構(gòu)：有興趣的可以分別研究上面的三種LVS結(jié)構(gòu)。mysql集群部署和實現(xiàn)方法1>

假設(shè)現(xiàn)在有4臺服務(wù)器〔mysql官方推薦的最小配置服務(wù)器開啟的服務(wù)角色Ndb1MysqldMysql

APINdb_mgmd管理節(jié)點<master>HeartbeatDirector<master>1Ndb2MysqldMysql

APINdb_mgmd管理節(jié)點<backup>HeartbeatDirector<standby>Sql1MysqldMysql

API〔realserverNdbd存儲節(jié)點Arptables訪問路由Sql2MysqldMysql

API〔realserverNdbd存儲節(jié)點Arptables訪問路由2服務(wù)器安裝配置和網(wǎng)絡(luò)連接<以下為所有服務(wù)器各操作一遍,共4遍>安裝：將4臺服務(wù)器安裝CentOS5.2,選擇下面的包：ClusteringStorage

Clusteringmysql不需要安裝,但perl-mysql-xxx的所有包需要安裝開發(fā)工具包和類庫sshd服務(wù)SElinux

==>disable語言支持包不安裝,默認(rèn)美國英語設(shè)定主機(jī)名：Vi/etc/sysconfig/networkHostname=xxx:wq檢查主機(jī)名：Uname-a必須和上表中的一一對應(yīng)。否則有問題。Vi/etc/hosts更新：#yum

update

-y

&&

yum

-y

install

lynx

libawt

xorg-x11-deprecated-libs

nx

freenx

arptables_jf

httpd-devel下載：Mysql

cluster版本〔我下載的5.0.67社區(qū)版本：[root@ndb1

RHEL5]#

ls

-lh

MySQL*

|

awk

'{print

$9}'[root@ndb1

RHEL5]#

在服務(wù)器上安裝以上包,在安裝的過程中如果缺少包或者庫,采用：yum

install

xxxx自行安裝。建立目錄：#mkdir

/var/lib/mysql-cluster-p以下分別操作：安裝cluster組件：#Rpm

-Uvh

MySQL-xx-xx.rpm,根據(jù)不同,可以少安裝部分組件。根據(jù)你需要而定。163、26上,我安裝了：[root@ndb1

RHEL5]#

rpm

-aq

|

grep

MySQL.rhel5[root@ndb1

RHEL5]#

101、77上,我安裝了：[root@sql1

~]#

rpm

-aq

|

grep

MySQL[root@sql1

~]#

以下在ndb1〔164和ndb2〔26上操作[root@ndb1~]#vi/var/lib/mysql-cluster/config.ini[NDBDDEFAULT]NoOfReplicas=2DataMemory=800MIndexMemory=400M[MYSQLDDEFAULT][NDB_MGMDDEFAULT][TCPDEFAULT]#Sectionfortheclustermanagementnode[NDB_MGMD]#IPaddressofthemanagementnode<thissystem>ID=1[NDB_MGMD]#IPaddressofthemanagementnode<thissystem>ID=2#Sectionforthestoragenodes[NDBD]#IPaddressofthefirststoragenodeDataDir=/var/lib/mysql-cluster[NDBD]#IPaddressofthesecondstoragenodeDataDir=/var/lib/mysql-cluster#one[MYSQLD]perstoragenode[MYSQLD][MYSQLD][MYSQLD][MYSQLD][MYSQLD][MYSQLD][MYSQLD]:wq以下在mysqlAPI上操作〔這里,我設(shè)定了7個API,以后可以隨時加入MysqldAPI的配置文件：Vi/etc/f[root@ndb1~]#cat/etc/f[mysqld]ndbclusterndb-connectstring="host=64,host=6"[ndb_mgm]connect-string="host=64,host=6"[ndbd]connect-string="host=64,host=6":wq分別啟動ndb_mgmd/ndbd/mysqld164/26:ndb_mgmd-f/var/lib/mysql-cluster/config.ini77/101:Ndbd--initial164/26/77/101:/etc/rc.d/init.d/mysqlstart在管理節(jié)點ndb1<164>和ndb2<26>上查看各節(jié)點的情況：[root@ndb1~]#ndb_mgm--NDBCluster--ManagementClient--ndb_mgm>showConnectedtoManagementServerat:64:1186ClusterConfiguration[ndbd<NDB>]2node<s>id=3<Version:5.0.67,Nodegroup:0,Master>id=4<Version:5.0.67,Nodegroup:0>[ndb_mgmd<MGM>]2node<s>id=1<Version:5.0.67>id=2<Version:5.0.67>[mysqld<API>]7node<s>id=5<Version:5.0.67>id=6<Version:5.0.67>id=7<Version:5.0.67>id=8<Version:5.0.67>id=9<notconnected,acceptingconnectfromanyhost>id=10<notconnected,acceptingconnectfromanyhost>id=11<notconnected,acceptingconnectfromanyhost>ndb_mgm>以上說明一切正常。將服務(wù)增加到開機(jī)啟動服務(wù)項中：164/26:echo

'ndb_mgmd

-f

/var/lib/mysql-cluster/config.ini'

>

/etc/rc.d/init.d/ndb_mgmdchmod

755

/etc/rc.d/init.d/ndb_mgmd77/101:Echo'ndbd'>/etc/rc.d/init.d/ndbdChmod755/etc/rc.d/init.d/ndbdChkconfig--level2345ndbdonOK,到此mysqlcluster配置完成。強調(diào)：1由于數(shù)據(jù)放在內(nèi)存中,需要在ndb節(jié)點上加大內(nèi)存的數(shù)量。按照1：1.1的比例,如果數(shù)據(jù)量達(dá)到3.6GB,需要4GB的內(nèi)存。2由于NDB和mysqld〔API都很耗費內(nèi)存,所以建議將NDB放在164和26上?？赡軉拥臅r候會有警告,但是沒關(guān)系的。查看數(shù)據(jù)和內(nèi)存情況：77：[root@sql2~]#toptop-16:39:36up1:59,1user,loadaverage:1.37,0.76,0.60Tasks:80total,2running,78sleeping,0stopped,0zombieCpu<s>:4.0%us,4.0%sy,0.0%ni,87.3%id,2.9%wa,0.2%hi,1.5%si,0.0%stMem:2075600ktotal,2005868kused,69732kfree,68256kbuffersSwap:2031608ktotal,0kused,2031608kfree,1400812kcachedPIDUSERPRNIVIRTRESSHRS%CPU%MEMTIME+COMMAND2306mysql250119m15m23791root1501587m484m由于77只有2GB的內(nèi)存,而在config.ini中,把1.2GB的內(nèi)存分配給了NDB,所以,加上mysqld用掉的,2GB的內(nèi)存似乎已經(jīng)所剩無幾了。查看77上的數(shù)據(jù)大?。篬root@sql2~]#cd/var/lib/mysql-cluster/ndb_4_fs/[root@sql2ndb_4_fs]#du-lh1.3GB連接API創(chuàng)建數(shù)據(jù)庫：由于上面4臺都做為mysqld的API,所以創(chuàng)建數(shù)據(jù)庫的時候,都需要創(chuàng)建一遍。以下操作在4臺API上都需要操作：#Mysql-uroot-pxxxxxxxxxxxx-AMysql>createdatabasetestdatabase;Mysql>grantallon*.testdatabasetoroot@'192.168.131.%'identifiedby'xxxxxxxxxxxxxxx';Mysql>flushprivileges;Mysql>createtabletest<int<1>>;Mysql>insertintotest<1>;Mysql>quit;以上做完以后,可以通過任意一臺API上創(chuàng)建表,并寫數(shù)據(jù)到表中,其他數(shù)據(jù)庫都會同步寫入。分別連接每臺服務(wù)器進(jìn)行檢查：#Mysql-uroot-pxxxxxxxxxxxx-AMysql>usetestdatabase;Mysql>select*fromtest;如果輸出結(jié)果完全相同,表明mysqlcluster已經(jīng)可以正常工作了。在2臺API上設(shè)置LVSMysqlcluster做好以后,數(shù)據(jù)庫分別建立同名的數(shù)據(jù)庫以后,權(quán)限分配好,然后只要在一臺上寫入數(shù)據(jù),其他的NDB就存儲了相同的數(shù)據(jù)。用程序連接任意一臺API寫數(shù)據(jù),如果程序中未設(shè)置API的選擇和判斷,只使用了其中一個API,一旦API當(dāng)機(jī),則無法寫入數(shù)據(jù),必須修改程序。即便做了API的判斷和選擇,因為沒有實現(xiàn)負(fù)載均衡,服務(wù)器的性能沒有充分利用。高可用性也沒有達(dá)到目標(biāo)。所以,我們現(xiàn)在在2臺API之間做LVS。LVS采用ultramonkey〔首先在NDB1〔164和NDB2〔26上下載heartbeat的軟件包：下載所有的rpm包：Cd/usr/local/srcMkdirheartbeatCdheartbeat#Wgetx我下載了如下的軟件包：[root@ndb1heartbeat]#ls-lh*.rpm|awk'{print$9}';heartbeat-heartbeat-ldirectord-heartbeat-pils-heartbeat-stonith-libnet-perl-Mail-IMAPClient-perl-Parse-R[root@ndb1heartbeat]#Heartbeat中包含以下幾部分：1>

MasterDirector〔分發(fā)器--MD2>

BackupDirector〔備份分發(fā)器--BD3>

Realserver〔真實服務(wù)器,可以有2個以上--RSIP設(shè)置并確認(rèn)：MD:Eth1:.1/24MD:Eth1:.2--用戶訪問的統(tǒng)一虛擬IP。。。等等以下操作在所有服務(wù)器上執(zhí)行：主機(jī)名確認(rèn)：分別執(zhí)行：#uname-a主機(jī)名對應(yīng)表中所列。在MD和BD修改IP轉(zhuǎn)發(fā)：#vimodprobe.shmodprobe

ip_vs_dhmodprobe

ip_vs_ftpmodprobe

ip_vsmodprobe

ip_vs_lblcmodprobe

ip_vs_lblcrmodprobe

ip_vs_lcmodprobe

ip_vs_nqmodprobe

ip_vs_rrmodprobe

ip_vs_sedmodprobe

ip_vs_shmodprobe

ip_vs_wlcmodprobe

ip_vs_wrr

:wq#chmod755modprobe.sh#shmodprobe.sh#vi

/etc/modulesip_vs_dhip_vs_ftpip_vsip_vs_lblcip_vs_lblcrip_vs_lcip_vs_nqip_vs_rrip_vs_sedip_vs_ship_vs_wlcip_vs_wrr:wq#Vi/etc/sysctl.conf=0改為：=1使修改生效：/sbin/sysctl-p在MD和BD上安裝heartbeat軟件包#Rpm-Uvhperl-xx-xx-xx.rpm#Yuminstallheartbeat#Rpm-Uvh#rpm-Uvh缺少perl包,就使用yuminstallperl-xx-xx#Perl-CPAN-eshell這樣安裝的perl包不知道為何不好使？奇怪這里VIP實際上是綁定在2臺director上。所以director之間需要做心跳處理。心跳線使用eth1口,用交叉線連接起來。這樣可以避免影響其他服務(wù)器。配置heartbeatHeartbeat有3個配置文件：Ha.cfAuthkeysHaresourcesldirectord進(jìn)程的配置文件Ldirectord.cf一共需要配置4個配置文件。#viha.cflogfacilitylocal0bcasteth1mcasteth169410auto_failbackoffnodendb1nodendb2respawnhacluster/usr/lib/heartbeat/ipfailapiauthipfailgid=haclientuid=hacluster:wq#viauthkeysauth33md5514a49f83820e34c:wq#viharesourcesndb1\ldirectord::ldirectord.cf\LVSSyncDaemonSwap::master\Ndb2上需要將主機(jī)名更改一下。:wq設(shè)置屬性并使heartbeat開機(jī)啟動#chmod600/etc/ha.d/authkeys#/sbin/chkconfig--level2345heartbeaton#/sbin/chkconfig--delldirectord啟動heartbeat：/etc/init.d/ldirectordstop/etc/init.d/heartbeatstart在MD和BD上檢查VIP是否生效：ip

addr

sh

eth0[root@ndb1

ha.d]#

ip

addr

sh

eth02:

eth0:

<BROADCAST,MULTICAST,UP,LOWER_UP>

mtu

1500

qdisc

pfifo_fast

qlen

1000

link/ether

00:30:48:28:c6:85

brd

ff:ff:ff:ff:ff:ff

inet

64/24

brd

55

scope

global

eth0

inet

05/24

brd

55

scope

global

secondary

eth0

inet6

fe80::230:48ff:fe28:c685/64

scope

link

valid_lft

forever

preferred_lft

forever[root@ndb1

ha.d]#

[root@ndb2

~]#

ip

addr

sh

eth02:

eth0:

<BROADCAST,MULTICAST,UP,LOWER_UP>

mtu

1500

qdisc

pfifo_fast

qlen

1000

link/ether

00:30:48:28:c4:af

brd

ff:ff:ff:ff:ff:ff

inet

6/24

brd

55

scope

global

eth0

inet6

fe80::230:48ff:fe28:c4af/64

scope

link

valid_lft

forever

preferred_lft

forever[root@ndb2

~]#現(xiàn)在在MD〔164上已經(jīng)生效了。檢查ldirectored進(jìn)程[root@ndb1ha.d]#/usr/sbin/ldirectordldirectord.cfstatusldirectordfor/etc/ha.d/ldirectord.cfisrunningwithpid:5596[root@ndb1ha.d]#[root@ndb2~]#/usr/sbin/ldirectordldirectord.cfstatusldirectordisstoppedfor/etc/ha.d/ldirectord.cf[root@ndb2~]#VIP生效的director應(yīng)該是running狀態(tài),standby應(yīng)該是stop狀態(tài)。利用ipvs檢查包轉(zhuǎn)發(fā)是否生效[root@ndb1ha.d]#/sbin/ipvsadm-L-nIPVirtualServerversion<size=4096>ProtLocalAddress:PortSchedulerFlags->RemoteAddress:PortForwardWeightActiveConnInActConnTCP05:3306wrr->7:3306Route133034->01:3306Route133038[root@ndb1ha.d]#[root@ndb2~]#/sbin/ipvsadm-L-nIPVirtualServerversion<size=4096>ProtLocalAddress:PortSchedulerFlags->RemoteAddress:PortForwardWeightActiveConnInActConn[root@ndb2~]#在MB上已經(jīng)生效了。在MD和BD上檢查LVSSyncDaemonSwap的狀態(tài)：[root@ndb1ha.d]#/etc/ha.d/resource.d/LVSSyncDaemonSwapmasterstatusmasterrunning<ipvs_syncmasterpid:5689>[root@ndb1ha.d]#[root@ndb2~]#/etc/ha.d/resource.d/LVSSyncDaemonSwapmasterstatusmasterstopped<ipvs_syncbackuppid:5493>[root@ndb2~]#同樣,standby的處于stopped狀態(tài)。以下在RS服務(wù)器上執(zhí)行：ARP轉(zhuǎn)發(fā)限制MD或者BD采用ARP欺騙將ARP包轉(zhuǎn)發(fā)給下面的realserver。為了轉(zhuǎn)發(fā)成功,需要做ARP限制。#/etc/init.d/arptables_jfstop#/usr/sbin/arptables-noarp-addrstart#/etc/init.d/arptables_jfsave#/sbin/chkconfig--level2345arptables_jfon#/etc/init.d/arptables_jfstart查看限制鏈表[root@sql2mysql-cluster]#/sbin/arptables-L-v-nChainIN<policyACCEPT29243packets,819Kbytes>pktsbytestargetinoutsource-ipdestination-ipsource-hwdestination-hwhlenophrdpro541512DROP**.0/000/0000/00any0000/00000000/00000000/0000ChainOUT<policyACCEPT3931packets,110Kbytes>pktsbytestargetinoutsource-ipdestination-ipsource-hwdestination-hwhlenophrdpro00mangle*eth0.0/000/0000/00any0000/00000000/00000000/0000--mangle-ip-sChainFORWARD<policyACCEPT0packets,0bytes>pktsbytestargetinoutsource-ipdestination-ipsource-hwdestination-hwhlenophrdpro[root@sql2mysql-cluster]#[root@sql1~]#/sbin/arptables-L-v-nChainIN<policyACCEPT29375packets,823Kbytes>pktsbytestargetinoutsource-ipdestination-ipsource-hwdestination-hwhlenophrdpro541512DROP**.0/000/0000/00any0000/00000000/00000000/0000ChainOUT<policyACCEPT3903packets,109Kbytes>pktsbytestargetinoutsource-ipdestination-ipsource-hwdestination-hwhlenophrdpro00mangle*eth0.0/000/0000/00any0000/00000000/00000000/0000--mangle-ip-sChainFORWARD<policyACCEPT0packets,0bytes>pktsbytestargetinoutsource-ipdestination-ipsource-hwdestination-hwhlenophrdpro[root@sql1~]#這樣,由MD或者BD轉(zhuǎn)發(fā)過來的ARP包就被鏈表控制了。設(shè)置如何接收ARP包以下在所有RS上執(zhí)行#cp/etc/sysconfig/network-scripts/ifcfg-lo/etc/sysconfig/network-scripts/ifcfg-lo:0#Vi/etc/sysconfig/network-scripts/ifcfg-lo\:0DEVICE=lo:0NETWORK=192.ONBOOT=yesNAME=loopback:wq#/sbin/ifuplo查看lo:0[root@sql1~]#ipaddrshlo1:lo:<LOOPBACK,UP,LOWER_UP>mtu16436qdiscnoqueuelink/loopback00:00:00:00:00:00brd00:00:00:00:00:00inetscopehostloinetbrdscopegloballo:0inet6::1/128scopehostvalid_lftforeverpreferred_lftforever[root@sql1~]#[root@sql2mysql-cluster]#ipaddrshlo1:lo:<LOOPBACK,UP,LOWER_UP>mtu16436qdiscnoqueuelink/loopback00:00:00:00:00:00brd00:00:00:00:00:00inetscopehostloinetbrdscopegloballo:0inet6::1/128scopehostvalid_lftforeverpreferred_lftforever[root@sql2mysql-cluster]#重新啟動服務(wù)器以下在所有服務(wù)器上執(zhí)行〔請確認(rèn)ip,服務(wù)器上沒有running任何正在使用的服務(wù)reboot啟動mysqlcluster：順序：ndb_mgmd--164/26Ndbd--101/77Mysqld--所有檢查服務(wù)是否正常以下在ndb上執(zhí)行#ndb_mgm[root@ndb1ha.d]#ndb_mgm--NDBCluster--ManagementClient--ndb_mgm>showConnectedtoManagementServerat:64:1186ClusterConfiguration[ndbd<NDB>]2node<s>id=3<Version:5.0.67,Nodegroup:0,Master>id=4<Version:5.0.67,Nodegroup:0>[ndb_mgmd<MGM>]2node<s>id=1<Version:5.0.67>id=2<Version:5.0.67>[mysqld<API>]7node<s>id=5<Version:5.0.67>id=6<Version:5.0.67>id=7<Version:5.0.67>id=8<Version:5.0.67>id=9<notconnected,acceptingconnectfromanyhost>id=10<notconnected,acceptingconnectfromanyhost>id=11<notconnected,acceptingconnectfromanyhost>ndb_mgm>一切正常。檢查heartbeat是否正常：關(guān)閉BD,在MD上查看日志：[root@ndb1ha.d]#tail-f/var/log/messagesDec1719:42:21ndb1heartbeat:[5462]:info:Receivedshutdownnoticefrom'ndb2'.Dec1719:42:21ndb1heartbeat:[5462]:info:Resourcesbeingacquiredfromndb2.Dec1719:42:21ndb1harc[7085]:info:Running/etc/ha.d/rc.d/statusstatusDec1719:42:21ndb1mach_down[7118]:info:/usr/share/heartbeat/mach_down:nice_failback:foreignresourcesacquiredDec1719:42:21ndb1mach_down[7118]:info:mach_downtakeovercompletefornodendb2.Dec1719:42:21ndb1heartbeat:[5462]:info:mach_downtakeovercomplete.Dec1719:42:21ndb1ldirectord[7153]:Invokingldirectordinvokedas:/etc/ha.d/resource.d/ldirectordldirectord.cfstatusDec1719:42:21ndb1ldirectord[7153]:ldirectordfor/etc/ha.d/ldirectord.cfisrunningwithpid:5596Dec1719:42:21ndb1ldirectord[7153]:ExitingfromldirectordstatusDec1719:42:21ndb1heartbeat:[7086]:info:LocalResourceacquisitioncompleted.Dec1719:42:21ndb1harc[7175]:info:Running/etc/ha.d/rc.d/ip-request-respip-request-respDec1719:42:21ndb1ip-request-resp[7175]:receivedip-request-respldirectord::ldirectord.cfOKyesDec1719:42:21ndb1ResourceManager[7196]:info:Acquiringresourcegroup:ndb1ldirectord::ldirectord.cfLVSSyncDaemonSwap::masterDec1719:42:22ndb1ldirectord[7223]:Invokingldirectordinvokedas:/etc/ha.d/resource.d/ldirectordldirectord.cfstatusDec1719:42:22ndb1ldirectord[7223]:ldirectordfor/etc/ha.d/ldirectord.cfisrunningwithpid:5596Dec1719:42:22ndb1ldirectord[7223]:ExitingfromldirectordstatusDec1719:42:22ndb1ResourceManager[7196]:info:Running/etc/ha.d/resource.d/ldirectordldirectord.cfstartDec1719:42:23ndb1ldirectord[7245]:Invokingldirectordinvokedas:/etc/ha.d/resource.d/ldirectordldirectord.cfstartDec1719:42:23ndb1IPaddr2[7291]:INFO:RunningOK如果沒有出現(xiàn)異常,表明一切正常。破壞性試驗1>

檢查ndbd關(guān)閉任意一臺ndbd的進(jìn)程,在ndb_mgm上查看是否失去連接。如果失去連接,表示已經(jīng)識別出來。此時在數(shù)據(jù)庫表中增加內(nèi)容之后啟動剛剛關(guān)閉的ndbd,檢查新寫入的數(shù)據(jù)是否已經(jīng)被同步過來。如果同步過來,一切正常。2>

檢查heartbeat關(guān)閉MD,檢查BD的反應(yīng)：[root@ndb2~]#tail-f/var/log/messagesDec1719:47:22ndb2harc[6862]:info:Running/etc/ha.d/rc.d/statusstatusDec1719:47:23ndb2heartbeat:[6852]:info:Comm_now_up<>:updatingstatustoactiveDec1719:47:23ndb2heartbeat:[6852]:info:Localstatusnowsetto:'active'Dec1719:47:23ndb2heartbeat:[6852]:info:Startingchildclient"/usr/lib/heartbeat/ipfail"<498,496>Dec1719:47:23ndb2heartbeat:[6879]:info:Starting"/usr/lib/heartbeat/ipfail"asuid498gid496<pid6879>Dec1719:47:23ndb2heartbeat:[6852]:info:remoteresourcetransitioncompleted.Dec1719:47:23ndb2heartbeat:[6852]:info:remoteresourcetransitioncompleted.Dec1719:47:23ndb2heartbeat:[6852]:info:LocalResourceacquisitioncompleted.<none>Dec1719:47:23ndb2heartbeat:[6852]:info:Initialresourceacquisitioncomplete<T_RESOURCES<them>>Dec1719:47:29ndb2ipfail:[6879]:info:Pingnodecountisbalanced.Dec1719:47:43ndb2heartbeat:[6852]:info:Receivedshutdownnoticefrom'ndb1'.Dec1719:47:43ndb2heartbeat:[6852]:info:Resourcesbeingacquiredfromndb1.Dec1719:47:43ndb2heartbeat:[6884]:info:acquireallHAresources<standby>.Dec1719:47:43ndb2ResourceManager[6911]:info:Acquiringresourcegroup:ndb2ldirectord::ldirectord.cfLVSSyncDaemonSwap::masterDec1719:47:43ndb2ldirectord[6957]:ldirectordisstoppedfor/etc/ha.d/ldirectord.cfDec1719:47:43ndb2ldirectord[6957]:Exitingwithexit_status3:ExitingfromldirectordstatusDec1719:47:43ndb2heartbeat:[6885]:info:LocalResourceacquisitioncompleted.Dec1719:47:43ndb2ldirectord[6961]:ldirectordisstoppedfor/etc/ha.d/ldirectord.cfDec1719:47:43ndb2ldirectord[6961]:Exitingwithexit_status3:ExitingfromldirectordstatusDec1719:47:43ndb2ResourceManager[6911]:info:Running/etc/ha.d/resource.d/ldirectordldirectord.cfstartDec1719:47:44ndb2ldirectord[6986]:StartingLinuxDirectorasdaemonDec1719:47:44ndb2ldirectord[6988]:Addedvirtualserver:05:3306Dec1719:47:44ndb2ldirectord[6988]:Quiescentrealserver:01:3306mappedfrom01:3306<x05:3306><Weightsetto0>Dec1719:47:44ndb2ldirectord[6988]:Quiescentrealserver:7:3306mappedfrom7:3306<x05:3306><Weightsetto0>Dec1719:47:44ndb2ResourceManager[6911]:info:Running/etc/ha.d/resource.d/LVSSyncDaemonSwapmasterstartDec1719:47:44ndb2kernel:IPVS:stoppingsyncthread5493...Dec1719:47:45ndb2kernel:IPVS:syncthreadstopped!Dec1719:47:45ndb2LVSSyncDaemonSwap[7050]:info:ipvs_syncbackupdownDec1719:47:45ndb2kernel:IPVS:syncthreadstarted:state=MASTER,mcast_ifn=eth0,syncid=0Dec1719:47:45ndb2LVSSyncDaemonSwap[7050]:info:ipvs_syncmasterupDec1719:47:45ndb2LVSSyncDaemonSwap[7050]:info:ipvs_syncmasterobtainedDec1719:47:45ndb2IPaddr2[7102]:INFO:ResourceisstoppedDec1719:47:45ndb2ResourceManager[6911]:info:Running/etc/ha.d/resource.d/IPaddr2startDec1719:47:45ndb2IPaddr2[7214]:INFO:ip-finetaddraddbrddeveth0Dec1719:47:45ndb2avahi-daemon[2776]:Registeringnewaddressrecordforoneth0.Dec1719:47:45ndb2IPaddr2[7214]:INFO:iplinkseteth0upDec1719:47:45ndb2IPaddr2[7214]:INFO:/usr/lib/heartbeat/send_arp-i200-r5-peth0autonot_usednot_usedDec1719:47:45ndb2kernel:IPVS:ip_vs_wrr_schedule<>:noavailableserversDec1719:47:45ndb2kernel:IPVS:ip_vs_wrr_schedule<>:noavailableserversDec1719:47:45ndb2IPaddr2[7185]:INFO:SuccessDec1719:47:45ndb2kernel:IPVS:ip_vs_wrr_schedule<>:noavailableserversDec1719:47:45ndb2heartbeat:[6884]:info:allHAresourceacquisitioncompleted<standby>.Dec1719:47:45ndb2heartbeat:[6852]:info:Standbyresourceacquisitiondone[all].Dec1719:47:45ndb2harc[7277]:info:Running/etc/ha.d/rc.d/statusstatusDec1719:47:45ndb2kernel:IPVS:ip_vs_wrr_schedule<>:noavailableserversDec1719:47:45ndb2lastmessagerepeated14timesDec1719:47:45ndb2mach_down[7293]:info:/usr/share/heartbeat/mach_down:nice_failback:foreignresourcesacquiredDec1719:47:45ndb2kernel:IPVS:ip_vs_wrr_schedule<>:noavailableserversDec1719:47:45ndb2mach_down[7293]:info:mach_downtakeovercompletefornodendb1.Dec1719:47:45ndb2kernel:IPVS:ip_vs_wrr_schedule<>:noavailableserversDec1719:47:45ndb2heartbeat:[6852]:info:mach_downtakeovercomplete.Dec1719:47:45ndb2harc[7327]:info:Running/etc/ha.d/rc.d/ip-request-respip-request-respDec1719:47:45ndb2ip-request-resp[7327]:receivedip-request-respldirectord::ldirectord.cfOKyesDec1719:47:45ndb2ResourceManager[7348]:info:Acquiringresourcegroup:ndb2ldirectord::ldirectord.cfLVSSyncDaemonSwap::masterDec1719:47:45ndb2kernel:IPVS:ip_vs_wrr_schedule<>:noavailableserversDec1719:47:46ndb2lastmessagerepeated3timesDec1719:47:46ndb2ldirectord[7375]:ldirectordfor/etc/ha.d/ldirectord.cfisrunningwithpid:6988Dec1719:47:46ndb2ldirectord[7375]:ExitingfromldirectordstatusDec1719:47:46ndb2ResourceManager[7348]:info:Running/etc/ha.d/resource.d/ldirectordldirectord.cfstartDec1719:47:46ndb2kernel:IPVS:ip_vs_wrr_schedule<>:noavailableserversDec1719:47:46ndb2lastmessagerepeated6timesDec1719:47:46ndb2IPaddr2[7443]:INFO:RunningOKDec1719:47:46ndb2kernel:IPVS:ip_vs_wrr_schedule<>:noavailableserversDec1719:48:16ndb2lastmessagerepeated289timesDec1719:48:16ndb2heartbeat:[6852]:WARN:nodendb1:isdeadDec1719:48:16ndb2heartbeat:[6852]:info:Deadnodendb1gaveupresources.Dec1719:48:16ndb2heartbeat:[6852]:info:Linkndb1:eth1dead.Dec1719:48:16ndb2ipfail:[6879]:info:Statusupdate:Nodendb1nowhasstatusdeadDec1719:48:16ndb2kernel:IPVS:ip_vs_wrr_schedule<>:noavailableserversDec1719:48:17ndb2lastmessagerepeated8timesDec1719:48:17ndb2ipfail:[6879]:info:NS:Wearedead.:<Dec1719:48:17ndb2ipfail:[6879]:info:LinkStatusupdate:Linkndb1/eth1nowhasstatusdeadDec1719:48:17ndb2kernel:IPVS:ip_vs_wrr_schedule<>:noavailableserversDec1719:48:17ndb2ipfail:[6879]:info:Wearedead.:<Dec1719:48:17ndb2ipfail:[6879]:info:Askingothersideforpingnodecount.Dec1719:48:18ndb2kernel:IPVS:ip_vs_wrr_schedule<>:noavailableservers[root@ndb2~]#tail-f/var/log/messagesDec1719:47:22ndb2harc[6862]:info:Running/etc/ha.d/rc.d/statusstatusDec1719:47:23ndb2heartbeat:[6852]:info:Comm_now_up<>:updatingstatustoactiveDec1719:47:23ndb2heartbeat:[6852]:info:Localstatusnowsetto:'active'Dec1719:47:23ndb2heartbeat:[6852]:info:Startingchildclient"/usr/lib/heartbeat/ipfail"<498,496>Dec1719:47:23ndb2heartbeat:[6879]:info:Starting"/usr/lib/heartbeat/ipfail"asuid498gid496<pid6879>Dec1719:47:23ndb2heartbeat:[6852]:info:remoteresourcetransitioncompleted.Dec1719:47:23ndb2heartbeat:[6852]:info:remoteresourcetransitioncompleted.Dec1719:47:23ndb2heartbeat:[6852]:info:LocalResourceacquisitioncompleted.<none>Dec1719:47:23ndb2heartbeat:[6852]:info:Initialresourceacquisitioncomplete<T_RESOURCES<them>>Dec1719:47:29ndb2ipfail:[6879]:info:Pingnodecountisbalanced.Dec1719:47:43ndb2heartbeat:[6852]:info:Receivedshutdownnoticefrom'ndb1'.Dec1719:47:43ndb2heartbeat:[6852]:info:Resourcesbeingacquiredfromndb1.Dec1719:47:43ndb2heartbeat:[6884]:info:acquireallHAresources<standby>.Dec1719:47:43ndb2ResourceManager[6911]:info:Acquiringresourcegroup:ndb2ldirectord::ldirectord.cfLVSSyncDaemonSwap::masterDec1719:47:43ndb2ldirectord[6957]:ldirectordisstoppedfor/etc/ha.d/ldirectord.cfDec1719:47:43ndb2ldirectord[6957]:Exitingwithexit_status3:ExitingfromldirectordstatusDec1719:47:43ndb2heartbeat:[6885]:info:LocalResourceacquisitioncompleted.Dec1719:47:43ndb2ldirectord[6961]:ldirectordisstoppedfor/etc/ha.d/ldirectord.cfDec1719:47:43ndb2ldirectord[6961]:Exitingwithexit_status3:ExitingfromldirectordstatusDec1719:47:43ndb2ResourceManager[6911]:info:Running/etc/ha.d/resource.d/ldirectordldirectord.cfstartDec1719:47:44ndb2ldirectord[6986]:StartingLinuxDirectorasdaemonDec1719:47:44ndb2ldirectord[6988]:Addedvirtualserver:05:3306Dec1719:47:44ndb2ldirectord[6988]:Quiescentrealserver:01:3306mappedfrom01:3306<x05:3306><Weightsetto0>Dec1719:47:44ndb2ldirectord[6988]:Quiescentrealserver:7:3306mappedfrom7:3306<x05:3306><Weightsetto0>Dec1719:47:44ndb2ResourceManager[6911]:info:Running/etc/ha.d/resource.d/LVSSyncDaemonSwapmasterstartDec1719:47:44ndb2kernel:IPVS:stoppingsyncthread5493...Dec1719:47:45ndb2kernel:IPVS:syncthreadstopped!Dec1719:47:45ndb2LVSSyncDaemonSwap[7050]:info:ipvs_syncbackupdownDec1719:47:45ndb2kernel:IPVS:syncthreadstarted:state=MASTER,mcast_ifn=eth0,syncid=0Dec1719:47:45ndb2LVSSyncDaemonSwap[7050]:info:ipvs_syncmasterupDec1719:47:45ndb2LVSSyncDaemonSwap[7050]:info:ipvs_syncmasterobtainedDec1719:47:45ndb2IPaddr2[7102]:INFO:ResourceisstoppedDec1719:47:45ndb2ResourceManager[6911]:info:Running/etc/ha.d/resource.d/IPaddr2startDec1719:47:45ndb2IPaddr2[7214]:INFO:ip-finetaddraddbrddeveth0Dec1719:47:45ndb2avahi-daemon[2776]:Registeringnewaddressrecordforoneth0.Dec1719:47:45ndb2IPaddr2[7214]:INFO:iplinkseteth0upDec1719:47:45ndb2IPaddr2[7214]:INFO:/usr/lib/heartbeat/send_arp-i200-r5-peth0autonot_usednot_usedDec1719:47:45ndb2kernel:IPVS:ip_vs_wrr_schedule<>:noavailableserversDec1719:47:45ndb2kernel:IPVS:ip_vs_wrr_schedule<>:noavailableserversDec1719:47:45ndb2IPaddr2[7185]:INFO:SuccessDec1719:47:45ndb2kernel:IPVS:ip_vs_wrr_schedule<>:noavailableserversDec1719:47:45ndb2heartbeat:[6884]:info:allHAresourceacquisitioncompleted<standby>.Dec1719:47:45ndb2heartbeat:[6852]:info:Standbyresourceacquisitiondone[all].Dec1719:47:45ndb2harc[7277]:info:Running/etc/ha.d/rc.d/statusstatusDec1719:47:45ndb2kernel:IPVS:ip_vs_wrr_schedule<>:noavailableserversDec1719:47:45ndb2lastmessagerepeated14timesDec1719:47:45ndb2mach_down[7293]:info:/usr/share/heartbeat/mach_down:nice_failback:foreignresourcesacquiredDec1719:47:45ndb2kernel:IPVS:ip_vs_wrr_schedule<>:noavailableserversDec1719:47:45ndb2mach_down[7293]:info:mach_downtakeovercompletefornodendb1.Dec1719:47:45ndb2kernel:IPVS:ip_vs_wrr_schedule<>:noavailableserversDec1719:47:45ndb2heartbeat:[6852]:info:mach_downtakeovercomplete.Dec1719:47:45ndb2harc[7327]:info:Running/etc/ha.d/rc.d/ip-request-respip-request-respDec1719:47:45ndb2ip-request-resp[7327]:receivedip-request-respldirectord::ldirectord.cfOKyesDec1719:47:45ndb2ResourceManager[7348]:info:Acquiringresourcegroup:ndb2ldirectord::ldirectord.cfLVSSyncDaemonSwap::masterDec1719:47:45ndb2kernel:IPVS:ip_vs_wrr_schedule<>:noavailableserversDec1719:47:46ndb2lastmessagerepeated3timesDec1719:47:46ndb2ldirectord[7375]:ldirectordfor/etc/ha.d/ldirectord.cfisrunningwithpid:6988Dec1719:47:46ndb2ldirectord[7375]:ExitingfromldirectordstatusDec1719:47:46ndb2ResourceManager[7348]:info:Running/etc/ha.d/resource.d/ldirectordldirectord.cfstartDec1719:47:46ndb2kernel:IPVS:ip_vs_wrr_schedule<>:noavailableserversDec1719:47:46ndb2lastmessagerepeated6timesDec1719:47:46ndb2IPaddr2[7443]:INFO:RunningOKDec1719:47:46ndb2kernel:IPVS:ip_vs_wrr_schedule<>:noavailableserversDec1719:48:16ndb2lastmessagerepeated289timesDec1719:48:16ndb2heartbeat:[6852]:WARN:nodendb1:isdeadDec1719:48:16ndb2heartbeat:[6852]:info:Deadnodendb1gaveupresources.Dec1719:48:16ndb2heartbeat:[6852]:info:Linkndb1:eth1dead.Dec1719:48:16ndb2ipfail:[6879]:info:Statusupdate:Nodendb1nowhasstatusdeadDec1719:48:16ndb2kernel:IPVS:ip_vs_wrr_schedule<>:noavailableserversDec1719:48:17ndb2lastmessagerepeated8timesDec1719:48:17ndb2ipfail:[6879]:info:NS:Wearedead.:<Dec1719:48:17ndb2ipfail:[6879]:info:LinkStatusupdate:Linkndb1/eth1nowhasstatusdeadDec1719:48:17ndb2kernel:IPVS:ip_vs_wrr_schedule<>:noavailableserversDec1719:48:17ndb2ipfail:[6879]:info:Wearedead.:<Dec1719:48:17ndb2ipfail:[6879]:info:Askingothersideforpingnodecount.Dec1719:48:18ndb2kernel:IPVS:ip_vs_wrr_schedule<>:noavailableservers如果沒有錯誤,表明heartbeat已經(jīng)切換。此時再次插入數(shù)據(jù)驗證,如果還可以繼續(xù)寫入,表明配置完全成功。Mysqlcluster的測試報告：在8上部署測試腳本,讓這臺服務(wù)器表示一個客戶端請求讀寫數(shù)據(jù)庫。測試腳本1：[root@localhostmysql-cluster]#cat/data/p<?php$link=mysql_connect<'05','ldirector','xxxxxxxxx'>;mysql_select_db<'kingsoft',$link>;$sql="insertinto`preference`<`id`,`preferenceSerialNumber`,`username`,`preferenceTypeId`,`isExpired`,`isUsed`,`preferenceUsername`,`equalMoney`,`genDatetime`,`useDatetime`,`grantDatetime`,`expriedDatetime`>values<NULL,'514a49f83820e34c877ff48770e48ea7','liujun','2','1','1','kingsoft','512.23','2008-12-03','2008-12-03','2008-12-03','2008-12-03for<$i=0;$i<100;$i++>{mysql_query<$sql>;}mysql_close<$link>;?>測試腳本2：[root@localhostmysql-cluster]#cattest.sh#!/bin/shi=0;j=0;while[$i-lt1000]dowget-qhttp://pay.kingsoft.sug/test.php;i=`expr$i+1`;donesleep2;find.-name"test.php.*"|xargsrm-rf;while[$j-lt1000]domysql-uldirector-pxxxxxxxxxxx-e"usekingsoft;insertintopreference<preferenceSerialNumber,username,preferenceTypeId,preferenceUsername,equalMoney,genDatetime,useDatetime,grantDatetime,expriedDatetime>values<'514a49f83820e34c877ff48770e48ea7','liujun2','3','liujun33333','33.8','2008-12-237:05:00','2008-12-237:15:00','2008-12-237:25:00','2008-12-23j=`expr$j+1`;donesleep3;server=`mysql-uldirector-pxxxxxxxxxx-e"usekingsoft;selectcount<*>frompreference"`;datetime=`date+%T`;echo$datetime""$server>>/tmp/mysql-cluster