架构图:

环境规划

角色

IP

VIP

用途

DB1

192.168.56.101

192.168.56.111/113

主Master提供wirte+read

DB2

192.168.56.102

192.168.56.112

备Master提供read

Monitor Server

192.168.56.103

NULL

负责所有的监控工作的监控守护进程,决定节点的移除等等

准备工作

所有节点上关闭iptables和selinux

~]# iptables -F

~]# iptables -t nat -F

~]# /etc/init.d/iptables stop

~]# chkconfig iptables off

~]# chkconfig --list iptables

iptables        0:off   1:off   2:off   3:off   4:off   5:off   6:off

~]# grep SELINUX= /etc/selinux/config |grep ^[^#]

SELINUX=disabled

软件环境准备

OS VersionRed Hat Enterprise Linux Server release 6.3 (Santiago)

MySQL Version: MySQL-server-5.6.21-1.el6.x86_64

MMM Version:mysql-mmm-2.2.1

MySQL DownLoad Path: http://dev.mysql.com/downloads/mysql/

MMM DownLoad Path:http://mysql-mmm.org/_media/:mmm2:mysql-mmm-2.2.1.tar.gz

前提:MySQL主主同步已经配置完成

DB1>show processlist\G

*************************** 2. row ***************************

Id: 6

User: slave

Host: 192.168.56.102:39971

db: NULL

Command: Binlog Dump

Time: 658

State: Master has sent all binlog to slave; waiting for binlog to be updated

Info: NULL

*************************** 3. row ***************************

Id: 7

User: system user

Host:

db: NULL

Command: Connect

Time: 135

State: Waiting for master to send event

Info: NULL

*************************** 4. row ***************************

Id: 8

User: system user

Host:

db: NULL

Command: Connect

Time: 135

State: Slave has read all relay log; waiting for the slave I/O thread to update it

Info: NULL

4 rows in set (0.00 sec)

DB2>show processlist\G

*************************** 1. row ***************************

Id: 1

User: system user

Host:

db: NULL

Command: Connect

Time: 778

State: Slave has read all relay log; waiting for the slave I/O thread to update it

Info: NULL

*************************** 2. row ***************************

Id: 2

User: system user

Host:

db: NULL

Command: Connect

Time: 778

State: Waiting for master to send event

Info: NULL

*************************** 4. row ***************************

Id: 20

User: slave

Host: 192.168.56.101:32870

db: NULL

Command: Binlog Dump

Time: 255

State: Master has sent all binlog to slave; waiting for binlog to be updated

Info: NULL

4 rows in set (0.00 sec)

注意

One should set read-only=1 in the confiuration of all MySQL servers, MMM will change
that to read-only=0 on the host with the active master role.

应该把所有节点设置为read_only模式,MMM将会修改active master为read-only=0提供wirte功能

MMM搭建步骤

1.安装Mysql-mmm

#所有节点都安装

~]# tar -zxvf mysql-mmm-2.2.1.tar.gz

~]# cd mysql-mmm-2.2.1

~]# make && make install

2.创建MMM代理和监控账号

#在DB1和DB2上分别执行

由于AB互为主从,故在一边执行、两边检查即可

#创建监控账号

DB1> GRANT REPLICATION CLIENT ON *.* TO 'mmm_monitor'@'192.168.56.%' IDENTIFIED BY 'monitor_password';

Query OK, 0 rows affected (0.02 sec)

#创建代理账号

DB1>GRANT SUPER,REPLICATION CLIENT,PROCESS ON *.* TO 'mmm_agent'@'192.168.56.%'  IDENTIFIED BY 'agent_password';

Query OK, 0 rows affected (0.00 sec)

DB1>flush privileges;

Query OK, 0 rows affected (0.03 sec)

#检查A和B两db上是否都存在监控和代理账号

DB1>select user,host,password from mysql.user where user in ('mmm_monitor','mmm_agent');

+-------------+--------------+-------------------------------------------+

| user        | host         | password                                  |

+-------------+--------------+-------------------------------------------+

| mmm_monitor | 192.168.56.% | *C1F414D9BAF378B656A849B31F9F8AF3125F558B |

| mmm_agent   | 192.168.56.% | *9404D503D0FAE1825CFEF873D0E7A38A26BBAB0F |

+-------------+--------------+-------------------------------------------+

2 rows in set (0.02 sec)

DB2>select user,host,password from mysql.user where user in ('mmm_monitor','mmm_agent');

+-------------+--------------+-------------------------------------------+

| user        | host         | password                                  |

+-------------+--------------+-------------------------------------------+

| mmm_monitor | 192.168.56.% | *C1F414D9BAF378B656A849B31F9F8AF3125F558B |

| mmm_agent   | 192.168.56.% | *9404D503D0FAE1825CFEF873D0E7A38A26BBAB0F |

+-------------+--------------+-------------------------------------------+

2 rows in set (0.00 sec)

3.配置mysql-mmm

所有的配置选项都集合在了一个叫/etc/mysql-mmm/mmm_common.conf的单独文件中,系统中所有主机的该文件内容都是一样的, 配置完后不要忘记了拷贝这个文件到所有的主机(包括监控主机)!

a)      mmm_common.conf

#在DB1上配置mmm_common.conf,并且拷贝给其他所有主机(包括监控主机)

~]# vim /etc/mysql-mmm/mmm_common.conf

active_master_role      writer

cluster_interface               eth2

pid_path                                /var/run/mmm_agentd.pid

bin_path                                /usr/lib/mysql-mmm/

replication_user       slave        #MySQL主主同步的帐号(这些要和前面设置的保持一致!)

replication_password   mysqlab              #同步的密码

agent_user      mmm_agent           #mmm-agent用户名

agent_password  agent_password  #mmm-agent密码

ip              192.168.56.101      #DB1的IP

mode            master

peer            db1

ip              192.168.56.102      #DB2的IP

mode            master

peer            db2

hosts                                   db1, db2

ips                                     192.168.56.113              #write VIP

mode                                    exclusive

hosts                                   db1, db2

ips                                     192.168.56.111,192.168.56.112           #Read VIP

mode                                    balanced

#拷贝给所有主机(包括监控主机)

~]# scp /etc/mysql-mmm/mmm_agent.conf 192.168.56.102:/etc/mysql-mmm/

~]# scp /etc/mysql-mmm/mmm_agent.conf 192.168.56.103:/etc/mysql-mmm/

b)     mmm_agent.conf

在MySQL DB主机(DB1和DB2)上,需要编辑/etc/mysql-mmm/mmm_agent.conf文件

#根据其他主机的不同更改db1的值(db2就将db1更改成db2)

~]# cat /etc/mysql-mmm/mmm_agent.conf

include mmm_common.conf

this db1             #DB2上此处为db2

c)      mmm_mon.conf

监控主机上我们需要编辑/etc/mysql-mmm/mmm_mon.conf文件

~]# vim /etc/mysql-mmm/mmm_mon.conf

include mmm_common.conf

ip                                      127.0.0.1

pid_path                                /var/run/mmm_mond.pid

bin_path                                /usr/lib/mysql-mmm/

status_path                             /var/lib/misc/mmm_mond.status

ping_ips                                192.168.56.101, 192.168.56.102         #监控服务器ip

auto_set_online                                             60

monitor_user                    mmm_monitor                #mmm_monitor用户名

monitor_password                RepMonitor          #mmm_monitor用户名

debug 0

4.启动MMM代理

#(在DB1和DB2主机上)编辑/etc/default/mysql-mmm-agent来开启

~]#  cat /etc/default/mysql-mmm-agent

ENABLED=1

#然后启动它:

~]# /etc/init.d/mysql-mmm-agent start

Daemon bin: '/usr/sbin/mmm_agentd'

Daemon pid: '/var/run/mmm_agentd.pid'

Starting MMM Agent daemon... Ok

5.启动MMM监控

#在监控主机上

~]# /etc/init.d/mysql-mmm-monitor start

Daemon bin: '/usr/sbin/mmm_mond'

Daemon pid: '/var/run/mmm_mond.pid'

Starting MMM Monitor daemon: Ok

6.设置开机自启动MMM服务

#DB节点

~]# chkconfig --add mysql-mmm-agent

~]# chkconfig mysql-mmm-agent on

~]# chkconfig --list mysql-mmm-agent

mysql-mmm-agent 0:off   1:off   2:on    3:on    4:on    5:on    6:off

#Monitor节点

~]# chkconfig --add mysql-mmm-monitor

~]# chkconfig  mysql-mmm-monitor on

~]# chkconfig --list mysql-mmm-monitor

mysql-mmm-monitor       0:off   1:off   2:on    3:on    4:on    5:on    6:off

7.查看监控状态

~]# mmm_control  show

  db1(192.168.56.101) master/ONLINE. Roles: reader(192.168.56.111), writer(192.168.56.113)

  db2(192.168.56.102) master/ONLINE. Roles: reader(192.168.56.112)

~]# mmm_control  checks

db2  ping         [last change: 2014/12/02 18:18:41]  OK

db2  mysql        [last change: 2014/12/02 18:18:41]  OK

db2  rep_threads  [last change: 2014/12/02 18:18:41]  OK

db2  rep_backlog  [last change: 2014/12/02 18:18:41]  OK: Backlog is null

db1  ping         [last change: 2014/12/02 18:18:41]  OK

db1  mysql        [last change: 2014/12/02 18:18:41]  OK

db1  rep_threads  [last change: 2014/12/02 18:18:41]  OK

db1  rep_backlog  [last change: 2014/12/02 18:18:41]  OK: Backlog is null

8.测试切换

#以下所有测试都是基于如下状态

~]# mmm_control show

db1(192.168.56.101) master/ONLINE. Roles: reader(192.168.56.112)

db2(192.168.56.102) master/ONLINE. Roles: reader(192.168.56.111), writer(192.168.56.113)

a)      停止DB1上MySQL服务

~]# tail -f /var/log/mysql-mmm/mmm_mond.log

tem error: 111

2014/12/03 10:37:50 ERROR Check 'mysql' on 'db1' has failed for 10 seconds! Message: ERROR: Connect error (host = 192.168.56.101:3306, user = mmm_monitor)! Lost connection to MySQL server at 'reading initial communication packet', system error: 111

2014/12/03 10:37:52 FATAL State of host 'db1' changed from ONLINE to HARD_OFFLINE (ping: OK, mysql: not OK)

2014/12/03 10:37:52  INFO Removing all roles from host 'db1':

2014/12/03 10:37:52  INFO     Removed role 'reader(192.168.56.112)' from host 'db1'

2014/12/03 10:37:52  INFO Orphaned role 'reader(192.168.56.112)' has been assigned to 'db2'

结果

~]# mmm_control show

  db1(192.168.56.101) master/HARD_OFFLINE. Roles:

  db2(192.168.56.102) master/ONLINE. Roles: reader(192.168.56.111), reader(192.168.56.112), writer(192.168.56.113)

DB1上的VIP【reader(192.168.56.112)】会自动迁移DB2

b)     停止DB2上MySQL服务

~]# tail -f /var/log/mysql-mmm/mmm_mond.log

2014/12/03 10:54:10 ERROR Check 'mysql' on 'db2' has failed for 10 seconds! Message: ERROR: Connect error (host = 192.168.56.102:3306, user = mmm_monitor)! Lost connection to MySQL server at 'reading initial communication packet', system error: 111

2014/12/03 10:54:10  INFO Check 'rep_backlog' on 'db1' is ok!

2014/12/03 10:54:11 FATAL State of host 'db2' changed from ONLINE to HARD_OFFLINE (ping: OK, mysql: not OK)

2014/12/03 10:54:11  INFO Removing all roles from host 'db2':

2014/12/03 10:54:11  INFO     Removed role 'reader(192.168.56.111)' from host 'db2'

2014/12/03 10:54:11  INFO     Removed role 'writer(192.168.56.113)' from host 'db2'

2014/12/03 10:54:11  INFO Orphaned role 'writer(192.168.56.113)' has been assigned to 'db1'

2014/12/03 10:54:11  INFO Orphaned role 'reader(192.168.56.111)' has been assigned to 'db1'

2014/12/03 10:54:29  INFO Check 'rep_threads' on 'db1' is ok!

2014/12/03 10:54:32  INFO Check 'rep_backlog' on 'db1' is ok!

~]# tail -f /var/log/mysql-mmm/mmm_agentd.log

2014/12/03 10:53:59 FATAL Couldn't allow writes: ERROR: Can't connect to MySQL (host = 192.168.56.102:3306, user = mmm_agent)! Lost connection to MySQL server at 'reading initial communication packet', system error: 111

2014/12/03 10:54:02 FATAL Couldn't allow writes: ERROR: Can't connect to MySQL (host = 192.168.56.102:3306, user = mmm_agent)! Lost connection to MySQL server at 'reading initial communication packet', system error: 111

2014/12/03 10:54:05 FATAL Couldn't allow writes: ERROR: Can't connect to MySQL (host = 192.168.56.102:3306, user = mmm_agent)! Lost connection to MySQL server at 'reading initial communication packet', system error: 111

2014/12/03 10:54:08 FATAL Couldn't allow writes: ERROR: Can't connect to MySQL (host = 192.168.56.102:3306, user = mmm_agent)! Lost connection to MySQL server at 'reading initial communication packet', system error: 111

2014/12/03 10:54:11  INFO We have some new roles added or old rules deleted!

2014/12/03 10:54:11  INFO Deleted: reader(192.168.56.111), writer(192.168.56.113)

2014/12/03 10:54:11 FATAL Couldn't deny writes: ERROR: Can't connect to MySQL (host = 192.168.56.102:3306, user = mmm_agent)! Lost connection to MySQL server at 'reading initial communication packet', system error: 111

结果

~]# mmm_control show

  db1(192.168.56.101) master/ONLINE. Roles: reader(192.168.56.111), reader(192.168.56.112), writer(192.168.56.113)

  db2(192.168.56.102) master/HARD_OFFLINE. Roles:

DB2上负责读的VIP(192.168.56.111) 以及负责写的VIP(192.168.56.113) 会自动迁移到 DB1

启动DB2上的MySQL服务

Monitor~]# tail -f /var/log/mysql-mmm/mmm_mond.log

2014/12/03 11:02:13  INFO Check 'mysql' on 'db2' is ok!

2014/12/03 11:02:14 FATAL State of host 'db2' changed from HARD_OFFLINE to AWAITING_RECOVERY

2014/12/03 11:02:14  WARN Check 'rep_backlog' on 'db1' is in unknown state! Message: UNKNOWN: Connect error (host = 192.168.56.101:3306, user = mmm_monitor)! Can't connect to MySQL server on '192.168.56.101' (4)

2014/12/03 11:02:19  INFO Check 'rep_backlog' on 'db1' is ok!

2014/12/03 11:02:24  WARN Check 'rep_threads' on 'db1' is in unknown state! Message: UNKNOWN: Connect error (host = 192.168.56.101:3306, user = mmm_monitor)! Can't connect to MySQL server on '192.168.56.101' (4)

2014/12/03 11:02:26  WARN Check 'rep_backlog' on 'db1' is in unknown state! Message: UNKNOWN: Connect error (host = 192.168.56.101:3306, user = mmm_monitor)! Can't connect to MySQL server on '192.168.56.101' (4)

2014/12/03 11:02:29  INFO Check 'rep_threads' on 'db1' is ok!

2014/12/03 11:02:31  INFO Check 'rep_backlog' on 'db1' is ok!

2014/12/03 11:02:41  WARN Check 'rep_threads' on 'db1' is in unknown state! Message: UNKNOWN: Connect error (host = 192.168.56.101:3306, user = mmm_monitor)! Can't connect to MySQL server on '192.168.56.101' (4)

2014/12/03 11:02:46  INFO Check 'rep_threads' on 'db1' is ok!

2014/12/03 11:02:54  WARN Check 'rep_backlog' on 'db1' is in unknown state! Message: UNKNOWN: Connect error (host = 192.168.56.101:3306, user = mmm_monitor)! Can't connect to MySQL server on '192.168.56.101' (4)

2014/12/03 11:02:59  INFO Check 'rep_backlog' on 'db1' is ok!

2014/12/03 11:03:04  WARN Check 'rep_threads' on 'db1' is in unknown state! Message: UNKNOWN: Connect error (host = 192.168.56.101:3306, user = mmm_monitor)! Can't connect to MySQL server on '192.168.56.101' (4)

2014/12/03 11:03:09  INFO Check 'rep_threads' on 'db1' is ok!

2014/12/03 11:03:14 FATAL State of host 'db2' changed from AWAITING_RECOVERY to ONLINE because of auto_set_online(60 seconds). It was in state AWAITING_RECOVERY for 60 seconds

2014/12/03 11:03:14  INFO Moving role 'reader(192.168.56.112)' from host 'db1' to host 'db2'

DB1~]# tail -f /var/log/mysql-mmm/mmm_agentd.log

2014/12/03 11:03:15  INFO We have some new roles added or old rules deleted!

2014/12/03 11:03:15  INFO Deleted: reader(192.168.56.112)

DB2~]# tail -f /var/log/mysql-mmm/mmm_agentd.log

2014/12/03 11:03:14  INFO We have some new roles added or old rules deleted!

2014/12/03 11:03:14  INFO Added:   reader(192.168.56.112)

2014/12/03 11:03:15 FATAL Couldn't configure IP '192.168.56.112' on interface 'eth1': undef

结果

~]# mmm_control show

  db1(192.168.56.101) master/ONLINE. Roles: reader(192.168.56.111), writer(192.168.56.113)

  db2(192.168.56.102) master/ONLINE. Roles: reader(192.168.56.112)

DB1上负责读的VIP(192.168.56.112)自动迁移到 DB2上,但是负责写的VIP,仍在DB1上。

c)      只读节点上stop slave

~]#  mmm_control show

  db1(192.168.56.101) master/ONLINE. Roles: reader(192.168.56.111), writer(192.168.56.113)

  db2(192.168.56.102) master/ONLINE. Roles: reader(192.168.56.112)

#DB2 stop slave

mysql> stop slave;

Query OK, 0 rows affected (0.07 sec)

Monitor~]# tail -f /var/log/mysql-mmm/mmm_mond.log

2014/12/03 12:10:14 FATAL State of host 'db2' changed from ONLINE to REPLICATION_FAIL

2014/12/03 12:10:14  INFO Removing all roles from host 'db2':

2014/12/03 12:10:14  INFO     Removed role 'reader(192.168.56.112)' from host 'db2'

2014/12/03 12:10:14  INFO Orphaned role 'reader(192.168.56.112)' has been assigned to 'db1'

2014/12/03 12:10:17 FATAL State of host 'db2' changed from REPLICATION_FAIL to ONLINE

2014/12/03 12:10:17  INFO Moving role 'reader(192.168.56.112)' from host 'db1' to host 'db2'

2014/12/03 12:10:24  INFO Check 'rep_threads' on 'db1' is ok!

2014/12/03 12:10:51  INFO Check 'rep_threads' on 'db1' is ok!

2014/12/03 12:11:15  INFO Check 'rep_backlog' on 'db1' is ok!

2014/12/03 12:11:20 FATAL State of host 'db2' changed from ONLINE to REPLICATION_FAIL

2014/12/03 12:11:20  INFO Removing all roles from host 'db2':

2014/12/03 12:11:20  INFO     Removed role 'reader(192.168.56.112)' from host 'db2'

2014/12/03 12:11:20  INFO Orphaned role 'reader(192.168.56.112)' has been assigned to 'db1'

2014/12/03 12:11:23 FATAL State of host 'db2' changed from REPLICATION_FAIL to ONLINE

2014/12/03 12:11:23  INFO Moving role 'reader(192.168.56.112)' from host 'db1' to host 'db2'

2014/12/03 12:11:54  INFO Check 'rep_backlog' on 'db1' is ok!

2014/12/03 12:12:22  INFO Check 'rep_backlog' on 'db1' is ok!

2014/12/03 12:12:26 FATAL State of host 'db2' changed from ONLINE to REPLICATION_FAIL

2014/12/03 12:12:26  INFO Removing all roles from host 'db2':

2014/12/03 12:12:26  INFO     Removed role 'reader(192.168.56.112)' from host 'db2'

2014/12/03 12:12:26  INFO Orphaned role 'reader(192.168.56.112)' has been assigned to 'db1'

2014/12/03 12:12:29 FATAL State of host 'db2' changed from REPLICATION_FAIL to ONLINE

2014/12/03 12:12:29  INFO Moving role 'reader(192.168.56.112)' from host 'db1' to host 'db2'

2014/12/03 12:12:30  INFO Check 'rep_threads' on 'db1' is ok!

2014/12/03 12:12:53  INFO Check 'rep_threads' on 'db1' is ok!

2014/12/03 12:13:30 FATAL State of host 'db2' changed from ONLINE to REPLICATION_FAIL

2014/12/03 12:13:30 FATAL Host db2 is flapping!

2014/12/03 12:13:30  INFO Removing all roles from host 'db2':

2014/12/03 12:13:30  INFO     Removed role 'reader(192.168.56.112)' from host 'db2'

2014/12/03 12:13:31  INFO Orphaned role 'reader(192.168.56.112)' has been assigned to 'db1'

2014/12/03 12:13:33 FATAL State of host 'db2' changed from REPLICATION_FAIL to AWAITING_RECOVERY (because it's flapping)

2014/12/03 12:13:45  INFO Check 'rep_threads' on 'db1' is ok!

结果

~]#  mmm_control show

  db1(192.168.56.101) master/ONLINE. Roles: reader(192.168.56.111), reader(192.168.56.112), writer(192.168.56.113)

  db2(192.168.56.102) master/AWAITING_RECOVERY. Roles:

DB2上的VIP192.168.56.112)会自动迁移到DB1

d)     读写节点上stop slave

#正常

~]#  mmm_control show

  db1(192.168.56.101) master/ONLINE. Roles: reader(192.168.56.111), writer(192.168.56.113)

  db2(192.168.56.102) master/ONLINE. Roles: reader(192.168.56.112)

#db1上stop slave

结果

~]#  mmm_control show

  db1(192.168.56.101) master/ONLINE. Roles: reader(192.168.56.111), writer(192.168.56.113)

  db2(192.168.56.102) master/ONLINE. Roles: reader(192.168.56.112)

无任何变化,理论上也应该是对现有的环境无任何影响

e)     停止MMM监控主机上的monitor服务

~]# /etc/init.d/mysql-mmm-monitor stop

Daemon bin: '/usr/sbin/mmm_mond'

Daemon pid: '/var/run/mmm_mond.pid'

Shutting down MMM Monitor daemon: .. Ok

Monitor~]# tail -f /var/log/mysql-mmm/mmm_mond.log

2014/12/03 14:09:28  INFO Signal received: exiting...

2014/12/03 14:09:28  INFO Shutting down checker 'ping_ip'...

2014/12/03 14:09:28  INFO Shutting down checker 'mysql'...

2014/12/03 14:09:29  INFO Shutting down checker 'rep_backlog'...

2014/12/03 14:09:29  INFO Shutting down checker 'rep_threads'...

2014/12/03 14:09:29  INFO Shutting down checker 'ping'...

2014/12/03 14:09:29  INFO END

2014/12/03 14:09:29  INFO Child exited normally (with exitcode 0), shutting down

结果

DB1 ~]# ip a|grep eth1

3: eth1: <broadcast,multicast,up,lower_up>mtu 1500 qdisc pfifo_fast state UP qlen 1000

    inet 192.168.56.101/24 brd 192.168.56.255 scope global eth1

    inet 192.168.56.111/32 scope global eth1

    inet 192.168.56.113/32 scope global eth1

DB2 ~]# ip a|grep eth1

3: eth1: <broadcast,multicast,up,lower_up>mtu 1500 qdisc pfifo_fast state UP qlen 1000

    inet 192.168.56.102/24 brd 192.168.56.255 scope global eth1

inet 192.168.56.112/32 scope global eth1

VIP都还在之前的节点上

启动MMM监控服务

~]#  mmm_control show

 db1(192.168.56.101) master/ONLINE. Roles: reader(192.168.56.111), writer(192.168.56.113)

   db2(192.168.56.102) master/ONLINE. Roles: reader(192.168.56.112)

结果

 DB1DB2上的相关服务无影响。

 

架构图

环境规划

角色

IP

VIP

用途

DB1

192.168.56.101

192.168.56.111/113

主Master提供wirte+read

DB2

192.168.56.102

192.168.56.112

备Master提供read

DB3&&Monitor Server

192.168.56.103

192.156.56.114

MMM监控节点&&MySQL slave节点

添加到MMM环境中

1)    配置DB3的Master为DB1

D> show slave status\G

*************************** 1. row ***************************

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.56.101

Master_User: slave

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: master_slave.000026

Read_Master_Log_Pos: 411

Relay_Log_File: MySQL_Manager-relay-bin.000002

Relay_Log_Pos: 286

Relay_Master_Log_File: master_slave.000026

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: 411

Relay_Log_Space: 467

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

Master_UUID: 64e874c1-6a35-11e4-bcc1-0800276fc92d

Master_Info_File: /data/mysql/master.info

SQL_Delay: 0

SQL_Remaining_Delay: NULL

Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it

Master_Retry_Count: 86400

Master_Bind:

Last_IO_Error_Timestamp:

Last_SQL_Error_Timestamp:

Master_SSL_Crl:

Master_SSL_Crlpath:

Retrieved_Gtid_Set:

Executed_Gtid_Set:

Auto_Position: 0

1 row in set (0.00 sec)

2)    创建MMM代理和监控账号

#创建监控账号

DB3> GRANT REPLICATION CLIENT ON *.* TO 'mmm_monitor'@'192.168.56.%' IDENTIFIED BY 'monitor_password';

Query OK, 0 rows affected (0.02 sec)

#创建代理账号

DB3>GRANT SUPER,REPLICATION CLIENT,PROCESS ON *.* TO 'mmm_agent'@'192.168.56.%'  IDENTIFIED BY 'agent_password';

Query OK, 0 rows affected (0.00 sec)

DB3>flush privileges;

Query OK, 0 rows affected (0.03 sec)

3)    添加DB3到MMM中

a)      mmm_common.conf

~]# cat /etc/mysql-mmm/mmm_common.conf

active_master_role      writer

cluster_interface               eth1

pid_path                                /var/run/mmm_agentd.pid

bin_path                                /usr/lib/mysql-mmm/

replication_user       slave

replication_password   mysqlab

agent_user      mmm_agent

agent_password  agent_password

ip              192.168.56.101

mode            master

peer            db1

ip              192.168.56.102

mode            master

peer            db2

ip              192.168.56.103

mode            slave

peer            db3

hosts                                   db1, db2

ips                                     192.168.56.113

mode                                    exclusive

hosts                                   db1, db2,db3

ips                                     192.168.56.111,192.168.56.112,192.168.56.114

mode                                    balanced

所有Node节点都修改为一致

b)     mmm_agent.conf

#DB3上编辑mmm_agent.conf

Monitor~]# cat /etc/mysql-mmm/mmm_agent.conf

include mmm_common.conf

this db3

c)      mmm_mon.conf

~]# cat /etc/mysql-mmm/mmm_mon.conf

include mmm_common.conf

ip                                              127.0.0.1

pid_path                                /var/run/mmm_mond.pid

bin_path                                /usr/lib/mysql-mmm/

status_path                             /var/lib/misc/mmm_mond.status

ping_ips                                192.168.56.101, 192.168.56.102,192.168.56.103

auto_set_online                           60

monitor_user                    mmm_monitor

monitor_password                monitor_password

debug 0

4)    启动MMM代理

DB1、DB2、DB3所有节点上启动agent服务

~]# /etc/init.d/mysql-mmm-agent start

Daemon bin: '/usr/sbin/mmm_agentd'

Daemon pid: '/var/run/mmm_agentd.pid'

Starting MMM Agent daemon... Ok

#在监控主机上

~]# /etc/init.d/mysql-mmm-monitor start

Daemon bin: '/usr/sbin/mmm_mond'

Daemon pid: '/var/run/mmm_mond.pid'

Starting MMM Monitor daemon: Ok

5)    查看状态

~]# mmm_control show

  db1(192.168.56.101) master/ONLINE. Roles: reader(192.168.56.111), writer(192.168.56.113)

  db2(192.168.56.102) master/ONLINE. Roles: reader(192.168.56.114)

  db3(192.168.56.103) slave/ONLINE. Roles: reader(192.168.56.112)

~]# mmm_control checks

db2  ping         [last change: 2014/12/03 14:45:49]  OK

db2  mysql        [last change: 2014/12/03 15:16:30]  OK

db2  rep_threads  [last change: 2014/12/03 14:45:49]  OK

db2  rep_backlog  [last change: 2014/12/03 14:45:49]  OK: Backlog is null

db3  ping         [last change: 2014/12/03 14:45:49]  OK

db3  mysql        [last change: 2014/12/03 15:16:58]  OK

db3  rep_threads  [last change: 2014/12/03 14:45:49]  OK

db3  rep_backlog  [last change: 2014/12/03 14:45:49]  OK: Backlog is null

db1  ping         [last change: 2014/12/03 14:45:49]  OK

db1  mysql        [last change: 2014/12/03 14:45:49]  OK

db1  rep_threads  [last change: 2014/12/03 14:45:49]  OK

db1  rep_backlog  [last change: 2014/12/03 14:45:49]  OK: Backlog is nulldb1  rep_threads  [last change: 2014/12/03 14:45:49]  OK

db1  rep_backlog  [last change: 2014/12/03 14:45:49]  OK: Backlog is null

6)    测试slave的Master转移

#正常

~]# mmm_control show

 db1(192.168.56.101) master/ONLINE. Roles: reader(192.168.56.112), writer(192.168.56.113)

  db2(192.168.56.102) master/ONLINE. Roles: reader(192.168.56.114)

  db3(192.168.56.103) slave/ONLINE. Roles: reader(192.168.56.111)

#103节点的master是101,接下来stop 101上的mysql在查看

#看监控节点mond.log

2014/12/10 20:21:28  WARN Check 'rep_threads' on 'db1' is in unknown state! Message: UNKNOWN: Connect error (host = 192.168.56.101:3306, user = mmm_monitor)! Lost connection to MySQL server at 'reading initial communication packet', system error: 111

2014/12/10 20:21:33  WARN Check 'rep_backlog' on 'db1' is in unknown state! Message: UNKNOWN: Connect error (host = 192.168.56.101:3306, user = mmm_monitor)! Lost connection to MySQL server at 'reading initial communication packet', system error: 111

2014/12/10 20:21:42 ERROR Check 'mysql' on 'db1' has failed for 10 seconds! Message: ERROR: Connect error (host = 192.168.56.101:3306, user = mmm_monitor)! Lost connection to MySQL server at 'reading initial communication packet', system error: 111

2014/12/10 20:21:45 FATAL State of host 'db1' changed from ONLINE to HARD_OFFLINE (ping: OK, mysql: not OK)

2014/12/10 20:21:45  INFO Removing all roles from host 'db1':

2014/12/10 20:21:45  INFO     Removed role 'reader(192.168.56.112)' from host 'db1'

2014/12/10 20:21:45  INFO     Removed role 'writer(192.168.56.113)' from host 'db1'

2014/12/10 20:21:45  INFO Orphaned role 'writer(192.168.56.113)' has been assigned to 'db2'

2014/12/10 20:21:45  INFO Orphaned role 'reader(192.168.56.112)' has been assigned to 'db3'

#查看103的master已经转移为102

从而实现了slave的master转移


 
 

最新文章

  1. 数据持久化以及DAO模式的简单使用
  2. springMVC中实现servlet依赖注入
  3. Android复习资料
  4. [转]CABasicAnimation用法
  5. jsp与Servlet
  6. [原创汉化]linux前端神器 WebStorm8 汉化
  7. Eclipse @override报错解决 必须覆盖超类方法
  8. iCloud之旅
  9. Android的理解
  10. as3文本框的动态拖拽和编辑
  11. Spring学习—生成图片验证码
  12. Hibernate (四)
  13. Mysql注入小tips --持续更新中
  14. python3-datetime.date详解(一)
  15. 谈谈Nginx-HTTPS加密技术
  16. c语言基础课第三次作业
  17. TNetHTTPClient 使用
  18. BootstrapValidator验证
  19. 压缩文件破解rarcrack-支持格式zip,rar和7z
  20. Cisco交换机堆叠与HSRP之间的区别

热门文章

  1. PCA本质和SVD
  2. (转载)robots.txt写法大全和robots.txt语法的作用
  3. 记录一下两个比较常用的md5加密算法
  4. JQuery 自动触发事件
  5. iOS开发UI篇—核心动画(基础动画)
  6. python基础之文件操作
  7. CentOS7安装Oracle 11gR2 安装
  8. Google之Chromium浏览器源码学习——base公共通用库(三)
  9. C语言头文件组织与包含原则
  10. 浅析python 中__name__ = &#39;__main__&#39; 的作用