Tel：028-86922220 我有需求

MySQL数据库——MMM高可用群集配置（实战！）

MMM简介

MMM（Master-Master replication manager for MySQL）是一套支持双主故障切换和双主日常管理的脚本程序。MMM使用Perl语言开发，主要用来监控和管理MySQL Master-Master（双主）复制，可以说是mysql主主复制管理器。虽然叫做双主复制，但是业务上同一时刻只允许对一个主进行写入，另一台备选主上提供部分读服务，以加速在主主切换时刻备选主的预热，可以说MMM这套脚本程序一方面实现了故障切换的功能，另一方面其内部附加的工具脚本也可以实现多个slave的read负载均衡。关于mysql主主复制配置的监控、故障转移和管理的一套可伸缩的脚本套件（在任何时候只有一个节点可以被写入），这个套件也能对居于标准的主从配置的任意数量的从服务器进行读负载均衡，所以你可以用它来在一组居于复制的服务器启动虚拟ip，除此之外，它还有实现数据备份、节点之间重新同步功能的脚本。

在霍山等地区，都构建了全面的区域性战略布局，加强发展的系统性、市场前瞻性、产品创新能力，以专注、极致的服务理念，为客户提供成都做网站、成都网站制作 网站设计制作按需定制开发,公司网站建设,企业网站建设,品牌网站制作,成都全网营销,成都外贸网站建设公司,霍山网站建设费用合理。

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

实验环境

主服务器1：192.168.52.135   db1     writer VIP：192.168.52.200
主服务器2：192.168.52.133   db2

从服务器1：192.168.52.134   db3     reader VIP：192.168.52.210,192.168.52.220
从服务器2：192.168.52.148   db4

监控服务器：192.168.52.150

实验操作

一、分别在四台MySQL服务器上安装mariadb服务

1、配置本地yum源（四台服务器上分别进行相同操作）

[root@localhost ~]# wget -O /etc/yum.repos.d/CentOS-Base.repo http://mirrors.aliyun.com/repo/Centos-7.repo
#配置aliyun源
--2019-11-25 09:51:39--  http://mirrors.aliyun.com/repo/Centos-7.repo
正在解析主机 mirrors.aliyun.com (mirrors.aliyun.com)... 124.14.2.219, 124.14.2.218, 124.14.2.234, ...
正在连接 mirrors.aliyun.com (mirrors.aliyun.com)|124.14.2.219|:80... 已连接。
已发出 HTTP 请求，正在等待回应... 200 OK
长度：2523 (2.5K) [application/octet-stream]
正在保存至: “/etc/yum.repos.d/CentOS-Base.repo”

100%[====================================================================================>] 2,523       --.-K/s 用时 0s      

2019-11-25 09:51:39 (225 MB/s) - 已保存 “/etc/yum.repos.d/CentOS-Base.repo” [2523/2523])

[root@localhost ~]# yum -y install epel-release   #安装epel-release源
.......................//省略过程
[root@localhost ~]# yum clean all && yum makecache   #清除缓存旧的缓存，同步现在服务器的缓存
........................//省略过程

2、在主服务器1上安装mariadb（MySQL服务的一种）服务

[root@localhost ~]# hostnamectl set-hostname db1    #修改主机名
[root@localhost ~]# su   #切换bash环境
[root@db1 ~]#
[root@db1 ~]# yum install mariadb-server mariadb -y   #安装服务
....................//省略过程
[root@db1 ~]# vim /etc/my.cnf   #修改配置文件
[mysqld]
log_error=/var/lib/mysql/mysql.err
log=/var/lib/mysql/mysql_log.log
log_slow_queries=/var/lib/mysql_slow_queris.log
binlog-ignore-db=mysql,information_schema
character_set_server=utf8
log_bin=mysql_bin
server_id=1
log_slave_updates=true
sync_binlog=1
auto_increment_increment=2
auto_increment_offset=1
[root@db1 ~]# systemctl stop firewalld.service    #关闭防火墙
[root@db1 ~]# setenforce 0   #关闭增强性安全功能
[root@db1 ~]# systemctl start mariadb.service    #开启服务
[root@db1 ~]# netstat -ntap | grep 3306   #查看端口
tcp        0      0 0.0.0.0:3306            0.0.0.0:*               LISTEN      4601/mysqld         
[root@db1 ~]#

3、在主服务器2上安装mariadb服务

[root@localhost ~]# hostnamectl set-hostname db2
[root@localhost ~]# su
[root@db2 ~]#
[root@db2 ~]# yum install mariadb-server mariadb -y
[root@db2 ~]# vim /etc/my.cnf
[mysqld]
log_error=/var/lib/mysql/mysql.err
log=/var/lib/mysql/mysql_log.log
log_slow_queries=/var/lib/mysql_slow_queris.log
binlog-ignore-db=mysql,information_schema
character_set_server=utf8
log_bin=mysql_bin
server_id=2
log_slave_updates=true
sync_binlog=1
auto_increment_increment=2
auto_increment_offset=1
[root@db2 ~]# systemctl stop firewalld.service 
[root@db2 ~]# setenforce 0
[root@db2 ~]# systemctl start mariadb.service 
[root@db2 ~]# netstat -ntap | grep 3306
tcp        0      0 0.0.0.0:3306            0.0.0.0:*               LISTEN      50643/mysqld        
[root@db2 ~]#

4、在从服务器1上安装mariadb服务

[root@localhost ~]# hostnamectl set-hostname db3
[root@localhost ~]# su
[root@db3 ~]#
[root@db3 ~]# yum install mariadb-server mariadb -y

[root@db3 ~]# vim /etc/my.cnf
[mysqld]
log_error=/var/lib/mysql/mysql.err
log=/var/lib/mysql/mysql_log.log
log_slow_queries=/var/lib/mysql_slow_queris.log
binlog-ignore-db=mysql,information_schema
character_set_server=utf8
log_bin=mysql_bin
server_id=11
log_slave_updates=true
sync_binlog=1
auto_increment_increment=2
auto_increment_offset=1
[root@db3 ~]# systemctl stop firewalld.service 
[root@db3 ~]# setenforce 0
[root@db3 ~]# systemctl start mariadb.service 
[root@db3 ~]# netstat -ntap | grep 3306
tcp        0      0 0.0.0.0:3306            0.0.0.0:*               LISTEN      50578/mysqld        
[root@db3 ~]#

5、在从服务器2上安装mariadb服务

[root@localhost ~]# hostnamectl set-hostname db4
[root@localhost ~]# su
[root@db4 ~]#
[root@db4 ~]# yum install mariadb-server mariadb -y

[root@db4 ~]# vim /etc/my.cnf
[mysqld]
log_error=/var/lib/mysql/mysql.err
log=/var/lib/mysql/mysql_log.log
log_slow_queries=/var/lib/mysql_slow_queris.log
binlog-ignore-db=mysql,information_schema
character_set_server=utf8
log_bin=mysql_bin
server_id=22
log_slave_updates=true
sync_binlog=1
auto_increment_increment=2
auto_increment_offset=1
[root@db4 ~]# systemctl stop firewalld.service 
[root@db4 ~]# setenforce 0
[root@db4 ~]# systemctl start mariadb.service 
[root@db4 ~]# netstat -ntap | grep 3306
tcp        0      0 0.0.0.0:3306            0.0.0.0:*               LISTEN      39804/mysqld        
[root@db4 ~]#

二、配置MySQL多主多从模式

1、分别查看主服务器1和主服务器2的log_bin日志和pos值的位置

主服务器1：
[root@db1 ~]# mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 2
Server version: 5.5.64-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> show master status;   #查看状态
+------------------+----------+--------------+--------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB         |
+------------------+----------+--------------+--------------------------+
| mysql_bin.000003 |      245 |              | mysql,information_schema |
+------------------+----------+--------------+--------------------------+
1 row in set (0.00 sec)
主服务器2：
[root@db2 ~]# mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 2
Server version: 5.5.64-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> show master status;   #查看状态
+------------------+----------+--------------+--------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB         |
+------------------+----------+--------------+--------------------------+
| mysql_bin.000003 |      245 |              | mysql,information_schema |
+------------------+----------+--------------+--------------------------+
1 row in set (0.00 sec)

2、主服务器1与主服务器2互相提升访问权限

主服务器1：
MariaDB [(none)]> grant replication slave on *.* to 'replication'@'192.168.52.%' identified by '123456'; 
#提权
Query OK, 0 rows affected (0.01 sec)

主服务器2：
MariaDB [(none)]> grant replication slave on *.* to 'replication'@'192.168.52.%' identified by '123456';
#提权
Query OK, 0 rows affected (0.01 sec)
主服务器1：
MariaDB [(none)]> change master to master_host='192.168.52.133',master_user='replication',master_password='123456',master_log_file='mysql_bin.000003',master_log_pos=245;
#指定同步服务器的具体信息
Query OK, 0 rows affected (0.01 sec)

MariaDB [(none)]> start slave;   #开启同步
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> show slave status\G;   #查看状态
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.52.133
                  Master_User: replication
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql_bin.000003
          Read_Master_Log_Pos: 575
               Relay_Log_File: mariadb-relay-bin.000002
                Relay_Log_Pos: 694
        Relay_Master_Log_File: mysql_bin.000003
             Slave_IO_Running: Yes   #确认开启
            Slave_SQL_Running: Yes   #确认开启

             Master_Server_Id: 2
1 row in set (0.00 sec)

ERROR: No query specified
主服务器2：
MariaDB [(none)]> change master to master_host='192.168.52.135',master_user='replication',master_password='123456',master_log_file='mysql_bin.000003',master_log_pos=245;
#指定同步服务器的具体信息
Query OK, 0 rows affected (0.02 sec)

MariaDB [(none)]> start slave;   #开启同步
Query OK, 0 rows affected (0.01 sec)

MariaDB [(none)]> show slave status\G;   #查看状态
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.52.135
                  Master_User: replication
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql_bin.000003
          Read_Master_Log_Pos: 575
               Relay_Log_File: mariadb-relay-bin.000002
                Relay_Log_Pos: 694
        Relay_Master_Log_File: mysql_bin.000003
             Slave_IO_Running: Yes   #确认开启
            Slave_SQL_Running: Yes   #确认开启

             Master_Server_Id: 1
1 row in set (0.00 sec)

ERROR: No query specified

3、测试主主同步

主服务器1：
MariaDB [(none)]> create database school;   #创建数据库school
Query OK, 1 row affected (0.01 sec)

MariaDB [(none)]> show databases;   #查看数据库
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| school             |   #创建成功
| test               |
+--------------------+
5 rows in set (0.00 sec)
主服务器2：
MariaDB [(none)]> show databases;   #查看数据库
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| school             |    #同步成功
| test               |
+--------------------+
5 rows in set (0.00 sec)
MariaDB [(none)]> drop database school;   #删除数据库school
Query OK, 0 rows affected (0.01 sec)

MariaDB [(none)]> show databases;   #查看数据库（删除成功）
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+
4 rows in set (0.00 sec)

MariaDB [(none)]>
主服务器1：
MariaDB [(none)]> show databases;   #查看数据库（同步成功）
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+
4 rows in set (0.00 sec)

MariaDB [(none)]>

4、配置从服务器1、2作为主服务器1的从库

从服务器1：
[root@db3 ~]# mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 2
Server version: 5.5.64-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> change master to master_host='192.168.52.135',master_user='replication',master_password='123456',master_log_file='mysql_bin.000003',master_log_pos=245;
Query OK, 0 rows affected (0.01 sec)

MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.01 sec)

MariaDB [(none)]>
从服务器2：
[root@db4 ~]# mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 2
Server version: 5.5.64-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> change master to master_host='192.168.52.135',master_user='replication',master_password='123456',master_log_file='mysql_bin.000003',master_log_pos=245;
Query OK, 0 rows affected (0.02 sec)

MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]>

5、测试多主多从模式

主服务器1：
MariaDB [(none)]> create database myschool;   #创建数据库myschool
Query OK, 1 row affected (0.01 sec)

MariaDB [(none)]> show databases;   #查看数据库
+--------------------+
| Database           |
+--------------------+
| information_schema |
| myschool           |        #创建成功
| mysql              |
| performance_schema |
| test               |
+--------------------+
5 rows in set (0.00 sec)

MariaDB [(none)]> quit
Bye
[root@db1 ~]#
主服务器2：
MariaDB [(none)]> show databases;   #查看数据库
+--------------------+
| Database           |
+--------------------+
| information_schema |
| myschool           |          #同步成功
| mysql              |
| performance_schema |
| test               |
+--------------------+
5 rows in set (0.00 sec)

MariaDB [(none)]> quit
Bye
[root@db2 ~]#
从服务器1：
MariaDB [(none)]> show databases;   #查看数据库
+--------------------+
| Database           |
+--------------------+
| information_schema |
| myschool           |         #同步成功
| mysql              |
| performance_schema |
| test               |
+--------------------+
5 rows in set (0.00 sec)

MariaDB [(none)]> quit
Bye
[root@db3 ~]#
从服务器2：
MariaDB [(none)]> show databases;   #查看数据库
+--------------------+
| Database           |
+--------------------+
| information_schema |
| myschool           |         #同步成功
| mysql              |
| performance_schema |
| test               |
+--------------------+
5 rows in set (0.00 sec)

MariaDB [(none)]> quit
Bye
[root@db4 ~]#

三、安装配置MySQL-MMM

1、四台MySQL服务器分别安装MMM


[root@db1 ~]# yum -y install mysql-mmm*
........//省略过程
[root@db1 ~]#

[root@db2 ~]# yum -y install mysql-mmm*
.......//省略过程
[root@db2 ~]# 

[root@db3 ~]# yum -y install mysql-mmm*
........//省略过程
[root@db3 ~]# 

[root@db4 ~]# yum -y install mysql-mmm*
.........//省略过程
[root@db4 ~]#

2、修改/etc/mysql-mmm/mmm_common.conf 配置文件

[root@db1 ~]# vim /etc/mysql-mmm/mmm_common.conf 

    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
    agent_password          123456



    ip      192.168.52.135
    mode    master
    peer    db2



    ip      192.168.52.133
    mode    master
    peer    db1



    ip      192.168.52.134
    mode    slave



    ip      192.168.52.148
    mode    slave



    hosts   db1, db2
    ips     192.168.52.200
    mode    exclusive



    hosts   db3, db4
    ips     192.168.52.210, 192.168.52.220
    mode    balanced

3、将/etc/mysql-mmm/mmm_common.conf配置文件，推送到其它三个服务器

[root@db1 ~]# scp /etc/mysql-mmm/mmm_common.conf root@192.168.52.133:/etc/mysql-mmm/
root@192.168.52.133's password: 
mmm_common.conf                                                                             100%  837     1.1MB/s   00:00    
[root@db1 ~]# scp /etc/mysql-mmm/mmm_common.conf root@192.168.52.134:/etc/mysql-mmm/
root@192.168.52.134's password: 
mmm_common.conf                                                                             100%  837     1.1MB/s   00:00    
[root@db1 ~]# scp /etc/mysql-mmm/mmm_common.conf root@192.168.52.148:/etc/mysql-mmm/
root@192.168.52.148's password: 
mmm_common.conf                                                                             100%  837   951.0KB/s   00:00

4、在监控服务器monitor上配置本地yum源

[root@localhost ~]# hostnamectl set-hostname monitor
[root@localhost ~]# su
[root@monitor ~]# wget -O /etc/yum.repos.d/CentOS-Base.repo http://mirrors.aliyun.com/repo/Centos-7.repo
--2019-11-25 11:40:39--  http://mirrors.aliyun.com/repo/Centos-7.repo
正在解析主机 mirrors.aliyun.com (mirrors.aliyun.com)... 124.14.2.222, 124.14.2.218, 124.14.2.234, ...
正在连接 mirrors.aliyun.com (mirrors.aliyun.com)|124.14.2.222|:80... 已连接。
已发出 HTTP 请求，正在等待回应... 200 OK
长度：2523 (2.5K) [application/octet-stream]
正在保存至: “/etc/yum.repos.d/CentOS-Base.repo”

100%[====================================================================================>] 2,523       --.-K/s 用时 0s      

2019-11-25 11:40:39 (663 MB/s) - 已保存 “/etc/yum.repos.d/CentOS-Base.repo” [2523/2523])

[root@monitor ~]#
[root@monitor ~]# yum -y install epel-release
..........//省略过程
[root@monitor ~]# yum clean all && yum makecache
.........//省略过程
[root@monitor ~]# yum -y install mysql-mmm*
.........//省略过程

5、在主服务器1，将配置文件/etc/mysql-mmm/mmm_common.conf推送到monitor

[root@db1 ~]# scp /etc/mysql-mmm/mmm_common.conf root@192.168.52.150:/etc/mysql-mmm/
root@192.168.52.150's password: 
mmm_common.conf                                                                             100%  837     1.4MB/s   00:00    
[root@db1 ~]#

6、修改配置文件/etc/mysql-mmm/mmm_mon.conf

[root@monitor ~]# vim /etc/mysql-mmm/mmm_mon.conf 
    ping_ips            192.168.52.135,192.168.52.133,192.168.52.134,192.168.52.148   #监控服务器IP
    auto_set_online     10

    monitor_user        mmm_monitor   #用户名
    monitor_password    123456    #密码

7、分别在四台MySQL服务器授权monitor访问

主服务器1：
[root@db1 ~]# mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 8
Server version: 5.5.64-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> grant super, replication client, process on *.* to 'mmm_agent'@'192.168.52.%' identified by '123456'; 
Query OK, 0 rows affected (0.01 sec)

MariaDB [(none)]> grant replication client on *.* to 'mmm_monitor'@'192.168.52.%' identified by '123456';
Query OK, 0 rows affected (0.01 sec)

MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.01 sec)

MariaDB [(none)]> quit
Bye
[root@db1 ~]#
主服务器2：
[root@db2 ~]# mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 6
Server version: 5.5.64-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> grant super, replication client, process on *.* to 'mmm_agent'@'192.168.52.%' identified by '123456';
Query OK, 0 rows affected (0.01 sec)

MariaDB [(none)]> grant replication client on *.* to 'mmm_monitor'@'192.168.52.%' identified by '123456';
Query OK, 0 rows affected (0.01 sec)

MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.01 sec)

MariaDB [(none)]> quit
Bye
[root@db2 ~]#
从服务器1：
[root@db3 ~]# mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 5
Server version: 5.5.64-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> grant super, replication client, process on *.* to 'mmm_agent'@'192.168.52.%' identified by '123456';
Query OK, 0 rows affected (0.01 sec)

MariaDB [(none)]> grant replication client on *.* to 'mmm_monitor'@'192.168.52.%' identified by '123456';
Query OK, 0 rows affected (0.01 sec)

MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.01 sec)

MariaDB [(none)]> quit
Bye
[root@db3 ~]#
从服务器2：
[root@db4 ~]# mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 5
Server version: 5.5.64-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> grant super, replication client, process on *.* to 'mmm_agent'@'192.168.52.%' identified by '123456';
Query OK, 0 rows affected (0.01 sec)

MariaDB [(none)]> grant replication client on *.* to 'mmm_monitor'@'192.168.52.%' identified by '123456';
Query OK, 0 rows affected (0.02 sec)

MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> quit
Bye
[root@db4 ~]#

8、分别修改MySQL服务器的/etc/mysql-mmm/mmm_agent.conf文件

主服务器1：
[root@db1 ~]# vim /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   #根据不同主机修改
[root@db1 ~]# systemctl start mysql-mmm-agent.service    #开启服务
[root@db1 ~]# 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.
[root@db1 ~]# 
主服务器2：
[root@db2 ~]# vim /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 db2   #根据不同主机修改
[root@db2 ~]# systemctl start mysql-mmm-agent.service    #开启服务
[root@db2 ~]# 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.
[root@db2 ~]#
从服务器1：
[root@db3 ~]# vim /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 db3   #根据不同主机修改
[root@db3 ~]# systemctl start mysql-mmm-agent.service    #开启服务
[root@db3 ~]# 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.
[root@db3 ~]#
从服务器2：
[root@db4 ~]# vim /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 db4   #根据不同主机修改
[root@db4 ~]# systemctl start mysql-mmm-agent.service    #开启服务
[root@db4 ~]# 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.
[root@db4 ~]#

四、测试MMM高可用

1、开启mysql-mmm-monitor.service

[root@monitor ~]# systemctl stop firewalld.service    #关闭防火墙
[root@monitor ~]# setenforce 0   #关闭增强性安全功能
[root@monitor ~]# systemctl start mysql-mmm-monitor.service    #开启服务
[root@monitor ~]# mmm_control show   #测试群集
  db1(192.168.52.135) master/ONLINE. Roles: writer(192.168.52.200)
  db2(192.168.52.133) master/ONLINE. Roles: 
  db3(192.168.52.134) slave/ONLINE. Roles: reader(192.168.52.220)
  db4(192.168.52.148) slave/ONLINE. Roles: reader(192.168.52.210)

2、检查群集（全部ok）

[root@monitor ~]# mmm_control checks all
db4  ping         [last change: 2019/11/25 12:15:00]  OK
db4  mysql        [last change: 2019/11/25 12:15:00]  OK
db4  rep_threads  [last change: 2019/11/25 12:15:00]  OK
db4  rep_backlog  [last change: 2019/11/25 12:15:00]  OK: Backlog is null
db2  ping         [last change: 2019/11/25 12:15:00]  OK
db2  mysql        [last change: 2019/11/25 12:15:00]  OK
db2  rep_threads  [last change: 2019/11/25 12:15:00]  OK
db2  rep_backlog  [last change: 2019/11/25 12:15:00]  OK: Backlog is null
db3  ping         [last change: 2019/11/25 12:15:00]  OK
db3  mysql        [last change: 2019/11/25 12:15:00]  OK
db3  rep_threads  [last change: 2019/11/25 12:15:00]  OK
db3  rep_backlog  [last change: 2019/11/25 12:15:00]  OK: Backlog is null
db1  ping         [last change: 2019/11/25 12:15:00]  OK
db1  mysql        [last change: 2019/11/25 12:15:00]  OK
db1  rep_threads  [last change: 2019/11/25 12:15:00]  OK
db1  rep_backlog  [last change: 2019/11/25 12:15:00]  OK: Backlog is null

[root@monitor ~]#

3、切换writer为db2

[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.52.135) master/ONLINE. Roles: 
  db2(192.168.52.133) master/ONLINE. Roles: writer(192.168.52.200)
  db3(192.168.52.134) slave/ONLINE. Roles: reader(192.168.52.220)
  db4(192.168.52.148) slave/ONLINE. Roles: reader(192.168.52.210)

[root@monitor ~]#

4、在monitor安装mariadb服务

[root@monitor ~]# yum install mariadb-server mariadb -y
.......//省略过程
[root@monitor ~]#

5、分别在主服务器1授权monitor访问

主服务器1：
[root@db1 ~]# mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 464
Server version: 5.5.64-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> grant all on *.* to 'testdb'@'192.168.52.150' identified by '123456';   #授权
Query OK, 0 rows affected (0.01 sec)

MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.01 sec)

MariaDB [(none)]>

6、用monitor登录主服务器虚拟IP

[root@monitor ~]# mysql -u testdb -p123456 -h 192.168.52.200
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 584
Server version: 5.5.64-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| myschool           |
| mysql              |
| performance_schema |
| test               |
+--------------------+
5 rows in set (0.00 sec)

MariaDB [(none)]>

7、关闭主服务器1的MySQL服务

[root@db1 ~]# systemctl stop mariadb.service 
[root@db1 ~]#
8、在monitor服务器上删除数据库myschool
monitor：
MariaDB [(none)]> drop database myschool;
Query OK, 0 rows affected (0.01 sec)

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+
4 rows in set (0.00 sec)

MariaDB [(none)]>

9、分别查看三个没有关闭的服务器数据库

主服务器2:
MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+
4 rows in set (0.00 sec)

MariaDB [(none)]> 
从服务器1:
MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+
4 rows in set (0.00 sec)

MariaDB [(none)]> 
从服务器2:
MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+
4 rows in set (0.00 sec)

MariaDB [(none)]>

10、重启主服务器1的MySQL服务，查看数据库

[root@db1 ~]# systemctl stop mariadb.service 
[root@db1 ~]# 
[root@db1 ~]# mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 464
Server version: 5.5.64-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> show databases;   #查看数据库（同步成功）
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+
4 rows in set (0.00 sec)

MariaDB [(none)]>

本文题目：MySQL数据库——MMM高可用群集配置（实战！）
转载注明：http://bzwzjz.com/article/ijsche.html

其他资讯

Copyright © 2007-2020 广东宝晨空调科技有限公司 All Rights Reserved 粤ICP备2022107769号
友情链接： 网站制作 成都网站建设 成都网站建设 网站制作公司 重庆网站建设 重庆手机网站建设 网站设计 网站建设方案 古蔺网站建设 网站制作 成都网站设计 成都网站建设公司 成都商城网站制作 营销型网站建设 响应式网站设计 成都网站制作 重庆网站制作 成都网站制作 网站建设开发 营销网站建设 成都企业网站建设公司 定制网站制作