MySQL中MMM高可用架構(gòu)的安裝配置流程

下文我給大家簡單講講關(guān)于MySQL中MMM高可用架構(gòu)的安裝配置流程，大家之前了解過相關(guān)類似主題內(nèi)容嗎？感興趣的話就一起來看看這篇文章吧，相信看完MySQL中MMM高可用架構(gòu)的安裝配置流程對(duì)大家多少有點(diǎn)幫助吧。

實(shí)驗(yàn)環(huán)境

實(shí)驗(yàn)過程

搭建MySQL多主多從模式

1、在四臺(tái)MySQL云服務(wù)器上，都安裝MySQL，過程省略

2、配置ALI云源，然后安裝epel-rlease源，為了下面安裝mysql-mmm工具套件。

[root@localhost ~]# wget -O /etc/yum.repos.d/CentOS-Base.repo http://mirrors.aliyun.com/repo/Centos-7.repo
[root@localhost ~]# yum -y install epel-release
[root@localhost ~]# yum clean all && yum makecache

3、配置修改m1主配置文件。

vim /etc/my.cnf 

[mysqld]
log_error=/var/lib/mysql/mysql.err                     #配置錯(cuò)誤日志
log=/var/lib/mysql/mysql_log.log                       #配置常用日志
log_slow_queries=/var/lib/mysql_slow_queris.log        #配置慢日志
binlog-ignore-db=mysql,information_schema              #配置不需要記錄二進(jìn)制日志的數(shù)據(jù)庫
character_set_server=utf8                              #配置字符集
log_bin=mysql_bin                                      #開啟binlog日志用于主從數(shù)據(jù)復(fù)制
server_id=1                                            #每臺(tái)server-id的值不能一樣
log_slave_updates=true                                 #此數(shù)據(jù)庫宕機(jī)，備用數(shù)據(jù)庫接管
sync_binlog=1                                          #允許同步二進(jìn)制日志
auto_increment_increment=2                             #字段依次遞增多少
auto_increment_offset=1                                #自增字段的起始值：1、3、5等奇數(shù)ID

配置沒問題后，把配置文件復(fù)制到另外一臺(tái)主云服務(wù)器

[root@localhost ~]# scp /etc/my.cnf root@192.168.58.136:/etc/

4、配置mysql-m1、mysql-m2主主模式
首先查看log bin日志和pos值的位置。

[root@localhost1 ~]# mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MySQL connection id is 1065
Server version: 5.5.24-log Source distribution

Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

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

[root@localhost2 ~]# mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MySQL connection id is 1065
Server version: 5.5.24-log Source distribution

Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

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

然后，mysql-m1、mysql-m2互相提升訪問權(quán)限。

mysql-m1
MySQL [(none)]> grant replication slave on *.* to 'replication'@'192.168.58.%' identified by '123456'; 
MySQL [(none)]> change master to master_host='192.168.58.136',master_user='replication',master_password='123456',master_log_file='mysql_bin.000002',master_log_pos=107;
MySQL [(none)]> flush privileges;

mysql-m2
MySQL [(none)]> grant replication slave on *.* to 'replication'@'192.168.58.%' identified by '123456'; 
MySQL [(none)]> change master to master_host='192.168.58.131',master_user='replication',master_password='123456',master_log_file='mysql_bin.000002',master_log_pos=107;
MySQL [(none)]> flush privileges;

最后分別查看mysql-m1、mysql-m2云服務(wù)器的主從狀態(tài),主要查看

Slave_IO_Running: Yes

Slave_SQL_Running: Yes。

MySQL [(none)]> start slave;
MySQL [(none)]> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.58.131
                  Master_User: replication
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql_bin.000002
          Read_Master_Log_Pos: 107
               Relay_Log_File: localhost-relay-bin.000012
                Relay_Log_Pos: 253
        Relay_Master_Log_File: mysql_bin.000002
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

說明主主同步配置成功。
測(cè)試主主同步，在mysql-m1新建一個(gè)庫test01

mysql-m1
MySQL [(none)]> create database test01;
MySQL [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| #mysql50#.mozilla  |
| bbs                |
| mysql              |
| performance_schema |
| test               |
| test01             |
+--------------------+
7 rows in set (0.22 sec)

mysql-m2                                #測(cè)試成功
MySQL [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| #mysql50#.mozilla  |
| mysql              |
| performance_schema |
| test               |
| test01             |
+--------------------+
7 rows in set (0.22 sec)

5、配置myqsl-m3、mysql-m4作為mysql-m1的從庫。

首先將mysql-m1上的/etc/my.cnf文件，復(fù)制到myqsl-m3、mysql-m4兩臺(tái)云服務(wù)器上。

mysql-m1
[root@localhost ~]# scp /etc/my.cnf root@192.168.58.146:/etc/
[root@localhost ~]# scp /etc/my.cnf root@192.168.58.147:/etc/

查看mysql-m1中數(shù)據(jù)庫的狀態(tài)值。

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

在mysql-m3、mysql-m4上分別執(zhí)行。

mysql-m3
MySQL [(none)]> change master to master_host='192.168.58.131',master_user='replication',master_password='123456',master_log_file='mysql_bin.000002',master_log_pos=107;

mysql-m4
MySQL [(none)]> change master to master_host='192.168.58.131',master_user='replication',master_password='123456',master_log_file='mysql_bin.000002',master_log_pos=107;

分別查看mysql-m3、mysql-m4云服務(wù)器的主從狀態(tài)，如下所示。

MySQL [(none)]> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.58.131
                  Master_User: replication
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql_bin.000002
          Read_Master_Log_Pos: 107
               Relay_Log_File: localhost-relay-bin.000012
                Relay_Log_Pos: 253
        Relay_Master_Log_File: mysql_bin.000002
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

安裝配置MySQL-MMM

CentOS默認(rèn)沒有mysql-mmm軟件包，由于之前我們epel官方源已經(jīng)安裝好，在五臺(tái)主機(jī)上都要安裝MMM

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

安裝完后，對(duì)MMM進(jìn)行配置

[root@localhost mongodb1]# vim /etc/mysql-mmm/mmm_common.conf 
<host default>
    cluster_interface       ens33                       #網(wǎng)卡名稱
    ……
    replication_user        replication
    replication_password    123456
    agent_user              mmm_agent
    agent_password          123456

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

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

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

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

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

<role reader>
    hosts   db3, db4
    ips     192.168.58.200, 192.168.58.210
    mode    balanced
</role>                   #將配置文件復(fù)制到其他幾臺(tái)數(shù)據(jù)庫云服務(wù)器對(duì)應(yīng)的目錄下
[root@localhost mysql-mmm]# scp mmm_common.conf root@192.168.58.136:/etc/mysql-mmm/
[root@localhost mysql-mmm]# scp mmm_common.conf root@192.168.58.146:/etc/mysql-mmm/
[root@localhost mysql-mmm]# scp mmm_common.conf root@192.168.58.147:/etc/mysql-mmm/
[root@localhost mysql-mmm]# scp mmm_common.conf root@192.168.58.148:/etc/mysql-mmm/

在作為monitor云服務(wù)器的終端上配置

cd /etc/mysql-mmm/ #改密碼
vim mmm_mon.conf

<host default>
    monitor_user        mmm_monitor
    monitor_password    123456
</host>

在所有數(shù)據(jù)庫上為mmm_agent授權(quán)

MySQL [(none)]> grant super, replication client, process on *.* to 'mmm_agent'@'192.168.58.%' identified by '123456';
#為mmm_agent授權(quán)

在所有數(shù)據(jù)庫上為mmm_moniter授權(quán)

MySQL [(none)]> grant replication client on *.* to 'mmm_monitor'@'192.168.58.%' identified by '123456';
#為mmm_monitor授權(quán)
MySQL [(none)]> flush privileges
#刷新權(quán)限設(shè)置

修改所有數(shù)據(jù)庫的mmm_agent.conf

[root@localhost mysql-mmm]# 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        #分別在四臺(tái)數(shù)據(jù)庫云服務(wù)器上設(shè)置為db1,db2,db3,db4
~                  

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

[root@localhost mysql-mmm]# systemctl start mysql-mmm-agent.service
#啟動(dòng)mmm-agent服務(wù)
[root@localhost mysql-mmm]# systemctl enable mysql-mmm-agent.service
#設(shè)置mmm-agent服務(wù)開機(jī)啟動(dòng)

在monitor云服務(wù)器上配置

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

<monitor>
    ip                  127.0.0.1
    pid_path            /run/mysql-mmm-monitor.pid
    bin_path            /usr/libexec/mysql-mmm
    status_path         /var/lib/mysql-mmm/mmm_mond.status
    ping_ips            192.168.58.131,192.168.58.136,192.168.58.146,192.168.58.147
    #指定監(jiān)管的云服務(wù)器IP地址
    auto_set_online     10

    # The kill_host_bin does not exist by default, though the monitor will
    # throw a warning about it missing.  See the section 5.10 "Kill Host
    # Functionality" in the PDF documentation.
    #
    # kill_host_bin     /usr/libexec/mysql-mmm/monitor/kill_host
    #
</monitor>

<host default>
    monitor_user        mmm_monitor   #設(shè)置監(jiān)管賬戶
    monitor_password    123456        #設(shè)置監(jiān)管密碼
</host>
[root@localhost mysql-mmm]# systemctl start mysql-mmm-monitor.service   #啟動(dòng)mysql-mmm-monitor
[root@localhost mysql-mmm]# mmm_control show   #查看節(jié)點(diǎn)運(yùn)行情況
  db1(192.168.58.131) master/ONLINE. Roles: writer(192.168.58.100)
  db2(192.168.58.136) master/ONLINE. Roles: 
  db3(192.168.58.146) slave/ONLINE. Roles: reader(192.168.58.200)
  db4(192.168.58.147) slave/ONLINE. Roles: reader(192.168.58.210)

當(dāng)我們把mysql-m3的mysql服務(wù)停掉以后，對(duì)應(yīng)的VIP會(huì)自動(dòng)綁定到mysql-m4上

[root@localhost mysql-mmm]# mmm_control show   
  db1(192.168.58.131) master/ONLINE. Roles: writer(192.168.58.100)
  db2(192.168.58.136) master/ONLINE. Roles: 
  db3(192.168.58.146) slave/HARD_OFFLINE. Roles:
 db4(192.168.58.147) slave/ONLINE. Roles: reader(192.168.58.210)(192.168.58.200)
大家覺得MySQL中MMM高可用架構(gòu)的安裝配置流程這篇文章怎么樣，是否有所收獲。如果想要了解更多相關(guān)，可以繼續(xù)關(guān)注我們的行業(yè)資訊板塊。