HAproxy+Mycat+keepalived配置指南资料

Mycat+HAproxy+keepalived配置指南

为了解决mycat的单点故障问题，选用haproxy和keepalived部署mycat集群。

重要提醒：

关于防火墙和SELINUX方案：

SELINUX

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

IPTABLES（方案两种）

1,关闭防火墙，万事大吉

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

-jACCEPT,并开启外界访问端口

注：eth0为网卡设备，可能会有不同

224.0.0.0.18为keepalived主从机器通信地址，必须开启，否则都抢vip

所有需要外界访问的端口都在防火墙中开启，或者直接关闭防火墙。

部署图：

10

这里把mycat和keepalived+haproxy安装在同一个服务器了，实际情况可以把mycat拎

出去单独部署

一，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对应配置文件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参与读的负载均衡，2：从当前使用的写服务器所属的读服务器获取数据，所以

如果此写服务器宕机了，其读服务器亦不能使用，所以可以配置多个writeHost,balance

配置为1.

WriteType一般设置为6,设置为1衣示随机写入writeHost的数据库，可能导致数

据不一致

4启动mycat

#/usr/local/mycat/bin/mycatstart

5查看日志

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

日志内容如下：

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启动成功，8066端口为对外服务端口

HAproxy安装配置（主从服务器完全一样）：

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#最大连接数

userroot#用户

grouproot#组

daemon#以守护进程方式运行

defaults

logglobal

optiondontlognull

optionredispatch

retries3

maxconn20000

timeoutconnect5000

timeoutclient50000

timeoutserver50000

listenadmin_stats10:48800#统计页面

##oracle服务器的地址，安装keepalived后更改为虚拟vip

statsuri/admin_stats

statsauthadmin:admin#认证用户名及密码

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选项：

SYSLOGD_OPTIONS=W-C5-r”

4.2新增haproxy.conf文件

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

增加：

$ModLoadimudp

$UDPServerRun514

local2.*/var/log/haproxy.log

5重启日志服务

#servicersyslogrestart

6启动haproxy

#servicehaproxystart

7下载xinetd

#yuminstall-yxinetd

8配置xinetd守护进程，监听mycat是否启动（通过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#启用该服务的脚本

log_on_failure+=USERID

disable=no#启用服务

}

保存退出

#chmod755/etc/xinetd.d/mycat_status#更改权限，使其有执行权限

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#更改权限，使其有执行权限

注意：红色部分为mycat执行文件目录(此文件注意空格，笔者出错很久才配置

好)

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加入自启动服务

#chkconfig--addxinetd

#chkconfig--level2345xinetdon

7访问统计页面

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

观察上图，发现本机的mycat(mycat_100)的状态Status为UP,而mycat_110

状态为DOWN,

这是因为ip：10的防火墙没有开启48700端口，开启服务器端

口

#vi/etc/sysconfig/iptables

增加：

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

然后重启防火墙

#serviceiptabesrestart

发现状态全部是UP了，图就不贴了。

三，keepalived安装配置(主从略有不同)

Keepalived安装编译依赖openssl,所以安装keepalived前先安装openssl

3.1keepalived依赖openssl,openssl-devel

Yum安装openssl-devel，会自动安装openssl

#yuminstall-yopenssl-devel

3.2yum安装keepalived

#yuminstall-ykeepalived

安装完成后看看安装目录

#rpm-qlkeepalived

3.3配置日志,keepalived默认不打印日志

#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文件

主从库的配置基本相同，权重priority和state不同

配置如下：

!ConfigurationFileforkeepalived

global_defs{

router_idLVS_DEVEL

)

vrrp_scriptchk_naproxy{

script"/etc；keepalived/chk_haproxy.sh"#检测haproxy进程

interval2

weight2

)

vrrp_instanceVI_1{

StateMASTER#从库为BACKUP

interfaceetho#对外提供ip的网卡，可能有不同ethl...

virtual_router_id51#主从配置形同的组id

priority120#权重越高优先抢虚拟vip,从库低于主库

advert_int1

authentication{

auth_typePASS

auth_pass1111

}

track_script{#检测haproxy进程状态

chk_haproxy#配置内容为上方的vrrp_script名

)

virtual_ipaddress{

00#虚拟vip主从库的同网段ip

)

notify_master/etc/keepalived/notify_master.sh

notify_backup/etc/keepalived/notify_backup.sh

notify_fault/etc/keepalived/notify_fault.sh

}

注:priority主从配置策略，主机priority-从priority<weight»这样启动

keepalived的时候先启动priority小的，这样当同机的haproxy挂了，另一台自动

接管虚拟vip,维续提供vip服务

3.4编写检测haproxy进程的shell脚本

#vi/etc/keepalived/chk_haproxy.sh

内容入下：

#!/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

保存退出

添加脚本的执行权限

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

脚本notify_master.sh

#vi/etc/keepalived/notify_master.sh

内容入下：

#!/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

保存退出

添加脚本的执行权阳

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

脚本notify_backup.sh

#vi/etc/keepalived/notify_backup.sh

内容入下：

#!/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

添加脚本的执行权限

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

脚本notify_fault.sh

#vi/etc/keepalived/notify_fault.sh

内容人下：

#!/bin/bash

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

echo"[fault]">>$LOGFILE

date>>$LOGFILE

添加脚本的执行权限

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

3.5更改前文haproxy配置文件的监听地址

#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

所有配置完成

测试：

启动主从服务器的mycat,keepalived

启动命令：

#/usr/local/mycat/bin/mycatstart#启动mycat

#servicekeepalivedstart

在主从服务器执行命令：

#ipaddr

主服务器显示如下：

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

主服务器抢到了虚拟vip

从服务器显示如下：

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

从服务器为抢到vip,因为其权重配置的比主服务器小

在主从服务器上执行命令#servicehaproxystatus查看haproxy状态

主服务器显示：

Haprxoy(pid12882)正在运行...

从服务器显示：

Haproxy已停止

若此时手动启动haproxy发现启动不起来报错

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

socket(00:48800)....

这是正常的，因为此时vip被主服务器抢走了。

然后关闭主服务器的keepalived,命令#servicekeepalivedstop

再次执行#ipaddr发现从服务器抢到了vip,主服务器失掉了vip

浏览器访问http://192.168.193.200:48800/admin_status(haproxy.conf中配

置)