程式師世界 >> 數據庫知識 >> MYSQL數據庫 >> MySQL綜合教程 >> MMM高可用MySQL服務集群解決方案

MMM高可用MySQL服務集群解決方案

編輯：MySQL綜合教程

MMM高可用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地址集群角色 MySQL版本系統版本 Master1 192.168.1.210 主Master可讀、寫 mysql-5.6.28 CentOS6.7 Master2 192.168.1.211 備Master可讀、寫 mysql-5.6.28 CentOS6.7 Slave1 192.168.1.250 Slave節點只讀 mysql-5.6.28 CentOS6.7 Slave2 192.168.1.209 Slave節點只讀 mysql-5.6.28 CentOS6.7 Monitor 192.168.1.21 MMM服務管理端 mysql-5.6.28 CentOS6.7

虛擬IP地址：

writer IP 192.168.1.230 寫入VIP，僅支持單節點寫入 reader IP 192.168.1.231 只讀VIP，每個數據庫節點一個讀VIP，可以通過LVS、HAproxy等負載均衡軟件對讀VIP做負載均衡 reader IP 192.168.1.232 reader IP 192.168.1.233 reader IP 192.168.1.234

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-ppasswd
mysql>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-ppasswd
mysql>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-ppasswd
mysql>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.conf
mmm_common.conf#在所有的節點配置，配置相同
mmm_mon.conf#僅在MMM管理端配置
mmm_mon_log.conf
mmm_tools.conf

vim /etc/mysql-mmm/mmm_common.conf 所有的MMM節點配置相同

active_master_rolewriter


cluster_interfaceeth0
pid_path/var/run/mysql-mmm/mmm_agentd.pid
bin_path/usr/libexec/mysql-mmm/
replication_userreplication
replication_passwordreplication
agent_usermmm_agent
agent_passwordagentpasswd



ip192.168.1.210
modemaster
peerdb2



ip192.168.1.211
modemaster
peerdb1



ip192.168.1.209
modeslave



ip192.168.1.250
modeslave



hostsdb1,db2
ips192.168.1.230
modeexclusive



hostsdb1,db2,db3,db4
ips192.168.1.231,192.168.1.232,192.168.1.233,192.168.1.234
modebalanced

Step6：在MMM管理節點上配置mmm_mon.conf

[root@monitor ~]# vim /etc/mysql-mmm/mmm_mon.conf

includemmm_common.conf


ip127.0.0.1
pid_path/var/run/mysql-mmm/mmm_mond.pid
bin_path/usr/libexec/mysql-mmm
status_path/var/lib/mysql-mmm/mmm_mond.status
ping_ips192.168.1.1,192.168.1.2,192.168.1.210,192.168.1.211,192.168.1.209,192.168.1.
250
flap_duration3600
flap_count3
auto_set_online8

#Thekill_host_bindoesnotexistbydefault,thoughthemonitorwill
#throwawarningaboutitmissing.Seethesection5.10"KillHost
#Functionality"inthePDFdocumentation.
#
#kill_host_bin/usr/libexec/mysql-mmm/monitor/kill_host
#



monitor_usermmm_monitor
monitor_passwordmonitorpasswd


debug0

Step7：在所有的MySQL節點配置mmm_agent.conf

[root@master1mysql]#vim/etc/mysql-mmm/mmm_agent.conf
includemmm_common.conf
thisdb1#在四台mysql節點上設置對應的db，分別為db1、db2、db3、db4

Step8：所有節點設置ENABLED=1

cat/etc/default/mysql-mmm-agent
#mysql-mmm-agentdefaults
ENABLED=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_controlshow
db1(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_onlinedb1
OK:Stateof'db1'changedtoONLINE.Nowyoucanwaitsometimeandcheckitsnewroles!
[root@monitor~]#mmm_controlset_onlinedb2
OK:Stateof'db2'changedtoONLINE.Nowyoucanwaitsometimeandcheckitsnewroles!
[root@monitor~]#mmm_controlset_onlinedb3
OK:Stateof'db3'changedtoONLINE.Nowyoucanwaitsometimeandcheckitsnewroles!
[root@monitor~]#mmm_controlset_onlinedb4
OK:Stateof'db4'changedtoONLINE.Nowyoucanwaitsometimeandcheckitsnewroles!
[root@monitor~]#mmm_controlshow
db1(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_controlchecksall
db4ping[lastchange:2016/02/2705:13:57]OK
db4mysql[lastchange:2016/02/2705:13:57]OK
db4rep_threads[lastchange:2016/02/2705:13:57]OK
db4rep_backlog[lastchange:2016/02/2705:13:57]OK:Backlogisnull
db2ping[lastchange:2016/02/2705:13:57]OK
db2mysql[lastchange:2016/02/2705:13:57]OK
db2rep_threads[lastchange:2016/02/2705:13:57]OK
db2rep_backlog[lastchange:2016/02/2705:13:57]OK:Backlogisnull
db3ping[lastchange:2016/02/2705:13:57]OK
db3mysql[lastchange:2016/02/2705:13:57]OK
db3rep_threads[lastchange:2016/02/2705:13:57]OK
db3rep_backlog[lastchange:2016/02/2705:13:57]OK:Backlogisnull
db1ping[lastchange:2016/02/2705:13:57]OK
db1mysql[lastchange:2016/02/2705:13:57]OK
db1rep_threads[lastchange:2016/02/2705:13:57]OK
db1rep_backlog[lastchange:2016/02/2705:13:57]OK:Backlogisnull

Step10：查看各節點虛擬IP分配情況

Master1

[root@master1~]#ipa|grepeth0
2:eth0:mtu1500qdiscpfifo_faststateUPqlen1000
inet192.168.1.210/24brd192.168.1.255scopeglobaleth0
inet192.168.1.234/32scopeglobaleth0
inet192.168.1.230/32scopeglobaleth0

Mster2

[root@master2~]#ipa|grepeth0
2:eth0:mtu1500qdiscpfifo_faststateUPqlen1000
inet192.168.1.211/24brd192.168.1.255scopeglobaleth0
inet192.168.1.231/32scopeglobaleth0

Slave1

[root@slave1~]#ipa|grepeth0
2:eth0:mtu1500qdiscpfifo_faststateUPqlen1000
inet192.168.1.250/24brd192.168.1.255scopeglobaleth0
inet192.168.1.213/32scopeglobaleth0
inet192.168.1.233/32scopeglobaleth0

Slave2

[root@slave2~]#ipa|grepeth0
2:eth0:mtu1500qdiscpfifo_faststateUPqlen1000
inet192.168.1.209/24brd192.168.1.255scopeglobaleth0
inet192.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>usetest1
Databasechanged
mysql>createtablett1(idint,namevarchar(20));
QueryOK,0rowsaffected(0.13sec)

mysql>insertintott1(id,name)values(1,'july'),(2,'dime');
QueryOK,2rowsaffected(0.04sec)
Records:2Duplicates:0Warnings:0

mysql>select*fromtt1;
+------+------+
|id|name|
+------+------+
|1|july|
|2|dime|
+------+------+
2rowsinset(0.00sec)

Step12：測試MMM故障轉移功能

1、關閉Master1上的MySQL服務，查看狀態

[root@monitor~]#mmm_controlshow
db1(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_onlinedb1
OK:Stateof'db1'changedtoONLINE.Nowyoucanwaitsometimeandcheckitsnewroles!
[root@monitor~]#mmm_controlshow
db1(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_rolewriterdb1
OK:Role'writer'hasbeenmovedfrom'db2'to'db1'.Nowyoucanwaitsometimeandchecknewrolesinfo!

[root@monitor~]#mmm_controlshow
db1(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-version
javaversion"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.xml
1500
writedb


writedb
myslaves

true

啟動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.xml
2015-10-2921:01:34,715INFOcontext.MysqlRuntimeContext-AmoebaforMysqlcurrentversoin=5.1.45-mysql-amoeba-proxy-3.0.4-BETA
log4j:WARNipaccessconfigloadcompletedfromfile:/usr/local/amoeba/conf/access_list.conf
2015-10-2921:01:35,065INFOnet.ServerableConnectionManager-Serverlisteningon0.0.0.0/0.0.0.0:8066.
JavaHotSpot(TM)64-BitServerVMwarning:ignoringoptionPermSize=16m;supportwasremovedin8.0
JavaHotSpot(TM)64-BitServerVMwarning:ignoringoptionMaxPermSize=96m;supportwasremovedin8.0
2015-10-2921:11:40[INFO]ProjectName=Amoeba-MySQL,PID=26119,starting...
log4j:WARNlog4jconfigloadcompletedfromfile:/usr/local/amoeba/conf/log4j.xml
2015-10-2921:11:41,446INFOcontext.MysqlRuntimeContext-AmoebaforMysqlcurrentversoin=5.1.45-mysql-amoeba-proxy-3.0.4-BETA
log4j:WARNipaccessconfigloadcompletedfromfile:/usr/local/amoeba/conf/access_list.conf
2015-10-2921:11:41,843INFOnet.ServerableConnectionManager-Serverlisteningon0.0.0.0/0.0.0.0:8066.

查看java進程

[root@amoeba~]#netstat-ntlp|grepjava
tcp00:::8066:::*LISTEN26119/java

測試Amoeba負載均衡功能

[root@monitor~]#mysql-uroot-p741616710-h192.168.1.31-P8066
Warning:Usingapasswordonthecommandlineinterfacecanbeinsecure.
WelcometotheMySQLmonitor.Commandsendwith;or\g.
YourMySQLconnectionidis102888364
Serverversion:5.1.45-mysql-amoeba-proxy-3.0.4-BETASourcedistribution

Copyright(c)2000,2015,Oracleand/oritsaffiliates.Allrightsreserved.

OracleisaregisteredtrademarkofOracleCorporationand/orits
affiliates.Othernamesmaybetrademarksoftheirrespective
owners.

Type'help;'or'\h'forhelp.Type'\c'toclearthecurrentinputstatement.

mysql>usetest1

Databasechanged
mysql>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)