Linux下的MySQL主主复制和Mysql-MMM实现(Mysql双主多从高可用)

叁歲伎倆 2022-06-16 02:55 267阅读 0赞

mysql主从架构中其实就一个主在工作，而从就相当于一个备份机器，从通过日志监测的方式来备份主库上的数据而保证主库的数据安全。在这种架构中如果从上的数据做了改变，主数据是不会用任何变化的。因为mysql主从架构主要是mysql从监控mysql主的日志变化来实现同步，相反的在这个架构中主并没有监控从的日志变化。所以，mysql从数据反生变化，主也就没有什么变化了。  
通过上述描述，可以看到如果想实现主主复制，无非就是在mysql主从架构上让mysql主实现监测从的日志变化，从而实现两台机器相互同步。(主从的架构前面有博文 [http://www.linuxidc.com/Linux/2013-10/91682.htm][http_www.linuxidc.com_Linux_2013-10_91682.htm] )  
实验环境：两台服务器：  
主机名：HA1,HA2(呵呵,这个主机名是英文缩写High availability，高可用的意思)  
ip:

192.168.1.231

192.168.1.232

主机系统：[CentOS][]6.4  
mysql版本5.5.22  
首先，看下HA1（192.168.1.231）的mysql配置文件  
vim /etc/my.cnf   
\# Example MySQL config file for very large systems.   
\#   
\# This is for a large system with memory of 1G-2G where the system runs mainly   
\# MySQL.   
\#   
\# MySQL programs look for option files in a set of   
\# locations which depend on the deployment platform.   
\# You can copy this option file to one of those   
\# locations. For information about these locations, see:   
\# http://dev.mysql.com/doc/mysql/en/option-files.html   
\#   
\# In this file, you can use all long options that a program supports.   
\# If you want to know which options a program supports, run the program   
\# with the "--help" option.   
\# The following options will be passed to all MySQL clients   
\[client\]   
\#password      = your\_password   
port            = 3306  
socket          = /usr/local/mysql/tmp/mysql.sock   
\# Here follows entries for some specific programs   
\# The MySQL server   
\[mysqld\]   
port            = 3306  
socket          = /usr/local/mysql/tmp/mysql.sock   
skip-external-locking   
key\_buffer\_size = 384M  
max\_allowed\_packet = 1M  
table\_open\_cache = 512  
sort\_buffer\_size = 2M  
read\_buffer\_size = 2M  
read\_rnd\_buffer\_size = 8M  
myisam\_sort\_buffer\_size = 64M  
thread\_cache\_size = 8  
query\_cache\_size = 32M  
\# Try number of CPU's\*2 for thread\_concurrency   
thread\_concurrency = 8  
\# Don't listen on a TCP/IP port at all. This can be a security enhancement,   
\# if all processes that need to connect to mysqld run on the same host.   
\# All interaction with mysqld must be made via Unix sockets or named pipes.   
\# Note that using this option without enabling named pipes on Windows   
\# (via the "enable-named-pipe" option) will render mysqld useless!   
\#   
\#skip-networking   
\# Replication Master Server (default)   
\# binary logging is required for replication   
log-bin=mysql-bin  
log-slave-updates   
\# required unique id between 1 and 2^32 - 1   
\# defaults to 1 if master-host is not set   
\# but will not function as a master if omitted   
server-id      = 1

在这个配置文件中，需要特别注意的三处地方：  
log-bin=mysql-bin：这个选项基本默认都是开着的，如果没有打开，可以手动打开。  
log-slave-updates：这个选项特别的重要它是为了让slave也能充当master，同时也为了更好的服务于 m-m + s 的环境，保证slave挂在任何一台master上都会接收到另一个master的写入信息。当然不局限于这个架构，级联复制的架构同样也需要log-slave-updates的支持。  
server-id = 1：这个ID为服务器ID如果配置一样会出现冲突，而不能复制  
接着再看下HA2（192.168.1.232）的mysql配置文件  
vim /etc/my.cnf   
\# Example MySQL config file for very large systems.   
\#   
\# This is for a large system with memory of 1G-2G where the system runs mainly   
\# MySQL.   
\#   
\# MySQL programs look for option files in a set of   
\# locations which depend on the deployment platform.   
\# You can copy this option file to one of those   
\# locations. For information about these locations, see:   
\# http://dev.mysql.com/doc/mysql/en/option-files.html   
\#   
\# In this file, you can use all long options that a program supports.   
\# If you want to know which options a program supports, run the program   
\# with the "--help" option.   
\# The following options will be passed to all MySQL clients   
\[client\]   
\#password      = your\_password   
port            = 3306  
socket          = /usr/local/mysql/tmp/mysql.sock   
\# Here follows entries for some specific programs   
\# The MySQL server   
\[mysqld\]   
port            = 3306  
socket          = /usr/local/mysql/tmp/mysql.sock   
skip-external-locking   
key\_buffer\_size = 384M  
max\_allowed\_packet = 1M  
table\_open\_cache = 512  
sort\_buffer\_size = 2M  
read\_buffer\_size = 2M  
read\_rnd\_buffer\_size = 8M  
myisam\_sort\_buffer\_size = 64M  
thread\_cache\_size = 8  
query\_cache\_size = 32M  
\# Try number of CPU's\*2 for thread\_concurrency   
thread\_concurrency = 8  
\# Don't listen on a TCP/IP port at all. This can be a security enhancement,   
\# if all processes that need to connect to mysqld run on the same host.   
\# All interaction with mysqld must be made via Unix sockets or named pipes.   
\# Note that using this option without enabling named pipes on Windows   
\# (via the "enable-named-pipe" option) will render mysqld useless!   
\#   
\#skip-networking   
\# Replication Master Server (default)   
\# binary logging is required for replication   
log-bin=mysql-bin  
log-slave-updates   
\# required unique id between 1 and 2^32 - 1   
\# defaults to 1 if master-host is not set   
\# but will not function as a master if omitted   
server-id      = 10  
\# Replication Slave (comment out master section to use this)

在HA2的mysql配置文件中，除了server-id不一样，其他几乎一模一样。配置文件写好后，我们把两台服务器上的mysql服务器启动起来。

首先，登录HA2（192.168.1.232）的mysql中，查看master状态  
mysql> show master status;   
\+------------------+----------+--------------+------------------+  
| File            | Position | Binlog\_Do\_DB | Binlog\_Ignore\_DB |   
\+------------------+----------+--------------+------------------+  
| mysql-bin.000016 |      615 |              |                  |   
\+------------------+----------+--------------+------------------+  
1 row in set (0.00 sec)   
mysql>

然后，登录HA1（192.168.1.231）的msyql中，把HA2配置成自己的主，

在做这个之前先在两台机器的mysql中建立一个可以复制用的帐号：  
mysql>grant all on \*.\* to duyunlong@'192.168.1.%' identified by '123456';   
Query OK, 0 rows affected (0.01 sec)   
mysql>change master to master\_host='192.168.1.232',master\_user='duyunlong',master\_password='123456',master\_log\_file='mysql-bin.000016',master\_log\_pos=615;

同上，查看HA1(192.168.1.231)master，然后登录HA2(192.168.1.232),把HA1(192.168.1.231)，配置成自己的主，然后分别在两台机器的mysql中，启动slave  
启动后HA1状态  
mysql> show slave status \\G;   
\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 1. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*  
              Slave\_IO\_State: Waiting for master to send event   
                  Master\_Host: 192.168.1.232  
                  Master\_User: duyunlong   
                  Master\_Port: 3306  
                Connect\_Retry: 60  
              Master\_Log\_File: mysql-bin.000016  
          Read\_Master\_Log\_Pos: 615  
              Relay\_Log\_File: HA1-relay-bin.000002  
                Relay\_Log\_Pos: 346  
        Relay\_Master\_Log\_File: mysql-bin.000016  
            Slave\_IO\_Running: Yes   
            Slave\_SQL\_Running: Yes   
              Replicate\_Do\_DB:   
          Replicate\_Ignore\_DB:   
          Replicate\_Do\_Table:   
      Replicate\_Ignore\_Table:   
      Replicate\_Wild\_Do\_Table:   
  Replicate\_Wild\_Ignore\_Table:   
                  Last\_Errno: 0  
                  Last\_Error:   
                Skip\_Counter: 0  
          Exec\_Master\_Log\_Pos: 615  
              Relay\_Log\_Space: 500  
              Until\_Condition: None  
              Until\_Log\_File:   
                Until\_Log\_Pos: 0  
          Master\_SSL\_Allowed: No   
          Master\_SSL\_CA\_File:   
          Master\_SSL\_CA\_Path:   
              Master\_SSL\_Cert:   
            Master\_SSL\_Cipher:   
              Master\_SSL\_Key:   
        Seconds\_Behind\_Master: 0  
Master\_SSL\_Verify\_Server\_Cert: No   
                Last\_IO\_Errno: 0  
                Last\_IO\_Error:   
              Last\_SQL\_Errno: 0  
              Last\_SQL\_Error:   
  Replicate\_Ignore\_Server\_Ids:   
            Master\_Server\_Id: 10  
1 row in set (0.00 sec)   
ERROR:   
No query specified

可以看到 Slave\_IO\_Running: Yes  
Slave\_SQL\_Running: Yes  
然后在看HA2的状态：  
mysql> show slave status \\G;   
\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 1. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*  
              Slave\_IO\_State: Waiting for master to send event   
                  Master\_Host: 192.168.1.231  
                  Master\_User: duyunlong   
                  Master\_Port: 3306  
                Connect\_Retry: 60  
              Master\_Log\_File: mysql-bin.000018  
          Read\_Master\_Log\_Pos: 552  
              Relay\_Log\_File: HA2-relay-bin.000002  
                Relay\_Log\_Pos: 441  
        Relay\_Master\_Log\_File: mysql-bin.000018  
            Slave\_IO\_Running: Yes   
            Slave\_SQL\_Running: Yes   
              Replicate\_Do\_DB:   
          Replicate\_Ignore\_DB:   
          Replicate\_Do\_Table:   
      Replicate\_Ignore\_Table:   
      Replicate\_Wild\_Do\_Table:   
  Replicate\_Wild\_Ignore\_Table:   
                  Last\_Errno: 0  
                  Last\_Error:   
                Skip\_Counter: 0  
          Exec\_Master\_Log\_Pos: 552  
              Relay\_Log\_Space: 595  
              Until\_Condition: None  
              Until\_Log\_File:   
                Until\_Log\_Pos: 0  
          Master\_SSL\_Allowed: No   
          Master\_SSL\_CA\_File:   
          Master\_SSL\_CA\_Path:   
              Master\_SSL\_Cert:   
            Master\_SSL\_Cipher:   
              Master\_SSL\_Key:   
        Seconds\_Behind\_Master: 0  
Master\_SSL\_Verify\_Server\_Cert: No   
                Last\_IO\_Errno: 0  
                Last\_IO\_Error:   
              Last\_SQL\_Errno: 0  
              Last\_SQL\_Error:   
  Replicate\_Ignore\_Server\_Ids:   
            Master\_Server\_Id: 1  
1 row in set (0.00 sec)   
ERROR:   
No query specified

可以看到Slave\_IO\_Running: Yes  
Slave\_SQL\_Running: Yes  
接下来，我们要测试，是不是已经可以主主复制了呢，首先登录HA1(192.168.1.231)的mysql中，建立一数据库，当然在测试前我们先看下，两台服务器中的mysql中有哪些数据  
首先看下HA1(192.168.1.231)  
\[root@HA1 ~\]\# mysql mysql -uduyunlong -p123456 -h192.168.1.231 -e 'show databases;'   
\+--------------------+  
| Database          |   
\+--------------------+  
| information\_schema |   
| mysql              |   
| performance\_schema |   
| test              |   
| wanghaipeng        |   
\+--------------------+  
\[root@HA1 ~\]\#

在HA1（192.168.1.231）可以看到数据库“b”已经复制过来了。  
那么到此，主主复制架构已经陈功！

Mysql-MMM实现(Mysql双主多从高可用)   
  
  
一．mysql-mmm实现mysql 高可用架构   
  
     MMM 即Master-Master Replication Manager for MySQL（mysql 主主复制管理器）关于 mysql主主复制配置的监控、故障转移和管理的一套可伸缩的脚本套件（在任何时候只有一个节点可以被写入），这个套件也能对居于标准的主从配置的任意数量的从服务器进行读负载均衡，所以你可以用它来在一组居于复制的服务器启动虚拟 ip，除此之外，它还有实现数据备份、节点之间重新同步功能的脚本。  
   
     MySQL 本身没有提供 replication failover 的解决方案，通过 MMM 方案能实现服务器的故障转移，从而实现 mysql 的高可用。MMM 项目来自 Google：http://code.google.com/p/mysql-master-master官方网站为：http://mysql-mmm.org   
  
  
mysql-mmm     主要功能由下面三个脚本提供  
   
mmm\_mond     负责所有的监控工作的监控守护进程，决定节点的移除等等   
  
mmm\_agentd   运行在 mysql 服务器上的代理守护进程，通过简单远程服务集提供给监控节点  
   
mmm\_control   通过命令行管理 mmm\_mond 进程   
  
mysql-mmm 的监管端会提供多个虚拟 IP（VIP），包括一个可写 VIP，多个可读 VIP， 通过监管的管理，这些 IP 会绑定在可用 mysql 之上，当某一台 mysql 宕机时，监管会将 VIP迁移至其他 mysql。   
  
  
在整个监管过程中，需要在 mysql 中添加相关授权用户，以便让 mysql 可以支持监理机的维护。 授权的用户包括一个 mmm\_monitor 用户和一个 mmm\_agent 用户，如果想使用 mmm的备份工具则还要添加一个 mmm\_tools 用户。  
  
部署开始，由于机器资源有限，这里的实验，slave 就用一台了。   
  
二．部署的前期工作   
1．环境描述   
vmvare 虚拟机：4 台   
  
系统版本：CentOS release 6.6 (Final)  2.6.32-504.el6.x86\_64   
  
mysql版本：5.5.32   
  
mysql-mmm版本：   
  
4台虚拟机信息：  
  
MMM管理机:192.168.0.149  Monitor      test-A  
  
master1:192.168.0.150    server-id=1  test-B  
  
master2:192.168.0.160    server-id=3  test-D  
  
slave:192.168.0.151      server-id=2  test-C  
  
  
虚拟IP：  
10.0.0.13   write  
10.0.0.14   read  
10.0.0.15   read  
10.0.0.16   read  
  
  
Mysql-MMM 架构配置简介:   
  
1.master1, master2 两台安装 mysql,并做主主的配置   
  
2.slave1 上安装 mysql,并配置作为 master1 的从服务器。  
   
3.master1/2, slave1,Monitor 这四台都要安装 mysql-mmm,并配置：mmm\_common.conf、mmm\_agent.conf 以及 mmm\_mon.conf 文件   
  
  
三、配置 mysql-master-1/2（主主同步),mysql-master-1 与 mysql-slave（主从同步）   
  
注：所有的mysql都是新安装的，所以没有任何数据，环境相同。   
  
1.1、 改my.cnf然后重启服务   
mysql-master-1:   
  
\[mysqld\]  
server-id       = 1  
log-bin=mysql-bin  
log-slave-updates    
auto\_increment\_offset=1    
auto\_increment\_increment=2  
  
  
mysql-master-2:   
  
\[mysqld\]  
server-id       = 3  
log-bin=mysql-bin  
log-slave-updates    
auto\_increment\_offset=2    
auto\_increment\_increment=2  
  
  
1.2、 配置master1和master2 做主主同步   
  
master1 和 master2 都需要创建链接用户   
mysql> grant replication slave on \*.\* to 'rep'@'192.168.0.%' identified by   
'test123';   
Query OK, 0 rows affected (0.02 sec)   
  
  
master1操作：  
mysql>  show master status;  
\+------------------+----------+--------------+---------------------------------------------+  
| File             | Position | Binlog\_Do\_DB | Binlog\_Ignore\_DB                            |  
\+------------------+----------+--------------+---------------------------------------------+  
| mysql-bin.000006 |      107 |              | mysql,performance\_schema,information\_schema |  
\+------------------+----------+--------------+---------------------------------------------+  
1 row in set (0.00 sec)  
   
master2操作：  
mysql>   change master to  master\_host='192.168.0.150',  master\_port=3306,  master\_user='rep',  master\_password='test123',  master\_log\_file='mysql-bin.000006', master\_log\_pos=107;  
mysql> start slave;  
mysql> show slave status\\G;  
\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 1. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*  
               Slave\_IO\_State: Waiting for master to send event  
                  Master\_Host: 192.168.0.150  
                  Master\_User: rep  
                  Master\_Port: 3306  
                Connect\_Retry: 60  
              Master\_Log\_File: mysql-bin.000006  
          Read\_Master\_Log\_Pos: 107  
               Relay\_Log\_File: test-D-relay-bin.000006  
                Relay\_Log\_Pos: 253  
        Relay\_Master\_Log\_File: mysql-bin.000006  
             Slave\_IO\_Running: Yes  
            Slave\_SQL\_Running: Yes  
              Replicate\_Do\_DB:   
          Replicate\_Ignore\_DB:   
           Replicate\_Do\_Table:   
       Replicate\_Ignore\_Table:   
      Replicate\_Wild\_Do\_Table:   
  Replicate\_Wild\_Ignore\_Table:   
                   Last\_Errno: 0  
                   Last\_Error:   
                 Skip\_Counter: 0  
          Exec\_Master\_Log\_Pos: 107  
              Relay\_Log\_Space: 410  
              Until\_Condition: None  
               Until\_Log\_File:   
                Until\_Log\_Pos: 0  
           Master\_SSL\_Allowed: No  
           Master\_SSL\_CA\_File:   
           Master\_SSL\_CA\_Path:   
              Master\_SSL\_Cert:   
            Master\_SSL\_Cipher:   
               Master\_SSL\_Key:   
        Seconds\_Behind\_Master: 0  
Master\_SSL\_Verify\_Server\_Cert: No  
                Last\_IO\_Errno: 0  
                Last\_IO\_Error:   
               Last\_SQL\_Errno: 0  
               Last\_SQL\_Error:   
  Replicate\_Ignore\_Server\_Ids:   
             Master\_Server\_Id: 1  
1 row in set (0.00 sec)  
  
  
ERROR:   
No query specified  
  
mysql>  grant replication slave on \*.\* to 'rep'@'192.168.0.%' identified by 'test123';  
mysql>  show master status;                                  +------------------+----------+--------------+------------------+  
| File             | Position | Binlog\_Do\_DB | Binlog\_Ignore\_DB |  
\+------------------+----------+--------------+------------------+  
| mysql-bin.000003 |      107 |              |                  |  
\+------------------+----------+--------------+------------------+  
1 row in set (0.00 sec)  
  
  
master1操作：  
mysql>  change master to master\_host='192.168.0.160', master\_port=3306,  master\_user='rep', master\_password='test123',  master\_log\_file='mysql-bin.000003', master\_log\_pos=107;  
mysql> start slave;  
mysql> show slave status\\G;  
  
2.1、  slave修改my.cnf并重启服务   
  
vi /data/3307/my.cnf   
  
\[mysqld\]   
server-id = 2   
\[root@test-C ~\]\#  mysqladmin -uroot -p456 shutdown -S /data/3307/mysql.sock  
  
\[root@test-C ~\]\#  /application/mysql/bin/mysqld\_safe --defaults-file=/data/3307/my.cnf &  
  
注：从上我用的是多实例  
  
  
2.2、  配置同步参数   
  
查看 master1 主库的记录点信息   
  
flush tables with read lock;     \#锁表   
  
mysql>  show master status;   
  
\+------------------+----------+--------------+---------------------------------------------+  
| File             | Position | Binlog\_Do\_DB | Binlog\_Ignore\_DB                            |  
\+------------------+----------+--------------+---------------------------------------------+  
| mysql-bin.000006 |      107 |              | mysql,performance\_schema,information\_schema |  
\+------------------+----------+--------------+---------------------------------------------+  
1 row in set (0.00 sec)  
  
mysqldump -uroot --event -A -B >/tmp/master1.sql   \# 备份主库   
  
unlock tables;   \#解锁   
  
  
slave 从库进行操作:  
  
mysql -uroot <master1.sql -s="" data="" 3307="" mysql.sock<="" strong="" style="word-wrap: break-word;">   
  
mysql> CHANGE MASTER TO MASTER\_HOST='192.168.0.150',  MASTER\_PORT=3306, MASTER\_USER='rep', MASTER\_PASSWORD='test123', MASTER\_LOG\_FILE='mysql-bin.000006',  MASTER\_LOG\_POS=107;   
  
Query OK, 0 rows affected (0.04 sec)   
   
mysql> start slave;   
Query OK, 0 rows affected (0.02 sec)   
   
mysql> show slave status\\G;   
   
四、配置mysql-mmm   
  
4.1、 安装mysql-mmm   
  
注：需要在这四台 server 上都安装 mysql-mmm   
  
CentOS 软件仓库默认是不含这些软件的，必须要有epel这个包的支持。所以我们必须先安装epel。   
  
四台同时操作:  
cd tools   
wget http://dl.fedoraproject.org/pub/epel/6/i386/epel-release-6-8.noarch.rpm   
  
rpm -Uvh epel-release-6-8.noarch.rpm    
  
yum install -y mysql-mmm\*   
  
  
   
4.2、 配置mmm代理和监控账号的权限   
  
现在环境已经配置好，我没有配置忽略 mysql库和 user表，所以只要在任意一台主库上执行下面的操作，其他的库就都有这俩账号了。   
  
mysql> GRANT REPLICATION CLIENT ON \*.\* TO 'mmm\_monitor'@'192.168.0.%' IDENTIFIED  BY 'test123';  
  
mysql> GRANT SUPER, REPLICATION CLIENT, PROCESS ON \*.\* TO 'mmm\_agent'@'192.168.0.%' IDENTIFIED BY 'test123';    
      
Query OK, 0 rows affected (0.00 sec)  
  
mysql> flush privileges;   
  
Query OK, 0 rows affected (0.06 sec)  
  
注：master1、master2、slave 是一样的   
  
mysql> select user,host from mysql.user;   
\+-------------+-------------+  
| user        | host        |  
\+-------------+-------------+  
| root        | 127.0.0.1   |  
| mmm\_agent   | 192.168.0.% |  
| mmm\_monitor | 192.168.0.% |  
| rep         | 192.168.0.% |  
| root        | ::1         |  
|             | localhost   |  
| root        | localhost   |  
|             | test-B      |  
| root        | test-B      |  
\+-------------+-------------+  
8 rows in set (0.00 sec)  
        
   
4.3、 所有服务器均需配置/etc/mysql-mmm/mmm\_common.conf   
  
vi /etc/mysql-mmm/mmm\_common.conf       
  
active\_master\_role      writer  
  
  
    cluster\_interface            eth0  
    pid\_path                      /var/run/mysql-mmm/mmm\_agentd.pid  
    bin\_path                      /usr/libexec/mysql-mmm/  
    replication\_user            rep  
    replication\_password     test123  
    agent\_user                   mmm\_agent  
    agent\_password            test123  
  
  
  
    ip      192.168.0.150  
    mode    master  
    peer    db2  
  
  
  
    ip      192.168.0.160  
    mode    master  
    peer    db1  
  
  
  
  
    ip      192.168.0.151  
    mode    slave  
  
  
  
    hosts   db1, db2  
    ips     10.0.0.13  
    mode    exclusive  
  
  
  
  
    hosts   db1, db2, db3  
    ips     10.0.0.14, 10.0.0.15, 10.0.0.16  
    mode    balanced  
  
  
  
   
4.4、数据库主机配置  
  
vim /etc/mysql-mmm/mmm\_agent.conf   
  
hostname      ip  my.cnf -serverid  dbname   
master1  192.168.0.150      1         db1   
master2  192.168.0.160      3         db2   
slave1   192.168.0.151      2         db3   
  
根据上表对三台 mysql服务器的/etc/mysql-mmm/mmm\_agent.conf 配置文件进行修改   
例：   
\[root@mysql-mmm-master1 tools\]\#  
  
vi /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   
   
4.5、 monitor主机配置  
  
vim /etc/mysql-mmm/mmm\_mon.conf   
  
include mmm\_common.conf  
  
  
    ip                  127.0.0.1  
    pid\_path            /var/run/mysql-mmm/mmm\_mond.pid  
    bin\_path            /usr/libexec/mysql-mmm  
    status\_path         /var/lib/mysql-mmm/mmm\_mond.status  
    ping\_ips            192.168.0.150, 192.168.0.151, 192.16  
8.0.160  
    auto\_set\_online     30  
  
  
    \# The kill\_host\_bin does not exist by default, though th  
e monitor will  
    \# throw a warning about it missing.  See the section 5.1  
0 "Kill Host   
    \# Functionality" in the PDF documentation.  
    \#  
    \# kill\_host\_bin     /usr/libexec/mysql-mmm/monitor/kill\_  
host  
    \#  
  
  
  
    monitor\_user        mmm\_monitor  
    monitor\_password    test123  
  
  
debug 0  
  
  
4.6、 启动mysql-mmm   
master-1,master-2,slave 启动代理：   
  
编辑/etc/default/mysql-mmm-agent 来开启   
  
\[root@mysql-mmm-master2 tools\]\# vi /etc/default/mysql-mmm-agent   
  
\# mysql-mmm-agent defaults   
ENABLED=1   
  
所有数据库主机启动 mmm-agent:   
  
/etc/init.d/mysql-mmm-agent start   
  
monitor 主机启动 mmm-monitor   
  
/etc/init.d/mysql-mmm-monitor start   
   
4.7、 mmm\_control命令监控mysql 服务器状态   
  
\[root@mysql-mmm-monitor ~\]\# mmm\_control show   
  db1(192.168.0.150) master/ONLINE. Roles: reader(10.0.0.15), writer(10.0.0.13)  
  db2(192.168.0.160) master/ONLINE. Roles: reader(10.0.0.14)  
  db3(192.168.0.151) slave/ONLINE. Roles: reader(10.0.0.16)  
   
4.8、 测试两个mysql服务器能否实现故障自动切换   
将db1的mysql服务停止   
  
\[root@test-B ~\]\# /etc/init.d/mysqld stop  
Shutting down MySQL. SUCCESS!   
等待30秒在 mysql-mmm-monitor 服务器上进行监控查看   
  
\[root@test-A ~\]\# mmm\_control show  
  db1(192.168.0.150) master/HARD\_OFFLINE. Roles:   
  db2(192.168.0.160) master/ONLINE. Roles: reader(10.0.0.14), writer(10.0.0.13)  
  db3(192.168.0.151) slave/ONLINE. Roles: reader(10.0.0.15), reader(10.0.0.16)  
  
slave检查master\_host 是否切换到了另一个主库地址：  
\[root@test-C ~\]\# mysql -uroot -p -e "show slave status\\G" -S /data/3307/mysql.sock                      
Enter password:   
\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 1. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*  
               Slave\_IO\_State: Waiting for master to send event  
                  Master\_Host: 192.168.0.160  
                  Master\_User: rep  
                  Master\_Port: 3306  
                Connect\_Retry: 60  
              Master\_Log\_File: mysql-bin.000003  
          Read\_Master\_Log\_Pos: 537  
               Relay\_Log\_File: relay-bin.000002  
                Relay\_Log\_Pos: 253  
        Relay\_Master\_Log\_File: mysql-bin.000003  
             Slave\_IO\_Running: Yes  
            Slave\_SQL\_Running: Yes  
              Replicate\_Do\_DB:   
          Replicate\_Ignore\_DB: mysql  
           Replicate\_Do\_Table:   
       Replicate\_Ignore\_Table:   
      Replicate\_Wild\_Do\_Table:   
  Replicate\_Wild\_Ignore\_Table:   
                   Last\_Errno: 0  
                   Last\_Error:   
                 Skip\_Counter: 0  
          Exec\_Master\_Log\_Pos: 537  
              Relay\_Log\_Space: 403  
              Until\_Condition: None  
               Until\_Log\_File:   
                Until\_Log\_Pos: 0  
           Master\_SSL\_Allowed: No  
           Master\_SSL\_CA\_File:   
           Master\_SSL\_CA\_Path:   
              Master\_SSL\_Cert:   
            Master\_SSL\_Cipher:   
               Master\_SSL\_Key:   
        Seconds\_Behind\_Master: 0  
Master\_SSL\_Verify\_Server\_Cert: No  
                Last\_IO\_Errno: 0  
                Last\_IO\_Error:   
               Last\_SQL\_Errno: 0  
               Last\_SQL\_Error:   
  Replicate\_Ignore\_Server\_Ids:   
             Master\_Server\_Id: 3  
  
  
恢复master-1(db1)   
\[root@test-B ~\]\# /etc/init.d/mysqld start  
Starting MySQL.. SUCCESS!   
monitor端检查恢复情况   
\[root@test-A ~\]\# mmm\_control show  
  db1(192.168.0.150) master/ONLINE. Roles: reader(10.0.0.16)  
  db2(192.168.0.160) master/ONLINE. Roles: reader(10.0.0.14), writer(10.0.0.13)  
  db3(192.168.0.151) slave/ONLINE. Roles: reader(10.0.0.15)  
  
可以看到当 db1 恢复后就充当 slave 的角色了！只有当 db2 挂了以后db1 又会担当起主服务器的写入功能。  
  
4.9、 mmm\_control命令介绍  
Valid commands are:  
    help                              - show this message  
    ping                              - ping monitor  
    show                              - show status  
    checks \[|all \[|all\]\] - show checks status  
    set\_online                 - set host online  
    set\_offline                - set host offline  
    mode                              - print current mode.  
    set\_active                        - switch into active mode.  
    set\_manual                        - switch into manual mode.  
    set\_passive                       - switch into passive mode.  
    move\_role \[--force\] - move exclusive role to host   
                                        (Only use --force if you know what you are doing!)  
    set\_ip                - set role with ip to host   
  
  
五、配置过程中我遇到的一些问题和解决方法  
问题：  
配置过程中，到最后查看所有服务器状态，从服务器不在状态：  
\[root@test-A ~\]\# mmm\_control show  
\[root@test-A ~\]\# mmm\_control show  
  db1(192.168.0.150) master/ONLINE. Roles: reader(10.0.0.15), writer(10.0.0.13)  
  db2(192.168.0.160) master/ONLINE. Roles: reader(10.0.0.14), reader(10.0.0.16)  
  db3(192.168.0.151) slave/HARD\_OFFLINE. Roles:   
如上，解决方法：  
    从服务器上的mysql，原来做测试时，用的多实例，mysql服务端口为3307。停掉从服务器上的主从，然后在配置文件中把端口改为3306，重启服务。重新做一下主从同步后，重新启动MMM的代理服务后，再次在MMM管理端查看所有服务器状态，已全部正常，如下：  
\[root@test-A ~\]\# mmm\_control show                      
  db1(192.168.0.150) master/ONLINE. Roles: reader(10.0.0.15), writer(10.0.0.13)  
  db2(192.168.0.160) master/ONLINE. Roles: reader(10.0.0.14)  
  db3(192.168.0.151) slave/ONLINE. Roles: reader(10.0.0.16)

[http_www.linuxidc.com_Linux_2013-10_91682.htm]: http://www.linuxidc.com/Linux/2013-10/91682.htm
[CentOS]: http://www.linuxidc.com/topicnews.aspx?tid=14