Atlas+keepalived+mysql实现HA+主从同步.docx
《Atlas+keepalived+mysql实现HA+主从同步.docx》由会员分享,可在线阅读,更多相关《Atlas+keepalived+mysql实现HA+主从同步.docx(18页珍藏版)》请在冰豆网上搜索。
Atlas+keepalived+mysql实现HA+主从同步
Atlas+lvs+keepalived+mysql+主从复制负载均衡搭建
部署文档
2015-8-31
(V1.0)
版本#
作者
审核
修改条目
发布日期(mm/dd/yy)
1.0
梁斌
新建
2015/8/31
1、部署背景信息
基于mysql的负载均衡有很多种方式,如haproxy,前面一篇博客有介绍,还可以用更高效lvs做负载均衡,下面是基于perconaxtradbcluster的三个节点的多主复制+atlas的lvs负载均衡,其实这里是不需要用atlas的,因为atlas是用来做连接池和读写分离的,而多主架构是不需要读写分离的(如果是基于mysqlreplication的就需要atlas做负载均衡了),但为了测试atlas能不能用lvs做负载均衡,就顺便做了下实验。
1.节点规划
1.1mysql数据节点:
db169
db172
db173
三个节点为xtradbcluster节点。
1.2keepalived节点:
db162
db163
虚拟ip为192.168.1.201
haproxy节点(仅为了对比lvs的性能才安装的):
db169(部署在xtradbcluster的一个节点上)
1.3atlas节点:
和xtradbcluster节点部署在一起,也为三个节点
注意:
atlas和mysql要部署在一个节点上,如果不在一个节点上则不能用lvsdr模式负载均衡
1.4客户端测试节点:
db55
ip地址为192.168.1.*,节点名为db+ip地址末位
2.安装lvs及keepavlied(db162、db163上)
2.1安装依赖包
yum-yinstallkernel-develmakegccopenssl-devellibnl*
下载并连接linuxkernel文件,注意版本要一致(uname-a)
[root@db163~]#ln-s/usr/src/kernels/2.6.32-358.el6.x86_64//usr/src/linux
安装keepalived、lvs
[root@db162~]#yuminstallipvsadm
[root@db162~]#yuminstallkeepalived
[root@db163~]#yuminstallipvsadm
[root@db163~]#yuminstallkeepalived
2.2.配置keepavlied,注意lvs不需要单独配置,在keepalived里配置就行了
[root@db162~]#cat/etc/keepalived/keepalived.conf
!
ConfigurationFileforkeepalived
global_defs{
router_idMySQL_LB1
}
vrrp_sync_groupVSG{
group{
MySQL_Loadblancing
}
}
vrrp_instanceMySQL_Loadblancing{
stateMASTER
interfaceeth0
virtual_router_id51
priority101
advert_int1
authentication{
auth_typePASS
auth_pass123456
}
virtual_ipaddress{
192.168.1.201
}
}
virtual_server192.168.1.2011234{
delay_loop6
lb_algorr
lb_kindDR
#nat_mask255.255.255.0
#persistence_timeout50
protocolTCP
real_server192.168.1.1691234{
weight3
TCP_CHECK{
connect_timeout3
nb_get_retry3
delay_before_retry3
connect_port1234
}
}
real_server192.168.1.1721234{
weight3
TCP_CHECK{
connect_timeout3
nb_get_retry3
delay_before_retry3
connect_port1234
}
}
real_server192.168.1.1731234{
weight3
TCP_CHECK{
connect_timeout3
nb_get_retry3
delay_before_retry3
connect_port1234
}
}
}
备机上的keepalived配置
[root@db163~]#cat/etc/keepalived/keepalived.conf
!
ConfigurationFileforkeepalived
global_defs{
router_idMySQL_LB2
}
vrrp_sync_groupVSG{
group{
MySQL_Loadblancing
}
}
vrrp_instanceMySQL_Loadblancing{
stateBACKUP
interfaceeth0
virtual_router_id51
priority100
advert_int1
authentication{
auth_typePASS
auth_pass123456
}
virtual_ipaddress{
192.168.1.201
}
}
virtual_server192.168.1.2011234{
delay_loop6
lb_algorr
lb_kindDR
#nat_mask255.255.255.0
#persistence_timeout50
protocolTCP
real_server192.168.1.1691234{
weight3
TCP_CHECK{
connect_timeout3
nb_get_retry3
delay_before_retry3
connect_port1234
}
}
real_server192.168.1.1721234{
weight3
TCP_CHECK{
connect_timeout3
nb_get_retry3
delay_before_retry3
connect_port1234
}
}
real_server192.168.1.1731234{
weight3
TCP_CHECK{
connect_timeout3
nb_get_retry3
delay_before_retry3
connect_port1234
}
}
}
3.realserver(数据节点)上的配置
分别在三个数据节点db169、db172、db173上安装如下脚本:
[root@db172~]#cat/etc/init.d/lvsdr.sh
#!
/bin/bash
VIP=192.168.1.201
./etc/rc.d/init.d/functions
case"$1"in
start)
/sbin/ifconfiglodown
/sbin/ifconfigloup
echo"1">/proc/sys/net/ipv4/conf/lo/arp_ignore
echo"2">/proc/sys/net/ipv4/conf/lo/arp_announce
echo"1">/proc/sys/net/ipv4/conf/all/arp_ignore
echo"2">/proc/sys/net/ipv4/conf/all/arp_announce
/sbin/sysctl-p>/dev/null2>&1
/sbin/ifconfiglo:
0$VIPnetmask255.255.255.255up
/sbin/routeadd-host$VIPdevlo:
0
echo"LVS-DRrealserverstartssuccessfully.\n"
;;
stop)
/sbin/ifconfiglo:
0down
/sbin/routedel$VIP>/dev/null2>&1
echo"0">/proc/sys/net/ipv4/conf/lo/arp_ignore
echo"0">/proc/sys/net/ipv4/conf/lo/arp_announce
echo"0">/proc/sys/net/ipv4/conf/all/arp_ignore
echo"0">/proc/sys/net/ipv4/conf/all/arp_announce
echo"LVS-DRrealserverstopped."
;;
status)
isLoOn=`/sbin/ifconfiglo:
0|grep"$VIP"`
isRoOn=`/bin/netstat-rn|grep"$VIP"`
if["$isLoOn"==""-a"$isRoOn"==""];then
echo"LVS-DRrealserverhastorunyet."
else
echo"LVS-DRrealserverisrunning."
fi
exit3
;;
*)
echo"Usage:
$0{start|stop|status}"
exit1
esac
exit0
增加x权限:
chmod+x/etc/init.d/lvsdr.sh
增加开机自启动:
echo"/etc/init.d/lvsdr.shstart">>/etc/rc.local
4.分别在三个数据节点db169、db172、db173上安装atlas
下载atlas,并yum安装
yuminstall-yAtlas-2.1.el6.x86_64.rpm
配置atlas
[root@db172~]#cat/usr/local/mysql-proxy/conf/f
[mysql-proxy]
#带#号的为非必需的配置项目
#管理接口的用户名
admin-username=admin
#管理接口的密码
admin-password=123456
#Atlas后端连接的MySQL主库的IP和端口,可设置多项,用逗号分隔
proxy-backend-addresses=192.168.1.173:
3306
#Atlas后端连接的MySQL从库的IP和端口,@后面的数字代表权重,用来作负载均衡,若省略则默认为1,可设置多项,用逗号分隔
proxy-read-only-backend-addresses=192.168.1.169:
3306@1,192.168.1.172:
3306@1
#用户名与其对应的加密过的MySQL密码,密码使用PREFIX/bin目录下的加密程序encrypt加密,下行的user1和user2为示例,将其替换为你的MySQL的用户名和加密密码!
pwds=usr_test:
/iZxz+0GRoA=,usr_test2:
/iZxz+0GRoA=,root:
/iZxz+0GRoA=
#设置Atlas的运行方式,设为true时为守护进程方式,设为false时为前台方式,一般开发调试时设为false,线上运行时设为true
daemon=true
#设置Atlas的运行方式,设为true时Atlas会启动两个进程,一个为monitor,一个为worker,monitor在worker意外退出后会自动将其重启,设为false时只有worker,没有monitor,一般开发调试时设为false,线上运行时设为true
keepalive=true
#工作线程数,对Atlas的性能有很大影响,可根据情况适当设置
event-threads=10
#日志级别,分为message、warning、critical、error、debug五个级别
log-level=message
#日志存放的路径
log-path=/usr/local/mysql-proxy/log
#SQL日志的开关,可设置为OFF、ON、REALTIME,OFF代表不记录SQL日志,ON代表记录SQL日志,REALTIME代表记录SQL日志且实时写入磁盘,默认为OFF
#sql-log=OFF
#实例名称,用于同一台机器上多个Atlas实例间的区分
#instance=test
#Atlas监听的工作接口IP和端口
proxy-address=0.0.0.0:
1234
#Atlas监听的管理接口IP和端口
admin-address=0.0.0.0:
2345
#分表设置,此例中person为库名,mt为表名,id为分表字段,3为子表数量,可设置多项,以逗号分隔,若不分表则不需要设置该项
#tables=person.mt.id.3
#默认字符集,设置该项后客户端不再需要执行SETNAMES语句
#charset=utf8
#允许连接Atlas的客户端的IP,可以是精确IP,也可以是IP段,以逗号分隔,若不设置该项则允许所有IP连接,否则只允许列表中的IP连接
#client-ips=127.0.0.1,192.168.1
#Atlas前面挂接的LVS的物理网卡的IP(注意不是虚IP),若有LVS且设置了client-ips则此项必须设置,否则可以不设置
#lvs-ips=192.168.1.1
5.启动数据节点(分别在三个数据节点上db169、db172、db173)
5.1启动mysql数据库
5.2启动atlas:
/usr/local/mysql-proxy/bin/mysqld-proxydteststart
5.3启动lvs脚本:
/etc/init/lvsdr.shstart
6.启动keepalived(db162、db163上)
/etc/init.d/keepalivedstart
7.验证:
启动keepalived后,主节点为db162,查看vip是不是启动了:
[root@db162~]#ipad
1:
lo:
mtu16436qdiscnoqueuestateUNKNOWN
link/loopback00:
00:
00:
00:
00:
00brd00:
00:
00:
00:
00:
00
inet127.0.0.1/8scopehostlo
inet6:
:
1/128scopehost
valid_lftforeverpreferred_lftforever
2:
eth0:
mtu1500qdiscpfifo_faststateUPqlen1000
link/ether00:
1d:
7d:
a8:
40:
d9brdff:
ff:
ff:
ff:
ff:
ff
inet192.168.1.162/24brd192.168.1.255scopeglobaleth0
inet192.168.1.201/32scopeglobaleth0
inet6fe80:
:
21d:
7dff:
fea8:
40d9/64scopelink
valid_lftforeverpreferred_lftforever
验证此节点没有1234端口监听:
[root@db162~]#netstat-anp|grep1234
此处无输出
在192.168.1.55(db55)上连接192.168.1.201(注意此节点没有1234端口在监听,发来的连接会被路由到真正的数据节点)
[root@db55~]#mysql-h192.168.1.201-P1234-uroot-p123456
WelcometotheMySQLmonitor.Commandsendwith;or\g.
YourMySQLconnectionidis1871354501
Serverversion:
5.0.81-logPerconaXtraDBClusterbinary(GPL)5.6.19-25.6,Revision824,wsrep_25.6.r4111
Copyright(c)2000,2014,Oracleand/oritsaffiliates.Allrightsreserved.
OracleisaregisteredtrademarkofOracleCorporationand/orits
affiliates.Othernamesmaybetrademarksoftheirrespective
owners.
Type'help;'or'\h'forhelp.Type'\c'toclearthecurrentinputstatement.
mysql>showdatabases;
+--------------------+
|Database|
+--------------------+
|information_schema|
|dd|
|mcldb|
|mysql|
|mysqlslap|
|performance_schema|
|test|
+--------------------+
7rowsinset(0.00sec)
数据是正确的
8.监控lvs
使用sysbench压力测试,然后监控线程分布:
[root@topdbsoft]#sysbench--test=oltp--num-threads=100--max-requests=100000--oltp-table-size=1000000--oltp-test-mode=nontrx--db-driver=mysql--mysql-db=dd--mysql-host=192.168.1.201--mysql-port=1234--mysql-user=root--mysql-password=123456--oltp-nontrx-mode=select--oltp-read-only=on--db-ps-mode=disablerun
sysbench0.4.12:
multi-threadedsystemevaluationbenchmark
Runningthetestwithfollowingoptions:
Numberofthreads:
100
DoingOLTPtest.
Runningnon-transactionaltest
Doingread-onlytest
UsingSpecialdistribution(12iterations,1pctofvaluesarereturnedin75pctcases)
Using"BEGIN"forstartingtransactions
Usingauto_incontheidcolumn
MaximumnumberofrequestsforOLTPtestislimitedto100000
Threadsstarted!
Done.
OLTPteststatistics:
queriesperformed:
read:
100033
write:
0
other:
0
total:
100033
transactions:
100033(13416.81persec.)
deadlocks:
0(0.00persec.)
read/writerequests:
100033(13416.81persec.)
otheroperations:
0(0.00persec.)
Testexecutionsummary:
totaltime:
7.4558s
totalnumberofevents:
100033
totaltimetakenbyeventexecution:
744.5136
per-requeststatistics:
min:
0.71ms
avg:
7.44ms
max:
407.23ms
approx.95percentile:
28.56ms
Threadsfairness:
events(avg/stddev):
1000.3300/831.91
executiontime(avg/stddev):
7.4451/0.00
[root@db162~]#ipvsadm-Ln
IPVirtualServerversion1.2.1(size=4096)
ProtLocalAddress:
PortSchedulerFlags
->RemoteAddress:
PortForwardWeightActiveConnInActConn
TCP192.168.1.201:
1234rr
->192.168.1.169:
1234Route3033
->192.168.1.172:
1234Route3034
->192.168.1.173:
1234Route3034
可以看出负载均衡在了三个节点。
另外可以自己关机测试下keepalived的故障转移。
注意点:
1.atlas和mysql数据节点要放在一台机器上,如果atlas在别处搭建,则lvs会无法使用。
2.keepavlied最好和数据节点不在同一台机器上,否则可能会有问题。
3.最好有真机测试,虚拟机上之前一直没捣鼓好。
4.keepalived配置最好根据示例文件修改,如果有空格、ta