MySQL-MMM高可用

標籤：技術分享   .com   --   管理   hang   複製   epel   位置   Requires   

MySQL-MMM高可用MMM簡介

MMM（Master-Master replication manager for MySQL,MySQL主主複製管理器），是一套支援雙主故障切換和雙主日常管理的指令碼程式。這套指令碼程式中主要包含幾個組件：

1、mmm_mon:監控進程，負責所有的監控工作，決定和處理所有節點角色活動。此指令碼需要在監管機上運行。

2、mmm_agent:運行在每個MySQL伺服器上的代理進程，完成監控的探針工作和執行簡單的遠程服務設定。此指令碼需要在被監管機上運行。

3、mmm_control:一個簡單的指令碼，提供管理mmm_mond進程的命令。

mysql_mmm的監管端會提供多個虛擬IP（VIP），包括一個可寫VIP，多個可讀VIP，通過監管的管理，這些IP會綁定在可用MySQL之上，在當某一台MySQL宕機時，監管會將VIP遷移至其他MySQL。

實驗環境

主機	IP地址
mysql-m1主伺服器	192.168.58.131
mysql-m2主伺服器	192.168.58.136
mysql-m3從伺服器	192.168.58.146
mysql-m4從伺服器	192.168.58.147
mysql-monitor監視Proxy 伺服器	192.168.58.148

實驗過程搭建MySQL多主多從模式

1、在四台MySQL伺服器上，都安裝MySQL，過程省略

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

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

3、配置修改m1主設定檔。

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                                      #開啟binlog日誌用於主從資料複製server_id=1                                            #每台server-id的值不能一樣log_slave_updates=true                                 #此資料庫宕機，備用資料庫接管sync_binlog=1                                          #允許同步二進位日誌auto_increment_increment=2                             #欄位依次遞增多少auto_increment_offset=1                                #自增欄位的起始值：1、3、5等奇數ID

配置沒問題後，把設定檔複製到另外一台主伺服器

[[email protected] ~]# scp /etc/my.cnf [email protected]:/etc/

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

[[email protected] ~]# mysqlWelcome to the MariaDB monitor.  Commands end with ; or \g.Your MySQL connection id is 1065Server version: 5.5.24-log Source distributionCopyright (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)[[email protected] ~]# mysqlWelcome to the MariaDB monitor.  Commands end with ; or \g.Your MySQL connection id is 1065Server version: 5.5.24-log Source distributionCopyright (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互相提升存取權限。

mysql-m1MySQL [(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-m2MySQL [(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伺服器的主從狀態,主要查看

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

說明主主同步配置成功。
測試主主同步，在mysql-m1建立一個庫test01

mysql-m1MySQL [(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                                #測試成功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檔案，複製到myqsl-m3、mysql-m4兩台伺服器上。

mysql-m1[[email protected] ~]# scp /etc/my.cnf [email protected]:/etc/[[email protected] ~]# scp /etc/my.cnf [email protected]:/etc/

查看mysql-m1中資料庫的狀態值。

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上分別執行。

mysql-m3MySQL [(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-m4MySQL [(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伺服器的主從狀態，如下所示。

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預設沒有mysql-mmm軟體包，由於之前我們epel官方源已經安裝好，在五台主機上都要安裝MMM

[[email protected] ~]# yum -y install mysql-mmm*

安裝完後，對MMM進行配置

[[email protected] mongodb1]# vim /etc/mysql-mmm/mmm_common.conf <host default>    cluster_interface       ens33                       #網卡名稱    ……    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>                   #將設定檔複製到其他幾台資料庫伺服器對應的目錄下[[email protected] mysql-mmm]# scp mmm_common.conf [email protected]:/etc/mysql-mmm/[[email protected] mysql-mmm]# scp mmm_common.conf [email protected]:/etc/mysql-mmm/[[email protected] mysql-mmm]# scp mmm_common.conf [email protected]:/etc/mysql-mmm/[[email protected] mysql-mmm]# scp mmm_common.conf [email protected]:/etc/mysql-mmm/

在作為monitor伺服器的終端上配置

cd /etc/mysql-mmm/ #改密碼vim mmm_mon.conf<host default>    monitor_user        mmm_monitor    monitor_password    123456</host>

在所有資料庫上為mmm_agent授權

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

在所有資料庫上為mmm_moniter授權

MySQL [(none)]> grant replication client on *.* to ‘mmm_monitor‘@‘192.168.58.%‘ identified by ‘123456‘;#為mmm_monitor授權MySQL [(none)]> flush privileges#重新整理使用權限設定

修改所有資料庫的mmm_agent.conf

[[email protected] mysql-mmm]# vim /etc/mysql-mmm/mmm_agent.confinclude 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        #分別在四台資料庫伺服器上設定為db1,db2,db3,db4~                  

在所有資料庫伺服器上啟動mysql-mmm-agent

[[email protected] mysql-mmm]# systemctl start mysql-mmm-agent.service#啟動mmm-agent服務[[email protected] mysql-mmm]# systemctl enable mysql-mmm-agent.service#設定mmm-agent服務開機啟動

在monitor伺服器上配置

[[email protected] mysql-mmm]# cd /etc/mysql-mmm/[[email protected] 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    #指定監管的伺服器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   #設定監管賬戶    monitor_password    123456        #設定監管密碼</host>[[email protected] mysql-mmm]# systemctl start mysql-mmm-monitor.service   #啟動mysql-mmm-monitor[[email protected] 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/ONLINE. Roles: reader(192.168.58.200)  db4(192.168.58.147) slave/ONLINE. Roles: reader(192.168.58.210)

當我們把mysql-m3的mysql服務停掉以後，對應的VIP會自動綁定到mysql-m4上

[[email protected] 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高可用