一、Mysql-mmm集群技术概述

概述：

MMM（Master-Master replication manager for MySQL）是一套支持双主故障切换和双主日常管理的脚本程序。MMM使用Perl语言开发，主要用来监控和管理MySQL Master-Master（双主）复制，可以说是mysql主主复制管理器。

虽然叫做双主复制，但是业务上同一时刻只允许对一个主进行写入，另一台备选主上提供部分读服务，以加速在主主切换时刻备选主的预热，可以说MMM这套脚本程序一方面实现了故障切换的功能，另一方面其内部附加的工具脚本也可以实现多个slave的read负载均衡。

MMM提供了自动和手动两种方式移除一组服务器中复制延迟较高的服务器的虚拟ip，同时它还可以备份数据，实现两节点之间的数据同步等。由于MMM无法完全的保证数据一致性，所以MMM适用于对数据的一致性要求不是很高，但是又想最大程度的保证业务可用性的场景。
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-YsQnMZeS-1624004441798)(en-resource://database/847:1)]
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-pFExUqVZ-1624004441801)(en-resource://database/849:1)]

二、Mysql-mmm优缺点

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-3SwrtKsN-1624004441802)(en-resource://database/851:1)]

三、Mysql-mmm内部工作架构

进程类型：

mmm_mond：监控进程，负责所有的监控工作，决定和处理所有节点角色活动。此脚本需要在监管机上运行

mmm_agentd：运行在每个mysql服务器上（Master和Slave）的代理进程，完成监控的探针工作和执行简单的远端服务设置。此脚本需要在被监管机上运行

mmm_control：一个简单的脚本，提供管理mmm_mond进程的命令

工作架构：

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-JDDB8Kcw-1624004441805)(en-resource://database/853:1)]

工作原理：

mysql-mmm的监管端会提供多个虚拟IP（VIP），包括一个可写VIP，多个可读VIP

通过监管的管理，这些IP会绑定在可用mysql之上

当某一台mysql宕机时，监管会将VIP迁移至其他mysql

四、案例搭建Mysql-mmm+mysql 5.6双主高可用集群

案例环境：

准备五台MySQL服务器
分别是：

案例步骤：

部署master1节点的ntp服务以及域名解析：

hostnamectl set-hostname master1

 cat <<END >>/etc/hosts
192.168.1.1 master1
192.168.1.2 master2
192.168.1.3 slave1
192.168.1.4 slave2
192.168.1.5 monitor
END

配置master2、slave1、slave2、monitor节点同步ntp时间及域名解析（在此只列举master2单台主机配置）

yum -y install ntp
sed -i '/^server/s/^/#/g' /etc/ntp.conf 
grep "^#server" /etc/ntp.conf

cat <<END >>/etc/ntp.conf 
server 127.127.1.0
fudge 127.127.1.0 stratum 8
END

systemctl start ntpd
systemctl enable ntpd
netstat -anptu | grep ntpd

yum -y install ntpdate
ntpdate 192.168.1.1

以上操作每台服务器都要操作

配置master1实现双主复制

master1：

vi /etc/my.cnf

server-id=1
log-bin=mysql-bin
log-slave-updates
sync_binlog=1
auto_increment_increment=2
auto_increment_offset=1
relay-log=relay1-log-bin
relay-log-index=slave-relay1-bin.index

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-JH96pIUp-1624004441806)(en-resource://database/855:1)]

注解：

sync_binlog=1                                                ##主机每次提交事务的时候把二进制日志的内容同步到磁盘上，所以即使服务器崩溃，也会把时间写入到日志中；

auto_increment_increment=2                  ##以下两参数用于主主复制中，用于错开增值，防止键值冲突

systemctl restart mysqld
mysql -uroot -p123456

master1：

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      154 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

mysql> grant replication slave on *.* to 'master'@'192.168.100.%' identified by '123456';
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> exit
Bye

配置master2实现双主复制

master2：

cat <<END >>/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
END

systemctl restart mysqld
mysql -uroot -p123456

change master to master_host='192.168.100.1',master_user='master',master_password='123456',master_log_file='mysql-bin.000001',master_log_pos=154;
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;

mysql> exit
Bye

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-7HCoje8i-1624004441807)(en-resource://database/857:1)]
master2：

mysql> grant replication slave on *.* to 'master'@'192.168.1.%' identified by '123456';
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      611 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

mysql> exit
Bye

master1上操作：

mysql -uroot -p123456

mysql> change master to master_host='192.168.1.2',master_user='master',master_password='123456',master_log_file='mysql-bin.000001',master_log_pos=613;
Query OK, 0 rows affected, 2 warnings (0.02 sec)

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

mysql> show slave status\G;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-tua9IbqH-1624004441808)(en-resource://database/859:1)]

mysql> create database aaa;
Query OK, 1 row affected (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| aaa                |
| bb                 |
| mysql              |
| performance_schema |
| sys                |
| zps                |
+--------------------+
7 rows in set (0.00 sec)

master2上操作：

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| aaa                |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

配置slave1和slave2实现主从复制（在此只列举slave1单台主机配置）

[root@slave1 ~]# cat <<END >>/etc/my.cnf
server-id=4
relay-log=relay4-log-bin
relay-log-index=slave-relay4-bin.index
END

[root@slave1 ~]# systemctl restart mysqld
[root@slave1 ~]# mysql -uroot -p123456

mysql> change master to master_host='192.168.1.1',master_user='master',master_password='123456',master_log_file='mysql-bin.000001',master_log_pos=3687;
Query OK, 0 rows affected, 2 warnings (0.02 sec)

mysql> start slave;
Query OK, 0 rows affected (0.01 sec)

mysql> show slave status\G;

[root@slave2 ~]# cat <<END >>/etc/my.cnf
server-id=5
relay-log=relay5-log-bin
relay-log-index=slave-relay5-bin.index
END

[root@slave2 ~]# systemctl restart mysqld
[root@slave2 ~]# mysql -uroot -p123456

mysql> change master to master_host='192.168.1.1',master_user='master',master_password='123456',master_log_file='mysql-bin.000001',master_log_pos=3687;
Query OK, 0 rows affected, 2 warnings (0.02 sec)

mysql> start slave;
Query OK, 0 rows affected (0.01 sec)

mysql> show slave status\G;

分别在master1、master2、slave1、slave2 monitor五个节点安装mysql-mmm软件程序并且配置agent指定本机的节点名称（在此只列举master1单台主机配置）

配置本地yum源，上传Mysql_mmm_rpm文件夹到master1的/root下
master1:

[root@master1 ~]# ls
anaconda-ks.cfg  boost_1_59_0.tar.bz2  mysql-5.7.12.tar.gz  Mysql_mmm_rpm

vi /etc/yum.repos.d/centos.repo
[123]
name=123
baseurl=file:///mnt
enabled=1
gpgcheck=0


[Mysql_mmm_rpm]
name=Mysql_mmm_rpm
baseurl=file:///root/Mysql_mmm_rpm
enabled=1
gpgcheck=0

scp -r Mysql_mmm_rpm root@192.168.1.2:/root/
scp -r Mysql_mmm_rpm root@192.168.1.3:/root/
scp -r Mysql_mmm_rpm root@192.168.1.4:/root/
scp -r Mysql_mmm_rpm root@192.168.1.5:/root/

cd /etc/yum.repos.d/
scp -r centos.repo root@192.168.1.2:/etc/yum.repos.d/
scp -r centos.repo root@192.168.1.3:/etc/yum.repos.d/
scp -r centos.repo root@192.168.1.4:/etc/yum.repos.d/
scp -r centos.repo root@192.168.1.5:/etc/yum.repos.d/

yum -y install mysql-mmm mysql-mmm-agent mysql-mmm-tools

分别在master1、master2、slave1、slave2

vi /etc/mysql-mmm/mmm_agent.conf
# The 'this' variable refers to this server.  Proper operation requires
# that 'this' server (db1 by default), as well as all other servers, have the
# proper IP addresses set in mmm_common.conf.
this db1

:wq

在master1节点上授权monitor节点连接数据库集群

mysql -uroot -p123456

mysql> grant replication client on *.* to 'mmm_monitor'@'192.168.1.%' identified by 'monitor';
Query OK, 0 rows affected (0.00 sec)

mysql> grant super,replication client,process on *.* to 'mmm_agent'@'192.168.1.%' identified by 'agent';
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;

Query OK, 0 rows affected (0.00 sec)

mysql> exit

权限注解：

replication client：权限用于执行show master status等命令。这些命令是用来查看复制状态的；

replication slave：是用于连接主库从库进行读取二进制文件进而实现复制的；

super：杀死mysql中连接的进程，设置全局变量，重置主从配置的权限；process：具有查看当前运行的sql的权限 ，以及explain执行计划；

安装monitor节点上的mysql-mmm软件程序

[root@monitor ~]# yum -y install mysql-mmm mysql-mmm-tools mysql-mmm-monitor

配置 monitor节点上的mysql-mmm的配置文件并且复制到各个mysql节点；

[root@monitor ~]# vi /etc/mysql-mmm/mmm_common.conf

active_master_role      writer                                ##指定活跃角色为写角色
<host default>
    cluster_interface       ens33                            ##承载的网卡
    pid_path                /var/run/mysql-mmm/mmm_agentd.pid
    bin_path                /usr/libexec/mysql-mmm/           ##可执行文件路径
    replication_user        master                       ##mysql集群主从复制账户
    replication_password    123456
    agent_user              mmm_agent       ##monitor连接集群的账户
    agent_password          agent
</host>

<host db1>
    ip      192.168.1.1
    mode    master
    peer    db2                                                            ##指定对立的节点名
</host>

<host db2>
    ip      192.168.1.2
    mode    master
    peer    db1
</host>

<host db3>
    ip      192.168.1.3
    mode    slave
</host>

<host db4>
    ip      192.168.1.4
    mode    slave
</host>

<role writer>
    hosts   db1, db2
    ips     192.168.1.100
    mode    exclusive                                       ##同一时间存在单个主

</role>

<role reader>
    hosts   db3, db4
    ips     192.168.1.101, 192.168.1.102
    mode    balanced                              ##轮询
</role>

[root@monitor ~]# vi  /etc/mysql-mmm/mmm_mon.conf
include mmm_common.conf

<monitor>

    ip                  127.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_ips            192.168.1.1, 192.168.1.2, 192.168.1.3, 192.168.1.4    
                                               ##指定监听的所有节点ip

         auto_set_online     60                    ##判定其online的时间，超过60s认为其down

   </monitor>

<host default>

    monitor_user        mmm_monitor             ##monitor的工作用户

    monitor_password    monitor

</host>

debug 0

for i in 1 2 3 4;do scp /etc/mysql-mmm/mmm_common.conf root@192.168.1.$i:/etc/mysql-mmm/; done

启动master1、master2、slave1、slave2四个节点上的mysql-mmm服务（在此只列举master1单台主机配置）

systemctl daemon-reload
systemctl start mysql-mmm-agent
netstat -utpln |grep mmm

启动monitor节点上的mysql-mmm服务并查看集群状态

[root@monitor ~]# systemctl daemon-reload

[root@monitor ~]# systemctl start mysql-mmm-monitor

[root@monitor ~]# netstat -utpln |grep mmm

tcp        0      0 127.0.0.1:9988          0.0.0.0:*               LISTEN      
15266/mmm_mond

[root@monitor ~]#  mmm_control show
  db1(192.168.1.1) master/ONLINE. Roles: writer(192.168.1.100)
  db2(192.168.1.2) master/ONLINE. Roles: 
  db3(192.168.1.3) slave/ONLINE. Roles: reader(192.168.1.101)
  db4(192.168.1.4) slave/ONLINE. Roles: reader(192.168.1.102)

客户端验证测试读写数据

master1:

[root@master1 ~]# mysql -uroot -p123456

mysql> grant all on *.* to 'client'@'192.168.1.%' identified by '123456';

Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;

Query OK, 0 rows affected (0.00 sec)

mysql> exit

客户机：

[root@client ~]#  yum -y install mysql
[root@client ~]# mysql -uclient -p123456 -h192.168.1.100
MySQL [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| aaa                |
| bb                 |
| mysql              |
| performance_schema |
| sys                |
| zps                |
+--------------------+
7 rows in set (0.03 sec)

[root@client ~]# mysql -uclient -p123456 -h192.168.1.101
MySQL [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| aaa                |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.02 sec)

MySQL [(none)]> exit

将master1主节点的mysql服务停止，测试集群状况

[root@master1 ~]# systemctl stop mysqld

[root@monitor ~]#  mmm_control show
  db1(192.168.1.1) master/HARD_OFFLINE. Roles: 
  db2(192.168.1.2) master/ONLINE. Roles: writer(192.168.1.100)
  db3(192.168.1.3) slave/ONLINE. Roles: reader(192.168.1.101)
  db4(192.168.1.4) slave/ONLINE. Roles: reader(192.168.1.102)
  
注解：
         ONLINE. Roles表示在线节点；

         HARD_OFFLINE表示ping不通并且(或者)mysql连接中断,会导致hard_offline状态；

         admin_offline是手动下线的状态；

 [root@monitor ~]#  tail /var/log/mysql-mmm/mmm_mond.log
2021/06/18 02:57:41 FATAL Couldn't open status file '/var/lib/mysql-mmm/mmm_mond.status': Starting up without status information.
2021/06/18 02:58:42 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 60 seconds
2021/06/18 02:58:42 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 60 seconds
2021/06/18 02:58:42 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 60 seconds
2021/06/18 02:58:42 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 60 seconds
2021/06/18 03:19:04 FATAL State of host 'db1' changed from ONLINE to HARD_OFFLINE (ping: OK, mysql: not OK)

[root@localhost ~]# mysql -uclient -p123456 -h192.168.1.100
MySQL [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| aaa                |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.03 sec)

MySQL [(none)]> exit
Bye

关闭slave1和slave2节点的写功能，使其处于只读状态

[root@slave1 ~]# cat <<END >>/etc/my.cnf
read_only=1
END

[root@slave2 ~]# cat <<END >>/etc/my.cnf
read_only=1
END

注解：

read_only=1           ##开启数据库服务的只读服务，注意只有低于super权限的普通用户才会受此限制；

客户端访问测试slave节点是否可写

[root@localhost ~]# mysql -uclient -p123456 -h192.168.1.100
MySQL [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| aaa                |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

MySQL [(none)]> create database sss;
Query OK, 1 row affected (0.01 sec)

MySQL [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| aaa                |
| mysql              |
| performance_schema |
| sss                |
| sys                |
+--------------------+
6 rows in set (0.00 sec)

MySQL [(none)]> exit
Bye

[root@localhost ~]# mysql -uclient -p123456 -h192.168.1.102
MySQL [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| aaa                |
| hhh                |
| mysql              |
| performance_schema |
| sss                |
| sys                |
+--------------------+
7 rows in set (0.02 sec)

MySQL [(none)]> create database nnnn;

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

MySQL [(none)]> exit

[root@slave1 ~]# systemctl stop mysqld
[root@monitor ~]#  mmm_control show
  db1(192.168.1.1) master/HARD_OFFLINE. Roles: 
  db2(192.168.1.2) master/ONLINE. Roles: writer(192.168.1.100)
  db3(192.168.1.3) slave/HARD_OFFLINE. Roles: 
  db4(192.168.1.4) slave/ONLINE. Roles: reader(192.168.1.101), reader(192.168.1.102)
  
[root@master1 ~]# systemctl start mysqld
[root@monitor ~]#  mmm_control show
  db1(192.168.1.1) master/ONLINE. Roles: 
  db2(192.168.1.2) master/ONLINE. Roles: writer(192.168.1.100)
  db3(192.168.1.3) slave/HARD_OFFLINE. Roles: 
  db4(192.168.1.4) slave/ONLINE. Roles: reader(192.168.1.101), reader(192.168.1.102)
  
[root@master2 ~]# systemctl stop mysqld
[root@monitor ~]#  mmm_control show
  db1(192.168.1.1) master/ONLINE. Roles: writer(192.168.1.100)
  db2(192.168.1.2) master/HARD_OFFLINE. Roles: 
  db3(192.168.1.3) slave/HARD_OFFLINE. Roles: 
  db4(192.168.1.4) slave/ONLINE. Roles: reader(192.168.1.101), reader(192.168.1.102)
  
[root@slave1 ~]# systemctl start mysqld
[root@monitor ~]#  mmm_control show
  db1(192.168.1.1) master/ONLINE. Roles: writer(192.168.1.100)
  db2(192.168.1.2) master/ONLINE. Roles: 
  db3(192.168.1.3) slave/ONLINE. Roles: reader(192.168.1.101)
  db4(192.168.1.4) slave/ONLINE. Roles: reader(192.168.1.102)