Mycat+HAproxy+keepalived配置指南

為了解決mycat的單點(diǎn)故障問題，選用haproxy和keepalived部署mycat集群。

重要提醒：

關(guān)于防火墻和SELINUX方案：

SELINUX

vi/etc/selinux/config文件配置SELINUX=disabled

IPTABLES（方案兩種）

1,關(guān)閉防火墻，萬事大吉

2,在/etc/sysconfig/iptables文件中加入?AINPUT-ieth0-d224.0.0.0/8

-jACCEPT,并開啟外界訪問端口

注：eth0為網(wǎng)卡設(shè)備，可能會(huì)有不同

224.0.0.0.18為keepalived主從機(jī)器通信地址，必須開啟，否則都搶vip

所有需要外界訪問的端口都在防火墻中開啟，或者直接關(guān)閉防火墻。

部署圖：

10

這里把mycat和keepalived+haproxy安裝在同一個(gè)服務(wù)器了，實(shí)際情況可以把mycat拎

出去單獨(dú)部署

一，mycat的安裝(主從配置完全一樣)

1.卜,載mycat安裝包(/usr/local/)

https:〃Hithub.〈om/MyCA-ADache/Mycat?download/blob/master/l.5-GA/Mycat-

server-1.5-GA-20160225120029-linux.tar.HZ

2解壓

#tar-zxvfMycat-server-1.5-GA-20160225120029-linux.tar.gz

3配置mycat

#cd/usr/local/mycat/conf

wrapper.conf文件

#viwrapper.conf

#主要配置java的路徑

#3avaApplication

Wmand=/usr/local/jdkl.7/bin/java

server.xml文件

#viserver.xml

#配置mycat的用戶名和密:碼以及schemal

<username=,,mycat,,>

<propertyname=,,Dassword,,>mycat</property>

<propertyname=wschemasw>TESTDB</property>

</user>

<username=,,user,,>

<propertyname-woasswordw>user</property>

<propertyname=wschemasw>TESTDB</property>

<propertyname=,,readOnly,,>true</property>

</user>

注：schemas對(duì)應(yīng)配置文件schema.xml中的〈schemaname="TESTDB”>

schema.xml

#vischemal.xml

<?xmlversion="1.0"?>

<!DOCTYPEmycat:schemaSYSTEM"schema.dtd">

<mycat:schemaxmlns:mycat=,,http://org.opencloudb/">

<schemaname="TESTDB"checkSQLschema="false"sqlMaxLimit="100"

dataNode="dnl">

</schema>

<dataNodename="dnl"dataHost="localhostl"database="dbl"/>

<dataHostname="localhostl"maxCon="1000"minCon="l"balance="l"

writeType="0"dbType="oracle"dbDriver="jdbc">

<heartbeat>select1fromdual</heartbeat>

<connectionInitSql>altersessionset

nls_date_format='yyyy-mn-ddhh24:mi:ss*</connectionInitSql>

<writeHosthost="hostMl"

url="jdbc:oracle:thin:@300:1521:orcl"user="system"

password="manager">

<readHosthost="hostSl"

url="jdbc:oracle:thin:@10:1521:orcl"user="system"

password="manager"></readHost>

</writeHost>

</dataHost>

</mycat:schema>

注：此案例主要用到mycat的讀寫分離

dataHost中屬性balance取值0：不開啟讀寫分離，1：全部readHost和standby

v/riteHost參與讀的負(fù)載均衡，2：從當(dāng)前使用的寫服務(wù)器所屬的讀服務(wù)器獲取數(shù)據(jù)，所以

如果此寫服務(wù)器宕機(jī)了，其讀服務(wù)器亦不能使用，所以可以配置多個(gè)writeHost,balance

配置為1.

WriteType一般設(shè)置為6,設(shè)置為1衣示隨機(jī)寫入writeHost的數(shù)據(jù)庫，可能導(dǎo)致數(shù)

據(jù)不一致

4啟動(dòng)mycat

#/usr/local/mycat/bin/mycatstart

5查看日志

#cat/usr/local/mycat/logs/mycat.log

日志內(nèi)容如下：

02/2520:13:48.776INFO[WrapperSimpleAppMain](MycatServer.java:197)

02/2520:13:48.777INFO[WrapperSimpleAppMain](MycatServer.java:198)

-MyCatisreadytostartup...

02/2520:13:48.777INFO[WrapperSimpleAppMain](MycatServer.java:208)

-Startupprocessors...,totalprocessors:1，aiothreadpoolsize:4

eachprocessallocatedsocketbufferpoolbytes,bufferchunk

size:4096bufferpool'scapacity(buferPool/bufferChunk)is:1000

02/2520:13:48.777INFO[WrapperSimpleAppMain](MycatServer.java:209)

-sysconfigparams:SystemConfig[processorBufferLocalPercent=100>

■FrontSocketSoRcvbuf=1048576,frontSocketSoSndbuf=4194304,

backSocketSoRcvbuf=4194304,backSocketSoSndbuf=1048576,

frontSocketNoDelay=l,backSocketNoDelay=l,maxStringLiteralLength=65535,

frontWriteQueueSize=2048,bindlp=,serverPort=8066,

managerPort=9066jcharset=utf8jprocessors=ljprocessorExecutor=4,

timerExecutor=2,managerExecutor=2,idleTimeout=1800000,

catletClassCheckSeconds=60,sqlExecuteTimeout=300,

processorCheckPeriod=1000?dataNodeIdleCheckPeriod=300000,

dataNodeHeartbeatPeriod=10000JclusterHeartbeatUser=_HEARTBEAT_USER_,

clusterHeartbeatPass=_HEARTBEAT_PASS_,clusterHeartbeatPeriod=5000,

clusterHeartbeatTimeout=10000JclusterHeartbeatRetry=10Jtxlsolation=3,

parserCommentVersion=50148,sqlRecordCount=10,

processorBufferPool=4096000,processorBufferChunk=4096,

defaultMaxLimit=100,sequnceHandlerType=0,

sqllnterceptor=erceptor.impl.DefaultSqllnterceptorj

sqlInterceptorType=select,

sqlInterceptorFile=/usr/local/mycat/logs/sql.txt,mutiNodeLimitType=0,

mutiNodePatchSize=100,defaultSqlParser=druidparser,usingAIO=0,

packetHeaderSize=4,maxPacketSize=16777216,mycatNode!d=l]

02/2520:13:48,882INFO[WrapperSimpleAppMain](MycatServer.java:268)

-usingnionetworkhandler

02/2520:13:49.073INFO[WrapperSimpleAppMain](MycatServer.java:286)

-$_MyCatManagerisstartedandlisteningon9066

02/2520:13:49.075INFO[WrapperSimpleAppMain](MycatServer.java:290)

-$_MyCatServerisstartedandlisteningon8066

02/2520:13:49.075INFO[WrapperSimpleAppMainl(MycatServer.java:292)

02/2520:13:49.075INFO[WrapperSimpleAppMain](MycatServer.java:295)

-InitializedataHost...

02/2520:13:49.675INFO[WrapperSimpleAppMain]

(PhysicalDBPool.java:294)-initbackendmyqslsource,createconnections

total1forhostMlindex:0

02/2520:13:49.076INFO[WrapperSimpleAppMain]

(PhysicalDatasource.java:373)-notildeconnectioninpool,createnew

connectionforhostMlofschemadbl

02/2520:13:49.828INFO[BusinessExecutorO]

(GetConnectionHandler.java:66)-connectedsuccessfulyJDBCConnection

[id=l,autocommit=true,pool=org.opencloudb.jdbc.JDBCDatasource@5535c2c7,

schema=dbl,dbType=ORACLE,oldSchema=null,packet工d=0,txlsolation=0,

running=false,borrowed=trueJhost=null?port=-l,

con=oracle.jdbc.driver.-4CConnection@3c86c285,respHandler=null,

attachement=null,headerOutputed=false,modifiedSQLExecuted=false,

startTime=1456402429110.lastTime=1456402429828,isSpark=false,

processor=.NI0Processor@334cc8ce]

02/2520:13:49.894INFO[WrapperSimpleAppMain]

(PhysicalDBPool.java:319)-initresult:finished1success1targetcount:1

02/2520:13:49.894INFO[WrapperSimpleAppMain]

(PhysicalDBPool.java:265)-localhostlindex:?initsuccess

02/2520:18:50.172WARN[TimerO](NIOProcessor.java:199)-found

backendconnectionSQLtimeout,closeitDDBCConnection

[id=l,autocommit=true,pool=org.opencloudb.jdbc.3DBCDatasource@5535c2c7,

schema=dbl,dbType=ORACLEjoldSchema=null,packetld=0,txlsolation=0,

running=false,borrowed=true,host=null,port="l,

con=oracle.jdbc.driver.-4CConnection@3c86c285,

respHandler=org.opencloudb.mysql.nio.handler.ConnectionHeartBeatHandler

@13610249,attachement=null,headerOutputed=false,

modifiedSQLExecuted=false,startTime=1456402429110,

lastTime=1456402428030jisSpark=false,

processor=.NI0Processor@334cc8ce]

Mycat啟動(dòng)成功，8066端口為對(duì)外服務(wù)端口

HAproxy安裝配置（主從服務(wù)器完全一樣）：

1yum安裝haproxy

#yuminstall-yhaproxy

2查看安裝路徑

#rpm-qlhaproxy

3配置文件

#vi/etc/haproxy/haproxy.cfg

global

loglocal2#日志

chroot/var/lib/haproxy

pidfile/var/run/haproxy.pid

maxconn20000#最大連接數(shù)

userroot#用戶

grouproot#組

daemon#以守護(hù)進(jìn)程方式運(yùn)行

defaults

logglobal

optiondontlognull

optionredispatch

retries3

maxconn20000

timeoutconnect5000

timeoutclient50000

timeoutserver50000

listenadmin_stats10:48800#統(tǒng)計(jì)頁面

##oracle服務(wù)器的地址，安裝keepalived后更改為虛擬vip

statsuri/admin_stats

statsauthadmin:admin#認(rèn)證用戶名及密碼

modehttp

optionhttplog

listenallmycat_service10:8098

modetcp

optiontcplog

optionhttpchkOPTIONS*HTTP/1,l\r\nHost:\www

balanceroundrobin

servermycat_11010:8066checkport48700

inter5srise2fall3

servermycat_10000:8066checkport48700

inter5srise2fall3

timeoutserver20000

listenallmycat_admin10:8099

modetcp

optiontcplog

optionhttpchkOPTIONS*HTTP/1.l\r\nHost:\www

balanceroundrobin

servermycat_11010:9066checkport48700

inter5srise2fall3

servermycat_10000:9066checkport48700

inter5srise2fall3

timeoutserver20000

4配置日志文件

4.T查看日志

#yumlistall|grepsyslog

#rpm-qlrsyslog

#vi/etc/sysconfig/rsyslog

增加-r選項(xiàng)：

SYSLOGD_OPTIONS=W-C5-r”

4.2新增haproxy.conf文件

#vi/etc/rsyslog.d/haproxy.conf

增加：

$ModLoadimudp

$UDPServerRun514

local2.*/var/log/haproxy.log

5重啟日志服務(wù)

#servicersyslogrestart

6啟動(dòng)haproxy

#servicehaproxystart

7下載xinetd

#yuminstall-yxinetd

8配置xinetd守護(hù)進(jìn)程，監(jiān)聽mycat是否啟動(dòng)（通過48700端口）

8.1

#vi/etc/xinetd.d/mycat_status#新文件

增加：

servicemycat_status

{

flags=REUSE

socket_type=stream#套接字格式TCP,LDP是dgram

port=48700

wait=no

user=nobody

server=/usr/local/bin/mycat_status#啟用該服務(wù)的腳本

log_on_failure+=USERID

disable=no#啟用服務(wù)

}

保存退出

#chmod755/etc/xinetd.d/mycat_status#更改權(quán)限，使其有執(zhí)行權(quán)限

8.2編寫mycat_status腳本

#vi/usr/local/bin/mycat_status

增加：

#!/bin/bash

#/usr/local/bin/mycat_status.sh

#Thisscriptchecksifamycatserverishealthyrunningon

localhost.Itwill

#return:

#

#"HTTP/l.x200OK\r"(ifmycatisrunningsmoothly)

#

#"HTTP/l.x503InternalServerError\r"(else)

mycat='/usr/local/mycat/bin/mycatstatus|grep'notrunning'|

wc-1%

if["$mycat"="O"];

then

/bin/echo-e"HTTP/1.1200OK\r\n"

else

/bin/echo-e"HTTP/1.1503ServiceUnavailable\r\n"

fi

保存退出

#chmod755/usr/local/bin/mycat_status#更改權(quán)限，使其有執(zhí)行權(quán)限

注意：紅色部分為mycat執(zhí)行文件目錄(此文件注意空格，筆者出錯(cuò)很久才配置

好)

8?3添加/etc/services中

#vi/etc/services

末尾處增加

mycat_status48700/tcp#mycat_status

重啟xinetd

#Servicexinetdrestart

8.4#netstat-antup|grep48700

若顯示

tcp00：：:48700：：：*LISTEN

36642/xinetd

則配置成功。

8.5將xinetd加入自啟動(dòng)服務(wù)

#chkconfig--addxinetd

#chkconfig--level2345xinetdon

7訪問統(tǒng)計(jì)頁面

http:〃10:48800/adminstats

，-192.168.193.100：4880gdminstatsv￡|Q牘

HAHroxyversioni.b.4,releasedZU14/U9/UZ

StatisticsReporttorpid3225

>Generalprocessinformation

Displayoption:Externalesources:

act眈UPJbadaipUP

actweUP.gongWMIbackupUP.gorgdown?Scope[?rnmir/stc

P?1=JS(process禮HOQIOC=1)

uptime=OdIh00m06s?LpdalesMS)

acty/eDOWW.gomgup口badcupDOMIgongup

systemlimits:memmeu-unimted.ufinitfl=0014?OninempM

act^eorbackupDOWlJnotchecked

maxsock=8014;nwcconn=4000;maxpipes=0?ReRshnow

cunemconns=1;cunentppes=QC;conicate二l?secactveocbadajpD0\Wformeuitenance(MAINT)?CSYexp。。

Runnngtasks：D6;idle=100%

actweo<backupSOFTSTOPPEDlo(manltnance

Note:"NOLBTDRAIFT：UPwthload^alanan(deadled

adminsutsI

QueueSessionraleSessionsBytesDeniedEiocsWarningsServer

QMMAXLimitCurMaxUrnifCurMAXlimitTbUILb攻UstInOutReqRe$pReq5nRe$pRetRedisStatusUslChkWghtActBekChkDwn(MntmeThrtJe

Contend1111200004969736000OPEN

Backend°|°|0]10120001001969736001000ihe$up1oo|oIIt|

tservice

QueueSessionrateSessionsBytesDen^dErrorsWarningsServer

CurMuLimitCurMaixUmitCurMaxLimitTotalLbTotLastInOutReqRespReqConnRespRetrRedisStatusLastChkWghtActBckChklXmD*rrtmeThrte

intend||0|0|-|0|0)20000|0||o|0|00|0OPEN|

of0

mycat.UO00-()—000-0000lh6sDOWN14coMinOms1Y11lh6s?

rrvcat.ioolo|o|J000oT0*

?00|I0|0023ml2sUP口KO/2O0in88msiT136rn52s.

Backend|0|0||0|0||0|0]T°?|oo|03631

0|o|o|23ml3UP|ih1o

2000|0|0001

觀察上圖，發(fā)現(xiàn)本機(jī)的mycat(mycat_100)的狀態(tài)Status為UP,而mycat_110

狀態(tài)為DOWN,

這是因?yàn)閕p：10的防火墻沒有開啟48700端口，開啟服務(wù)器端

口

#vi/etc/sysconfig/iptables

增加：

-AINPUT-mstate--stateNEW-mtcp-ptcp--dport48700-jACCEPT

然后重啟防火墻

#serviceiptabesrestart

發(fā)現(xiàn)狀態(tài)全部是UP了，圖就不貼了。

三，keepalived安裝配置(主從略有不同)

Keepalived安裝編譯依賴openssl,所以安裝keepalived前先安裝openssl

3.1keepalived依賴openssl,openssl-devel

Yum安裝openssl-devel，會(huì)自動(dòng)安裝openssl

#yuminstall-yopenssl-devel

3.2yum安裝keepalived

#yuminstall-ykeepalived

安裝完成后看看安裝目錄

#rpm-qlkeepalived

3.3配置日志,keepalived默認(rèn)不打印日志

#vi/etc/sysconfig/keepalived

更新KEEPALIVED_OPTIONS=n-Dw

為

KEEPALIVED_OPTIONS="-D-d-S0”

#vi/etc/rsyslog.d/keepalived_log.conf

增加

local0.?/var/log/keepalived.log

保存退出后重啟rsyslog

#servicersyslogrestart

I」志就配置好了

3.3配置keepalived.conf文件

主從庫的配置基本相同，權(quán)重priority和state不同

配置如下：

!ConfigurationFileforkeepalived

global_defs{

router_idLVS_DEVEL

)

vrrp_scriptchk_naproxy{

script"/etc；keepalived/chk_haproxy.sh"#檢測(cè)haproxy進(jìn)程

interval2

weight2

)

vrrp_instanceVI_1{

StateMASTER#從庫為BACKUP

interfaceetho#對(duì)外提供ip的網(wǎng)卡，可能有不同ethl...

virtual_router_id51#主從配置形同的組id

priority120#權(quán)重越高優(yōu)先搶虛擬vip,從庫低于主庫

advert_int1

authentication{

auth_typePASS

auth_pass1111

}

track_script{#檢測(cè)haproxy進(jìn)程狀態(tài)

chk_haproxy#配置內(nèi)容為上方的vrrp_script名

)

virtual_ipaddress{

00#虛擬vip主從庫的同網(wǎng)段ip

)

notify_master/etc/keepalived/notify_master.sh

notify_backup/etc/keepalived/notify_backup.sh

notify_fault/etc/keepalived/notify_fault.sh

}

注:priority主從配置策略，主機(jī)priority-從priority<weight?這樣啟動(dòng)

keepalived的時(shí)候先啟動(dòng)priority小的，這樣當(dāng)同機(jī)的haproxy掛了，另一臺(tái)自動(dòng)

接管虛擬vip,維續(xù)提供vip服務(wù)

3.4編寫檢測(cè)haproxy進(jìn)程的shell腳本

#vi/etc/keepalived/chk_haproxy.sh

內(nèi)容入下：

#!/bin/bash

STARTHAPROXY="/etc/init.d/haproxystart-f/etc/haproxy/haproxy.cfg"

A='ps-Chaproxy--no-header|wc-1'

if[$A-eq0];then

$STARTHAPROXY

sleep3

fi

if['ps-Chaproxy--no-header|wc-1'-eq0];then

exit0

else

exit1

fi

保存退出

添加腳本的執(zhí)行權(quán)限

#chmod+x/etc/keepalived/chk_haproxy.sh

腳本notify_master.sh

#vi/etc/keepalived/notify_master.sh

內(nèi)容入下：

#!/bin/bash

STARTHAPR0XY=*7etc/init.d/haproxystart-f/etc/haproxy/haproxy.cfg"

STOPHAPROXY="/etc/init.d/haproxystop"

L0GFILE=,7usr/local/keepalived/log/check_haproxy.log"

echo"[Master]">>$LOGFILE

date>>$LOGFILE

echo"Beingmaster...">>$LOGFILE2>&1

echo"stophaproxy...">>$LOGFILE2>&1

SSTOPHAPROXY>>$LOGFILE2>&1

echo"starthaproxy...">>$LOGFILE2>&1

$STARTHAPROXY>>$LCGFILE2>&1

echo"haproxystarted">>$LOGFILE

保存退出

添加腳本的執(zhí)行權(quán)陽

#chmod+x/etc/keepalived/notify_master.sh

腳本notify_backup.sh

#vi/etc/keepalived/notify_backup.sh

內(nèi)容入下：

#!/bin/bash

STARTHAPROXY="/etc/init.d/haproxystart-f/etc/haproxy/haproxy.cfg"

STOPHAPROXY="/etc/init.d/haproxystop"

LOGFILE="/usr/local/keepalived/log/check_haproxy.log"

echo"[Backup]">>$LOGFILE

date>>$LOGFILE

echo"Beingbackup...">>$LOGFILE2>&1

echo"stophaproxy...">>$LOGFILE2>&1

$STOPHAPROXY>>$LOGFILE2>&1

echo"starth叩roxy...“>>$LOGFILE2>&1

$STARTHAPROXY>>$LCGFILE2>&1

echo"haproxystarted">>$LOGFILE

添加腳本的執(zhí)行權(quán)限

#chmod+x/etc/keepalived/notify_backup.sh

腳本notify_fault.sh

#vi/etc/keepalived/notify_fault.sh

內(nèi)容人下：

#!/bin/bash

L0GFILE='7usr/local/keepalived/log/check_haproxy.log"

echo"[fault]">>$LOGFILE

date>>$LOGFILE

添加腳本的執(zhí)行權(quán)限

#chmod+x/etc/keepalived/notify_fault.sh

3.5更改前文haproxy配置文件的監(jiān)聽地址

#vi/etc/haproxy/haproxy.conf

更新

listenadmin_stats10:48800

listenallmycat_service10:8098

listenallmycat_service10:8098

為

listenadmin_stats00:48800

listenallmycat_service00:8098

listenallmycat_acmin00:8098

注:00為keepalived.conf中配置的虛擬vip

所有配置完成

測(cè)試：

啟動(dòng)主從服務(wù)器的mycat,keepalived

啟動(dòng)命令：

#/usr/local/mycat/bin/mycatstart#啟動(dòng)mycat

#servicekeepalivedstart

在主從服務(wù)器執(zhí)行命令：

#ipaddr

主服務(wù)器顯示如下：

1:lo:<LOOPBACK,UP,LOWER_UP>mtu65536qdiscnoqueuestateUNKNOWN

link/loopback00:00:00:00:00:00brd00:00:00:00:00:00

inet/8scopehostlo

inet6::1/128scopehost

valid_lftforeverpreferred_lftforever

2:eth0:<BROADCAST,MULTICAST,UP,LOWER_UP>mtu1500qdiscpfifo_faststate

UPqlen1000

link/ether00:0c:29:0d:f8:ddbrdff:ff:ff:ff:ff:ff

inet06/24brd55scopeglobaleth0

inet06/32scopeglobaleth0

inet6fe80::20c:29ff:fe0d:f8dd/64scopelink

valid_lftforeverpreferred_lftforever

主服務(wù)器搶到了虛擬vip

從服務(wù)器顯示如下：

1:lo:<LOOPBACK,UP,LOWER_UP>mtu65536qdiscnoqueuestateUNKNOWN

link/loopback00:00:00:00:00:00brd00:00:00:00:00:00

inet/8scopehostlo

inet6::1/128scopehost

valid_lftforeverpreferred_lftforever

2:eth0:<BROADCAST,MULTICAST,UP,LOWER_UP>mtu1506qdiscpfifo_faststate

UPqlen1000

link/ether00:0c:29:37:ad:72brdff:ff:ff:ff:ff:ff

inet16/24brd55scopeglobaleth0

inet6fe80::20c:29ff:fe37:ad72/64scopelink

valid_lftforeverpreferred_lftforever

從服務(wù)器為搶到vip,因?yàn)槠錂?quán)重配置的比主服務(wù)器小

在主從服務(wù)器上執(zhí)行命令#servicehaproxystatus查看haproxy狀態(tài)

主服務(wù)器顯示：

Haprxoy(pid12882)正在運(yùn)行...

從服務(wù)器顯示：

Haproxy已停止

若此時(shí)手動(dòng)啟動(dòng)haproxy發(fā)現(xiàn)啟動(dòng)不起來報(bào)錯(cuò)

[ALERT]183/115915(12890):Startingproxyallmycat_service:cannotbind

socket(00:48800)....

這是正常的，因?yàn)榇藭r(shí)vip被主服務(wù)器搶走了。

然后關(guān)閉主服務(wù)器的keepalived,命令#servicekeepalivedstop

再次執(zhí)行#ipaddr發(fā)現(xiàn)從服務(wù)器搶到了vip,主服務(wù)器失掉了vip

瀏覽器訪問http://192.168.193.200:48800/admin_status(haproxy.conf中配

置)