MMM概述
創(chuàng)新互聯(lián)專注為客戶提供全方位的互聯(lián)網(wǎng)綜合服務(wù)，包含不限于成都做網(wǎng)站、成都網(wǎng)站設(shè)計(jì)、陽(yáng)高網(wǎng)絡(luò)推廣、小程序定制開發(fā)、陽(yáng)高網(wǎng)絡(luò)營(yíng)銷、陽(yáng)高企業(yè)策劃、陽(yáng)高品牌公關(guān)、搜索引擎seo、人物專訪、企業(yè)宣傳片、企業(yè)代運(yùn)營(yíng)等，從售前售中售后，我們都將竭誠(chéng)為您服務(wù)，您的肯定，是我們最大的嘉獎(jiǎng)；創(chuàng)新互聯(lián)為所有大學(xué)生創(chuàng)業(yè)者提供陽(yáng)高建站搭建服務(wù)，24小時(shí)服務(wù)熱線：13518219792，官方網(wǎng)址：www.cdcxhl.com
MMM（Master-Master replication manager for MySQL）是一套支持雙主故障切換和雙主日常管理的腳本程序。MMM使用Perl語(yǔ)言開發(fā)，主要用來監(jiān)控和管理MySQL Master-Master（雙主）復(fù)制，可以說是mysql主主復(fù)制管理器。雖然叫做雙主復(fù)制，但是業(yè)務(wù)上同一時(shí)刻只允許對(duì)一個(gè)主進(jìn)行寫入，另一臺(tái)備選主上提供部分讀服務(wù)，以加速在主主切換時(shí)刻備選主的預(yù)熱，可以說MMM這套腳本程序一方面實(shí)現(xiàn)了故障切換的功能，另一方面其內(nèi)部附加的工具腳本也可以實(shí)現(xiàn)多個(gè)slave的read負(fù)載均衡。關(guān)于mysql主主復(fù)制配置的監(jiān)控、故障轉(zhuǎn)移和管理的一套可伸縮的腳本套件（在任何時(shí)候只有一個(gè)節(jié)點(diǎn)可以被寫入），這個(gè)套件也能對(duì)居于標(biāo)準(zhǔn)的主從配置的任意數(shù)量的從服務(wù)器進(jìn)行讀負(fù)載均衡，所以你可以用它來在一組居于復(fù)制的服務(wù)器啟動(dòng)虛擬ip，除此之外，它還有實(shí)現(xiàn)數(shù)據(jù)備份、節(jié)點(diǎn)之間重新同步功能的腳本。
MMM提供了自動(dòng)和手動(dòng)兩種方式移除一組服務(wù)器中復(fù)制延遲較高的服務(wù)器的虛擬ip，同時(shí)它還可以備份數(shù)據(jù)，實(shí)現(xiàn)兩節(jié)點(diǎn)之間的數(shù)據(jù)同步等。由于MMM無法完全的保證數(shù)據(jù)一致性，所以MMM適用于對(duì)數(shù)據(jù)的一致性要求不是很高，但是又想最大程度的保證業(yè)務(wù)可用性的場(chǎng)景。MySQL本身沒有提供replication failover的解決方案，通過MMM方案能實(shí)現(xiàn)服務(wù)器的故障轉(zhuǎn)移，從而實(shí)現(xiàn)mysql的高可用。對(duì)于那些對(duì)數(shù)據(jù)的一致性要求很高的業(yè)務(wù)，非常不建議采用MMM這種高可用架構(gòu)。
實(shí)驗(yàn)環(huán)境
主服務(wù)器1：192.168.52.135 db1 writer VIP：192.168.52.200
主服務(wù)器2：192.168.52.133 db2從服務(wù)器1：192.168.52.134 db3 reader VIP：192.168.52.210,192.168.52.220
從服務(wù)器2：192.168.52.148 db4監(jiān)控服務(wù)器：192.168.52.150
實(shí)驗(yàn)步驟
一、分別在四臺(tái)MySQL服務(wù)器上安裝mariadb服務(wù)
配置本地yum源（四臺(tái)服務(wù)器上分別進(jìn)行相同操作）
[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
正在解析主機(jī) 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... 已連接。
已發(fā)出 HTTP 請(qǐng)求，正在等待回應(yīng)... 200 OK
長(zhǎng)度：2523 (2.5K) [application/octet-stream]
正在保存至: “/etc/yum.repos.d/CentOS-Base.repo”100%[====================================================================================>] 2,523 --.-K/s 用時(shí) 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 #清除緩存舊的緩存，同步現(xiàn)在服務(wù)器的緩存
........................//省略過程
在主服務(wù)器1上安裝mariadb（MySQL服務(wù)的一種）服務(wù)
[root@localhost ~]# hostnamectl set-hostname db1 #修改主機(jī)名
[root@localhost ~]# su #切換bash環(huán)境
[root@db1 ~]#
[root@db1 ~]# yum install mariadb-server mariadb -y #安裝服務(wù)
....................//省略過程
[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 #關(guān)閉防火墻
[root@db1 ~]# setenforce 0 #關(guān)閉增強(qiáng)性安全功能
[root@db1 ~]# systemctl start mariadb.service #開啟服務(wù)
[root@db1 ~]# netstat -ntap | grep 3306 #查看端口
tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 4601/mysqld
[root@db1 ~]#
在主服務(wù)器2上安裝mariadb服務(wù)
[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 ~]#
在從服務(wù)器1上安裝mariadb服務(wù)
[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 ~]#
在從服務(wù)器2上安裝mariadb服務(wù)
[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、分別查看主服務(wù)器1和主服務(wù)器2的log_bin日志和pos值的位置
主服務(wù)器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 ServerCopyright (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; #查看狀態(tài)
+------------------+----------+--------------+--------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+--------------------------+
| mysql_bin.000003 | 245 | | mysql,information_schema |
+------------------+----------+--------------+--------------------------+
1 row in set (0.00 sec)
主服務(wù)器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 ServerCopyright (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; #查看狀態(tài)
+------------------+----------+--------------+--------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+--------------------------+
| mysql_bin.000003 | 245 | | mysql,information_schema |
+------------------+----------+--------------+--------------------------+
1 row in set (0.00 sec)
主服務(wù)器1與主服務(wù)器2互相提升訪問權(quán)限
主服務(wù)器1：
MariaDB [(none)]> grant replication slave on . to 'replication'@'192.168.52.%' identified by '123456';
#提權(quán)
Query OK, 0 rows affected (0.01 sec)主服務(wù)器2：
MariaDB [(none)]> grant replication slave on . to 'replication'@'192.168.52.%' identified by '123456';
#提權(quán)
主服務(wù)器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;
#指定同步服務(wù)器的具體信息
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; #查看狀態(tài)
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 #確認(rèn)開啟
Slave_SQL_Running: Yes #確認(rèn)開啟Master_Server_Id: 21 row in set (0.00 sec)
ERROR: No query specified
主服務(wù)器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;
#指定同步服務(wù)器的具體信息
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; #查看狀態(tài)
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 #確認(rèn)開啟
Slave_SQL_Running: Yes #確認(rèn)開啟Master_Server_Id: 11 row in set (0.00 sec)
ERROR: No query specified
測(cè)試主主同步
主服務(wù)器1：
MariaDB [(none)]> create database school; #創(chuàng)建數(shù)據(jù)庫(kù)school
Query OK, 1 row affected (0.01 sec)MariaDB [(none)]> show databases; #查看數(shù)據(jù)庫(kù)
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| school | #創(chuàng)建成功
| test |
+--------------------+
5 rows in set (0.00 sec)
主服務(wù)器2：
MariaDB [(none)]> show databases; #查看數(shù)據(jù)庫(kù)
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| school | #同步成功
| test |
+--------------------+
5 rows in set (0.00 sec)
MariaDB [(none)]> drop database school; #刪除數(shù)據(jù)庫(kù)school
Query OK, 0 rows affected (0.01 sec)MariaDB [(none)]> show databases; #查看數(shù)據(jù)庫(kù)（刪除成功）
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
4 rows in set (0.00 sec)MariaDB [(none)]>
主服務(wù)器1：
MariaDB [(none)]> show databases; #查看數(shù)據(jù)庫(kù)（同步成功）
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
4 rows in set (0.00 sec)MariaDB [(none)]>
配置從服務(wù)器1、2作為主服務(wù)器1的從庫(kù)
從服務(wù)器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 ServerCopyright (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)]>
從服務(wù)器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 ServerCopyright (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)]>
測(cè)試多主多從模式
主服務(wù)器1：
MariaDB [(none)]> create database myschool; #創(chuàng)建數(shù)據(jù)庫(kù)myschool
Query OK, 1 row affected (0.01 sec)MariaDB [(none)]> show databases; #查看數(shù)據(jù)庫(kù)
+--------------------+
| Database |
+--------------------+
| information_schema |
| myschool | #創(chuàng)建成功
| mysql |
| performance_schema |
| test |
+--------------------+
5 rows in set (0.00 sec)MariaDB [(none)]> quit
Bye
[root@db1 ~]#
主服務(wù)器2：
MariaDB [(none)]> show databases; #查看數(shù)據(jù)庫(kù)
+--------------------+
| Database |
+--------------------+
| information_schema |
| myschool | #同步成功
| mysql |
| performance_schema |
| test |
+--------------------+
5 rows in set (0.00 sec)MariaDB [(none)]> quit
Bye
[root@db2 ~]#
從服務(wù)器1：
MariaDB [(none)]> show databases; #查看數(shù)據(jù)庫(kù)
+--------------------+
| Database |
+--------------------+
| information_schema |
| myschool | #同步成功
| mysql |
| performance_schema |
| test |
+--------------------+
5 rows in set (0.00 sec)MariaDB [(none)]> quit
Bye
[root@db3 ~]#
從服務(wù)器2：
MariaDB [(none)]> show databases; #查看數(shù)據(jù)庫(kù)
+--------------------+
| Database |
+--------------------+
| information_schema |
| myschool | #同步成功
| mysql |
| performance_schema |
| test |
+--------------------+
5 rows in set (0.00 sec)MariaDB [(none)]> quit
Bye
[root@db4 ~]#
三、安裝配置MySQL-MMM
1、四臺(tái)MySQL服務(wù)器分別安裝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配置文件，推送到其它三個(gè)服務(wù)器
[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
在監(jiān)控服務(wù)器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
正在解析主機(jī) 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... 已連接。
已發(fā)出 HTTP 請(qǐng)求，正在等待回應(yīng)... 200 OK
長(zhǎng)度：2523 (2.5K) [application/octet-stream]
正在保存至: “/etc/yum.repos.d/CentOS-Base.repo”100%[====================================================================================>] 2,523 --.-K/s 用時(shí) 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*
.........//省略過程
在主服務(wù)器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 #監(jiān)控服務(wù)器IP
auto_set_online 10
monitor_user mmm_monitor #用戶名
monitor_password 123456 #密碼
分別在四臺(tái)MySQL服務(wù)器授權(quán)monitor訪問
主服務(wù)器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 ServerCopyright (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 ~]#~~~~主服務(wù)器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 ServerCopyright (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 ~]#從服務(wù)器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 ServerCopyright (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 ~]#從服務(wù)器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 ServerCopyright (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服務(wù)器的/etc/mysql-mmm/mmm_agent.conf文件
測(cè)試MMM高可用
開啟mysql-mmm-monitor.service
[root@monitor ~]# systemctl stop firewalld.service #關(guān)閉防火墻
[root@monitor ~]# setenforce 0 #關(guān)閉增強(qiáng)性安全功能
[root@monitor ~]# systemctl start mysql-mmm-monitor.service #開啟服務(wù)
[root@monitor ~]# mmm_control show #測(cè)試群集
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服務(wù)，分別在主服務(wù)器1授權(quán)monitor訪問
主服務(wù)器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 ServerCopyright (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'; #授權(quán)
Query OK, 0 rows affected (0.01 sec)MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.01 sec)MariaDB [(none)]>
用monitor登錄主服務(wù)器虛擬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 ServerCopyright (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服務(wù)器上刪除數(shù)據(jù)庫(kù)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)]>
分別查看三個(gè)沒有關(guān)閉的服務(wù)器數(shù)據(jù)庫(kù)
主服務(wù)器2:
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
4 rows in set (0.00 sec)MariaDB [(none)]>
從服務(wù)器1:
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
4 rows in set (0.00 sec)MariaDB [(none)]>
從服務(wù)器2:
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
4 rows in set (0.00 sec)MariaDB [(none)]>
重啟主服務(wù)器1的MySQL服務(wù)，查看數(shù)據(jù)庫(kù)
[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 ServerCopyright (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; #查看數(shù)據(jù)庫(kù)（同步成功）
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
4 rows in set (0.00 sec)MariaDB [(none)]>
重慶分公司
重慶分公司
