MMM高可用MySQL服务集群解决方案_MySQL
| MMM高可用方案简介 MMM（Master-Master Replication Manager for MySQL）主主复制管理器，是一套提供了MySQL主主复制配置的监控、故障迁移和管理的可伸缩的脚本程序。在MMM高可用解决方案中，可以配置双主多从架构，通过MySQL Replication技术可以实现两台MySQL服务器互为主从，并且在任何时候只有一个节点可以写入，避免多节点写入的数据冲突，同时，当可写节点故障时，MMM套件可以立即监控到，然后将服务自动切换到另一个主节点继续提供服务，从而实现MySQL的高可用。 简而言之，通过MMM可以实现监控和管理MySQL主主复制和服务状态，同时也可以监控多个Slave节点的复制和运行状态，并且可以做到任意节点发生故障时实现自动切换的功能。MMM也为MySQL提供了读、写分离架构的良好平台。 MMM套件的优缺点 MMM集群套件具有良好的稳定性、高可用性和可扩展性。当活动的Master节点出现故障时，备用Master节点可以立即接管，而其他的Slave节点也能自动切换到备用Master节点继续进行同步复制，而无需人为干涉；MMM架构需要多个节点、多个IP，对服务器数量有要求，在读写非常繁忙的业务系统下表现不是很稳定，可能会出现复制延时、切换失效等问题。MMM方案并不太适应于对数据安全性要求很高，并且读、写繁忙的环境中。 MMM高可用套件工作原理 MMM套件主要的功能是通过以下三个脚本实现的： 1、mmm_mond：监控进程，运行在管理节点上，主要复制对所有数据库的监控工作，同时决定和处理所有节点的角色切换。 2、mmm_agentd：代理进程，运行在每一台MySQL服务器上，主要完成监控的测试工作和执行简单的远端服务设置。 3、mmm_control：一个简单的管理脚本，用来查看和管理集群运行状态，同时管理mmm_mond进程。 MMM高可用MySQL配置方案 在通过MMM套件实现的双Master架构中，需要5个IP地址，两个Master节点各有一个固定的物理IP，另外两个只读IP（reader IP）和一个可以IP（writer IP），这三个虚拟IP不会固定在任何一个节点上，相反，它会在两个Master节点之间来回切换（如何切换取决于节点的高可用）。在正常情况下Master1有两个虚拟IP（reader IP和writer IP），Master2有一个虚拟IP（reader IP），如果Master1故障，那么所有的reader和writer虚拟IP都会分配到Master上。 环境说明：
虚拟IP地址：
MMM的安装和配置 Step1：MMM套件的安装 1、在MMM管理端monitor安装MMM所有套件 [root@monitor~]#rpm-ivhepel-release-6-8.noarch.rpm[root@monitor~]#yuminstallmysql-mmmmysql-mmm-agentmysql-mmm-toolsmysql-mmm-monitor
2、在各个MySQL节点上安装mysql-mmm-agent服务 [root@master1~]#yuminstallmysql-mmm-agent[root@master2~]#yuminstallmysql-mmm-agent[root@slave1~]#yuminstallmysql-mmm-agent[root@slave2~]#yuminstallmysql-mmm-agent
Step2：Master1和两个Slave上配置主从（这里需要提前做好配置，Master1和Master2主主配置也一样） 1、Master1上授权slave1、2的复制用户 [root@master1~]#mysql-uroot-ppasswdmysql>grantreplicationslaveon*.*to'repl'@'192.168.1.250'identifiedby'replpasswd';mysql>grantreplicationslaveon*.*to'repl'@'192.168.1.209'identifiedby'replpasswd';mysql>flushprivileges; 2、Slave1、2上设置指定Master1同步复制 [root@slave1~]#mysql-uroot-ppasswdmysql>changemasterto->master_host='192.168.1.210',->master_user='repl',->master_password='replpasswd',->master_port=3306,->master_log_file='mysql-bin.000034',->master_log_pos=120;QueryOK,0rowsaffected,2warnings(0.06sec) [root@slave2~]#mysql-uroot-ppasswdmysql>changemasterto->master_host='192.168.1.210',->master_user='repl',->master_password='replpasswd',->master_port=3306,->master_log_file='mysql-bin.000034',->master_log_pos=120;QueryOK,0rowsaffected,2warnings(0.02sec) Step3：在所有的MySQL节点的/etc/my.cnf中增加参数 read_only=1 Step4：在所有的MySQL节点添加以下两个用户 mysql> grant replication client on *.* to 'mmm_monitor'@'192.168.1.%' identified by 'monitorpasswd';mysql> grant super, replication client, process on *.* to 'mmm_agent'@'192.168.1.%' identified by 'agentpasswd';Step5：在MMM管理端monitor上配置mmm_common.conf [root@monitor~]#ls/etc/mysql-mmm/mmm_agent.confmmm_common.conf#在所有的节点配置，配置相同mmm_mon.conf#仅在MMM管理端配置mmm_mon_log.confmmm_tools.conf vim /etc/mysql-mmm/mmm_common.conf 所有的MMM节点配置相同 active_master_rolewriter
Step6：在MMM管理节点上配置mmm_mon.conf [root@monitor ~]# vim /etc/mysql-mmm/mmm_mon.conf includemmm_common.conf
Step7：在所有的MySQL节点配置mmm_agent.conf [root@master1mysql]#vim/etc/mysql-mmm/mmm_agent.confincludemmm_common.confthisdb1#在四台mysql节点上设置对应的db，分别为db1、db2、db3、db4
Step8：所有节点设置ENABLED=1 cat/etc/default/mysql-mmm-agent#mysql-mmm-agentdefaultsENABLED=1 Step9：启动MMM服务 在MMM管理端启动服务 [root@monitor ~]# /etc/init.d/mysql-mmm-monitor startStarting MMM Monitor Daemon: [ OK ]
在每个mysql节点启动服务 [root@master1 ~]# /etc/init.d/mysql-mmm-agent startStarting MMM Agent Daemon: [ OK ]
查看集群运行状态 [root@monitormysql-mmm]#mmm_controlshowdb1(192.168.1.210)master/AWAITING_RECOVERY.Roles:db2(192.168.1.211)master/AWAITING_RECOVERY.Roles:db3(192.168.1.209)slave/AWAITING_RECOVERY.Roles:db4(192.168.1.250)slave/AWAITING_RECOVERY.Roles: 若一直出现上面AWAITING_RECOVERY的状态，可以手动设置各个MySQL节点为online状态 [root@monitor~]#mmm_controlset_onlinedb1OK:Stateof'db1'changedtoONLINE.Nowyoucanwaitsometimeandcheckitsnewroles![root@monitor~]#mmm_controlset_onlinedb2OK:Stateof'db2'changedtoONLINE.Nowyoucanwaitsometimeandcheckitsnewroles![root@monitor~]#mmm_controlset_onlinedb3OK:Stateof'db3'changedtoONLINE.Nowyoucanwaitsometimeandcheckitsnewroles![root@monitor~]#mmm_controlset_onlinedb4OK:Stateof'db4'changedtoONLINE.Nowyoucanwaitsometimeandcheckitsnewroles![root@monitor~]#mmm_controlshowdb1(192.168.1.210)master/ONLINE.Roles:reader(192.168.1.234),writer(192.168.1.230)db2(192.168.1.211)master/ONLINE.Roles:reader(192.168.1.231)db3(192.168.1.209)slave/ONLINE.Roles:reader(192.168.1.232)db4(192.168.1.250)slave/ONLINE.Roles:reader(192.168.1.233) 检测各个节点的运行状态 [root@monitor~]#mmm_controlchecksalldb4ping[lastchange:2016/02/2705:13:57]OKdb4mysql[lastchange:2016/02/2705:13:57]OKdb4rep_threads[lastchange:2016/02/2705:13:57]OKdb4rep_backlog[lastchange:2016/02/2705:13:57]OK:Backlogisnulldb2ping[lastchange:2016/02/2705:13:57]OKdb2mysql[lastchange:2016/02/2705:13:57]OKdb2rep_threads[lastchange:2016/02/2705:13:57]OKdb2rep_backlog[lastchange:2016/02/2705:13:57]OK:Backlogisnulldb3ping[lastchange:2016/02/2705:13:57]OKdb3mysql[lastchange:2016/02/2705:13:57]OKdb3rep_threads[lastchange:2016/02/2705:13:57]OKdb3rep_backlog[lastchange:2016/02/2705:13:57]OK:Backlogisnulldb1ping[lastchange:2016/02/2705:13:57]OKdb1mysql[lastchange:2016/02/2705:13:57]OKdb1rep_threads[lastchange:2016/02/2705:13:57]OKdb1rep_backlog[lastchange:2016/02/2705:13:57]OK:Backlogisnull
Step10：查看各节点虚拟IP分配情况 Master1 [root@master1~]#ipa|grepeth02:eth0:mtu1500qdiscpfifo_faststateUPqlen1000inet192.168.1.210/24brd192.168.1.255scopeglobaleth0inet192.168.1.234/32scopeglobaleth0inet192.168.1.230/32scopeglobaleth0 Mster2 [root@master2~]#ipa|grepeth02:eth0:mtu1500qdiscpfifo_faststateUPqlen1000inet192.168.1.211/24brd192.168.1.255scopeglobaleth0inet192.168.1.231/32scopeglobaleth0 Slave1 [root@slave1~]#ipa|grepeth02:eth0:mtu1500qdiscpfifo_faststateUPqlen1000inet192.168.1.250/24brd192.168.1.255scopeglobaleth0inet192.168.1.213/32scopeglobaleth0inet192.168.1.233/32scopeglobaleth0 Slave2 [root@slave2~]#ipa|grepeth02:eth0:mtu1500qdiscpfifo_faststateUPqlen1000inet192.168.1.209/24brd192.168.1.255scopeglobaleth0inet192.168.1.232/32scopeglobaleth0
Step11：测试MMM实现MySQL高可用 1、授权一个可以通过远程使用VIP登陆集群的用户 mysql> grant all on *.* to 'hm'@'192.168.1.%' identified by '741616710';2、使用VIP192.168.1.230登陆，并做相关测试，在各个节点查看是否同步数据库 [root@monitor ~]# mysql -uhm -p741616710 -h192.168.1.230 mysql>showvariableslike'hostname%';+---------------+---------+|Variable_name|Value|+---------------+---------+|hostname|master1|+---------------+---------+1rowinset(0.01sec)mysql>createdatabasetest1;QueryOK,1rowaffected(0.00sec)mysql>usetest1Databasechangedmysql>createtablett1(idint,namevarchar(20));QueryOK,0rowsaffected(0.13sec)mysql>insertintott1(id,name)values(1,'july'),(2,'dime');QueryOK,2rowsaffected(0.04sec)Records:2Duplicates:0Warnings:0mysql>select*fromtt1;+------+------+|id|name|+------+------+|1|july||2|dime|+------+------+2rowsinset(0.00sec)
Step12：测试MMM故障转移功能 1、关闭Master1上的MySQL服务，查看状态 [root@monitor~]#mmm_controlshowdb1(192.168.1.210)master/HARD_OFFLINE.Roles:db2(192.168.1.211)master/ONLINE.Roles:reader(192.168.1.231),writer(192.168.1.230)db3(192.168.1.209)slave/ONLINE.Roles:reader(192.168.1.232),reader(192.168.1.234)db4(192.168.1.250)slave/ONLINE.Roles:reader(192.168.1.233) [root@monitor~]#mmm_controlset_onlinedb1OK:Stateof'db1'changedtoONLINE.Nowyoucanwaitsometimeandcheckitsnewroles![root@monitor~]#mmm_controlshowdb1(192.168.1.210)master/ONLINE.Roles:reader(192.168.1.232)db2(192.168.1.211)master/ONLINE.Roles:reader(192.168.1.231),writer(192.168.1.230)db3(192.168.1.209)slave/ONLINE.Roles:reader(192.168.1.234)db4(192.168.1.250)slave/ONLINE.Roles:reader(192.168.1.233) 2、Master1故障恢复后，若想让VIP继续回到Master1上，则可以按以下手动设置 [root@monitor~]#mmm_controlmove_rolewriterdb1OK:Role'writer'hasbeenmovedfrom'db2'to'db1'.Nowyoucanwaitsometimeandchecknewrolesinfo! [root@monitor~]#mmm_controlshowdb1(192.168.1.210)master/ONLINE.Roles:reader(192.168.1.232),writer(192.168.1.230)db2(192.168.1.211)master/ONLINE.Roles:reader(192.168.1.231)db3(192.168.1.209)slave/ONLINE.Roles:reader(192.168.1.234)db4(192.168.1.250)slave/ONLINE.Roles:reader(192.168.1.233)
MMM高可用MySQL结合Amoeba实现读写分离 Step1：Amoeba的安装 需准备除MMM集群之外的第六台服务器作为Amoeba服务器 1、Amoeba是基于Java开发的，因此需要安装Java环境 [root@amoeba~]#java-versionjavaversion"1.8.0_65"Java(TM)SERuntimeEnvironment(build1.8.0_65-b17)JavaHotSpot(TM)64-BitServerVM(build25.65-b01,mixedmode) 2、下载amoeba-mysql-3.0.5-RC-distribution.zip http://nchc.dl.sourceforge.net/project/amoeba/Amoeba%20for%20mysql/3.x/amoeba-mysql-3.0.5-RC-distribution.zip 3、解压到/usr/local/目录下 [root@amoebasrc]#unzipamoeba-mysql-3.0.5-RC-distribution.zip[root@amoebasrc]#mvamoeba-mysql-3.0.5-RC/usr/local/amoeba[root@amoeba~]#ls/usr/local/amoeba/benchmarkbinconfjvm.propertieslib Step2：配置Amoeba [root@amoeba ~]# vim /usr/local/amoeba/conf/dbServers.xml <!--{cke_protected}{C}%3C!%2D%2D%3Fxmlversion%3D%221.0%22encoding%3D%22gbk%22%3F%2D%2D%3E-->[root@amoeba ~]# vim /usr/local/amoeba/conf/amoeba.xml <!--{cke_protected}{C}%3C!%2D%2D%3Fxmlversion%3D%221.0%22encoding%3D%22gbk%22%3F%2D%2D%3E--><!--{cke_protected}{C}%3C!%2D%2D%0AEachConnectionManagerwillstartasthread%0AmanagerresponsiblefortheConnectionIOread%2CDeathDetection%0A%2D%2D%3E--><connectionmanagerlist></connectionmanagerlist><!--{cke_protected}{C}%3C!%2D%2Ddefaultusingfileloader%2D%2D%3E-->${amoeba.home}/conf/dbServers.xml${amoeba.home}/conf/rule.xml${amoeba.home}/conf/ruleFunctionMap.xml${amoeba.home}/conf/functionMap.xml1500writedbwritedbmyslavestrue启动Amoebafu服务 [root@amoebalocal]#/usr/local/amoeba/bin/launcher&atcom.meidusa.toolkit.net.ServerableConnectionManager.willStart(ServerableConnectionManager.java:144)atcom.meidusa.toolkit.net.util.LoopingThread.run(LoopingThread.java:59)2015-10-2921:00:44[INFO]ProjectName=Amoeba-MySQL,PID=25948,Systemshutdown....2015-10-2921:01:34[INFO]ProjectName=Amoeba-MySQL,PID=25996,starting...log4j:WARNlog4jconfigloadcompletedfromfile:/usr/local/amoeba/conf/log4j.xml2015-10-2921:01:34,715INFOcontext.MysqlRuntimeContext-AmoebaforMysqlcurrentversoin=5.1.45-mysql-amoeba-proxy-3.0.4-BETAlog4j:WARNipaccessconfigloadcompletedfromfile:/usr/local/amoeba/conf/access_list.conf2015-10-2921:01:35,065INFOnet.ServerableConnectionManager-Serverlisteningon0.0.0.0/0.0.0.0:8066.JavaHotSpot(TM)64-BitServerVMwarning:ignoringoptionPermSize=16m;supportwasremovedin8.0JavaHotSpot(TM)64-BitServerVMwarning:ignoringoptionMaxPermSize=96m;supportwasremovedin8.02015-10-2921:11:40[INFO]ProjectName=Amoeba-MySQL,PID=26119,starting...log4j:WARNlog4jconfigloadcompletedfromfile:/usr/local/amoeba/conf/log4j.xml2015-10-2921:11:41,446INFOcontext.MysqlRuntimeContext-AmoebaforMysqlcurrentversoin=5.1.45-mysql-amoeba-proxy-3.0.4-BETAlog4j:WARNipaccessconfigloadcompletedfromfile:/usr/local/amoeba/conf/access_list.conf2015-10-2921:11:41,843INFOnet.ServerableConnectionManager-Serverlisteningon0.0.0.0/0.0.0.0:8066. 查看java进程 [root@amoeba~]#netstat-ntlp|grepjavatcp00:::8066:::*LISTEN26119/java
测试Amoeba负载均衡功能 [root@monitor~]#mysql-uroot-p741616710-h192.168.1.31-P8066Warning:Usingapasswordonthecommandlineinterfacecanbeinsecure.WelcometotheMySQLmonitor.Commandsendwith;or/g.YourMySQLconnectionidis102888364Serverversion:5.1.45-mysql-amoeba-proxy-3.0.4-BETASourcedistributionCopyright(c)2000,2015,Oracleand/oritsaffiliates.Allrightsreserved.OracleisaregisteredtrademarkofOracleCorporationand/oritsaffiliates.Othernamesmaybetrademarksoftheirrespectiveowners.Type'help;'or'/h'forhelp.Type'/c'toclearthecurrentinputstatement.mysql>usetest1Databasechangedmysql>select*fromtt1;+------+---------+|id|name|+------+---------+|210|master1||211|master2|+------+---------+2rowsinset(0.00sec)mysql>select*fromtt1;+------+---------+|id|name|+------+---------+|210|master1||250|slave1|+------+---------+2rowsinset(0.00sec)mysql>select*fromtt1;+------+---------+|id|name|+------+---------+|210|master1||209|slave2|+------+---------+2rowsinset(0.01sec)mysql>select*fromtt1;+------+---------+|id|name|+------+---------+|210|master1||211|master2|+------+---------+2rowsinset(0.01sec)mysql>select*fromtt1;+------+---------+|id|name|+------+---------+|210|master1||211|master2|+------+---------+2rowsinset(0.01sec)mysql>select*fromtt1;+------+---------+|id|name|+------+---------+|210|master1||250|slave1|+------+---------+2rowsinset(0.01sec) |
