mmm实现mysql高可用

原创

冰冻火焰 2015-09-24 09:17:46 博主文章分类：mysql ©著作权

文章标签 mysql mmm 文章分类 服务器

©著作权归作者所有：来自51CTO博客作者冰冻火焰的原创作品，请联系作者获取转载授权，否则将追究法律责任

一 mysql主从搭建
略

一设置hosts解析
[root@localhost conf]# vi /etc/hosts （三台服务器相同都添加下面三项）
192.168.31.2 M1
192.168.31.3 M2
192.168.31.4 slave1

二设置各台服务器的名字分别与hosts文件中的对应
略

三创建相应的帐号
GRANT REPLICATION SLAVE , REPLICATION CLIENT ON * . * TO 'mmm_slave'@'192.168.31.%' IDENTIFIED BY '123456'; （用于主从和主主复制 M1，M2 上都添加这个用户）
flush privileges;

四 M1 M2 配置成主主模式
M1 配置文件
[mysqld]
innodb_file_per_table=1
log-bin=/data/mysql/mysql-bin.log
binlog_format           = ROW
server-id=1
binlog-do-db=mmm_test
replicate-do-db=mmm_test

M2配置文件
[mysqld]
innodb_file_per_table=1
log-bin=/data/mysql/mysql-bin.log
binlog_format           = ROW
server-id=2
binlog-do-db=mmm_test
replicate-do-db=mmm_test

在M2上执行
change master to master_host='192.168.31.2',master_user='mmm_slave',master_password='123456',master_log_file='mysql-bin.000017',master_log_pos=326;
start slave;

在M1 上执行 (主主模型)
change master to master_host='192.168.31.3',master_user='mmm_slave',master_password='123456',master_log_file='mysql-bin.000001',master_log_pos=312;
start slave;

五配置M1和slave1 主从
slave1 配置文件
[mysqld]
server-id =3
innodb_file_per_table=1
log-bin=/data/mysql/mysql-bin.log
binlog_format           = ROW
replicate-do-db=mmm_test

执行
change master to master_host='192.168.31.2',master_user='mmm_slave',master_password='123456',master_log_file='mysql-bin.000017',master_log_pos=326;
start slave;

六安装MySQL-MMM (三台服务器执行结果相同)
[root@M1 software]# wget http://download.fedoraproject.org/pub/epel/5/x86_64/epel-release-5-4.noarch.rpm
[root@M1 software]# rpm -ivh epel-release-5-4.noarch.rpm
[root@M1 software]# yum -y install mysql-mmm*
[root@M1 software]# rpm -qa | grep mysql-mmm
mysql-mmm-2.2.1-2.el6.noarch
mysql-mmm-agent-2.2.1-2.el6.noarch
mysql-mmm-tools-2.2.1-2.el6.noarch
mysql-mmm-monitor-2.2.1-2.el6.noarch

七在三个节点上授权

GRANT PROCESS,SUPER,REPLICATION CLIENT ON * . * TO 'mmm_agent'@'192.168.31.%' IDENTIFIED BY '123456'; (代理帐号，是MMM代理用来变成只读模式和同步master等的)
GRANT REPLICATION CLIENT ON * . * TO 'mmm_monitor'@'192.168.31.%' IDENTIFIED BY '123456'; (监听帐号，是MMM监控服务器用来对MySQL服务器做健康检查的)
flush privileges;

八配置 MMM 监控、代理服务
   1 三台服务器上修改mmm_common.conf (三台服务器上执行步骤相同)
   [root@M1 conf]# cd /etc/mysql-mmm/
   [root@M1 mysql-mmm]# cp mmm_common.conf mmm_common.conf.bak
   [root@M1 mysql-mmm]# vi mmm_common.conf
   active_master_role      writer

   <host default>
       cluster_interface       eth1 # 这里视情况而定我的三台服务器这里不相同
       pid_path                /var/run/mysql-mmm/mmm_agentd.pid
       bin_path                /usr/libexec/mysql-mmm/
       replication_user        mmm_slave # 前面创建的复制帐号
       replication_password    123456   # 复制帐号的密码
       agent_user              mmm_agent # 前面创建的代理帐号
       agent_password          123456   # 代理帐号的密码
   </host>

   <host M1>
       ip      192.168.31.2
       mode    master
       peer    M1
   </host>

   <host M2>
       ip      192.168.31.3
       mode    master
       peer    M2
   </host>

   <host slave1>
       ip      192.168.31.4
       mode    slave
   </host>

   <role writer>
       hosts   M1, M2   # 能够作为writer的服务器
       ips     192.168.31.10 # writer节点的虚拟ip, 应用的写请求将直接连接到这个ip
       mode    exclusive # 排他模式
   </role>

   <role reader>
       hosts   M1,M2,slave1 # 作为reader的服务器
       ips     192.168.31.20 # reader节点虚拟ip,应用的读请求将直接连接到这个ip
       mode    balanced # 平衡模式
   </role>

   2 修改M1的mmm_agent.conf
   [root@M1 mysql-mmm]# vi 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 M1

   3 修改M2的mmm_agent.conf
   [root@M2 mysql-mmm]# vi 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 M2

   4 修改slave1的mmm_agent.conf
   [root@slave1 mysql-mmm]# vi 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 slave1

   5 修改 mon 上的mmm_mon.conf (这里就是M1 共用一台服务器)
   [root@M1 mysql-mmm]# vi mmm_mon.conf
   include mmm_common.conf

   <monitor>
       ip                  ip                  192.168.31.2
       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.31.2,192.168.31.3,192.168.31.4
       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>

   debug 0



九启动各个相关服务
   1 三台服务器都启动mysql-mmm-agent服务
   [root@M1 mysql-mmm]# /etc/init.d/mysql-mmm-agent start
   [root@M2 mysql-mmm]# /etc/init.d/mysql-mmm-agent start
   [root@slave1 mysql-mmm]# /etc/init.d/mysql-mmm-agent start

   2 在M1 上启动mysql-mon服务
   [root@M1 mysql-mmm]# /etc/init.d/mysql-mmm-monitor start

   3 查看mysql-mon 监控机上的MMM状态信息
   [root@M1 mysql-mmm]# mmm_control show
   出现Can't connect to monitor daemon
   则yum -y install perl-Time-HiRes*
   如果还是出现 Can't connect to monitor daemon 则要检查主从是否有问题

   [root@M1 ~]# mmm_control show # 再次执行会出现下面这几行
M1(192.168.31.2) master/ONLINE. Roles: writer(192.168.31.10)
M2(192.168.31.3) master/ONLINE. Roles: reader(192.168.31.20)
slave1(192.168.31.4) slave/ONLINE. Roles:

[root@M1 ~]# mmm_control checks all # 出现下面这些
M1      ping         [last change: 2015/09/24 00:39:19] OK
M1      mysql        [last change: 2015/09/24 00:39:19] OK
M1      rep_threads [last change: 2015/09/24 00:39:19] ERROR: Replication is broken
......
M2      rep_backlog [last change: 2015/09/24 00:39:19] OK: Backlog is null

十查看集群日志
[root@M1 ~]# tail -f /var/log/mysql-mmm/mmm_agentd.log
2015/09/23 18:38:11 FATAL Child exited with exitcode 98, restarting after 10 second sleep
2015/09/23 18:38:21 FATAL Listener: Can't create socket!
.......
2015/09/24 00:39:13 INFO Added:   writer(192.168.31.10)

[root@M1 ~]# tail -f /var/log/mysql-mmm/mmm_mond.log
2015/09/24 00:35:10 WARN Checker 'mysql' is dead!
......
2015/09/24 00:35:16 INFO Child exited normally (with exitcode 0), shutting down

[root@slave1 mysql-mmm]# tail -f /var/log/mysql-mmm/mmm_agentd.log
2015/09/23 02:53:38 INFO END
.......
2015/09/24 00:38:55 INFO Child exited normally (with exitcode 0), shutting down
2015/09/24 00:38:55 INFO Changing active master to 'M1'

十一测试
我们使用在M1 ip a 可以发现 192.168.31.10 在M1上
模拟M1宕机
使用 service mysql stop

在查看 ip a   发现vip 在M2上了。