1、Atlas+keepalived+mysql实现HA+主从同步Atlas+lvs+keepalived +mysql+主从复制负载均衡搭建部署文档2015-8-31(V1.0)版本#作者审核修改条目发布日期(mm/dd/yy)1.0梁斌新建2015/8/311、部署背景信息基于mysql的负载均衡有很多种方式,如haproxy,前面一篇博客有介绍,还可以用更高效lvs做负载均衡,下面是基于percona xtradb cluster的三个节点的多主复制+atlas的lvs负载均衡,其实这里是不需要用atlas的,因为atlas是用来做连接池和读写分离的,而多主架构是不需要读写分离的(如果是基于
2、mysql replication的就需要atlas做负载均衡了),但为了测试atlas能不能用lvs做负载均衡,就顺便做了下实验。1.节点规划1.1 mysql数据节点:db169db172db173三个节点为xtradb cluster节点。 1.2 keepalived节点:db162db163虚拟ip为192.168.1.201 haproxy节点(仅为了对比lvs的性能才安装的):db169(部署在xtradb cluster的一个节点上)1.3 atlas节点:和xtradb cluster节点部署在一起,也为三个节点注意:atlas和mysql要部署在一个节点上,如果不在一个节点
3、上则不能用lvs dr模式负载均衡1.4 客户端测试节点:db55ip地址为192.168.1.* ,节点名为db+ip地址末位2.安装lvs及keepavlied(db162、db163上)2.1安装依赖包yum -y install kernel-devel make gcc openssl-devel libnl*下载并连接linux kernel文件,注意版本要一致(uname -a)rootdb163 # ln -s /usr/src/kernels/2.6.32-358.el6.x86_64/ /usr/src/linux安装keepalived、lvsrootdb162 # yu
4、m install ipvsadmrootdb162 # yum install keepalivedrootdb163 # yum install ipvsadmrootdb163 # yum install keepalived2.2.配置keepavlied,注意lvs不需要单独配置,在keepalived里配置就行了rootdb162 # cat /etc/keepalived/keepalived.conf! Configuration File for keepalivedglobal_defs router_id MySQL_LB1vrrp_sync_group VSG grou
5、p MySQL_Loadblancing vrrp_instance MySQL_Loadblancing state MASTER interface eth0 virtual_router_id 51 priority 101 advert_int 1 authentication auth_type PASS auth_pass 123456 virtual_ipaddress 192.168.1.201 virtual_server 192.168.1.201 1234 delay_loop 6 lb_algo rr lb_kind DR # nat_mask 255.255.255.
6、0 #persistence_timeout 50 protocol TCP real_server 192.168.1.169 1234 weight 3 TCP_CHECK connect_timeout 3 nb_get_retry 3 delay_before_retry 3 connect_port 1234 real_server 192.168.1.172 1234 weight 3 TCP_CHECK connect_timeout 3 nb_get_retry 3 delay_before_retry 3 connect_port 1234 real_server 192.1
7、68.1.173 1234 weight 3 TCP_CHECK connect_timeout 3 nb_get_retry 3 delay_before_retry 3 connect_port 1234 备机上的keepalived配置 rootdb163 # cat /etc/keepalived/keepalived.conf! Configuration File for keepalivedglobal_defs router_id MySQL_LB2vrrp_sync_group VSG group MySQL_Loadblancing vrrp_instance MySQL_
8、Loadblancing state BACKUP interface eth0 virtual_router_id 51 priority 100 advert_int 1 authentication auth_type PASS auth_pass 123456 virtual_ipaddress 192.168.1.201 virtual_server 192.168.1.201 1234 delay_loop 6 lb_algo rr lb_kind DR # nat_mask 255.255.255.0 #persistence_timeout 50 protocol TCP re
9、al_server 192.168.1.169 1234 weight 3 TCP_CHECK connect_timeout 3 nb_get_retry 3 delay_before_retry 3 connect_port 1234 real_server 192.168.1.172 1234 weight 3 TCP_CHECK connect_timeout 3 nb_get_retry 3 delay_before_retry 3 connect_port 1234 real_server 192.168.1.173 1234 weight 3 TCP_CHECK connect_
10、timeout 3 nb_get_retry 3 delay_before_retry 3 connect_port 1234 3.realserver(数据节点)上的配置分别在三个数据节点db169、db172、db173上安装如下脚本:rootdb172 # cat /etc/init.d/lvsdr.sh #!/bin/bashVIP=192.168.1.201 . /etc/rc.d/init.d/functions case $1 instart) /sbin/ifconfig lo down /sbin/ifconfig lo up echo 1 /proc/sys/net/ipv
11、4/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/null 2&1 /sbin/ifconfig lo:0 $VIP netmask 255.255.255.255 up /sbin/route add -host $VIP dev lo:0 echo LVS-DR real ser
12、ver starts successfully.n ; stop) /sbin/ifconfig lo:0 down /sbin/route del $VIP /dev/null 2&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-DR real ser
13、ver stopped. ;status) isLoOn=/sbin/ifconfig lo:0 | grep $VIP isRoOn=/bin/netstat -rn | grep $VIP if $isLoOn = -a $isRoOn = ; then echo LVS-DR real server has to run yet. else echo LVS-DR real server is running. fi exit 3 ;*) echo Usage: $0 start|stop|status exit 1 esac exit 0增加x权限:chmod +x /etc/init
14、.d/lvsdr.sh 增加开机自启动:echo /etc/init.d/lvsdr.sh start /etc/rc.local4.分别在三个数据节点db169、db172、db173上安装atlas下载atlas,并yum安装yum install -y Atlas-2.1.el6.x86_64.rpm 配置atlasrootdb172 # cat /usr/local/mysql-proxy/conf/f mysql-proxy#带#号的为非必需的配置项目#管理接口的用户名admin-username = admin#管理接口的密码admin-password = 123456#Atla
15、s后端连接的MySQL主库的IP和端口,可设置多项,用逗号分隔proxy-backend-addresses = 192.168.1.173:3306#Atlas后端连接的MySQL从库的IP和端口,后面的数字代表权重,用来作负载均衡,若省略则默认为1,可设置多项,用逗号分隔proxy-read-only-backend-addresses = 192.168.1.169:33061,192.168.1.172:33061#用户名与其对应的加密过的MySQL密码,密码使用PREFIX/bin目录下的加密程序encrypt加密,下行的user1和user2为示例,将其替换为你的MySQL的用户名
16、和加密密码!pwds = usr_test:/iZxz+0GRoA=, usr_test2:/iZxz+0GRoA= ,root:/iZxz+0GRoA=#设置Atlas的运行方式,设为true时为守护进程方式,设为false时为前台方式,一般开发调试时设为false,线上运行时设为truedaemon = true#设置Atlas的运行方式,设为true时Atlas会启动两个进程,一个为monitor,一个为worker,monitor在worker意外退出后会自动将其重启,设为false时只有worker,没有monitor,一般开发调试时设为false,线上运行时设为truekeepal
17、ive = 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实例间的区分#
18、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#默认字符集,设置该项后客户端不再需要执行SET NAMES语句#charset = utf8#允许连接Atlas的客户端的IP,可以是精确IP,也可以是IP段,以逗号分隔,若不设置该项则允许所有IP连接,否则
19、只允许列表中的IP连接#client-ips = 127.0.0.1, 192.168.1#Atlas前面挂接的LVS的物理网卡的IP(注意不是虚IP),若有LVS且设置了client-ips则此项必须设置,否则可以不设置#lvs-ips = 192.168.1.15.启动数据节点(分别在三个数据节点上db169、db172、db173)5.1 启动mysql数据库5.2 启动atlas : /usr/local/mysql-proxy/bin/mysqld-proxyd test start5.3 启动lvs脚本: /etc/init/lvsdr.sh start6.启动keepalived
20、(db162、db163上)/etc/init.d/keepalived start7.验证:启动keepalived后,主节点为db162,查看vip是不是启动了:rootdb162 # ip ad1: lo: mtu 16436 qdisc noqueue state UNKNOWN link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00 inet 127.0.0.1/8 scope host lo inet6 :1/128 scope host valid_lft forever preferred_lft forever2: eth0
21、: mtu 1500 qdisc pfifo_fast state UP qlen 1000 link/ether 00:1d:7d:a8:40:d9 brd ff:ff:ff:ff:ff:ff inet 192.168.1.162/24 brd 192.168.1.255 scope global eth0 inet 192.168.1.201/32 scope global eth0 inet6 fe80:21d:7dff:fea8:40d9/64 scope link valid_lft forever preferred_lft forever验证此节点没有1234端口监听:rootd
22、b162 # netstat -anp|grep 1234此处无输出在192.168.1.55(db55)上连接192.168.1.201(注意此节点没有1234端口在监听,发来的连接会被路由到真正的数据节点)rootdb55 # mysql -h 192.168.1.201 -P1234 -uroot -p123456Welcome to the MySQL monitor. Commands end with ; or g.Your MySQL connection id is 1871354501Server version: 5.0.81-log Percona XtraDB Clus
23、ter binary (GPL) 5.6.19-25.6, Revision 824, wsrep_25.6.r4111Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type help; or h for help. Type
24、c to clear the current input statement.mysql show databases;+-+| Database |+-+| information_schema | dd | mcldb | mysql | mysqlslap | performance_schema | test |+-+7 rows in set (0.00 sec)数据是正确的8.监控lvs使用sysbench压力测试,然后监控线程分布:roottopdb soft# sysbench -test=oltp -num-threads=100 -max-requests=100000 -
25、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=disable runsysbench 0.4.12: multi-threaded system evaluation benchmarkRunning the te
26、st with following options:Number of threads: 100Doing OLTP test.Running non-transactional testDoing read-only testUsing Special distribution (12 iterations, 1 pct of values are returned in 75 pct cases)Using BEGIN for starting transactionsUsing auto_inc on the id columnMaximum number of requests for
27、 OLTP test is limited to 100000Threads started!Done.OLTP test statistics: queries performed: read: 100033 write: 0 other: 0 total: 100033 transactions: 100033 (13416.81 per sec.) deadlocks: 0 (0.00 per sec.) read/write requests: 100033 (13416.81 per sec.) other operations: 0 (0.00 per sec.)Test exec
28、ution summary: total time: 7.4558s total number of events: 100033 total time taken by event execution: 744.5136 per-request statistics: min: 0.71ms avg: 7.44ms max: 407.23ms approx. 95 percentile: 28.56msThreads fairness: events (avg/stddev): 1000.3300/831.91 execution time (avg/stddev): 7.4451/0.00
29、rootdb162 # ipvsadm -LnIP Virtual Server version 1.2.1 (size=4096)Prot LocalAddress:Port Scheduler Flags - RemoteAddress:Port Forward Weight ActiveConn InActConnTCP 192.168.1.201:1234 rr - 192.168.1.169:1234 Route 3 0 33 - 192.168.1.172:1234 Route 3 0 34 - 192.168.1.173:1234 Route 3 0 34 可以看出负载均衡在了三个节点。另外可以自己关机测试下keepalived的故障转移。注意点:1.atlas和mysql数据节点要放在一台机器上,如果atlas在别处搭建,则lvs会无法使用。2.keepavlied最好和数据节点不在同一台机器上,否则可能会有问题。3.最好有真机测试,虚拟机上之前一直没捣鼓好。4.keepalived配置最好根据示例文件修改,如果有空格、ta
copyright@ 2008-2022 冰豆网网站版权所有
经营许可证编号:鄂ICP备2022015515号-1