MySQL-MMM高可用架构

目录

• MySQL-MMM高可用架构
  • 一、MMM
    • 1. MMM的概述
    • 2. MMM的应用场景
    • 3. MMM的特点
    • 4. 关于MMM高可用架构的说明
    • 5. 用户及授权
  • 二、案例环境
    • 1. 服务器配置
    • 2. 服务器环境
    • 3. 修改主机名称
  • 三、案例实施
    • 1. 搭建MySQL多主多从架构
      • 1.1 master1、master2、slave1、slave2节点上安装mysql5.7
      • 1.2 修改master1配置文件
      • 1.3 把配置文件复制到其他3台数据库服务器并重启mysql服务器
      • 1.4 配置主主复制，两台主服务器相互复制
        • 1.4.1 在两台主服务器上都执行授予从的权限，从服务器上不需要执行
        • 1.4.2 在两台主服务器上查看
        • 1.4.3 在master1上配置同步
        • 1.4.4 在master2上配置同步
      • 1.5 配置主从复制，在两台从服务器上做
      • 1.6 测试主主、主从同步情况
    • 2. 安装配置MySQL-MMM
      • 2.1 在所有服务器上安装MySQL-MMM
      • 2.2 阿里云源仓库配置方法
      • 2.3 在master1上对MySQL-MMM进行配置
      • 2.4 把配置文件复制到其他4台主机
      • 2.5 修改所有数据库服务器的代理配置文件mmm_agent.conf
      • 2.6 在monitor监控服务器上修改监控配置文件mmm_mon.conf
      • 2.7 在所有数据库上为 mmm_agent（代理进程）授权
      • 2.8 在所有数据库上为mmm_monitor（监控进程）授权
      • 2.8 在所有数据库服务器上启动mysql-mmm-agent
      • 2.9 在monitor服务器上启动mysql-mmm-monitor
      • 2.10 在monitor服务器上测试群集
        • 2.10.1 查看各节点的情况
        • 2.10.2 检测监控功能是否完善
        • 2.10.3 指定绑定VIP的主机
    • 3. 故障测试
      • 3.1 模拟master宕机以及恢复
        • 3.1.1 停止master1的mysql服务
        • 3.1.2 查看VIP漂移情况
        • 3.1.3 重启master1的mysql服务
        • 3.1.4 查看master1是否恢复
      • 3.2 模拟从服务器宕机以及恢复
        • 3.2.1 停止slave1的mysql服务
        • 3.2.2 查看VIP漂移情况
        • 3.2.3 重启slave1的mysql服务
        • 3.2.4 查看slave1是否恢复
      • 3.3 客户端测试
        • 3.3.1 在master1服务器上为monitor服务器地址授权登录
        • 3.3.2 在monitor服务器上使用VIP登录
        • 3.3.3 客户端创建数据，测试同步情况

1. MMM的概述

MMM（Master-Master replication manager for MySQL,MySQL主主复制管理器）是一套支持双主故障切换和双主日常管理的脚本程序。MMM使用Perl语言开发，主要从来监控和管理MySQL Master-Master（双主）复制，虽然叫做双主复制，但是业务上同一时刻只允许一个主进行写入，另一台备选主上提供部分读服务，以加速在主主切换时备选主的预热，可以说MMM这套脚本程序一方面实现了故障切换的功能，另一方面其内部附加的工具也可以实现多个Slave的read负载均衡。

2. MMM的应用场景

MMM提供了自动和手动两种方式移除一组服务器中复制延迟较高的服务器的虚拟ip，同时它还可以备份数据，实现两节点之间的数据同步等。由于MMM无法完全保证数据的一致性，所以MMM适用于对数据的一致性要求不是很高的，但是又想最大程度地保证业务可用性的场景。对于那些对数据的一致性要求很高的业务，非常不建议采用MMM这种高可用架构。

3. MMM的特点

● MMM是一套灵活的脚本程序

● 基于perl语言实现

● 用来对mysql replication进行监控和故障迁移

● 管理MySQL Master-Master复制的配置

4. 关于MMM高可用架构的说明

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

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

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

● mysql-mmm的监管端会提供多个虚拟IP（VIP），包括一个可写VIP，多个可读VIP，通过监管的管理，这些IP会绑定在可用MySQL之上，当某一台MySQL宕机时，监管会将VIP迁移至其他MySQL。

5. 用户及授权

在整个监管过程中，需要在MySQL中添加相关授权yoghurt，以便让MySQL可以支持监理机的维护。授权的用户包括一个mmm_monitor用户和一个mmm_agent用户，如果想使用MMM的备份工具则还需要添加一个mmm_tools用户。

1. 服务器配置

服务器

主机名

操作系统

IP地址

主要软件

Master1服务器

master1

CentOS 7.4

192.168.122.10

MySQL 5.7、MySQL-MMM

Master2服务器

master2

CentOS 7.4

192.168.122.11

MySQL 5.7、MySQL-MMM

Slave1服务器

slave1

CentOS 7.4

192.168.122.100

MySQL 5.7、MySQL-MMM

Slave2服务器

slave2

CentOS 7.4

192.168.122.101

MySQL 5.7、MySQL-MMM

Monitor服务器

monitor

CentOS 7.4

192.168.122.12

MySQL-MMM

2. 服务器环境

systemctl stop firewalld
systemctl disable firewalld
setenforce 0

3. 修改主机名称

Master1服务器（192.168.122.10）

[root@localhost ~]# hostnamectl set-hostname master1

Master2服务器（192.168.122.11）

[root@localhost ~]# hostnamectl set-hostname master2

Slave1服务器（192.168.122.100）

[root@localhost ~]# hostnamectl set-hostname slave1

Slave2服务器（192.168.122.101）

[root@localhost ~]# hostnamectl set-hostname slave2

Monitor服务器（192.168.122.12）

[root@localhost ~]# hostnamectl set-hostname monitor

1. 搭建MySQL多主多从架构

1.1 master1、master2、slave1、slave2节点上安装mysql5.7

刷下列脚本，过程忽略

#!/bin/bash
systemctl stop firewalld
systemctl disable firewalld
setenforce 0

#--------mysql--------

#安装依赖包

yum -y install gcc gcc-c++ ncurses ncurses-devel bison cmake

#配置软件模块

cd /opt/
tar zxvf mysql-5.7.17.tar.gz
tar zxvf boost_1_59_0.tar.gz
mv boost_1_59_0 /usr/local/boost

cd /opt/mysql-5.7.17/
cmake \
-DCMAKE_INSTALL_PREFIX=/usr/local/mysql \
-DMYSQL_UNIX_ADDR=/usr/local/mysql/mysql.sock \
-DSYSCONFDIR=/etc \
-DSYSTEMD_PID_DIR=/usr/local/mysql \
-DDEFAULT_CHARSET=utf8  \
-DDEFAULT_COLLATION=utf8_general_ci \
-DWITH_EXTRA_CHARSETS=all \
-DWITH_INNOBASE_STORAGE_ENGINE=1 \
-DWITH_ARCHIVE_STORAGE_ENGINE=1 \
-DWITH_BLACKHOLE_STORAGE_ENGINE=1 \
-DWITH_PERFSCHEMA_STORAGE_ENGINE=1 \
-DMYSQL_DATADIR=/usr/local/mysql/data \
-DWITH_BOOST=/usr/local/boost \
-DWITH_SYSTEMD=1

#编译安装

make -j 2 && make install

#创建mysql用户

useradd -M -s /sbin/nologin  mysql

#修改mysql 配置文件

echo '[client]
port = 3306
default-character-set=utf8
socket=/usr/local/mysql/mysql.sock

[mysql]
port = 3306
default-character-set=utf8
socket = /usr/local/mysql/mysql.sock
auto-rehash

[mysqld]
user = mysql
basedir=/usr/local/mysql
datadir=/usr/local/mysql/data
port = 3306
character-set-server=utf8
pid-file = /usr/local/mysql/mysqld.pid
socket=/usr/local/mysql/mysql.sock
bind-address = 0.0.0.0
skip-name-resolve
max_connections=2048
default-storage-engine=INNODB
max_allowed_packet=16M
server-id = 1

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_AUTO_VALUE_ON_ZERO,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,PIPES_AS_CONCAT,ANSI_QUOTES' > /etc/my.cnf

#更改mysql安装目录和配置文件的属主属组

chown -R mysql:mysql /usr/local/mysql/
chown mysql:mysql /etc/my.cnf

#设置路径环境变量

echo 'export PATH=/usr/local/mysql/bin:/usr/local/mysql/lib:$PATH' >> /etc/profile
source /etc/profile

#初始化数据库

cd /usr/local/mysql/bin/
./mysqld \
--initialize-insecure \
--user=mysql \
--basedir=/usr/local/mysql \
--datadir=/usr/local/mysql/data

#添加mysqld系统服务

cp /usr/local/mysql/usr/lib/systemd/system/mysqld.service /usr/lib/systemd/system/
systemctl daemon-reload
systemctl start mysqld.service
systemctl enable mysqld

yum -y install expect
mima () {
passwd=$1
/usr/bin/expect <<-EOF
spawn mysqladmin -u root -p password $passwd
expect "Enter password:"
send "\r"
expect eof
EOF
}
mima "123456"

dl () {
/usr/bin/expect <<-EOF
spawn mysql -u root -p
expect "Enter password:" {send "123456\r"}
expect "mysql>" {send "grant all privileges on *.* to 'root'@'%' identified by '123456';\r"}
expect "mysql>" {send "quit\r"}
expect eof
EOF
}
dl

1.2 修改master1配置文件

Master1服务器（192.168.122.10）

[root@master1 ~]# vim /etc/my.cnf

......
[mysqld]
user = mysql
basedir = /usr/local/mysql
datadir = /usr/local/mysql/data
port = 3306
character_set_server=utf8
pid-file = /usr/local/mysql/mysqld.pid
socket = /usr/local/mysql/mysql.sock
server-id = 1
#每台 Mysql 主机的 server-id 不能相同
log-error=/usr/local/mysql/data/mysql_error.log
#错误日志
general_log=ON
#通用查询日志
general_log_file=/usr/local/mysql/data/mysql_general.log
slow_query_log=ON
#慢查询日志
slow_query_log_file=mysql_slow_query.log
long_query_time=5
binlog-ignore-db=mysql,information_schema
#不需要同步的库名
log_bin=mysql_bin
#开启二进制日志用于主从数据复制
log_slave_updates=true
#允许slave从master复制数据时可以写入到自己的二进制日志
sync_binlog=1
#"双1设置"，MySQL 在每写一次二进制日志时都会同步到磁盘中去
innodb_flush_log_at_trx_commit=1
#"双1设置"，每次事务提交时MySQL都会把缓存的数据写入日志文件，并且刷到磁盘中去
auto_increment_increment=2
#自增字段一次递增多少
auto_increment_offset=1
#自增字段的起始值

[root@master1 ~]# systemctl restart mysqld

1.3 把配置文件复制到其他3台数据库服务器并重启mysql服务器

注意：配置文件中的server-id不可相同，需要修改。

[root@master1 ~]# scp /etc/my.cnf root@192.168.122.11:/etc/
[root@master1 ~]# scp /etc/my.cnf root@192.168.122.100:/etc/
[root@master1 ~]# scp /etc/my.cnf root@192.168.122.101:/etc/

1.4 配置主主复制，两台主服务器相互复制

1.4.1 在两台主服务器上都执行授予从的权限，从服务器上不需要执行

Master1服务器（192.168.122.10）

[root@master1 ~]# mysql -u root -p
Enter password: 

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

Master2服务器（192.168.122.11）

[root@master2 ~]# mysql -u root -p
Enter password: 

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

1.4.2 在两台主服务器上查看

Master1服务器（192.168.122.10）

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

Master2服务器（192.168.122.11）

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

1.4.3 在master1上配置同步

Master1服务器（192.168.122.10）

mysql> change master to
    -> master_host='192.168.122.11',
    -> master_user='replication',
    -> master_password='123456',
    -> master_log_file='mysql_bin.000001',
    -> master_log_pos=154;
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
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.122.11
                  Master_User: replication
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql_bin.000001
          Read_Master_Log_Pos: 154
               Relay_Log_File: master1-relay-bin.000002
                Relay_Log_Pos: 320
        Relay_Master_Log_File: mysql_bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 154
              Relay_Log_Space: 529
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 2
                  Master_UUID: 51f017b7-0fe5-11ec-a583-000c299463f1
             Master_Info_File: /usr/local/mysql/data/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind:
      Last_IO_Error_Timestamp:
     Last_SQL_Error_Timestamp:
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set:
            Executed_Gtid_Set:
                Auto_Position: 0
         Replicate_Rewrite_DB:
                 Channel_Name:
           Master_TLS_Version:
1 row in set (0.00 sec)

1.4.4 在master2上配置同步

Master2服务器（192.168.122.11）

mysql> change master to
    -> master_host='192.168.122.10',
    -> master_user='replication',
    -> master_password='123456',
    -> master_log_file='mysql_bin.000001',
    -> master_log_pos=154;
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
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.122.10
                  Master_User: replication
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql_bin.000001
          Read_Master_Log_Pos: 154
               Relay_Log_File: master2-relay-bin.000002
                Relay_Log_Pos: 320
        Relay_Master_Log_File: mysql_bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 154
              Relay_Log_Space: 529
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 1
                  Master_UUID: 7587444a-0fe5-11ec-ac87-000c2959bebe
             Master_Info_File: /usr/local/mysql/data/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind:
      Last_IO_Error_Timestamp:
     Last_SQL_Error_Timestamp:
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set:
            Executed_Gtid_Set:
                Auto_Position: 0
         Replicate_Rewrite_DB:
                 Channel_Name:
           Master_TLS_Version:
1 row in set (0.00 sec)

1.5 配置主从复制，在两台从服务器上做

Slave1服务器（192.168.122.100）

[root@slave1 ~]# mysql -u root -p
Enter password: 

mysql> change master to
    -> master_host='192.168.122.10',
    -> master_user='replication',
    -> 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
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.122.10
                  Master_User: replication
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql_bin.000001
          Read_Master_Log_Pos: 154
               Relay_Log_File: slave1-relay-bin.000002
                Relay_Log_Pos: 320
        Relay_Master_Log_File: mysql_bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 154
              Relay_Log_Space: 528
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 1
                  Master_UUID: 7587444a-0fe5-11ec-ac87-000c2959bebe
             Master_Info_File: /usr/local/mysql/data/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind:
      Last_IO_Error_Timestamp:
     Last_SQL_Error_Timestamp:
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set:
            Executed_Gtid_Set:
                Auto_Position: 0
         Replicate_Rewrite_DB:
                 Channel_Name:
           Master_TLS_Version:
1 row in set (0.00 sec)

Slave2服务器（192.168.122.101）

[root@slave2 ~]# mysql -u root -p
Enter password: 

mysql> change master to
    -> master_host='192.168.122.10',
    -> master_user='replication',
    -> 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
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.122.10
                  Master_User: replication
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql_bin.000001
          Read_Master_Log_Pos: 154
               Relay_Log_File: slave2-relay-bin.000002
                Relay_Log_Pos: 320
        Relay_Master_Log_File: mysql_bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 154
              Relay_Log_Space: 528
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 1
                  Master_UUID: 7587444a-0fe5-11ec-ac87-000c2959bebe
             Master_Info_File: /usr/local/mysql/data/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind:
      Last_IO_Error_Timestamp:
     Last_SQL_Error_Timestamp:
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set:
            Executed_Gtid_Set:
                Auto_Position: 0
         Replicate_Rewrite_DB:
                 Channel_Name:
           Master_TLS_Version:
1 row in set (0.00 sec)

1.6 测试主主、主从同步情况

Master1服务器（192.168.122.10）

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

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

Master2服务器（192.168.122.11）

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

Slave1服务器（192.168.122.100）

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

Slave2服务器（192.168.122.101）

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

2. 安装配置MySQL-MMM

2.1 在所有服务器上安装MySQL-MMM

Master1服务器（192.168.122.10）

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

Master2服务器（192.168.122.11）

[root@master2 ~]# yum -y install epel-release
[root@master2 ~]# yum -y install mysql-mmm*

Slave1服务器（192.168.122.100）

[root@slave1 ~]# yum -y install epel-release
[root@slave1 ~]# yum -y install mysql-mmm*

Slave2服务器（192.168.122.101）

[root@slave2 ~]# yum -y install epel-release
[root@slave2 ~]# yum -y install mysql-mmm*

Monitor服务器（192.168.122.12）

[root@monitor ~]# yum -y install epel-release
[root@monitor ~]# yum -y install mysql-mmm*

注：若本地仓库中无以上软件，需先为各服务器配置在线源仓库。

2.2 阿里云源仓库配置方法

wget -O /etc/yum.repos.d/CentOS-Base.repo https://mirrors.aliyun.com/repo/Centos-7.repo
#将阿里云镜像下载到/etc/yum.repos.d/目录下
yum clean all && yum makecache
#清理缓存并且生成新的缓存

2.3 在master1上对MySQL-MMM进行配置

Master1服务器（192.168.122.10）

[root@master1 ~]# cd /etc/mysql-mmm/
[root@master1 mysql-mmm]# cp mmm_common.conf mmm_common.conf.bak
#修改配置文件前，先备份
[root@master1 mysql-mmm]# vim mmm_common.conf

active_master_role      writer

<host default>
    cluster_interface       ens33
    pid_path                /run/mysql-mmm-agent.pid
    bin_path                /usr/libexec/mysql-mmm/
    replication_user        replication
##指定主主、主从复制用户，要与前面一致
    replication_password    123456
    agent_user              mmm_agent
##指定monitor代理进程的用户名
    agent_password          123456
</host>

<host db1>
    ip      192.168.122.10
    mode    master
    peer    db2
##peer设置同级数据库
</host>

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

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

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

<role writer>
    hosts   db1, db2
    ips     192.168.122.200
##设定写VIP
    mode    exclusive
#只有一个 host 可以进行写操作模式
</role>

<role reader>
    hosts   db3, db4
    ips     192.168.122.201, 192.168.122.202
##设定读VIP
    mode    balanced
##多个 slave 主机可以进行读操作模式
</role>

2.4 把配置文件复制到其他4台主机

所有主机该配置文件内容都是相同的

Master1服务器（192.168.122.10）

[root@master1 mysql-mmm]# scp mmm_common.conf root@192.168.122.11:/etc/mysql-mmm/
[root@master1 mysql-mmm]# scp mmm_common.conf root@192.168.122.100:/etc/mysql-mmm/
[root@master1 mysql-mmm]# scp mmm_common.conf root@192.168.122.101:/etc/mysql-mmm/
[root@master1 mysql-mmm]# scp mmm_common.conf root@192.168.122.12:/etc/mysql-mmm/

注：因为scp将直接覆盖原配置文件，所以建议先将配置文件做好备份

2.5 修改所有数据库服务器的代理配置文件mmm_agent.conf

Master1服务器（192.168.122.10）

[root@master1 ~]# vim /etc/mysql-mmm/mmm_agent.conf

include mmm_common.conf
this db1
##根据不同的主机分别修改为db1/db2/db3/db4，默认配置为db1，因此master1无需修改

Master2服务器（192.168.122.11）

[root@master2 ~]# vim /etc/mysql-mmm/mmm_agent.conf

include mmm_common.conf
this db2

Slave1服务器（192.168.122.100）

[root@slave ~]# vim /etc/mysql-mmm/mmm_agent.conf

include mmm_common.conf
this db3

Slave2服务器（192.168.122.101）

[root@slave2 ~]# vim /etc/mysql-mmm/mmm_agent.conf

include mmm_common.conf
this db4

2.6 在monitor监控服务器上修改监控配置文件mmm_mon.conf

Monitor服务器（192.168.122.12）

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

include mmm_common.conf

<monitor>
    ip                  127.0.0.1
    pid_path            /run/mysql-mmm-monitor.pid
    bin_path            /usr/libexec/mysql-mmm
    status_path         /var/lib/mysql-mmm/mmm_mond.status
    ping_ips            192.168.122.10,192.168.122.11,192.168.122.100,192.168.122.101
##指定所有数据库服务器的IP
    auto_set_online     10
##指定自动上线时间

    # The kill_host_bin does not exist by default, though the monitor will
    # throw a warning about it missing.  See the section 5.10 "Kill Host
    # Functionality" in the PDF documentation.
    #
    # kill_host_bin     /usr/libexec/mysql-mmm/monitor/kill_host
    #
</monitor>

<host default>
    monitor_user        mmm_monitor
##指定mmm_monitor的用户名
    monitor_password    123456
##指定mmm_monitor的密码
</host>

debug 0

2.7 在所有数据库上为 mmm_agent（代理进程）授权

Master1服务器（192.168.122.10）

[root@master1 ~]# mysql -u root -p
Enter password: 

mysql> grant super, replication client, process on *.* to 'mmm_agent'@'192.168.80.%' identified by '123456';
Query OK, 0 rows affected, 1 warning (0.01 sec)

Master2服务器（192.168.122.11）

[root@master2 ~]# mysql -u root -p
Enter password: 

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

Slave1服务器（192.168.122.100）

[root@slave1 ~]# mysql -u root -p
Enter password: 

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

Slave2服务器（192.168.122.101）

[root@slave2 ~]# mysql -u root -p
Enter password: 

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

注：

权限

功能

process

显示或杀死属于其他用户的服务线程

super

允许用户终止任何查询；修改全局变量的SET语句；使用CHANGE MASTER，PURGE MASTER LOGS

replication client

查询主服务器、从服务器状态

2.8 在所有数据库上为mmm_monitor（监控进程）授权

Master1服务器（192.168.122.10）

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

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

Master2服务器（192.168.122.11）

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

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

Slave1服务器（192.168.122.100）

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

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

Slave2服务器（192.168.122.101）

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

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

2.8 在所有数据库服务器上启动mysql-mmm-agent

Master1服务器（192.168.122.10）

[root@master1 ~]# systemctl start mysql-mmm-agent.service
[root@master1 ~]# systemctl enable mysql-mmm-agent.service
Created symlink from /etc/systemd/system/multi-user.target.wants/mysql-mmm-agent.service to /usr/lib/systemd/system/mysql-mmm-agent.service.

Master2服务器（192.168.122.11）

[root@master2 ~]# systemctl start mysql-mmm-agent.service
[root@master2 ~]# systemctl enable mysql-mmm-agent.service
Created symlink from /etc/systemd/system/multi-user.target.wants/mysql-mmm-agent.service to /usr/lib/systemd/system/mysql-mmm-agent.service.

Slave1服务器（192.168.122.100）

[root@slave1 ~]# systemctl start mysql-mmm-agent.service
[root@slave1 ~]# systemctl enable mysql-mmm-agent.service
Created symlink from /etc/systemd/system/multi-user.target.wants/mysql-mmm-agent.service to /usr/lib/systemd/system/mysql-mmm-agent.service.

Slave2服务器（192.168.122.101）

[root@slave2 ~]# systemctl start mysql-mmm-agent.service
[root@slave2 ~]# systemctl enable mysql-mmm-agent.service
Created symlink from /etc/systemd/system/multi-user.target.wants/mysql-mmm-agent.service to /usr/lib/systemd/system/mysql-mmm-agent.service.

2.9 在monitor服务器上启动mysql-mmm-monitor

Monitor服务器（192.168.122.12）

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

2.10 在monitor服务器上测试群集

2.10.1 查看各节点的情况

Monitor服务器（192.168.122.12）

[root@monitor ~]# mmm_control show
  db1(192.168.122.10) master/ONLINE. Roles: writer(192.168.122.200)
  db2(192.168.122.11) master/ONLINE. Roles:
  db3(192.168.122.100) slave/ONLINE. Roles: reader(192.168.122.202)
  db4(192.168.122.101) slave/ONLINE. Roles: reader(192.168.122.201)

2.10.2 检测监控功能是否完善

Monitor服务器（192.168.122.12）

[root@monitor ~]# mmm_control checks all
db4  ping         [last change: 2021/09/08 02:05:52]  OK
db4  mysql        [last change: 2021/09/08 02:10:38]  OK
db4  rep_threads  [last change: 2021/09/08 02:25:20]  OK
db4  rep_backlog  [last change: 2021/09/08 02:10:38]  OK: Backlog is null
db2  ping         [last change: 2021/09/08 02:05:52]  OK
db2  mysql        [last change: 2021/09/08 02:10:38]  OK
db2  rep_threads  [last change: 2021/09/08 02:25:20]  OK
db2  rep_backlog  [last change: 2021/09/08 02:10:38]  OK: Backlog is null
db3  ping         [last change: 2021/09/08 02:05:52]  OK
db3  mysql        [last change: 2021/09/08 02:10:38]  OK
db3  rep_threads  [last change: 2021/09/08 02:25:20]  OK
db3  rep_backlog  [last change: 2021/09/08 02:10:38]  OK: Backlog is null
db1  ping         [last change: 2021/09/08 02:05:52]  OK
db1  mysql        [last change: 2021/09/08 02:10:38]  OK
db1  rep_threads  [last change: 2021/09/08 02:25:20]  OK
db1  rep_backlog  [last change: 2021/09/08 02:10:38]  OK: Backlog is null

2.10.3 指定绑定VIP的主机

Monitor服务器（192.168.122.12）

[root@monitor ~]# 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@monitor ~]# mmm_control show
  db1(192.168.122.10) master/ONLINE. Roles:
  db2(192.168.122.11) master/ONLINE. Roles: writer(192.168.122.200)
  db3(192.168.122.100) slave/ONLINE. Roles: reader(192.168.122.202)
  db4(192.168.122.101) slave/ONLINE. Roles: reader(192.168.122.201)

[root@monitor ~]# mmm_control move_role writer db1
OK: Role 'writer' has been moved from 'db2' to 'db1'. Now you can wait some time and check new roles info!
[root@monitor ~]# mmm_control show
  db1(192.168.122.10) master/ONLINE. Roles: writer(192.168.122.200)
  db2(192.168.122.11) master/ONLINE. Roles:
  db3(192.168.122.100) slave/ONLINE. Roles: reader(192.168.122.202)
  db4(192.168.122.101) slave/ONLINE. Roles: reader(192.168.122.201)

3. 故障测试

3.1 模拟master宕机以及恢复

3.1.1 停止master1的mysql服务

Master1服务器（192.168.122.10）

[root@master1 ~]# systemctl stop mysqld

3.1.2 查看VIP漂移情况

Monitor服务器（192.168.122.12）

[root@monitor ~]# mmm_control show
  db1(192.168.122.10) master/HARD_OFFLINE. Roles:
  db2(192.168.122.11) master/ONLINE. Roles: writer(192.168.122.200)
  db3(192.168.122.100) slave/ONLINE. Roles: reader(192.168.122.202)
  db4(192.168.122.101) slave/ONLINE. Roles: reader(192.168.122.201)

VIP成功漂移至master2，且master1显示HARD_OFFLINE

3.1.3 重启master1的mysql服务

Master1服务器（192.168.122.10）

[root@master1 ~]# systemctl start mysqld

3.1.4 查看master1是否恢复

Monitor服务器（192.168.122.12）

[root@monitor ~]# mmm_control show
  db1(192.168.122.10) master/AWAITING_RECOVERY. Roles:
  db2(192.168.122.11) master/ONLINE. Roles: writer(192.168.122.200)
  db3(192.168.122.100) slave/ONLINE. Roles: reader(192.168.122.202)
  db4(192.168.122.101) slave/ONLINE. Roles: reader(192.168.122.201)

显示等待恢复，等待数秒后重新查看，发现Master1在线，但未能获得VIP

[root@monitor ~]# mmm_control show
  db1(192.168.122.10) master/ONLINE. Roles:
  db2(192.168.122.11) master/ONLINE. Roles: writer(192.168.122.200)
  db3(192.168.122.100) slave/ONLINE. Roles: reader(192.168.122.202)
  db4(192.168.122.101) slave/ONLINE. Roles: reader(192.168.122.201)

3.2 模拟从服务器宕机以及恢复

3.2.1 停止slave1的mysql服务

Slave1服务器（192.168.122.100）

[root@slave1 ~]# systemctl stop mysqld

3.2.2 查看VIP漂移情况

Monitor服务器（192.168.122.12）

[root@monitor ~]# mmm_control show
  db1(192.168.122.10) master/ONLINE. Roles:
  db2(192.168.122.11) master/ONLINE. Roles: writer(192.168.122.200)
  db3(192.168.122.100) slave/HARD_OFFLINE. Roles:
  db4(192.168.122.101) slave/ONLINE. Roles: reader(192.168.122.201), reader(192.168.122.202)

slave1所对应的的VIP对slave2成功接管

3.2.3 重启slave1的mysql服务

Slave1服务器（192.168.122.100）

[root@slave1 ~]# systemctl start mysqld

3.2.4 查看slave1是否恢复

[root@monitor ~]# mmm_control show
  db1(192.168.122.10) master/ONLINE. Roles:
  db2(192.168.122.11) master/ONLINE. Roles: writer(192.168.122.200)
  db3(192.168.122.100) slave/AWAITING_RECOVERY. Roles:
  db4(192.168.122.101) slave/ONLINE. Roles: reader(192.168.122.201), reader(192.168.122.202)

[root@monitor ~]# mmm_control show
  db1(192.168.122.10) master/ONLINE. Roles:
  db2(192.168.122.11) master/ONLINE. Roles: writer(192.168.122.200)
  db3(192.168.122.100) slave/ONLINE. Roles:
  db4(192.168.122.101) slave/ONLINE. Roles: reader(192.168.122.201), reader(192.168.122.202)

[root@monitor ~]# mmm_control show
  db1(192.168.122.10) master/ONLINE. Roles:
  db2(192.168.122.11) master/ONLINE. Roles: writer(192.168.122.200)
  db3(192.168.122.100) slave/ONLINE. Roles: reader(192.168.122.202)
  db4(192.168.122.101) slave/ONLINE. Roles: reader(192.168.122.201)

在短暂的交接后，slave1重新获取到VIP，继续工作

3.3 客户端测试

3.3.1 在master1服务器上为monitor服务器地址授权登录

Master1服务器（192.168.122.10）

[root@master1 ~]# mysql -u root -p
Enter password: 

mysql> grant all on *.* to 'test'@'192.168.122.12' identified by '123456';
Query OK, 0 rows affected, 1 warning (0.00 sec)

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

3.3.2 在monitor服务器上使用VIP登录

Monitor服务器（192.168.122.12）

[root@monitor ~]# yum install -y mariadb-server mariadb
[root@monitor ~]# systemctl start mariadb.service
[root@monitor ~]# mysql -u test -p -h 192.168.122.200
Enter password:
##登录成功
MySQL [(none)]>

3.3.3 客户端创建数据，测试同步情况

Monitor服务器（192.168.122.12）

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

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

Master1服务器（192.168.122.10）

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| client_test        |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+
6 rows in set (0.00 sec)

Master2服务器（192.168.122.11）

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| client_test        |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+
6 rows in set (0.00 sec)

Slave1服务器（192.168.122.100）

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| client_test        |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+
6 rows in set (0.00 sec)

Slave2服务器（192.168.122.101）

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| client_test        |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+
6 rows in set (0.00 sec)