Atlas+keepalived+mysql(實(shí)現(xiàn)HA+主從同步)

Atlas+lvs+keepalived+mysql+主從復(fù)制負(fù)載均衡搭建部署文檔2015-8-31（V1.0）版本#作者審核修改條目發(fā)布日期(mm/dd/yy)1.0梁斌新建2015/8/31部署背景信息基于mysql的負(fù)載均衡有很多種方式，如haproxy，前面一篇博客有介紹，還可以用更高效lvs做負(fù)載均衡，下面是基于perconaxtradbcluster的三個(gè)節(jié)點(diǎn)的多主復(fù)制+atlas的lvs負(fù)載均衡，其實(shí)這里是不需要用atlas的，因?yàn)閍tlas是用來做連接池和讀寫分離的，而多主架構(gòu)是不需要讀寫分離的（如果是基于mysqlreplication的就需要atlas做負(fù)載均衡了），但為了測(cè)試atlas能不能用lvs做負(fù)載均衡，就順便做了下實(shí)驗(yàn)。1.節(jié)點(diǎn)規(guī)劃1.1mysql數(shù)據(jù)節(jié)點(diǎn)：db169db172db173三個(gè)節(jié)點(diǎn)為xtradbcluster節(jié)點(diǎn)。1.2keepalived節(jié)點(diǎn)：db162db163虛擬ip為01haproxy節(jié)點(diǎn)（僅為了對(duì)比lvs的性能才安裝的）：db169（部署在xtradbcluster的一個(gè)節(jié)點(diǎn)上）1.3atlas節(jié)點(diǎn)：和xtradbcluster節(jié)點(diǎn)部署在一起，也為三個(gè)節(jié)點(diǎn)注意：atlas和mysql要部署在一個(gè)節(jié)點(diǎn)上，如果不在一個(gè)節(jié)點(diǎn)上則不能用lvsdr模式負(fù)載均衡1.4客戶端測(cè)試節(jié)點(diǎn)：db55ip地址為192.168.1.*,節(jié)點(diǎn)名為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不需要單獨(dú)配置，在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}}}備機(jī)上的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（數(shù)據(jù)節(jié)點(diǎn)）上的配置分別在三個(gè)數(shù)據(jù)節(jié)點(diǎn)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權(quán)限：chmod+x/etc/init.d/lvsdr.sh增加開機(jī)自啟動(dòng)：echo"/etc/init.d/lvsdr.shstart">>/etc/rc.local4.分別在三個(gè)數(shù)據(jù)節(jié)點(diǎn)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]#帶#號(hào)的為非必需的配置項(xiàng)目#管理接口的用戶名admin-username=admin#管理接口的密碼admin-password=123456#Atlas后端連接的MySQL主庫(kù)的IP和端口，可設(shè)置多項(xiàng)，用逗號(hào)分隔proxy-backend-addresses=73:3306#Atlas后端連接的MySQL從庫(kù)的IP和端口，@后面的數(shù)字代表權(quán)重，用來作負(fù)載均衡，若省略則默認(rèn)為1，可設(shè)置多項(xiàng)，用逗號(hào)分隔proxy-read-only-backend-addresses=69:3306@1,72:3306@1#用戶名與其對(duì)應(yīng)的加密過的MySQL密碼，密碼使用PREFIX/bin目錄下的加密程序encrypt加密，下行的user1和user2為示例，將其替換為你的MySQL的用戶名和加密密碼！pwds=usr_test:/iZxz+0GRoA=,usr_test2:/iZxz+0GRoA=,root:/iZxz+0GRoA=#設(shè)置Atlas的運(yùn)行方式，設(shè)為true時(shí)為守護(hù)進(jìn)程方式，設(shè)為false時(shí)為前臺(tái)方式，一般開發(fā)調(diào)試時(shí)設(shè)為false，線上運(yùn)行時(shí)設(shè)為truedaemon=true#設(shè)置Atlas的運(yùn)行方式，設(shè)為true時(shí)Atlas會(huì)啟動(dòng)兩個(gè)進(jìn)程，一個(gè)為monitor，一個(gè)為worker，monitor在worker意外退出后會(huì)自動(dòng)將其重啟，設(shè)為false時(shí)只有worker，沒有monitor，一般開發(fā)調(diào)試時(shí)設(shè)為false，線上運(yùn)行時(shí)設(shè)為truekeepalive=true#工作線程數(shù)，對(duì)Atlas的性能有很大影響，可根據(jù)情況適當(dāng)設(shè)置event-threads=10#日志級(jí)別，分為message、warning、critical、error、debug五個(gè)級(jí)別log-level=message#日志存放的路徑log-path=/usr/local/mysql-proxy/log#SQL日志的開關(guān)，可設(shè)置為OFF、ON、REALTIME，OFF代表不記錄SQL日志，ON代表記錄SQL日志，REALTIME代表記錄SQL日志且實(shí)時(shí)寫入磁盤，默認(rèn)為OFF#sql-log=OFF#實(shí)例名稱，用于同一臺(tái)機(jī)器上多個(gè)Atlas實(shí)例間的區(qū)分#instance=test#Atlas監(jiān)聽的工作接口IP和端口proxy-address=:1234#Atlas監(jiān)聽的管理接口IP和端口admin-address=:2345#分表設(shè)置，此例中person為庫(kù)名，mt為表名，id為分表字段，3為子表數(shù)量，可設(shè)置多項(xiàng)，以逗號(hào)分隔，若不分表則不需要設(shè)置該項(xiàng)#tables=person.mt.id.3#默認(rèn)字符集，設(shè)置該項(xiàng)后客戶端不再需要執(zhí)行SETNAMES語句#charset=utf8#允許連接Atlas的客戶端的IP，可以是精確IP，也可以是IP段，以逗號(hào)分隔，若不設(shè)置該項(xiàng)則允許所有IP連接，否則只允許列表中的IP連接#client-ips=,192.168.1#Atlas前面掛接的LVS的物理網(wǎng)卡的IP(注意不是虛IP)，若有LVS且設(shè)置了client-ips則此項(xiàng)必須設(shè)置，否則可以不設(shè)置#lvs-ips=5.啟動(dòng)數(shù)據(jù)節(jié)點(diǎn)(分別在三個(gè)數(shù)據(jù)節(jié)點(diǎn)上db169、db172、db173)5.1啟動(dòng)mysql數(shù)據(jù)庫(kù)5.2啟動(dòng)atlas:/usr/local/mysql-proxy/bin/mysqld-proxydteststart5.3啟動(dòng)lvs腳本：/etc/init/lvsdr.shstart6.啟動(dòng)keepalived（db162、db163上）/etc/init.d/keepalivedstart7.驗(yàn)證：?jiǎn)?dòng)keepalived后，主節(jié)點(diǎn)為db162，查看vip是不是啟動(dòng)了：[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驗(yàn)證此節(jié)點(diǎn)沒有1234端口監(jiān)聽：[root@db162~]#netstat-anp|grep1234此處無輸出在5（db55）上連接01（注意此節(jié)點(diǎn)沒有1234端口在監(jiān)聽，發(fā)來的連接會(huì)被路由到真正的數(shù)據(jù)節(jié)點(diǎn)）[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)數(shù)據(jù)是正確的8.監(jiān)控lvs使用sysbench壓力測(cè)試，然后監(jiān)控線程分布：[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可以看出負(fù)載均衡在了三個(gè)節(jié)點(diǎn)。另外可以自己關(guān)機(jī)測(cè)試下keepalived的故障轉(zhuǎn)移。注意點(diǎn)：1.atlas和mysql數(shù)據(jù)節(jié)點(diǎn)要放在一臺(tái)機(jī)器上，如果atlas在別處搭建，則lvs會(huì)無法使用。2.keepavlied最好和數(shù)據(jù)節(jié)點(diǎn)不在同一臺(tái)機(jī)器上，否則可能會(huì)有問題。3.最好有真機(jī)測(cè)試，