MySQL數(shù)據(jù)庫(kù)——MMM高可用群集配置（實(shí)戰(zhàn)！）
MMM簡(jiǎn)介
MMM（Master-Master replication manager for MySQL）是一套支持雙主故障切換和雙主日常管理的腳本程序。MMM使用Perl語(yǔ)言開(kāi)發(fā)，主要用來(lái)監(jiān)控和管理MySQL Master-Master（雙主）復(fù)制，可以說(shuō)是mysql主主復(fù)制管理器。雖然叫做雙主復(fù)制，但是業(yè)務(wù)上同一時(shí)刻只允許對(duì)一個(gè)主進(jìn)行寫(xiě)入，另一臺(tái)備選主上提供部分讀服務(wù)，以加速在主主切換時(shí)刻備選主的預(yù)熱，可以說(shuō)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)可以被寫(xiě)入），這個(gè)套件也能對(duì)居于標(biāo)準(zhǔn)的主從配置的任意數(shù)量的從服務(wù)器進(jìn)行讀負(fù)載均衡，所以你可以用它來(lái)在一組居于復(fù)制的服務(wù)器啟動(dòng)虛擬ip，除此之外，它還有實(shí)現(xiàn)數(shù)據(jù)備份、節(jié)點(diǎn)之間重新同步功能的腳本。
在霍山等地區(qū)，都構(gòu)建了全面的區(qū)域性戰(zhàn)略布局，加強(qiáng)發(fā)展的系統(tǒng)性、市場(chǎng)前瞻性、產(chǎn)品創(chuàng)新能力，以專(zhuān)注、極致的服務(wù)理念，為客戶(hù)提供成都做網(wǎng)站、成都網(wǎng)站制作 網(wǎng)站設(shè)計(jì)制作按需定制開(kāi)發(fā),公司網(wǎng)站建設(shè),企業(yè)網(wǎng)站建設(shè),品牌網(wǎng)站制作,成都全網(wǎng)營(yíng)銷(xiāo),成都外貿(mào)網(wǎng)站建設(shè)公司,霍山網(wǎng)站建設(shè)費(fèi)用合理。
MMM提供了自動(dòng)和手動(dòng)兩種方式移除一組服務(wù)器中復(fù)制延遲較高的服務(wù)器的虛擬ip，同時(shí)它還可以備份數(shù)據(jù)，實(shí)現(xiàn)兩節(jié)點(diǎn)之間的數(shù)據(jù)同步等。由于MMM無(wú)法完全的保證數(shù)據(jù)一致性，所以MMM適用于對(duì)數(shù)據(jù)的一致性要求不是很高，但是又想最大程度的保證業(yè)務(wù)可用性的場(chǎng)景。MySQL本身沒(méi)有提供replication failover的解決方案，通過(guò)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ù)
1、配置本地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源
.......................//省略過(guò)程
[root@localhost ~]# yum clean all && yum makecache #清除緩存舊的緩存，同步現(xiàn)在服務(wù)器的緩存
........................//省略過(guò)程2、在主服務(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ù)
....................//省略過(guò)程
[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 #開(kāi)啟服務(wù)
[root@db1 ~]# netstat -ntap | grep 3306 #查看端口
tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 4601/mysqld
[root@db1 ~]#3、在主服務(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 ~]# 4、在從服務(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 ~]#5、在從服務(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 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; #查看狀態(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 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; #查看狀態(tài)
+------------------+----------+--------------+--------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+--------------------------+
| mysql_bin.000003 | 245 | | mysql,information_schema |
+------------------+----------+--------------+--------------------------+
1 row in set (0.00 sec)2、主服務(wù)器1與主服務(wù)器2互相提升訪問(wèn)權(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)
Query OK, 0 rows affected (0.01 sec)主服務(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; #開(kāi)啟同步
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)開(kāi)啟
Slave_SQL_Running: Yes #確認(rèn)開(kāi)啟
Master_Server_Id: 2
1 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; #開(kāi)啟同步
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)開(kāi)啟
Slave_SQL_Running: Yes #確認(rèn)開(kāi)啟
Master_Server_Id: 1
1 row in set (0.00 sec)
ERROR: No query specified3、測(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)]> 4、配置從服務(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 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)]>從服務(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 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、測(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*
........//省略過(guò)程
[root@db1 ~]#
[root@db2 ~]# yum -y install mysql-mmm*
.......//省略過(guò)程
[root@db2 ~]#
[root@db3 ~]# yum -y install mysql-mmm*
........//省略過(guò)程
[root@db3 ~]#
[root@db4 ~]# yum -y install mysql-mmm*
.........//省略過(guò)程
[root@db4 ~]#2、修改/etc/mysql-mmm/mmm_common.conf 配置文件
[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>3、將/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 4、在監(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
..........//省略過(guò)程
[root@monitor ~]# yum clean all && yum makecache
.........//省略過(guò)程
[root@monitor ~]# yum -y install mysql-mmm*
.........//省略過(guò)程5、在主服務(wù)器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 #監(jiān)控服務(wù)器IP
auto_set_online 10
<host default>
monitor_user mmm_monitor #用戶(hù)名
monitor_password 123456 #密碼
</host>7、分別在四臺(tái)MySQL服務(wù)器授權(quán)monitor訪問(wèn)
主服務(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 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 ~]#主服務(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 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 ~]#從服務(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 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 ~]#從服務(wù)器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服務(wù)器的/etc/mysql-mmm/mmm_agent.conf文件
主服務(wù)器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 #根據(jù)不同主機(jī)修改
[root@db1 ~]# systemctl start mysql-mmm-agent.service #開(kāi)啟服務(wù)
[root@db1 ~]# systemctl enable mysql-mmm-agent.service #開(kāi)機(jī)自啟動(dòng)
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 ~]# 主服務(wù)器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 #根據(jù)不同主機(jī)修改
[root@db2 ~]# systemctl start mysql-mmm-agent.service #開(kāi)啟服務(wù)
[root@db2 ~]# systemctl enable mysql-mmm-agent.service #開(kāi)機(jī)自啟動(dòng)
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 ~]#從服務(wù)器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 #根據(jù)不同主機(jī)修改
[root@db3 ~]# systemctl start mysql-mmm-agent.service #開(kāi)啟服務(wù)
[root@db3 ~]# systemctl enable mysql-mmm-agent.service #開(kāi)機(jī)自啟動(dòng)
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 ~]#從服務(wù)器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 #根據(jù)不同主機(jī)修改
[root@db4 ~]# systemctl start mysql-mmm-agent.service #開(kāi)啟服務(wù)
[root@db4 ~]# systemctl enable mysql-mmm-agent.service #開(kāi)機(jī)自啟動(dòng)
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 ~]#四、測(cè)試MMM高可用
1、開(kāi)啟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 #開(kāi)啟服務(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)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服務(wù)
[root@monitor ~]# yum install mariadb-server mariadb -y
.......//省略過(guò)程
[root@monitor ~]#5、分別在主服務(wù)器1授權(quán)monitor訪問(wèn)
主服務(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 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'; #授權(quán)
Query OK, 0 rows affected (0.01 sec)
MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.01 sec)
MariaDB [(none)]> 6、用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 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、關(guān)閉主服務(wù)器1的MySQL服務(wù)
[root@db1 ~]# systemctl stop mariadb.service
[root@db1 ~]# 8、在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)]>9、分別查看三個(gè)沒(méi)有關(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)]> 10、重啟主服務(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 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; #查看數(shù)據(jù)庫(kù)（同步成功）
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
4 rows in set (0.00 sec)
MariaDB [(none)]>
當(dāng)前名稱(chēng)：MySQL數(shù)據(jù)庫(kù)——MMM高可用群集配置（實(shí)戰(zhàn)！）
網(wǎng)站鏈接：http://www.muchs.cn/article44/ijsche.html
成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供網(wǎng)站設(shè)計(jì)、網(wǎng)站維護(hù)、微信公眾號(hào)、、ChatGPT、動(dòng)態(tài)網(wǎng)站
聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶(hù)投稿、用戶(hù)轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請(qǐng)盡快告知，我們將會(huì)在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng)，如需處理請(qǐng)聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時(shí)需注明來(lái)源： 創(chuàng)新互聯(lián)
網(wǎng)頁(yè)設(shè)計(jì)公司知識(shí)
- 寶山網(wǎng)站建設(shè)公司-網(wǎng)頁(yè)設(shè)計(jì)公司-網(wǎng)頁(yè)制作公司 2020-11-16
- 好的網(wǎng)頁(yè)設(shè)計(jì)公司如何選擇網(wǎng)絡(luò)空間？ 2022-08-03
- 通過(guò)專(zhuān)業(yè)的網(wǎng)頁(yè)設(shè)計(jì)公司促進(jìn)您的業(yè)務(wù) 2022-10-01
- 如何告訴網(wǎng)頁(yè)設(shè)計(jì)公司你想要什么 2022-08-05
- 網(wǎng)頁(yè)設(shè)計(jì)公司：面包屑導(dǎo)航優(yōu)化的必要性 2016-10-08
- 碑林網(wǎng)頁(yè)設(shè)計(jì)公司：判斷網(wǎng)站設(shè)計(jì)成功與否的標(biāo)準(zhǔn)是什么？ 2023-03-04
- 我該怎么選擇網(wǎng)頁(yè)設(shè)計(jì)公司？ 2019-06-19
- 網(wǎng)頁(yè)設(shè)計(jì)公司的優(yōu)勢(shì)和作用有哪些 2014-01-02
- 福田網(wǎng)頁(yè)設(shè)計(jì)公司選擇難，是因?yàn)槟鷽](méi)看這些 2022-08-27
- 佛山網(wǎng)頁(yè)設(shè)計(jì)公司：設(shè)計(jì)網(wǎng)頁(yè)這些問(wèn)題你都注意了嗎？ 2021-08-23
- 經(jīng)典網(wǎng)頁(yè)設(shè)計(jì)公司觀點(diǎn)？ 2022-06-27
- 選擇優(yōu)質(zhì)的成都網(wǎng)頁(yè)設(shè)計(jì)公司需考慮哪些問(wèn)題 2016-10-09