Mysql-mmm 高可用集群 （双主负载均衡 故障切换）

一、Mysql-mmm集群技术概述；
概述：MMM（Master-Master replication manager for MySQL）是一套支持双主故障切换和双主日常管理的脚本程序。MMM使用Perl语言开发，主要用来监控和管理MySQL Master-Master（双主）复制，可以说是mysql主主复制管理器。
虽然叫做双主复制，但是业务上同一时刻只允许对一个主进行写入，另一台备选主上提供部分读服务，以加速在主主切换时刻备选主的预热，可以说MMM这套脚本程序一方面实现了故障切换的功能，另一方面其内部附加的工具脚本也可以实现多个slave的read负载均衡。
MMM提供了自动和手动两种方式移除一组服务器中复制延迟较高的服务器的虚拟ip，同时它还可以备份数据，实现两节点之间的数据同步等。由于MMM无法完全的保证数据一致性，所以MMM适用于对数据的一致性要求不是很高，但是又想最大程度的保证业务可用性的场景。

环境准备：

系统	IP地址	主机名	所需软件
Centos 7.4 1708 64bit	192.168.59.207	master1	mysql-5.6.36.tar.gz mysql-mmm mysql-mmm-agentmysql-tools
Centos 7.4 1708 64bit	192.168.59.208	master2	mysql-5.6.36.tar.gz mysql-mmm mysql-mmm-agentmysql-tools
Centos 7.4 1708 64bit	192.168.59.209	slave1	mysql-5.6.36.tar.gz mysql-mmm mysql-mmm-agentmysql-tools
Centos 7.4 1708 64bit	192.168.59.210	slave2	mysql-5.6.36.tar.gz mysql-mmm mysql-mmm-agentmysql-tools
Centos 7.4 1708 64bit	192.168.59.211	monitor	mysql-5.6.36.tar.gz mysql-mmm mysql-mmm-monitor mysql-tools
Centos 7.4 1708 64bit	192.168.59.106	client	mysql

进程类型：
mmm_mond：监控进程，负责所有的监控工作，决定和处理所有节点角色活动。此脚本需要在监管机上运行；
mmm_agentd：运行在每个mysql服务器上（Master和Slave）的代理进程，完成监控的探针工作和执行简单的远端服务设置。此脚本需要在被监管机上运行；
mmm_control：一个简单的脚本，提供管理mmm_mond进程的命令；

工作原理：
mysql-mmm的监管端会提供多个虚拟IP（VIP），包括一个可写VIP，多个可读VIP；
通过监管的管理，这些IP会绑定在可用mysql之上；
当某一台mysql宕机时，监管会将VIP迁移至其他mysql；
环境：
配置master2、slave1、slave2、monitor节点同步ntp时间及域名解析（在此只列举master2单台主机配置）；
[root@master2 ~]# cat <>/etc/hosts
192.168.59.207 master1
192.168.59.208 master2
192.168.59.209 slave1
192.168.59.210 slave2
192.168.59.203 monitor
END

ntp前面也有就不在这演示了
配置master1实现双主复制；

[root@master1 ~]# cat <<END >>/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
END

注解：
sync_binlog=1 ##主机每次提交事务的时候把二进制日志的内容同步到磁盘上，所以即使服务器崩溃，也会把时间写入到日志中；
auto_increment_increment=2 ##以下两参数用于主主复制中，用于错开增值，防止键值冲突

[root@master2 ~]# mysql -uroot -p123123
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      120 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
mysql> grant replication slave on *.* to 'master'@'192.168.59.%' identified by '123123';
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql> exit

[root@master1 ~]# mysql -uroot -p123123
mysql> change master to master_host='192.168.59.208',master_user='master',master_password='123123',master_log_file='mysql-bin.000001',master_log_pos=120;
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;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.59.208
                  Master_User: master
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 421
               Relay_Log_File: relay1-log-bin.000002
                Relay_Log_Pos: 584
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
[root@master1 ~]# systemctl restart mysqld

配置master2实现双主复制；

[root@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
[root@master2 ~]# systemctl restart mysqld
[root@master2 ~]# mysql -uroot -p123123
mysql> change master to master_host='192.168.59.207',master_user='master',master_password='123123',master_log_file='mysql-bin.000001',master_log_pos=120;
Query OK, 0 rows affected, 2 warnings (0.01 sec)
mysql> start slave;
Query OK, 0 rows affected (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 -p123123
mysql> change master to master_host='192.168.59.207',master_user='master',master_password='123123',master_log_file='mysql-bin.000001',master_log_pos=120;
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;

*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.59.207
                  Master_User: master
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000002
          Read_Master_Log_Pos: 421
               Relay_Log_File: relay3-log-bin.000003
                Relay_Log_Pos: 584
        Relay_Master_Log_File: mysql-bin.000002
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

[root@master1 ~]# mysql -uroot -p123123
mysql> create database linux;
Query OK, 1 row affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| linux 	         |
| mysql              |
| performance_schema |
| test               |
+--------------------+
mysql> exit

[root@master2 ~]# mysql -uroot -p123123
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| linux	             |
| mysql              |
| performance_schema |
| test               |
+--------------------+
mysql> exit

[root@slave1 ~]# mysql -uroot -p123123
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| linux		         |
| mysql              |
| performance_schema |
| test               |
+--------------------+
mysql> exit

分别在master1、master2、slave1、slave2 monitor五个节点安装mysql-mmm软件程序并且配置agent指定本机的节点名称（在此只列举master1单台主机配置）；
配置本地yum源，上传Mysql_mmm_rpm文件夹到master1的/root下
scp -r Mysql_mmm_rpm root@192.168.59.209:/root/
scp -r Mysql_mmm_rpm root@192.168.59.210:/root/
scp -r Mysql_mmm_rpm root@192.168.59.208:/root/
scp -r Mysql_mmm_rpm root@192.168.59.211:/root/

cat /etc/yum.repos.d/xsy.repo 
[local]
name=local
baseurl=file:///mnt
enabled=1
gpgcheck=0
[Mysql_mmm_rpm]
name=Mysql_mmm_rpm
baseurl=file:///root/Mysql_mmm_rpm
enabled=1
gpgcheck=0


[root@master1 ~]# cd /etc/yum.repos.d/
scp xsy.repo root@192.168.59.209:/etc/yum.repos.d/
scp xsy.repo root@192.168.59.210:/etc/yum.repos.d/
scp xsy.repo root@192.168.59.211:/etc/yum.repos.d/
scp xsy.repo root@192.168.59.208:/etc/yum.repos.d/

[root@master1 ~]# yum -y install mysql-mmm mysql-mmm-agent mysql-mmm-tools
[root@master1 ~]# vi /etc/mysql-mmm/mmm_agent.conf
include mmm_common.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

[root@master2 ~]# egrep -v "^#|^$" /etc/mysql-mmm/mmm_agent.conf
include mmm_common.conf
this db2

[root@slave1 ~]# egrep -v "^#|^$" /etc/mysql-mmm/mmm_agent.conf
include mmm_common.conf
this db3

[root@slave2 ~]# egrep -v "^#|^$" /etc/mysql-mmm/mmm_agent.conf
include mmm_common.conf
this db4

在master1节点上授权monitor节点连接数据库集群；
[root@master1 ~]# mysql -uroot -p123123
mysql> grant replication client on *.* to 'mmm_monitor'@'192.168.59.%' identified by 'monitor';
Query OK, 0 rows affected (0.00 sec)
mysql> grant super,replication client,process on *.* to 'mmm_agent'@'192.168.59.%' 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       eth0			##承载的网卡
    pid_path                /var/run/mysql-mmm/mmm_agentd.pid
    bin_path                /usr/libexec/mysql-mmm/		##可执行文件路径
    replication_user        master			##mysql集群主从复制账户
    replication_password    123123
    agent_user              mmm_agent	##monitor连接集群的账户
    agent_password          agent
</host>
<host db1>
    ip      192.168.59.207
    mode    master
    peer    db2							##指定对立的节点名
</host>
<host db2>
    ip      192.168.59.208
    mode    master
    peer    db1
</host>
<host db3>
    ip      192.168.59.209
    mode    slave
</host>
<host db4>
    ip      192.168.59.210
    mode    slave
</host>
<role writer>
    hosts   db1, db2
    ips     192.168.59.250
    mode    exclusive					##同一时间存在单个主
</role>
<role reader>
    hosts   db3, db4
    ips     192.168.59.251, 192.168.59.252
    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.59.207, 192.168.59.208, 192.168.59.209, 192.168.59.210    					##指定监听的所有节点ip
	auto_set_online     60			##判定其online的时间，超过60s认为其down
   </monitor>
<host default>
    monitor_user        mmm_monitor		##monitor的工作用户
    monitor_password    monitor
</host>
debug 0
[root@monitor ~]# for i in 207 208 209 210;do scp /etc/mysql-mmm/mmm_common.conf root@192.168.59.$i:/etc/mysql-mmm/; done		##将配置文件复制到mysql节点

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

[root@master1 ~]# systemctl daemon-reload
[root@master1 ~]# systemctl start mysql-mmm-agent
[root@master1 ~]# netstat -utpln |grep mmm
tcp        0      0 192.168.59.207:9989    0.0.0.0:*               LISTEN      32670/mmm_agentd

启动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

# Warning: agent on host db4 is not reachable
  db1(192.168.59.207) master/ONLINE. Roles: writer(192.168.59.250)
  db2(192.168.59.208) master/ONLINE. Roles: 
  db3(192.168.59.209) slave/ONLINE. Roles: reader(192.168.59.251) 
  db4(192.168.59.210) slave/ONLINE. Roles: reader(192.168.59.252)

客户端验证测试读写数据；

[root@master1 ~]# mysql -uroot -p123123
mysql> grant all on *.* to 'client'@'192.168.59.%' identified by '123123';
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 -p123123 -h192.168.59.250
MySQL [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| linux              |
| mysql              |
| performance_schema |
| test               |
+--------------------+
5 rows in set (0.00 sec)
MySQL [(none)]> exit

[root@client ~]# mysql -uclient -p123123 -h192.168.59.251
MySQL [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| linux	             |
| mysql              |
| performance_schema |
| test               |
+--------------------+
MySQL [(none)]> exit

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

[root@master1 ~]# systemctl stop mysqld

[root@monitor ~]# mmm_control show
# Warning: agent on host db4 is not reachable
  db1(192.168.59.207) master/HARD_OFFLINE. Roles: 
  db2(192.168.59.208) master/ONLINE. Roles: writer(192.168.59.250)
  db3(192.168.59.209) slave/ONLINE. Roles: reader(192.168.59.251)
  db4(192.168.59.210) slave/ONLINE. Roles:

注解：
ONLINE. Roles表示在线节点；
HARD_OFFLINE表示ping不通并且(或者)mysql连接中断,会导致hard_offline状态；
admin_offline是手动下线的状态；

[root@monitor ~]# tail /var/log/mysql-mmm/mmm_mond.log
2018/08/10 07:44:37 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
2018/08/10 07:44:37 FATAL Agent on host 'db1' is reachable again
2018/08/10 07:50:28 FATAL Agent on host 'db1' is reachable again
2018/08/10 08:04:00 FATAL State of host 'db1' changed from ONLINE to HARD_OFFLINE (ping: OK, mysql: not OK)

[root@client ~]# mysql -uclient -p123123 -h192.168.59.250
MySQL [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| linux		         |
| mysql              |
| performance_schema |
| test               |
+--------------------+
MySQL [(none)]> exit

关闭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@client ~]# mysql -uclient1 -p123123 -h192.168.59.250
MySQL [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| linux	             |
| mysql              |
| performance_schema |
| test               |
+--------------------+
MySQL [(none)]> create database linuxfan1;
Query OK, 1 row affected (0.01 sec)
MySQL [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| linux	              |
| linuxfan1          |
| mysql              |
| performance_schema |
| test               |
+--------------------+
MySQL [(none)]> exit
Bye

[root@client ~]# mysql -uclient1 -p123123 -h192.168.59.251
MySQL [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| linux              |
| linuxfan1          |
| mysql              |
| performance_schema |
| test               |
+--------------------+
MySQL [(none)]> create database linuxfan2;
ERROR 1290 (HY000): The MySQL server is running with the --read-only option so it cannot execute this statement
MySQL [(none)]> exit

This is mysql-mmm
…