Atlas+keepalived+mysql(实现HA+主从同步)

Atlas+lvs+keepalived+mysql+主从复制负载均衡搭建部署文档2015-8-31（V1.0）版本#作者审核修改条目发布日期(mm/dd/yy)1.0梁斌新建2015/8/31部署背景信息基于mysql的负载均衡有很多种方式，如haproxy，前面一篇博客有介绍，还可以用更高效lvs做负载均衡，下面是基于perconaxtradbcluster的三个节点的多主复制+atlas的lvs负载均衡，其实这里是不需要用atlas的，因为atlas是用来做连接池和读写分离的，而多主架构是不需要读写分离的（如果是基于mysqlreplication的就需要atlas做负载均衡了），但为了测试atlas能不能用lvs做负载均衡，就顺便做了下实验。1.节点规划1.1mysql数据节点：db169db172db173三个节点为xtradbcluster节点。1.2keepalived节点：db162db163虚拟ip为01haproxy节点（仅为了对比lvs的性能才安装的）：db169（部署在xtradbcluster的一个节点上）1.3atlas节点：和xtradbcluster节点部署在一起，也为三个节点注意：atlas和mysql要部署在一个节点上，如果不在一个节点上则不能用lvsdr模式负载均衡1.4客户端测试节点：db55ip地址为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~]#yuminstallkeepalived2.2.配置keepavlied，注意lvs不需要单独配置，在keepalived里配置就行了[root@db162~]#cat/etc/keepalived/keepalived.conf!ConfigurationFileforkeepalivedglobal_defs{router_idMySQL_LB1}vrrp_sync_groupVSG{group{MySQL_Loadblancing}}vrrp_instanceMySQL_Loadblancing{stateMASTERinterfaceeth0virtual_router_id51priority101advert_int1authentication{auth_typePASSauth_pass123456}virtual_ipaddress{01}}virtual_server011234{delay_loop6lb_algorrlb_kindDR#nat_mask#persistence_timeout50protocolTCPreal_server691234{weight3TCP_CHECK{connect_timeout3nb_get_retry3delay_before_retry3connect_port1234}}real_server721234{weight3TCP_CHECK{connect_timeout3nb_get_retry3delay_before_retry3connect_port1234}}real_server731234{weight3TCP_CHECK{connect_timeout3nb_get_retry3delay_before_retry3connect_port1234}}}备机上的keepalived配置[root@db163~]#cat/etc/keepalived/keepalived.conf!ConfigurationFileforkeepalivedglobal_defs{router_idMySQL_LB2}vrrp_sync_groupVSG{group{MySQL_Loadblancing}}vrrp_instanceMySQL_Loadblancing{stateBACKUPinterfaceeth0virtual_router_id51priority100advert_int1authentication{auth_typePASSauth_pass123456}virtual_ipaddress{01}}virtual_server011234{delay_loop6lb_algorrlb_kindDR#nat_mask#persistence_timeout50protocolTCPreal_server691234{weight3TCP_CHECK{connect_timeout3nb_get_retry3delay_before_retry3connect_port1234}}real_server721234{weight3TCP_CHECK{connect_timeout3nb_get_retry3delay_before_retry3connect_port1234}}real_server731234{weight3TCP_CHECK{connect_timeout3nb_get_retry3delay_before_retry3connect_port1234}}}3.realserver（数据节点）上的配置分别在三个数据节点db169、db172、db173上安装如下脚本：[root@db172~]#cat/etc/init.d/lvsdr.sh#!/bin/bashVIP=01./etc/rc.d/init.d/functionscase"$1"instart)/sbin/ifconfiglodown/sbin/ifconfigloupecho"1">/proc/sys/net/ipv4/conf/lo/arp_ignoreecho"2">/proc/sys/net/ipv4/conf/lo/arp_announceecho"1">/proc/sys/net/ipv4/conf/all/arp_ignoreecho"2">/proc/sys/net/ipv4/conf/all/arp_announce/sbin/sysctl-p>/dev/null2>&1/sbin/ifconfiglo:0$VIPnetmask55up/sbin/routeadd-host$VIPdevlo:0echo"LVS-DRrealserverstartssuccessfully.\n";;stop)/sbin/ifconfiglo:0down/sbin/routedel$VIP>/dev/null2>&1echo"0">/proc/sys/net/ipv4/conf/lo/arp_ignoreecho"0">/proc/sys/net/ipv4/conf/lo/arp_announceecho"0">/proc/sys/net/ipv4/conf/all/arp_ignoreecho"0">/proc/sys/net/ipv4/conf/all/arp_announceecho"LVS-DRrealserverstopped.";;status)isLoOn=`/sbin/ifconfiglo:0|grep"$VIP"`isRoOn=`/bin/netstat-rn|grep"$VIP"`if["$isLoOn"==""-a"$isRoOn"==""];thenecho"LVS-DRrealserverhastorunyet."elseecho"LVS-DRrealserverisrunning."fiexit3;;*)echo"Usage:$0{start|stop|status}"exit1esacexit0增加x权限：chmod+x/etc/init.d/lvsdr.sh增加开机自启动：echo"/etc/init.d/lvsdr.shstart">>/etc/rc.local4.分别在三个数据节点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=73:3306#Atlas后端连接的MySQL从库的IP和端口，@后面的数字代表权重，用来作负载均衡，若省略则默认为1，可设置多项，用逗号分隔proxy-read-only-backend-addresses=69:3306@1,72: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，线上运行时设为truedaemon=true#设置Atlas的运行方式，设为true时Atlas会启动两个进程，一个为monitor，一个为worker，monitor在worker意外退出后会自动将其重启，设为false时只有worker，没有monitor，一般开发调试时设为false，线上运行时设为truekeepalive=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=:1234#Atlas监听的管理接口IP和端口admin-address=:2345#分表设置，此例中person为库名，mt为表名，id为分表字段，3为子表数量，可设置多项，以逗号分隔，若不分表则不需要设置该项#tables=person.mt.id.3#默认字符集，设置该项后客户端不再需要执行SETNAMES语句#charset=utf8#允许连接Atlas的客户端的IP，可以是精确IP，也可以是IP段，以逗号分隔，若不设置该项则允许所有IP连接，否则只允许列表中的IP连接#client-ips=,192.168.1#Atlas前面挂接的LVS的物理网卡的IP(注意不是虚IP)，若有LVS且设置了client-ips则此项必须设置，否则可以不设置#lvs-ips=5.启动数据节点(分别在三个数据节点上db169、db172、db173)5.1启动mysql数据库5.2启动atlas:/usr/local/mysql-proxy/bin/mysqld-proxydteststart5.3启动lvs脚本：/etc/init/lvsdr.shstart6.启动keepalived（db162、db163上）/etc/init.d/keepalivedstart7.验证：启动keepalived后，主节点为db162，查看vip是不是启动了：[root@db162~]#ipad1:lo:<LOOPBACK,UP,LOWER_UP>mtu16436qdiscnoqueuestateUNKNOWNlink/loopback00:00:00:00:00:00brd00:00:00:00:00:00inet/8scopehostloinet6::1/128scopehostvalid_lftforeverpreferred_lftforever2:eth0:<BROADCAST,MULTICAST,UP,LOWER_UP>mtu1500qdiscpfifo_faststateUPqlen1000link/ether00:1d:7d:a8:40:d9brdff:ff:ff:ff:ff:ffinet62/24brd55scopeglobaleth0inet01/32scopeglobaleth0inet6fe80::21d:7dff:fea8:40d9/64scopelinkvalid_lftforeverpreferred_lftforever验证此节点没有1234端口监听：[root@db162~]#netstat-anp|grep1234此处无输出在5（db55）上连接01（注意此节点没有1234端口在监听，发来的连接会被路由到真正的数据节点）[root@db55~]#mysql-h01-P1234-uroot-p123456WelcometotheMySQLmonitor.Commandsendwith;or\g.YourMySQLconnectionidis1871354501Serverversion:5.0.81-logPerconaXtraDBClusterbinary(GPL)5.6.19-25.6,Revision824,wsrep_25.6.r4111Copyright(c)2000,2014,Oracleand/oritsaffiliates.Allrightsreserved.OracleisaregisteredtrademarkofOracleCorporationand/oritsaffiliates.Othernamesmaybetrademarksoftheirrespectiveowners.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=01--mysql-port=1234--mysql-user=root--mysql-password=123456--oltp-nontrx-mode=select--oltp-read-only=on--db-ps-mode=disablerunsysbench0.4.12:multi-threadedsystemevaluationbenchmarkRunningthetestwithfollowingoptions:Numberofthreads:100DoingOLTPtest.Runningnon-transactionaltestDoingread-onlytestUsingSpecialdistribution(12iterations,1pctofvaluesarereturnedin75pctcases)Using"BEGIN"forstartingtransactionsUsingauto_incontheidcolumnMaximumnumberofrequestsforOLTPtestislimitedto100000Threadsstarted!Done.OLTPteststatistics:queriesperformed:read:100033write:0other:0total:100033transactions:100033(13416.81persec.)deadlocks:0(0.00persec.)read/writerequests:100033(13416.81persec.)otheroperations:0(0.00persec.)Testexecutionsummary:totaltime:7.4558stotalnumberofevents:100033totaltimetakenbyeventexecution:744.5136per-requeststatistics:min:0.71msavg:7.44msmax:407.23msapprox.95percentile:28.56msThreadsfairness:events(avg/stddev):1000.3300/831.91executiontime(avg/stddev):7.4451/0.00[root@db162~]#ipvsadm-LnIPVirtualServerversion1.2.1(size=4096)ProtLocalAddress:PortSchedulerFlags->RemoteAddress:PortForwardWeightActiveConnInActConnTCP01:1234rr->69:1234Route3033->72:1234Route3034->73:1234Route3034可以看出负载均衡在了三个节点。另外可以自己关机测试下keepalived的故障转移。注意点：1.atlas和mysql数据节点要放在一台机器上，如果atlas在别处搭建，则lvs会无法使用。2.keepavlied最好和数据节点不在同一台机器上，否则可能会有问题。3.最好有真机测试，