MMM高可用群集

MMM概述

创新互联专注为客户提供全方位的互联网综合服务，包含不限于成都做网站、成都网站设计、阳高网络推广、小程序定制开发、阳高网络营销、阳高企业策划、阳高品牌公关、搜索引擎seo、人物专访、企业宣传片、企业代运营等，从售前售中售后，我们都将竭诚为您服务，您的肯定，是我们最大的嘉奖；创新互联为所有大学生创业者提供阳高建站搭建服务，24小时服务热线：13518219792，官方网址：www.cdcxhl.com

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服务

配置本地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 #清除缓存旧的缓存，同步现在服务器的缓存
........................//省略过程

在主服务器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 ~]#

在主服务器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 ~]#

在从服务器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 ~]#

在从服务器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)

主服务器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';
#提权

主服务器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

测试主主同步

主服务器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)]>

配置从服务器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)]>

测试多主多从模式

主服务器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、修改

[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

将/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

在监控服务器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*
.........//省略过程

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

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

修改配置文件/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 #密码

分别在四台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 ~]#

从服务器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 ~]#

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

测试MMM高可用

开启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)

检查群集

[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 ~]#

切换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 ~]#

在monitor安装mariadb服务，分别在主服务器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)]>

用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)]

在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)]>

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

主服务器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)]>

重启主服务器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)]>