一、Mysql-MMM群集概述

（1）MMM概述

MMM全称：Master-Master replication manager for Mysql

MMM是一套支持Mysql双主故障切换和双主日常管理的脚本程序。使用perl语言开发，主要是用来监控和管理Mysql的双主复制，是Mysql双主复制的管理器

MMM的双主复制，虽然有两台主服务器，但是在同一时刻只允许一个主进行写入，另一台主服务器会提供部分读服务（实际上是没有提供读服务的，另外一台主服务器直接就是备份的状态，不提供任何服务），以加速在主主切换的时候服务器的预热，可以说MMM这套脚本程序一方面实现了故障切换的功能，另一方面其内部附加的工具脚本也可以实现多个slave从服务器的“读”的负载均衡，并且理论上实现了读写分离（读和写访问的服务器不一样）

MMM提供了自动和手动两种方式移除一组服务器中复制延迟较高的服务器的虚拟ip，同时它还可以备份数据，实现两节点之间的数据同步。

由于MMM无法完全保证数据一致性，所以MMM的使用场景一般都是对数据的一致性要求不太高并且想最大程度的保证业务可用性的场景

（2）MMM的优缺点

优点：

对于高可用性，出现故障自动切换

对于主主同步，在同一时间只提供一台数据库的写操作，保证数据的一致性

有较好的扩展性

缺点：

Monitor节点是单节点，当Monitor出现故障时，整个群集瘫痪，可以结合keepalived实现高可用

对主机的数量有要求，需要实现读写分离，对程序来说是个挑战

（3）MMM进程 `（脚本）` 的作用

MMM进程 (脚本) 的作用：

mmm_mond: 用于监控进程，负责所有的监控工作，决定和处理所有节点的角色活动，这个进程（脚本）需要在Monitor管理器上运行
mmm_agent: 这个一个运行在每个Mysql服务器上的代理进程，完成监控的探针工作和执行简单的远端服务设置，这个进程（脚本）需要在每个Mysql服务器上运行（每个被管理的机器）
mmm_control: 这是一个简单的脚本，提供管理mmm_mond进程的命令，使用mmm_control show命令以查看Monitor管理机器的状态

（4）MMM的工作架构

MMM的工作架构

双主服务器有一个共同的VIP，这个VIP只在一台主服务器上，当这台服务器发生故障后，VIP会飘逸到另一台主服务器上
每一台从服务器都有一个VIP，当某台从服务器发生故障后，这个VIP会漂移到另外一台从服务器，而那台从服务器会有两个VIP，这样做的目的是为了用户访问原来的VIP时也可以正常访问
双主互相都是主从，所有的从服务器都指向任意一台主服务器即可
每一台Mysql服务器都需安装mmm_agent，Monitor只需要安装mmm_mon
agent会监控自己所在的服务器，当自己所在的服务器发生故障时，agent就会通知mon，mon会命令agent做出相应操作，mon相当于一个管理者，agent像是监视者和操作者
Monitor的端口是TCP的9988，agent的端口是TCP的9989，9988端口监听9989端口

二、搭建Mysql-MMM高可用群集

（1）实验环境

（2）实验步骤

实验目的：搭建Mysql的MMM高可用群集

1.先把所有的主机都安装上mysql服务（略）

2.部署Master1的ntp服务和域名解析

******（1）先做基础配置
[root@rzy ~]# hostnamectl set-hostname master1
[root@rzy ~]# su
[root@master1 ~]# systemctl stop firewalld
[root@master1 ~]# setenforce 0
setenforce: SELinux is disabled
[root@master1 ~]# mount /dev/cdrom /mnt/
mount: /dev/sr0 写保护，将以只读方式挂载
mount: /dev/sr0 已经挂载或 /mnt 忙
       /dev/sr0 已经挂载到 /mnt 上
******（2）编写hosts文件，利用scp发送给其他四台服务器
[root@master1 ~]# cat <<a>> /etc/hosts
> 192.168.100.202 master1
> 192.168.100.211 master2
> 192.168.100.212 slave1
> 192.168.100.213 slave2
> 192.168.100.214 monitor
> a
[root@master1 ~]# scp /etc/hosts root@192.168.100.211:/etc/hosts
[root@master1 ~]# scp /etc/hosts root@192.168.100.212:/etc/hosts
[root@master1 ~]# scp /etc/hosts root@192.168.100.213:/etc/hosts
[root@master1 ~]# scp /etc/hosts root@192.168.100.214:/etc/hosts
[root@master1 ~]# scp /etc/hosts root@192.168.100.215:/etc/hosts
******（3）安装ntp服务
[root@master1 ~]# yum -y install ntp
。。。。。。
完毕！
[root@master1 ~]# sed -i '/^server/s/^/#/g' /etc/ntp.conf #把以server开头的行前面加#注释掉
[root@master1 ~]# cat <<a>> /etc/ntp.conf  #指定以本机时间为准
> server 127.127.1.0
> fudge 127.127.1.0 stratum 8
> a
[root@master1 ~]# systemctl restart ntpd
[root@master1 ~]# systemctl enable ntpd
Created symlink from /etc/systemd/system/multi-user.target.wants/ntpd.service to /usr/lib/systemd/system/ntpd.service.

3.配置master1、slave1、slave2、monitor安装ntpdate，向master1同步时间

******（1）使用yum安装ntpdate，并向master1同步时间，以下操作四台服务器全部都需要打一遍
yum -y install ntpdate #安装ntpdate
/usr/sbin/ntpdate 192.168.100.202 #向192.168.100.202同步时间
echo "/usr/sbin/ntpdate 192.168.100.202" >> /etc/rc.local #编写同步时间的命令至系统启动脚本，使系统每次启动都会进行同步
chmod +x /etc/rc.local #为系统启动脚本添加可执行权限

4.配置master1和master2实现双主复制

双主复制其实就是互相指主从

******（1）配置master1的主服务器，赋权用户、查看二进制日志和位置
[root@master1 ~]# cat <<a>> /etc/my.cnf  #修改mysql配置文件
> server-id=1
> log-bin=mysql-bin  #开启二进制日志
> log-slave-updates
> sync_binlog=1  #主机每次提交事务的时候把二进制日志的内容同步到磁盘上，这样即使服务器崩溃，也会把时间写入到日志中
> auto_increment_increment=2 #这两个auto参数用于主主复制中，主要是错开增值，防止键值冲突
> auto_increment_offset=1
> relay-log=relay1-log-bin #开启中继日志
> relay-log-index=slave-relay1-bin.index #设置中级日志的index日志，二进制日志也有这个
> a
[root@master1 ~]# systemctl restart mysqld
[root@master1 ~]# mysql -u root -p123123 -e "show master status;"  #查看二进制日志现在的position位置
mysql: [Warning] Using a password on the command line interface can be insecure.
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      154 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
[root@master1 ~]# mysql -u root -p123123 #进入mysql
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.7.12-log Source distribution
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> grant replication slave on *.* to 'master'@'192.168.100.%' identified by "123123"; 
#赋权用户master，密码为123123
#replication slave：用于连接主库、从库进行读取二进制文件而进行主从复制
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql> flush privileges; #更新权限，使赋权生效
Query OK, 0 rows affected (0.00 sec)
mysql> exit
Bye
******（2）配置master2设置为master1的从服务器，然后赋权用户用于给master1当主服务器
[root@rzy ~]# hostnamectl set-hostname master2
[root@rzy ~]# su
[root@master2 ~]# systemctl stop firewalld
[root@master2 ~]# setenforce 0
setenforce: SELinux is disabled
[root@master2 ~]# mount /dev/cdrom /mnt/
mount: /dev/sr0 写保护，将以只读方式挂载
mount: /dev/sr0 已经挂载或 /mnt 忙
       /dev/sr0 已经挂载到 /mnt 上
[root@master2 ~]# cat <<a>> /etc/my.cnf
> server-id=2
> log-bin=mysql-bin
> log-slave-updates
> sync_binlog=1
> auto_increment_increment=2
> auto_increment_offset=1
> relay-log=relay2-log-bin
> relay-log-index=slave-relay2-bin.index
> a
[root@master2 ~]# systemctl restart mysqld
[root@master2 ~]# mysql -u root -p123123
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.12-log Source distribution
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> change master to master_host='192.168.100.202',master_user='master',master_password='123123',master_log_file='mysql-bin.000001',master_log_pos=154; #这个日志名称和pos位置值和上面的master1查到的一样，用户和密码就是上面赋权的
Query OK, 0 rows affected, 2 warnings (0.01 sec)
mysql> start slave; #开启从功能
mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.100.202
                  Master_User: master
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 613
               Relay_Log_File: relay2-log-bin.000002
                Relay_Log_Pos: 779
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes  #这个IO读写和SQL语句为yes就表示开启从功能成功了
            Slave_SQL_Running: Yes
。。。。。。
mysql> show master status; #查看master2的二进制日志和positon位置
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      613 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
mysql> grant replication slave on *.* to 'master'@'192.168.100.%' identified by '123123'; #赋权用户用于主从复制
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> flush privileges; #刷新使赋权用户生效
Query OK, 0 rows affected (0.00 sec)
mysql> exit 
Bye
******（3）设置master1为master2的从服务器
[root@master1 ~]# mysql -u root -p123123 #进入mysql数据库
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.7.12-log Source distribution
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> change master to master_host='192.168.100.211',master_user='master',master_password='123123',master_log_file='mysql-bin.000001',master_log_pos=613; #和master2设置一样，这里的要和上面master2查到的二进制日志与位置相同
Query OK, 0 rows affected, 2 warnings (0.01 sec)
mysql> start slave; #开启从功能
Query OK, 0 rows affected (0.00 sec)
mysql> show slave status\G; #查看IO和SQL两个选项是否为yes
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.100.211
                  Master_User: master
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 1072
               Relay_Log_File: relay1-log-bin.000002
                Relay_Log_Pos: 779
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
。。。。。。

5.配置slave1和slave2实现主从复制

下面的操作每个slave都相同

[root@slave1 ~]# cat <<a>> /etc/my.cnf
> server-id=3 #这个id和下面中继日志的id每台slave要不一样，slave是不用开启自己的二进制日志的，因为会向master进行同步
> relay-log=relay3-log-bin
> relay-log-index=slave-relay3-bin.index
> a
[root@slave1 ~]# systemctl restart mysqld
[root@slave1 ~]# mysql -u root -p123123
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.12 Source distribution
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> change master to master_host='192.168.100.202',master_user='master',master_password='123123',master_log_file='mysql-bin.000001',master_log_pos=154; #这里指定master1或者master2都可以，因为master1和2已经做了双主复制了
Query OK, 0 rows affected, 2 warnings (0.00 sec)
mysql> start slave; #开启从功能
Query OK, 0 rows affected (0.00 sec)
mysql> show slave status\G; #确认两个选项是yes即可
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.100.202
                  Master_User: master
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 1072
               Relay_Log_File: relay3-log-bin.000002
                Relay_Log_Pos: 1238
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
。。。。。。

6.在master1、master2、slave1、slave2、monitor五个服务器安装mysql-mmm软件程序

五个服务器操作相似，利用yum源安装mmm软件

[root@master1 ~]# ll #传输Mysql_mmm_rpm目录，设置此目录为yum源进行本地安装mmm软件
总用量 128508
-rw-------. 1 root root     1264 1月  12 18:27 anaconda-ks.cfg
-rw-r--r--  1 root root 70389425 5月  11 14:35 boost_1_59_0.tar.bz2
-rw-r--r--  1 root root 50571897 5月  11 14:30 mysql-5.7.12.tar.gz
drwxr-xr-x  3 root root     8192 5月  23 23:31 Mysql_mmm_rpm
[root@master1 ~]# scp -r Mysql_mmm_rpm root@192.168.100.211:/root/ #在master1上面上传然后使用scp传给另外四个服务器
[root@master1 ~]# scp -r Mysql_mmm_rpm root@192.168.100.212:/root/
[root@master1 ~]# scp -r Mysql_mmm_rpm root@192.168.100.213:/root/
[root@master1 ~]# scp -r Mysql_mmm_rpm root@192.168.100.214:/root/
[root@master1 ~]# vim /etc/yum.repos.d/centos.repo  #修改yum源文件
  1 [aaa]
  2 name=aaa
  3 baseurl=file:///mnt
  4 enabled=1
  5 gpgcheck=0
  6 
  7 [Mysql_mmm_rpm]  #添加刚刚上传的目录为yum源库
  8 name=Mysql_mmm
  9 baseurl=file:///root/Mysql_mmm_rpm
 10 enabled=1
 11 gpgcheck=0
#保存退出
[root@master1 ~]# scp /etc/yum.repos.d/centos.repo root@192.168.100.211:/etc/yum.repos.d/centos.repo #同理写一份yum源文件然后使用scp传给另外四个服务器
[root@master1 ~]# scp /etc/yum.repos.d/centos.repo root@192.168.100.212:/etc/yum.repos.d/centos.repo 
[root@master1 ~]# scp /etc/yum.repos.d/centos.repo root@192.168.100.213:/etc/yum.repos.d/centos.repo 
[root@master1 ~]# scp /etc/yum.repos.d/centos.repo root@192.168.100.214:/etc/yum.repos.d/centos.repo 
****** 在master1、master2、slave1、slave2上安装agent
[root@master1 ~]# yum -y install mysql-mmm mysql-mmm-agent mysql-mmm-tools #在master1、master2、slave1、slave2上安装agent
****** 在monitor上安装monitor
[root@monitor ~]# yum -y install mysql-mmm mysql-mmm-tools mysql-mmm-monitor
。。。。。。
完毕！
******在每个安装了agent的服务器上面修改mmm_agent.conf文件
[root@master1 ~]# vim /etc/mysql-mmm/mmm_agent.conf 
  1 include mmm_common.conf
  2 
  3 # The 'this' variable refers to this server.  Proper operation requires
  4 # that 'this' server (db1 by default), as well as all other servers, have the
  5 # proper IP addresses set in mmm_common.conf.
  6 this db1
#保存退出
[root@master2 ~]# vim /etc/mysql-mmm/mmm_agent.conf 
  1 include mmm_common.conf
  2 
  3 # The 'this' variable refers to this server.  Proper operation requires
  4 # that 'this' server (db1 by default), as well as all other servers, have the
  5 # proper IP addresses set in mmm_common.conf.
  6 this db2
#保存退出
[root@slave1 ~]# vim /etc/mysql-mmm/mmm_agent.conf 
  1 include mmm_common.conf
  2 
  3 # The 'this' variable refers to this server.  Proper operation requires
  4 # that 'this' server (db1 by default), as well as all other servers, have the
  5 # proper IP addresses set in mmm_common.conf.
  6 this db3
#保存退出
[root@slave2 ~]# vim /etc/mysql-mmm/mmm_agent.conf 
  1 include mmm_common.conf
  2 
  3 # The 'this' variable refers to this server.  Proper operation requires
  4 # that 'this' server (db1 by default), as well as all other servers, have the
  5 # proper IP addresses set in mmm_common.conf.
  6 this db4
#保存退出

7.在master1服务器上授权monitor管理服务器连接数据库群集

[root@master1 ~]# mysql -u root -p123123
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.7.12-log Source distribution
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> grant replication client on *.* to 'mmm_monitor'@'192.168.100.%' identified by 'monitor'; 
#replication client：用于执行show master status等命令，使monitor服务器可以查看复制状态
#replication slave：用于连接主库、从库进行读取二进制文件而进行主从复制
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> grant super,replication client,process on *.* to 'mmm_agent'@'192.168.100.%' identified by 'agent'; 
#super：赋权mmm_agent权限使之可以杀死mysql中连接的进程，设置全局变量，充值主从配置权限，
#process：查看当前运行的sql的权限，以及explain执行计划
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> flush privileges; #刷新使赋权生效
Query OK, 0 rows affected (0.00 sec)
mysql> exit
Bye

8.配置monitor服务器上的mysql-mmm的配置文件并且复制到各个mysql服务器上

[root@monitor ~]# vim /etc/mysql-mmm/mmm_common.conf 
  1 active_master_role      writer  #指定活跃角色为写角色
  2 
  3 <host default>
  4     cluster_interface       ens32 #指定承载的网卡，要看一下自己是什么网卡
  5     pid_path                /run/mysql-mmm-agent.pid #pid存放路径
  6     bin_path                /usr/libexec/mysql-mmm/ #可执行文件路径
  7     replication_user        master #主从复制的账户
  8     replication_password    123123 #主从复制账户的密码
  9     agent_user              mmm_agent #连接mysql群集的账户
 10     agent_password          agent #连接mysql群集账户的密码
 11 </host>
 12 
 13 <host db1> #每个host就对应着每个mysql服务器中mmm_agent配置文件的服务器名称
 14     ip      192.168.100.202
 15     mode    master
 16     peer    db2 #此master从服务器的名称，做了双主复制，这里就写master2的名称即可
 17 </host>
 18 
 19 <host db2> #和上面的db1相同，这个是master2的配置项
 20     ip      192.168.100.211
 21     mode    master
 22     peer    db1 #同样写成master1的名称
 23 </host>
 24 
 25 <host db3> #db3和4都是slave从服务器的配置项
 26     ip      192.168.100.212
 27     mode    slave
 28 </host>
 29 
 30 <host db4>
 31     ip      192.168.100.213
 32     mode    slave
 33 </host>
 34 
 35 <role writer> #配置用于写的服务器，就是两个master就行
 36     hosts   db1, db2
 37     ips     192.168.100.250
 38     mode    exclusive #这个项就可以使两台master同一时间按只存在一台，这样可以实现高可用
 39 </role>
 40 
 41 <role reader> #配置用于读的服务器，就是两个slave
 42     hosts   db3, db4
 43     ips     192.168.100.251, 192.168.100.252
 44     mode    balanced #这个项表示采用轮询的方式，实现了读的负载均衡
 45 </role>
#保存退出
[root@monitor ~]# vim /etc/mysql-mmm/mmm_mon.conf 
  1 include mmm_common.conf
  2 
  3 <monitor>
  4     ip                  127.0.0.1
  5     pid_path            /run/mysql-mmm-monitor.pid
  6     bin_path            /usr/libexec/mysql-mmm
  7     status_path         /var/lib/mysql-mmm/mmm_mond.status
  8     ping_ips            192.168.100.202,192.168.100.211,192.168.100.212,192.168.100.213 #指定所有监听的服务器的ip
  9     auto_set_online     60 #判断存活时间为60秒，超过60秒服务器没有做出回应就认为该服务器已经崩溃，monitor会做出相应调整
 10 
 11     # The kill_host_bin does not exist by default, though the monitor will
 12     # throw a warning about it missing.  See the section 5.10 "Kill Host
 13     # Functionality" in the PDF documentation.
 14     #
 15     # kill_host_bin     /usr/libexec/mysql-mmm/monitor/kill_host
 16     #
 17 </monitor>
 18 
 19 <host default>
 20     monitor_user        mmm_monitor #这里的用户为monitor的工作用户就是master1赋权的用户
 21     monitor_password    monitor #密码要填写正确
 22 </host>
 23 
 24 debug 0
#保存退出
[root@monitor ~]# scp /etc/mysql-mmm/mmm_common.conf root@192.168.100.202:/etc/mysql-mmm/mmm_common.conf 
[root@monitor ~]# scp /etc/mysql-mmm/mmm_common.conf root@192.168.100.211:/etc/mysql-mmm/mmm_common.conf 
[root@monitor ~]# scp /etc/mysql-mmm/mmm_common.conf root@192.168.100.212:/etc/mysql-mmm/mmm_common.conf 
[root@monitor ~]# scp /etc/mysql-mmm/mmm_common.conf root@192.168.100.213:/etc/mysql-mmm/mmm_common.conf

9.启动master1、master2、slave1、slave2四台服务器上的mmm服务

四台服务器操作相同

[root@master1 ~]# systemctl daemon-reload #重载服务
[root@master1 ~]# systemctl start mysql-mmm-agent 
[root@master1 ~]# netstat -anpt | grep mmm
tcp        0      0 192.168.100.202:9989    0.0.0.0:*               LISTEN      1581/mmm_agentd     
[root@master2 ~]# systemctl daemon-reload
[root@master2 ~]# systemctl start mysql-mmm-agent
[root@master2 ~]# netstat -anpt | grep agent
tcp        0      0 192.168.100.211:9989    0.0.0.0:*               LISTEN      1574/mmm_agentd     
[root@slave1 ~]# systemctl daemon-reload
[root@slave1 ~]# systemctl start mysql-mmm-agent
[root@slave1 ~]# netstat -anpt | grep agent
tcp        0      0 192.168.100.212:9989    0.0.0.0:*               LISTEN      1515/mmm_agentd    
[root@slave2 ~]# systemctl daemon-reload
[root@slave2 ~]# systemctl start mysql-mmm-agent
[root@slave2 ~]# netstat -anpt | grep agent
tcp        0      0 192.168.100.213:9989    0.0.0.0:*               LISTEN      1517/mmm_agentd

10.启动monitor服务器上的mmm服务并且查看群集状态

[root@monitor ~]# systemctl daemon-reload
[root@monitor ~]# systemctl start mysql-mmm-monitor
[root@monitor ~]# netstat -anpt | grep mmm
tcp        0      0 127.0.0.1:9988          0.0.0.0:*               LISTEN      14391/mmm_mond  
[root@monitor ~]# mmm_control show #使用mmm_control查看群集状态，需要等待一段时间就会变成存活状态
  db1(192.168.100.202) master/ONLINE. Roles: writer(192.168.100.250)
  db2(192.168.100.211) master/ONLINE. Roles: 
  db3(192.168.100.212) slave/ONLINE. Roles: reader(192.168.100.251)
  db4(192.168.100.213) slave/ONLINE. Roles: reader(192.168.100.252)
#ONLINE. Roles：表示存活，可以使用的服务器
#HARD_OFFLINE. Roles：表示ping不同并且mysql连接中断，此服务器不可用
#admin_offline：表示是手动下线的状态

至此mysql的mmm群集搭建完成

（3）测试

1.客户端测试读写数据

******（1）先在master1上赋权client用户，使客户端可以往数据库写入数据
[root@master1 ~]# mysql -u root -p123123
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 175
Server version: 5.7.12-log Source distribution
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> grant all on *.* to 'client'@'192.168.100.%' identified by '123123';
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql> exit
Bye
******（2）使用客户端访问两个master的虚拟ip192.168.100.250，进行写入数据
[root@client ~]# mysql -u client -p123123 -h 192.168.100.250
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 288
Server version: 5.7.12-log Source distribution
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.13 sec)
mysql> create database aaa;
Query OK, 1 row affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| aaa                |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)
mysql> exit
Bye
******（3）使用客户端访问slave服务器虚拟ip192.168.100.251，查看是否写了进去
[root@client ~]# mysql -u client -p123123 -h 192.168.100.251
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 268
Server version: 5.7.12 Source distribution
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show databases; #查看数据，发现成功写了进去
+--------------------+
| Database           |
+--------------------+
| information_schema |
| aaa                |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.14 sec)
mysql> exit
Bye

2.将master1服务器停止，测试高可用是否可以正常切换

******（1）关闭master1的mysql服务
[root@master1 ~]# systemctl stop mysqld
******（2）在monitor上查看服务器状态master2是否顶替了master1
[root@monitor ~]# mmm_control show  #发现vip192.168.100.250成功跳转到了master2上
  db1(192.168.100.202) master/HARD_OFFLINE. Roles: 
  db2(192.168.100.211) master/ONLINE. Roles: writer(192.168.100.250)
  db3(192.168.100.212) slave/ONLINE. Roles: reader(192.168.100.251)
  db4(192.168.100.213) slave/ONLINE. Roles: reader(192.168.100.252)
[root@monitor ~]# tail /var/log/mysql-mmm/mmm_mond.log  #查看日志，可以看到db1节点ping可以ping通，但是mysql连接不上
2021/05/24 19:20:12 FATAL Couldn't open status file '/var/lib/mysql-mmm/mmm_mond.status': Starting up without status information.
2021/05/24 19:21:13 FATAL State of host 'db4' changed from AWAITING_RECOVERY to ONLINE because of auto_set_online(60 seconds). It was in state AWAITING_RECOVERY for 61 seconds
2021/05/24 19:21:13 FATAL State of host 'db2' changed from AWAITING_RECOVERY to ONLINE because of auto_set_online(60 seconds). It was in state AWAITING_RECOVERY for 61 seconds
2021/05/24 19:21:13 FATAL State of host 'db3' changed from AWAITING_RECOVERY to ONLINE because of auto_set_online(60 seconds). It was in state AWAITING_RECOVERY for 61 seconds
2021/05/24 19:21:13 FATAL State of host 'db1' changed from AWAITING_RECOVERY to ONLINE because of auto_set_online(60 seconds). It was in state AWAITING_RECOVERY for 61 seconds
2021/05/24 19:28:54 FATAL State of host 'db1' changed from ONLINE to HARD_OFFLINE (ping: OK, mysql: not OK)
******（3）此时再次使用客户端访问，查看之前写入的数据是否还存在 
[root@client ~]# mysql -u client -p123123 -h 192.168.100.250 -e "show databases;" #发现之前写入的数据存在
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Database           |
+--------------------+
| information_schema |
| aaa                |
| mysql              |
| performance_schema |
| sys                |
+--------------------+

3.关闭slave1和slave2服务器的写功能，使slave从服务器只能读

******（1）修改slave1和2的mysql主配置文件，read_only=1配置只有低于root账户也就是低于super权限的用户才会生效，而super_read_only=1是连root账户也会限制
[root@slave1 ~]# cat <<a>> /etc/my.cnf #修改配置文件，加入read_only=1即可
> super_read_only=1
> a
[root@slave1 ~]# systemctl restart mysqld
[root@slave2 ~]# cat <<a>> /etc/my.cnf
> super_read_only=1
> a
[root@slave2 ~]# systemctl restart mysqld
******（2）使用客户端进行测试slave是否可以写
[root@client ~]# mysql -u client -p123123 -h 192.168.100.251 #进入slave服务器虚拟ip192.168.100.251
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 76
Server version: 5.7.12 Source distribution
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> create database abca; #创建数据库发现无法创建
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    11
Current database: *** NONE ***
ERROR 1290 (HY000): The MySQL server is running with the --super-read-only option so it cannot execute this statement
#写需要访问master的虚拟ip，而读需要访问slave的虚拟ip，所以说理论上也是实现了读写分离

4.关闭一个slave服务器，查看虚拟ip是否会跳转

******（1）关闭slave1的mysql
[root@slave1 ~]# systemctl stop mysqld
******（2）在monitor上查看服务器状态
[root@monitor ~]# mmm_control show #发现slave1的虚拟ip跳到了slave2，这样使客户端即使访问251也可以z
  db1(192.168.100.202) master/HARD_OFFLINE. Roles: 
  db2(192.168.100.211) master/ONLINE. Roles: writer(192.168.100.250)
  db3(192.168.100.212) slave/HARD_OFFLINE. Roles: 
  db4(192.168.100.213) slave/ONLINE. Roles: reader(192.168.100.251), reader(192.168.100.252)

e away

No connection. Trying to reconnect…

Connection id: 11

Current database: *** NONE ***

ERROR 1290 (HY000): The MySQL server is running with the --super-read-only option so it cannot execute this statement

#写需要访问master的虚拟ip，而读需要访问slave的虚拟ip，所以说理论上也是实现了读写分离

### 4.关闭一个slave服务器，查看虚拟ip是否会跳转
```shell
******（1）关闭slave1的mysql
[root@slave1 ~]# systemctl stop mysqld
******（2）在monitor上查看服务器状态
[root@monitor ~]# mmm_control show #发现slave1的虚拟ip跳到了slave2，这样使客户端即使访问251也可以z
  db1(192.168.100.202) master/HARD_OFFLINE. Roles: 
  db2(192.168.100.211) master/ONLINE. Roles: writer(192.168.100.250)
  db3(192.168.100.212) slave/HARD_OFFLINE. Roles: 
  db4(192.168.100.213) slave/ONLINE. Roles: reader(192.168.100.251), reader(192.168.100.252)

Mysql的MMM高可用群集

一、Mysql-MMM群集概述

（1）MMM概述

（2）MMM的优缺点

（3）MMM进程 `（脚本）` 的作用

（4）MMM的工作架构

二、搭建Mysql-MMM高可用群集

（1）实验环境

（2）实验步骤

1.先把所有的主机都安装上mysql服务（略）

2.部署Master1的ntp服务和域名解析

3.配置master1、slave1、slave2、monitor安装ntpdate，向master1同步时间

4.配置master1和master2实现双主复制

5.配置slave1和slave2实现主从复制

6.在master1、master2、slave1、slave2、monitor五个服务器安装mysql-mmm软件程序

7.在master1服务器上授权monitor管理服务器连接数据库群集

8.配置monitor服务器上的mysql-mmm的配置文件并且复制到各个mysql服务器上

9.启动master1、master2、slave1、slave2四台服务器上的mmm服务

10.启动monitor服务器上的mmm服务并且查看群集状态

（3）测试

1.客户端测试读写数据

2.将master1服务器停止，测试高可用是否可以正常切换

3.关闭slave1和slave2服务器的写功能，使slave从服务器只能读

4.关闭一个slave服务器，查看虚拟ip是否会跳转

热门文章

最新文章

相关课程

相关电子书

相关实验场景

推荐镜像

Mysql的MMM高可用群集

一、Mysql-MMM群集概述

（1）MMM概述

（2）MMM的优缺点

（3）MMM进程 （脚本） 的作用

（4）MMM的工作架构

二、搭建Mysql-MMM高可用群集

（1）实验环境

（2）实验步骤

1.先把所有的主机都安装上mysql服务（略）

2.部署Master1的ntp服务和域名解析

3.配置master1、slave1、slave2、monitor安装ntpdate，向master1同步时间

4.配置master1和master2实现双主复制

5.配置slave1和slave2实现主从复制

6.在master1、master2、slave1、slave2、monitor五个服务器安装mysql-mmm软件程序

7.在master1服务器上授权monitor管理服务器连接数据库群集

8.配置monitor服务器上的mysql-mmm的配置文件并且复制到各个mysql服务器上

9.启动master1、master2、slave1、slave2四台服务器上的mmm服务

10.启动monitor服务器上的mmm服务并且查看群集状态

（3）测试

1.客户端测试读写数据

2.将master1服务器停止，测试高可用是否可以正常切换

3.关闭slave1和slave2服务器的写功能，使slave从服务器只能读

4.关闭一个slave服务器，查看虚拟ip是否会跳转

热门文章

最新文章

相关课程

相关电子书

相关实验场景

推荐镜像

（3）MMM进程 `（脚本）` 的作用