MySQL—MMM高可用群集的搭建（全過(guò)程，純干貨~~）

本篇文章主要介紹搭建MMM方案以及MMM架構(gòu)的原理。MMM方案不適用對(duì)數(shù)據(jù)一致性要求很高的業(yè)務(wù)。下面一起來(lái)學(xué)習(xí)學(xué)習(xí)。

先來(lái)看看具體的架構(gòu)拓?fù)?，如下?/h4>

其中，

角色	Hosts	IP地址	應(yīng)用屬性	VIP
master1	master	192.168.142.135	write	192.168.142.188
master2	backup	192.168.142.132	write|read	192.168.142.188
slave1	slave	192.168.142.136	read	192.168.142.200, 192.168.142.210
slave2	slave	192.168.142.137	read	192.168.142.200, 192.168.142.210
monitor	monitor	192.168.142.143	moitor	無(wú)VIP

具體配置步驟

一、實(shí)驗(yàn)環(huán)境配置

配置ALI云源（若已安裝可跳過(guò)）

這里以master1作示范，實(shí)際五臺(tái)均需要安裝

[root@master1 ~]# wget -O /etc/yum.repos.d/CentOS-Base.repo http://mirrors.aliyun.com/repo/Centos-7.repo
[root@master1 ~]# yum -y install epel-release
[root@master1 ~]# yum clean all && yum makecache

給所有主從服務(wù)器安裝MySQL，并配置

[root@master1 ~]# yum install mysql -y
[root@master1 ~]# vim /etc/my.cnf      #修改配置文件
    ##server id不能一樣
    server-id =
    log-bin = zhu-bin
    log-slave-updates = ture    ##開(kāi)啟主從同步
    sync_binlog = 1   ##二進(jìn)制文件立即寫入
    auto_increment_increment=2           ###增量為2   
    auto_increment_offset=1               ######  起始值為1
[root@master1 ~]# systemctl restart mysqld
[root@master1 ~]# systemctl enable mysqld

二、配置主主同步（兩個(gè)主服務(wù)器相互復(fù)制，互相平級(jí)）

m1給m2從權(quán)限，m2給m1從權(quán)限

[root@master1 ~]# mysql -u root -p
grant replication slave on *.* to 'myslave'@'192.168.142.%' identified by 'asd123';
    #允許從服務(wù)器使用myslave賬戶在主服務(wù)器上進(jìn)行復(fù)制操作（兩臺(tái)主均要授權(quán)）
show master status;
+------------------+----------+--------------+--------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB         |
+------------------+----------+--------------+--------------------------+
| mysql_bin.000001 |      245 |              | mysql,information_schema |
+------------------+----------+--------------+--------------------------+
1 row in set (0.00 sec)
    #查看作為主服務(wù)器的信息，關(guān)鍵點(diǎn)為position號(hào)碼
change master to master_host='主服務(wù)器地址',master_user='myslave',master_password='asd123',master_log_file='File',master_log_pos=position號(hào)碼;
    ##授予權(quán)限（m1為m2的主，m2為m1的主）
start slave;
show slave status\G
    Slave_IO_Running: Yes
    Slave_SQL_Running: Yes
    #查看作為從服務(wù)器的信息，是否同步開(kāi)啟

三、進(jìn)行主從同步（兩臺(tái)從指一臺(tái)同一臺(tái)主）

配置兩臺(tái)從服務(wù)器指向master1

[root@slave1 ~]# vim /etc/my.cnf
    server-id = 6    #不能有相同的
    log-bin=mysql-bin
    log-slave-updates=ture
    sync_binlog = 1
[root@slave1 ~]# systemctl restart mysqld
[root@slave1 ~]# netstat -atnp | grep 3306
tcp6       0      0 :::3306                 :::*                    LISTEN      38831/mysqld

#提權(quán)，指向master1（兩臺(tái)從操作相同?。。。。。?[root@slave1 ~]# mysql -uroot -p
change master to master_host='主服務(wù)器地址',master_user='myslave',master_password='asd123',master_log_file='主服務(wù)器查看到的信息',master_log_pos=號(hào)碼;
    ##授予權(quán)限（slave1、slave2為master1的從）
start slave;
show slave status\G
    Slave_IO_Running: Yes
    Slave_SQL_Running: Yes
    #查看作為從服務(wù)器的信息，是否同步開(kāi)啟

四、所有服務(wù)器安裝并配置MMM

修改mmm_common配置文件

[root@master1 ~]# yum -y install mysql-mmm*

#修改MMM配置文件
[root@master1 ~]# vim /etc/mysql-mmm/mmm_common.conf
cluster_interface       ens33
......
replication_user        myslave     #從服務(wù)器訪問(wèn)主服務(wù)器賬戶
replication_password    asd123
agent_user              mmm_user    #MMM用戶賬戶
agent_password          123123

<host db1>
    ip      192.168.142.135        #master1的IP地址
    mode    master
    peer    db2
</host>

<host db2>
    ip      192.168.142.132        #master2的IP地址
    mode    master
    peer    db1
</host>

<host db3>
    ip      192.168.142.136        #slave1的IP地址
    mode    slave
</host>

<host db4>
    ip      192.168.142.137        #slave2的IP地址
    mode    slave
</host>

<role writer>
    hosts   db1, db2
    ips     192.168.142.188       ###VIP虛擬IP地址
    mode    exclusive
</role>

<role reader>
    hosts   db3, db4
    ips     192.168.142.200, 192.168.142.210      ###VIP虛擬IP地址
    mode    balanced
</role>

#scp遠(yuǎn)程復(fù)制，將配置遠(yuǎn)程推送
[root@master1 ~]# scp /etc/mysql-mmm/mmm_common.conf root@192.168.142.132:/etc/mysql-mmm/
[root@master1 ~]# scp /etc/mysql-mmm/mmm_common.conf root@192.168.142.136:/etc/mysql-mmm/
[root@master1 ~]# scp /etc/mysql-mmm/mmm_common.conf root@192.168.142.137:/etc/mysql-mmm/
[root@master1 ~]# scp /etc/mysql-mmm/mmm_common.conf root@192.168.142.143:/etc/mysql-mmm/

修改mmm_monitor配置文件（僅monitor監(jiān)聽(tīng)端）

[root@localhost ~]# vim /etc/mysql-mmm/mmm_mon.conf
<monitor>
    ......
    ping_ips            192.168.142.135,192.168.142.132,192.168.142.136,192.168.142.137     #所有主從服務(wù)器IP地址
    auto_set_online     5
    #等待上線時(shí)間

<host default>
    monitor_user        mmm_monitor     ##創(chuàng)建監(jiān)控用戶
    monitor_password    123123
</host>

在所有主從數(shù)據(jù)庫(kù)中（MMM用戶、監(jiān)控用戶）進(jìn)行提權(quán)

[root@master1 ~]# mysql -u root -p
grant super,replication client,process on *.* to 'mmm_user'@'192.168.142.%' identified by '123123';
grant replication client on *.* to 'mmm_monitor'@'192.168.142.%' identified by '123123';
flush privileges
##每臺(tái)主從都要提權(quán)

修改agant配置文件（每臺(tái)主從都要修改）

位置：/etc/mysql-mmm/mmm_agent.conf

[root@master1 ~]# vim /etc/mysql-mmm/mmm_agent.conf
this db1（對(duì)應(yīng)的角色）

五、開(kāi)啟服務(wù)

所有主從服務(wù)器開(kāi)啟mmm_agant服務(wù)

[root@master1 ~]# systemctl start mysql-mmm-agent.service 
[root@master1 ~]# systemctl enable mysql-mmm-agent.service 

監(jiān)控端開(kāi)啟mmm_monitor服務(wù)

[root@localhost ~]# systemctl stop firewalld.service 
[root@localhost ~]# setenforce 0
[root@localhost ~]# systemctl start mysql-mmm-monitor.service
[root@localhost ~]# mmm_control show   ##查看監(jiān)控狀態(tài)

六、驗(yàn)證結(jié)果

[root@localhost mysql-mmm]# mmm_control show
  db1(192.168.142.135) master/ONLINE. Roles: writer(192.168.18.250)
  db2(192.168.142.132) master/ONLINE. Roles: 
  db3(192.168.142.136) slave/ONLINE. Roles: reader(192.168.18.251)
  db4(192.168.142.137) slave/ONLINE. Roles: reader(192.168.18.252)

//利用命令調(diào)整虛擬IP切換至master2：
[root@localhost mysql-mmm]# mmm_control move_role writer db2
OK: Role 'writer' has been moved from 'db1' to 'db2'. Now you can wait some time and check new roles info!
[root@localhost mysql-mmm]# mmm_control show
  db1(192.168.142.135) master/ONLINE. Roles: 
  db2(192.168.142.132) master/ONLINE. Roles: writer(192.168.18.250)
  db3(192.168.142.136) slave/ONLINE. Roles: reader(192.168.18.251)
  db4(192.168.142.137) slave/ONLINE. Roles: reader(192.168.18.252)

//檢測(cè)所有狀態(tài)是否都正常：
[root@localhost mysql-mmm]# mmm_control checks all
db4  ping         [last change: 2019/11/25 15:25:54]  OK
db4  mysql        [last change: 2019/11/25 15:25:54]  OK
db4  rep_threads  [last change: 2019/11/25 15:25:54]  OK
db4  rep_backlog  [last change: 2019/11/25 15:25:54]  OK: Backlog is null
db2  ping         [last change: 2019/11/25 15:25:54]  OK
db2  mysql        [last change: 2019/11/25 15:25:54]  OK
db2  rep_threads  [last change: 2019/11/25 15:25:54]  OK
db2  rep_backlog  [last change: 2019/11/25 15:25:54]  OK: Backlog is null
db3  ping         [last change: 2019/11/25 15:25:54]  OK
db3  mysql        [last change: 2019/11/25 15:25:54]  OK
db3  rep_threads  [last change: 2019/11/25 15:25:54]  OK
db3  rep_backlog  [last change: 2019/11/25 15:25:54]  OK: Backlog is null
db1  ping         [last change: 2019/11/25 15:25:54]  OK
db1  mysql        [last change: 2019/11/25 15:25:54]  OK
db1  rep_threads  [last change: 2019/11/25 15:25:54]  OK
db1  rep_backlog  [last change: 2019/11/25 15:25:54]  OK: Backlog is null

以上就是MMM群集搭建的全過(guò)程，感謝閱讀??！