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

配置本地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
<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
agent_password 123456
</host>

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

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

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

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

<role writer>
hosts db1, db2
ips 192.168.52.200
mode exclusive
</role>

<role reader>
hosts db3, db4
ips 192.168.52.210, 192.168.52.220
mode balanced
</role>

將/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
<host default>
monitor_user mmm_monitor #用戶名
monitor_password 123456 #密碼
</host>

分別在四臺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)]>