MySQL-MMM 高可用

MMM 簡(jiǎn)介

         MMM （Master-Master relication manager fro MySQL ,MySQK 主主復(fù)制管理器）是一套支持雙主故障切換和雙主日常管理的腳步程序。主要是用來(lái)監(jiān)控和管理MySQL Master-Master （雙主）復(fù)制，雖然叫做雙主復(fù)制，到時(shí)業(yè)務(wù)上同一時(shí)刻值允許對(duì)一個(gè)主進(jìn)行寫(xiě)入，另一臺(tái)備選主提供部分讀服務(wù)，以加速在主主切換時(shí)備選主的預(yù)熱。一方面實(shí)現(xiàn)了故障切換的功能，也可實(shí)現(xiàn)多個(gè) Slave 的read 負(fù)載均衡。

關(guān)于MMM 高可用架構(gòu)的說(shuō)明如下：

        mmm_mon :監(jiān)控進(jìn)程，負(fù)載所有的監(jiān)控工作，絕對(duì)和處理所有節(jié)點(diǎn)角色活動(dòng)。此腳本需要在監(jiān)管機(jī)上運(yùn)行。

        mmm_agent:運(yùn)行在每個(gè)MySQL 服務(wù)器上的代理進(jìn)程，完成監(jiān)控的探針工作和執(zhí)行簡(jiǎn)單的遠(yuǎn)程服務(wù)設(shè)置。此腳本需要在被監(jiān)管機(jī)上運(yùn)行。

        mmm_control:提供管理 mmm_mond 進(jìn)程命令。

        mysql-mmm 的監(jiān)管端會(huì)提供多個(gè)虛擬IP（VIP),包括一個(gè)可寫(xiě)VIP,多個(gè)可讀VIP,這些IP會(huì)綁定在可用MySQL 上，當(dāng)某一臺(tái)MySQL 宕機(jī)時(shí)，監(jiān)管會(huì)將VIP遷移到其他 MySQL。

案例環(huán)境

本案例環(huán)境使用五臺(tái)服務(wù)器模擬搭建：

         主機(jī)                         操作系統(tǒng)                      IP 地址                           主要軟件

mysql-master1         centos7x86_64             192.168.213.174              mysql-mmm*

mysql-master2         centos7x86_64              192.168.213.177              mysql-mmm*

mysql-slave 1            centos7x86_64              192.168.213.179              mysql-mmm*

mysql-slave 2            centos7x86_64              192.168.213.173              mysql-mmm*

mysql-monitor         centos7x86_64              192.168.213.178               mysql-mmm*

一. 搭建MySQL 多主多從模式

（1）在線下載安裝epel 源,五臺(tái)服務(wù)器都要安裝。

[root@master1 ~]# wget -O /etc/yum.repos.d/CentOS-Base.repo http://mirrors.aliyun.com/repo/Centos-7.repo

#將aliyun 的 yum源下載到本地網(wǎng)絡(luò)yum 源

[root@master1 ~]# yum -y install epel-release                             #下載epel 源

[root@master1 ~]# yum clean all && yum makecache            #清空所有，重新設(shè)置原數(shù)據(jù)緩存

（2）下載 mariadb (mysql 的分支），關(guān)閉防火墻功能   （mariadb 只在主從4個(gè)服務(wù)器上安裝）

[root@master1 ~]# yum -y install mariadb-server mariadb

[root@master1 ~]# systemctl stop firewalld.service                 #關(guān)閉防火墻
[root@master1 ~]# setenforce 0

（3）修改 mariadb 的配置文件

[root@master1 ~]# vim /etc/my.cnf

[mysqld]
log_error=/var/lib/mysql/mysql.err                                          #錯(cuò)誤日志路徑
log=/var/lib/mysql/mysql_log.log                                              #通用查詢?nèi)罩?br/>log_slow_queries=/var/lib/mysql_slow_queris.log                #慢查詢?nèi)罩韭窂?/strong>
binlog-ignore-db=mysql,information_schema                          #不需要同步的數(shù)據(jù)庫(kù)名稱
character_set_server=utf8                                                             #默認(rèn)字符集為 utf8
log_bin=mysql_bin                                                                           #啟用二進(jìn)制日志
server_id=1                                                                                        #服務(wù)器id , 注意：每臺(tái)服務(wù)器的id 都不一樣，確保唯一性
log_slave_updates=ture                                                                  #允許從服務(wù)器進(jìn)行日志更新
sync_binlog=1                                                                                    #允許從服務(wù)器同步二進(jìn)制日志
auto_increment_increment=2                                                        #字段一次遞增多少
auto_increment_offset=1                                                                #自增段的起始值

（4）啟動(dòng)mysql 服務(wù)，并查看默認(rèn)端口是否開(kāi)啟

[root@master1 ~]# systemctl restart mariadb
[root@master1 ~]# netstat -ntap | grep 3306
tcp        0      0 0.0.0.0:3306            0.0.0.0:*               LISTEN      41241/mysqld     

（5）設(shè)置數(shù)據(jù)庫(kù) root 用戶登錄的 密碼

[root@master1 ~]# mysqladmin -u root password '123'
[root@master1 ~]# mysql -uroot -p
Enter password:
Welcome to the MariaDB monitor.  Commands end with ; or \g.

          其他三臺(tái)數(shù)據(jù)庫(kù)服務(wù)器安裝與配置與 master1 相同，配置文件的其他參數(shù)都一樣，僅 server-id 不同。因此可以在 master 1 服務(wù)器上傳配置文件，注意修改 server-id.

[root@master01 ~]# scp -r /etc/my.cnf root@192.168.213.177:/etc/my.cnf
[root@master01 ~]# scp -r /etc/my.cnf root@192.168.213.179:/etc/my.cnf
[root@master01 ~]# scp -r /etc/my.cnf root@192.168.213.173:/etc/my.cnf

二.配置 master 1 he master 2 主主模式

（1）先查看 log bin 日志和pos 值

master 1

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

master 2

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

（2）master 1  和 master 2 互相提升訪問(wèn)權(quán)限

在m1上為m2授予從的權(quán)限

MariaDB [(none)]> grant replication slave on *.* to 'replication'@'192.168.213.%' identified by '123456';
Query OK, 0 rows affected (0.35 sec)

MariaDB [(none)]> change master to master_host='192.168.213.177',master_user='replication',master_password='123456',master_log_file='mysql_bin.000003',master_log_pos= 554;                                                                                                                                         #master 2 log bin 日志 和pos值
Query OK, 0 rows affected (0.02 sec)

在m2上也要為m1 授權(quán)從的權(quán)限

MariaDB [(none)]> grant replication slave on *.* to 'replication'@'192.168.213.%' identified by '123456';
Query OK, 0 rows affected (0.35 sec)

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

（3）開(kāi)啟兩臺(tái)主主服務(wù)器同步功能，并查詢主從狀態(tài)

tart slave;   //開(kāi)啟同步功能
show slave status\G;
     Slave_I0_Running: Yes                //這兩個(gè)選項(xiàng)參數(shù)必須為yes
     Slave_SQL_Running: Yes

主主同步配置完成，查看狀態(tài) Slave_IO  和 Slave_SQI 為 yes ,說(shuō)明主主同步成功

三.配置從服務(wù)器  Slave1 和Slave2,作為 Master 1 的從庫(kù)

查看 master1  的狀態(tài)值

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

在 slave1 和slave2 分別做

MariaDB [(none)]> change master to master_host='192.168.213.174',master_user='replication',master_password='123456',master_log_file='mysql_bin.000003',master_log_pos= 554;                    #master1 的ip
Query OK, 0 rows affected (0.02 sec)

開(kāi)啟同步功能，并查看同步狀態(tài)

start slave;   //開(kāi)啟同步功能
show slave status\G;
     Slave_I0_Running: Yes                //這兩個(gè)選項(xiàng)參數(shù)必須為yes
     Slave_SQL_Running: Yes

四 .安裝配置 MySQL-MMM

（1）安裝MMM 。Centos 默認(rèn)沒(méi)有 mysql-mmm 軟件包，使用epel 源，五臺(tái)服務(wù)器都要安裝 epel 和 MMM

[root@master1 ~]# yum install mysql-mmm* –y

（2）修改 MMM 的配置文件，系統(tǒng)中所有主機(jī)的該配置文件內(nèi)容都是一樣的，包括監(jiān)控主機(jī) mysql-monitor

[root@master1 ~]# cd /etc/mysql-mmm/                                  #配置文件路徑
[root@master1 mysql-mmm]# vim mmm_common.conf

<host default>
     cluster_interface      ens33                                           #網(wǎng)卡改為 ens33
     pid_path                /run/mysql-mmm-agent.pid
     bin_path                /usr/libexec/mysql-mmm/
     replication_user        replicant                                        #Mariadb 給與權(quán)限的用戶
     replication_password    123456                                     #給與權(quán)限的用戶密碼
     agent_user              mmm_agent                                    #agent 客戶端代理用戶
     agent_password          123456                                        #代理用戶密碼
</host>

<host db1>                                    #主服務(wù)器 master1
     ip      192.168.213.174
     mode    master                      #狀態(tài)為：主
     peer    db2                               #與主服務(wù)器master 2互相切換
</host>

<host db2>                                 #主服務(wù)器 master2
     ip      192.168.213.177          
     mode    master                      #狀態(tài)為：主
     peer    db1
</host>

<host db3>                                   #從服務(wù)器 slave1
     ip      192.168.213.179
     mode    slave                            #狀態(tài)為：slave
</host>

<host db4>                                       #從服務(wù)器 slave2
     ip      192.168.213.173
     mode    slave                               #狀態(tài)為：slave
</host>

<role writer>                                      #寫(xiě)操作
     hosts   db1, db2                             #主服務(wù)器master1 和master2 具有寫(xiě)的操作
     ips     192.168.213.100                   #指定虛擬IP
     mode    exclusive                   #指定模式，db1 和db2 只能存在一個(gè)IP地址，只能有一臺(tái)主機(jī)進(jìn)行寫(xiě)入操作
</role>

<role reader>                                       #讀操作
     hosts  db3, db4
     ips     192.168.213.110, 192.168.213.120          #虛擬IP
     mode    balanced                                   #負(fù)載均衡，讀取操作會(huì)從 db3 和db4 主機(jī)進(jìn)行
</role>

（3）遠(yuǎn)程復(fù)制，將 mmm_common.conf 配置文件傳送到其他4臺(tái)主機(jī)上

[root@master1 mysql-mmm]# scp mmm_common.conf root@192.168.213.177:/etc/mysql-mmm/
The authenticity of host '192.168.213.177 (192.168.213.177)' can't be established.
ECDSA key fingerprint is SHA256:KM7QwLupjrfzZ2YQdMOoGKJtIUgtz2agvwTzZOPHu2k.
ECDSA key fingerprint is MD5:f1:32:f7:7f:b7:eb:4e:9e:2e:fa:7e:8a:56:88:fe:c1.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '192.168.213.177' (ECDSA) to the list of known hosts.
root@192.168.213.177's password:    #對(duì)方root 用戶登錄密碼
mmm_common.conf                                           100%  842   329.6KB/s   00:00   
[root@master1 mysql-mmm]# scp mmm_common.conf root@192.168.213.179:/etc/mysql-mmm/
The authenticity of host '192.168.213.179 (192.168.213.179)' can't be established.
ECDSA key fingerprint is SHA256:HtLFtvYxQF5ER0eA1uKE8VgRx038LWpDYBbp1S1CrJ8.
ECDSA key fingerprint is MD5:23:41:18:56:8e:ed:f3:65:b1:5f:96:11:e9:11:cb:29.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '192.168.213.179' (ECDSA) to the list of known hosts.
root@192.168.213.179's password:
mmm_common.conf                                           100%  842   376.0KB/s   00:00   
[root@master1 mysql-mmm]# scp mmm_common.conf root@192.168.213.173:/etc/mysql-mmm/
The authenticity of host '192.168.213.173 (192.168.213.173)' can't be established.
ECDSA key fingerprint is SHA256:w910JWPfehgM09d+OlOiC6q61NjELLHDh6LWojkuYL0.
ECDSA key fingerprint is MD5:94:a5:a1:e0:4d:14:cc:3c:ff:8c:24:e5:3e:e1:2b:cd.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '192.168.213.173' (ECDSA) to the list of known hosts.
root@192.168.213.173's password:
mmm_common.conf                                           100%  842   565.0KB/s   00:00  

[root@master1 mysql-mmm]# scp mmm_common.conf root@192.168.213.178:/etc/mysql-mmm/
The authenticity of host '192.168.213.178 (192.168.213.178)' can't be established.
ECDSA key fingerprint is SHA256:ABSTPGOHvqKvUsfwD/uf5ESPpdT1RjvucRpzMqcUuzI.
ECDSA key fingerprint is MD5:f5:3a:8c:8b:1e:d5:a3:33:24:32:03:2d:4d:3e:e8:68.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '192.168.213.178' (ECDSA) to the list of known hosts.
root@192.168.213.178's password:
mmm_common.conf                                           100%  842   277.8KB/s   00:00 

（4）在4臺(tái)數(shù)據(jù)庫(kù)上為監(jiān)控 monitor 授權(quán)訪問(wèn)

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

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

MariaDB [(none)]> flush privileges;                #刷新權(quán)限
Query OK, 0 rows affected (0.00 sec)

（5）在數(shù)據(jù)庫(kù)主機(jī)上需要編輯   mmm_agent.conf 配置文件，根據(jù)不同主機(jī)修改為不同的值

[root@master1 mysql-mmm]# vim mmm_agent.conf

this db1               #分別修改為 db1 、db2 、db3 、db4

五 .監(jiān)控服務(wù)器 monitor 配置

[root@localhost ~]# cd /etc/mysql-mmm/
[root@localhost mysql-mmm]# vim mmm_mon.conf

<monitor>

ping_ips            192.168.213.174,192.168.213.177,192.168.213.179,192.168.213.173      #監(jiān)控所有數(shù)據(jù)庫(kù)的 IP 地址
auto_set_online     10                                         #設(shè)置10秒上線

<host default>
     monitor_user        mmm_monitor                  #用戶名
     monitor_password    123456                          #密碼

六 .啟動(dòng)監(jiān)控和代理

（1）在所有數(shù)據(jù)庫(kù)服務(wù)器啟動(dòng) mysql-mmm-agent

[root@master1 mysql-mmm]# systemctl start mysql-mmm-agent.service
[root@master1 mysql-mmm]# systemctl enable mysql-mmm-agent.service

（2）啟動(dòng)監(jiān)控服務(wù) mysql-mmm-monitor

[root@localhost mysql-mmm]# systemctl start mysql-mmm-monitor.service

（3）測(cè)試群集

[root@localhost mysql-mmm]# mmm_control show
  db1(192.168.213.174) master/ONLINE. Roles: writer(192.168.213.100)                  #虛擬IP
   db2(192.168.213.177) master/ONLINE. Roles:
   db3(192.168.213.179) slave/ONLINE. Roles: reader(192.168.213.110)
   db4(192.168.213.173) slave/ONLINE. Roles: reader(192.168.213.120)

    #對(duì)應(yīng)的真是的IP  

檢查狀態(tài)都需要全部 OK

[root@localhost mysql-mmm]# mmm_control checks all
db4  ping         [last change: 2018/09/10 10:50:52]  OK
db4  mysql        [last change: 2018/09/10 10:50:52]  OK
db4  rep_threads  [last change: 2018/09/10 10:50:52]  OK
db4  rep_backlog  [last change: 2018/09/10 10:50:52]  OK: Backlog is null
db2  ping         [last change: 2018/09/10 10:50:52]  OK
db2  mysql        [last change: 2018/09/10 10:50:52]  OK
db2  rep_threads  [last change: 2018/09/10 10:50:52]  OK
db2  rep_backlog  [last change: 2018/09/10 10:50:52]  OK: Backlog is null
db3  ping         [last change: 2018/09/10 10:50:52]  OK
db3  mysql        [last change: 2018/09/10 10:50:52]  OK
db3  rep_threads  [last change: 2018/09/10 10:50:52]  OK
db3  rep_backlog  [last change: 2018/09/10 10:50:52]  OK: Backlog is null
db1  ping         [last change: 2018/09/10 10:50:52]  OK
db1  mysql        [last change: 2018/09/10 10:50:52]  OK
db1  rep_threads  [last change: 2018/09/10 10:50:52]  OK
db1  rep_backlog  [last change: 2018/09/10 10:50:52]  OK: Backlog is null

七 .故障測(cè)試

（1）停掉主 db1 的數(shù)據(jù)庫(kù)，等待幾秒，可以看到數(shù)據(jù)庫(kù) db1 處于 HARD_OFFLINE. Roles （離線狀態(tài)），檢測(cè)不到數(shù)據(jù)庫(kù)的存在

[root@master1 mysql-mmm]# systemctl stop mariadb.service

[root@localhost mysql-mmm]# mmm_control show

db1(192.168.213.174) master/HARD_OFFLINE. Roles:
   db2(192.168.213.177) master/ONLINE. Roles: writer(192.168.213.100)
   db3(192.168.213.179) slave/ONLINE. Roles: reader(192.168.213.110)
   db4(192.168.213.173) slave/ONLINE. Roles: reader(192.168.213.120)

宕掉MySQL  數(shù)據(jù)庫(kù)，虛擬IP 會(huì)全部在另一臺(tái)正常數(shù)據(jù)庫(kù)上

再啟動(dòng)主db1 的數(shù)據(jù)庫(kù)

[root@master1 mysql-mmm]# systemctl start mariadb.service        #啟動(dòng)mysql  數(shù)據(jù)庫(kù)

[root@localhost mysql-mmm]# mmm_control show
   db1(192.168.213.174) master/AWAITING_RECOVERY. Roles:                  #等待回復(fù)
   db2(192.168.213.177) master/ONLINE. Roles: writer(192.168.213.100)
   db3(192.168.213.179) slave/ONLINE. Roles: reader(192.168.213.110)
   db4(192.168.213.173) slave/ONLINE. Roles:reader(192.168.213.120)

[root@localhost mysql-mmm]# mmm_control show
  db1(192.168.213.174) master/ONLINE. Roles:                                         #正常狀態(tài)（在線）
   db2(192.168.213.177) master/ONLINE. Roles: writer(192.168.213.100)
   db3(192.168.213.179) slave/ONLINE. Roles: reader(192.168.213.110)
   db4(192.168.213.173) slave/ONLINE. Roles: reader(192.168.213.120)

雖然主服務(wù)器 db1 從新上線，但是虛擬IP 并不會(huì)復(fù)位，

（3）停掉從服務(wù)器 db3 的數(shù)據(jù)庫(kù)

[root@localhost mysql-mmm]#  systemctl stop mariadb.service

[root@localhost mysql-mmm]# mmm_control show
# Warning: agent on host db3 is not reachable
   db1(192.168.213.174) master/ONLINE. Roles:
   db2(192.168.213.177) master/ONLINE. Roles: writer(192.168.213.100)
   db3(192.168.213.179) slave/HARD_OFFLINE. Roles:    #從服務(wù)器db3 關(guān)閉，虛擬IP 會(huì)全部飄移到靈位一臺(tái)從服務(wù)器db4
   db4(192.168.213.173) slave/ONLINE. Roles: reader(192.168.213.110), reader(192.168.213.120)

啟動(dòng)從 db3 的數(shù)據(jù)庫(kù)

[root@localhost mysql-mmm]#  systemctl start mariadb.service

[root@localhost mysql-mmm]# mmm_control show
# Warning: agent on host db3 is not reachable
   db1(192.168.213.174) master/ONLINE. Roles:
   db2(192.168.213.177) master/ONLINE. Roles: writer(192.168.213.100)
   db3(192.168.213.179) slave/AWAITING_RECOVERY. Roles:
   db4(192.168.213.173) slave/ONLINE. Roles: reader(192.168.213.110), reader(192.168.213.120)

[root@localhost mysql-mmm]# mmm_control show
  db1(192.168.213.174) master/ONLINE. Roles:
   db2(192.168.213.177) master/ONLINE. Roles: writer(192.168.213.100)
   db3(192.168.213.179) slave/ONLINE. Roles: reader(192.168.213.120)            #從服務(wù)器上線后，虛擬IP又恢復(fù)
   db4(192.168.213.173) slave/ONLINE. Roles: reader(192.168.213.110)

八 .測(cè)試數(shù)據(jù)同步狀況

以監(jiān)控服務(wù)器作為客戶端進(jìn)行遠(yuǎn)程登錄 mysql 服務(wù)進(jìn)行測(cè)試，在監(jiān)控服務(wù)器上安裝 mariadb 數(shù)據(jù)庫(kù)

[root@localhost mysql-mmm]# yum install mariadb-server mariadb –y

在主數(shù)據(jù)庫(kù) db1 上為監(jiān)控服務(wù)器授權(quán)登錄MariaDB 數(shù)據(jù)庫(kù)

MariaDB [(none)]> grant all on *.* to 'testdba'@'192.168.213.178' identified by '123456';
Query OK, 0 rows affected (0.00 sec)

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

在監(jiān)控服務(wù)器上使用虛擬IP 登錄MariaDB 數(shù)據(jù)庫(kù)，進(jìn)行數(shù)據(jù)插入，創(chuàng)建一個(gè)數(shù)據(jù)庫(kù)

[root@localhost mysql-mmm]# mysql -utestdba -p -h 192.168.213.100
Enter password:
Welcome to the MariaDB monitor.  Commands end with ; or \g.

MariaDB [school]> create database mogo;
Query OK, 1 row affected (0.01 sec)

登錄其他四臺(tái)數(shù)據(jù)庫(kù)，都可以查看到剛創(chuàng)建的數(shù)據(jù)庫(kù) mogo,證明群集同步成功

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mogo               |
| mysql              |
| performance_schema |
| test               |
+--------------------+