MySQL高可用MMM方案安装部署分享

一、架构形式(双master零slave)

MySQL高可用消除方案MMM(mysql多主复制管理器),mysqlmmm

一、MMM简介:

MMM即Multi-Master Replication Manager for
MySQL:mysql多主复制管理器,基于perl实现,关于mysql主主复制配置的监督检查、故障转移和管制的一套可伸缩的脚本套件(在任曾几何时候唯有二个节点能够被写入),MMM也能对从服务器进行读负载均衡,所以能够用它来在一组用于复制的服务器运营虚拟ip,除此而外,它还会有达成数据备份、节点之间重新联合功用的本子。MySQL本身并未有提供replication
failover的消除方案,通过MMM方案能实现服务器的故障转移,从而达成mysql的高可用。MMM不仅能提供浮动IP的魔法,要是当前的主服务器挂掉后,会将您后端的从服务器自动转接新的主服务器举行共同复制,不用手工业改动同步陈设。那几个方案是眼下相比成熟的化解方案。详细的情况请看官方网站:

图片 1

优点:高可用性,扩大性好,出现故障自动切换,对于主主同步,在同时只提供一台数据库写操作,保证的多寡的一致性。当主服务器挂掉未来,另三个主立刻接管,其余的从服务器能自动切换,不用人工干预。

缺点:monitor节点是单点,可是那个你也能够结合keepalived大概haertbeat做成高可用;至少多个节点,对主机的数码有要求,要求达成读写分离,还亟需在前者编写读写分离程序。在读写特别繁忙的事种类统下显现不是很平静,恐怕会油可是生复制延时、切换失效等主题材料。MMM方案并不太适应于对数据安全性供给极高,并且读、写繁忙的遇到中。

适用场景:

MMM的适用场景为数据库访问量大,并且能兑现读写分离的风貌。
Mmm首要功能由上边三个剧本提供:
mmm_mond
担负全部的监督检查工作的监督检查守护进度,决定节点的移除(mmm_mond进程定期心跳检查实验,战败则将write
ip浮动到其余一台master)等等
mmm_agentd
运维在mysql服务器上的代理守护进程,通过简单远程服务集提供给监控节点
mmm_control 通过命令行处理mmm_mond进程
在总体拘押进度中,要求在mysql中增多相关授权用户,授权的用户蕴涵三个mmm_monitor用户和二个mmm_agent用户,假若想使用mmm的备份工具则还要加上叁个mmm_tools用户。

二、布置施行

1、情形介绍

OS:centos7.2(六十一人)数据库系统:mysql5.7.13

关闭selinux

配置ntp,同步时间

角色

IP

hostname

Server-id

Write vip

Read vip

Master1

192.168.31.83

master1

1

192.168.31.2

Master2(backup)

192.168.31.141

master2

2

192.168.31.3

Slave1

192.168.31.250

slave1

3

192.168.31.4

Slave2

192.168.31.225

slave2

4

192.168.31.5

monitor

192.168.31.106

monitor1

2、在具有主机上配置/etc/hosts文件,增多如下内容:

192.168.31.83 master1
192.168.31.141 master2
192.168.31.250 slave1
192.168.31.225 slave2
192.168.31.106 monitor1

在具备主机上设置perl、perl-develperl-CPAN libart_lgpl.x86_64
rrdtool.x86_64 rrdtool-perl.x86_64包
#yum -y install perl-* libart_lgpl.x86_64 rrdtool.x86_64
rrdtool-perl.x86_64

注:使用centos7在线yum源安装

设置perl的连锁库

#cpan -i Algorithm::Diff Class::Singleton DBI DBD::mysql Log::Dispatch
Log::Log4perl Mail::Send Net::Ping Proc::Daemon Time::HiRes
Params::Validate Net::ARP

3、在master1、master2、slave1、slave2主机上安装mysql5.7和安排复制

master1和master2互为主题,slave1、slave2为master1的从
在每种mysql的安插文件/etc/my.cnf中进入以下内容,
注意server_id不能重新。

master1主机:

log-bin = mysql-bin
binlog_format = mixed
server-id = 1
relay-log = relay-bin
relay-log-index = slave-relay-bin.index
log-slave-updates = 1
auto-increment-increment = 2
auto-increment-offset = 1
master2主机:
log-bin = mysql-bin
binlog_format = mixed
server-id = 2
relay-log = relay-bin
relay-log-index = slave-relay-bin.index
log-slave-updates = 1
auto-increment-increment = 2
auto-increment-offset = 2
slave1主机:
server-id = 3
relay-log = relay-bin
relay-log-index = slave-relay-bin.index
read_only  = 1
slave2主机:
server-id = 4
relay-log = relay-bin
relay-log-index = slave-relay-bin.index
read_only  = 1

在产生了对my.cnf的退换后,通过systemctl restart mysqld重新启航mysql服务

4台数据库主机若要开启防火墙,要么破产防火墙只怕创设访问规则:

firewall-cmd –permanent –add-port=3306/tcp
firewall-cmd –reload
骨干配置(master1和master2配置成主主,slave1和slave2配置成master1的从):
在master1上授权:
mysql> grant replication slave on *.* to
[email protected]’192.168.31.%’
identified by ‘123456’;
在master2上授权:
mysql> grant replication slave on *.* to
[email protected]’192.168.31.%’
identified by ‘123456’;
把master2、slave1和slave2配置成master1的从库:
在master1上执行show master status; 获取binlog文件和Position点
mysql> show master status;
+——————+———-+————–+——————+————————————————–+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
Executed_Gtid_Set |
+——————+———-+————–+——————+—————————————————+
| mysql-bin.000001 | 452 | | | |
+——————+———-+————–+——————+—————————————————–+
在master2、slave1和slave2执行

mysql> change master to
master_host=’192.168.31.83′,master_port=3306,master_user=’rep’,master_password=’123456′,master_log_file=’mysql-bin.000001′,master_log_pos=452;
mysql>slave start;
验证主从复制:
master2主机:
mysql> show slave status\G;
*************************** 1. row
***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.31.83
Master_User: rep
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 452
Relay_Log_File: relay-bin.000002
Relay_Log_Pos: 320
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
slave1主机:
mysql> show slave status\G;
*************************** 1. row
***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.31.83
Master_User: rep
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 452
Relay_Log_File: relay-bin.000002
Relay_Log_Pos: 320
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
slave2主机:
mysql> show slave status\G;
*************************** 1. row
***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.31.83
Master_User: rep
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 452
Relay_Log_File: relay-bin.000002
Relay_Log_Pos: 320
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
如果Slave_IO_Running和Slave_SQL_Running都为yes,那么主从就曾经配置OK了
把master1配置成master2的从库:
在master2上执行show master status ;获取binlog文件和Position点
mysql> show master status;
+——————+———-+————–+——————+————————————————–+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
Executed_Gtid_Set |
+——————+———-+————–+——————+—————————————————+
| mysql-bin.000001 | 452 | | | |
+——————+———-+————–+——————+—————————————————-+
在master1上执行:
mysql> change master to
master_host=’192.168.31.141′,master_port=3306,master_user=’rep’,master_password=’123456′,master_log_file=’mysql-bin.000001′,master_log_pos=452;
mysql> start slave;
验证主从复制:
master1主机:
mysql> show slave status\G;
*************************** 1. row
***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.31.141
Master_User: rep
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 452
Relay_Log_File: relay-bin.000002
Relay_Log_Pos: 320
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
如果Slave_IO_Running和Slave_SQL_Running都为yes,那么主从就早已配置OK了
4、mysql-mmm配置:
在4台mysql节点上创办用户
创办代理账号:
mysql> grant super,replicationclient,process on *.* to
‘mmm_agent’@’192.168.31.%’ identified by ‘123456’;
创制监控账号:
mysql> grant replication client on *.* to
‘mmm_monitor’@’192.168.31.%’ identified by ‘123456’;
注1:因为事先的主从复制,以及着力已经是ok的,所以笔者在master1服务器推行就ok了。
自己研讨master2和slave1、slave2三台db上是否都留存监察和控制和代理账号
mysql> select user,host from mysql.user where user in
(‘mmm_monitor’,’mmm_agent’);
+————-+—————————-+
| user | host |
+————-+—————————-+
| mmm_agent | 192.168.31.% |
| mmm_monitor | 192.168.31.% |
+————-+——————————+

mysql> show grants for ‘mmm_agent’@’192.168.31.%’;
+—————————————————————————————————————————–+
| Grants for
[email protected]%
|
+—————————————————————————————————————————–+
| GRANT PROCESS, SUPER, REPLICATION CLIENT ON *.* TO
‘mmm_agent’@’192.168.31.%’ |
+—————————————————————————————————————————–+
mysql> show grants for ‘mmm_monitor’@’192.168.31.%’;
+—————————————————————————————————————————–+
| Grants for
[email protected]%
|
+—————————————————————————————————————————–+
| GRANT REPLICATION CLIENT ON *.* TO ‘mmm_monitor’@’192.168.31.%’ |
注2:
mmm_monitor用户:mmm监察和控制用于对mysql服务器进度健检
mmm_agent用户:mmm代理用来改换只读方式,复制的主服务器等
5、mysql-mmm安装
在monitor主机(192.168.31.106) 上设置监督程序
cd /tmp
wget

tar -zxf mysql-mmm-2.2.1.tar.gz
cd mysql-mmm-2.2.1
make install
在数据库服务器(master1、master2、slave1、slave2)上设置代理
cd /tmp
wget

tar -zxf mysql-mmm-2.2.1.tar.gz
cd mysql-mmm-2.2.1
make install

6、配置mmm

编写配置文件,五台主机必须一致:
成功安装后,全体的布局文件都放到了/etc/mysql-mmm/上边。管理服务器和数据库服务器上都要包罗一个一并的文书mmm_common.conf,内容如下:
active_master_rolewriter#主动的master剧中人物的标示,全体的db服务器要开启read_only参数,对于writer服务器监察和控制代理会自动将read_only属性关闭。
<host default>
cluster_interfaceeno16777736#集结的网络接口
pid_path /var/run/mmm_agentd.pid#pid路径
bin_path /usr/lib/mysql-mmm/#可施行文件路线
replication_user rep#复制用户
replication_password 123456#复制用户密码
agent_usermmm_agent#代办用户
agent_password 123456#代理用户密码
</host>
<host master1>#master1的host名
ip 192.168.31.83#master1的ip
mode master#剧中人物属性,master代表是主
peer
master2#与master1对等的服务器的host名,也正是master2的服务器host名
</host>
<host master2>#和master的定义同样
ip 192.168.31.141
mode master
peer master1
</host>
<host slave1>#从库的host名,假诺存在多少个从库能够重新雷同的布局
ip 192.168.31.250#从的ip
mode slave#slave的剧中人物属性代表当前host是从
</host>
<host slave2>#和slave的定义同样
ip 192.168.31.225
mode slave
</host>
<role writer>#writer角色配置
hosts
master1,master2#能拓展写操作的服务器的host名,纵然不想切换写操作这里能够只布置master,那样也得以制止因为互联网延时而开始展览write的切换,然而只要master出现故障那么当前的MMM就从未writer了唯有对外的read操作。
ips 192.168.31.2#对外提供的写操作的虚拟IP
mode
exclusive#exclusive代表只同意存在一个主,也正是只好提供二个写的IP
</role>
<role reader>#read角色配置
hosts
master2,slave1,slave2#对外提供读操作的服务器的host名,当然这里也能够把master加进来
ips 192.168.31.3, 192.168.31.4,
192.168.31.5#对外提供读操作的虚拟ip,那七个ip和host不是逐一对应的,并且ips也hosts的数码也能够区别等,如若那样计划的话当中一个hosts会分配七个ip
mode balanced#balanced代表负载均衡
</role>
并且将以此文件拷贝到别的的服务器,配置不改变
#for host in master1 master2 slave1 slave2 ; do scp
/etc/mysql-mmm/mmm_common.conf $host:/etc/mysql-mmm/ ; done
代理文件配置
编写制定 4台mysql节点机上的/etc/mysql-mmm/mmm_agent.conf
在数据库服务器上,还会有三个mmm_agent.conf供给修改,其内容是:
includemmm_common.conf
this master1
只顾:那么些布局只安顿db服务器,监察和控制服务器没有须要配备,this前面包车型大巴host名改成当下服务器的主机名。
开发银行代理进程
在 /etc/init.d/mysql-mmm-agent的台本文件的#!/bin/sh上面,出席如下内容
source /root/.bash_profile
增多成系统服务并安装为自运行
#chkconfig –add mysql-mmm-agent
#chkconfigmysql-mmm-agent on
#/etc/init.d/mysql-mmm-agent start
注:添加source
/root/.bash_profile目标是为着mysql-mmm-agent服务能启机自启。
机动运转和手动运转的独步天下分化,正是激活二个console
。那么评释在作为劳动运行的时候,恐怕是出于贫乏情况变量
劳动运行失利,报错新闻如下:
Daemon bin: ‘/usr/sbin/mmm_agentd’
Daemon pid: ‘/var/run/mmm_agentd.pid’
Starting MMM Agent daemon… Can’t locate Proc/Daemon.pm in @INC (@INC
contains: /usr/local/lib64/perl5 /usr/local/share/perl5
/usr/lib64/perl5/vendor_perl /usr/share/perl5/vendor_perl
/usr/lib64/perl5 /usr/share/perl5 .) at /usr/sbin/mmm_agentd line 7.
BEGIN failed–compilation aborted at /usr/sbin/mmm_agentd line 7.
failed

化解措施:

# cpanProc::Daemon
# cpan Log::Log4perl
# /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
# netstat -antp | grep mmm_agentd
tcp 0 0 192.168.31.83:9989 0.0.0.0:* LISTEN 9693/mmm_agentd
配备防火墙
firewall-cmd –permanent –add-port=9989/tcp
firewall-cmd –reload
编写 monitor主机上的/etc/mysql-mmm/mmm_mon.conf
includemmm_common.conf

<monitor>
ip 127.0.0.1##为了安全性,设置只在本机监听,mmm_mond私下认可监听9988
pid_path /var/run/mmm_mond.pid
bin_path /usr/lib/mysql-mmm/
status_path/var/lib/misc/mmm_mond.status
ping_ips192.168.31.83,192.168.31.141,192.168.31.250,192.168.31.225#用于测试网络可用性
IP 地址列表,只要个中有二个地点 ping
通,就代表互联网健康,这里并非写入本机地址
auto_set_online
0#安装自动online的时辰,私下认可是高出60s就将它设置为online,私下认可是60s,这里将其设为0正是立即online
</monitor>

<check default>
check_period 5
trap_period 10
timeout 2
#restart_after 10000
max_backlog 86400
</check>
check_period
叙述:检查周期默以为5s
默认值:5s
trap_period
讲述:一个节点被检验不成事的年华不断trap_period秒,就慎重的以为那几个节点失败了。
默认值:10s
timeout
叙述:检查超时的时刻
默认值:2s
restart_after
描述:在完成restart_after次检查后,重启checker进度
默认值:10000
max_backlog
叙述:记录检查rep_backlog日志的最大次数
默认值:60

<host default>
monitor_usermmm_monitor#监察db服务器的用户
monitor_password 123456#监察db服务器的密码
</host>
debug 0#debug 0符合规律格局,1为debug情势
起步监察和控制进度:
在 /etc/init.d/mysql-mmm-agent的脚本文件的#!/bin/sh上边,插足如下内容
source /root/.bash_profile
增添成系统服务并设置为自运行
#chkconfig –add mysql-mmm-monitor
#chkconfigmysql-mmm-monitor on
#/etc/init.d/mysql-mmm-monitor start

启航报错:

Starting MMM Monitor daemon: Can not locate Proc/Daemon.pm in @INC (@INC
contains: /usr/local/lib64/perl5 /usr/local/share/perl5
/usr/lib64/perl5/vendor_perl /usr/share/perl5/vendor_perl
/usr/lib64/perl5 /usr/share/perl5 .) at /usr/sbin/mmm_mond line 11.
BEGIN failed–compilation aborted at /usr/sbin/mmm_mond line 11.
failed
消除措施:安装下列perl的库
#cpanProc::Daemon
#cpan Log::Log4perl
[[email protected]
~]# /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
[[email protected]
~]# netstat -anpt | grep 9988
tcp 0 0 127.0.0.1:9988 0.0.0.0:* LISTEN 8546/mmm_mond
注1:无论是在db端依旧在监察和控制端如若有对配备文件实行改造操作都亟待重启代理进度和监察进度。
注2:MMM运行顺序:先运维monitor,再起步 agent

自己谈论集群状态:

[[email protected]
~]# mmm_control show
master1(192.168.31.83) master/ONLINE. Roles: writer(192.168.31.2)
master2(192.168.31.141) master/ONLINE. Roles: reader(192.168.31.5)
slave1(192.168.31.250) slave/ONLINE. Roles: reader(192.168.31.4)
slave2(192.168.31.225) slave/ONLINE. Roles: reader(192.168.31.3)

若是服务器状态不是ONLINE,能够用如下命令将服务器上线,比如:

#mmm_controlset_online主机名

例如:[[email protected]
~]#mmm_controlset_onlinemaster1
从地点的呈现能够见到,写请求的VIP在master1上,全数从节点也都把master1当做主节点。
翻看是不是启用vip
[[email protected]
~]# ipaddr show dev eno16777736
eno16777736: <BROADCAST,MULTICAST,UP,LOWER_UP>mtu 1500
qdiscpfifo_fast state UP qlen 1000
link/ether 00:0c:29:6d:2f:82 brdff:ff:ff:ff:ff:ff
inet 192.168.31.83/24 brd 192.168.31.255 scope global eno16777736
valid_lft forever preferred_lft forever
inet 192.168.31.2/32 scope global eno16777736
valid_lft forever preferred_lft forever
inet6 fe80::20c:29ff:fe6d:2f82/64 scope link
valid_lft forever preferred_lft forever
[[email protected]
~]# ipaddr show dev eno16777736
eno16777736: <BROADCAST,MULTICAST,UP,LOWER_UP>mtu 1500
qdiscpfifo_fast state UP qlen 1000
link/ether 00:0c:29:75:1a:9c brdff:ff:ff:ff:ff:ff
inet 192.168.31.141/24 brd 192.168.31.255 scope global dynamic
eno16777736
valid_lft 35850sec preferred_lft 35850sec
inet 192.168.31.5/32 scope global eno16777736
valid_lft forever preferred_lft forever
inet6 fe80::20c:29ff:fe75:1a9c/64 scope link
valid_lft forever preferred_lft forever
[[email protected]
~]# ipaddr show dev eno16777736
eno16777736: <BROADCAST,MULTICAST,UP,LOWER_UP>mtu 1500
qdiscpfifo_fast state UP qlen 1000
link/ether 00:0c:29:02:21:19 brdff:ff:ff:ff:ff:ff
inet 192.168.31.250/24 brd 192.168.31.255 scope global dynamic
eno16777736
valid_lft 35719sec preferred_lft 35719sec
inet 192.168.31.4/32 scope global eno16777736
valid_lft forever preferred_lft forever
inet6 fe80::20c:29ff:fe02:2119/64 scope link
valid_lft forever preferred_lft forever
[[email protected]
~]# ipaddr show dev eno16777736
eno16777736: <BROADCAST,MULTICAST,UP,LOWER_UP>mtu 1500
qdiscpfifo_fast state UP qlen 1000
link/ether 00:0c:29:e2:c7:fa brdff:ff:ff:ff:ff:ff
inet 192.168.31.225/24 brd 192.168.31.255 scope global dynamic
eno16777736
valid_lft 35930sec preferred_lft 35930sec
inet 192.168.31.3/32 scope global eno16777736
valid_lft forever preferred_lft forever
inet6 fe80::20c:29ff:fee2:c7fa/64 scope link
valid_lft forever preferred_lft forever
在master2,slave1,slave2主机上查看主mysql的针对性
mysql> show slave status\G;
*************************** 1. row
***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.31.83
Master_User: rep
Master_Port: 3306
Connect_Retry: 60

MMM高可用性测试:

服务器读写采有VIP地址举办读写,出现故障时VIP会漂移到别的节点,由别的节点提供劳动。
先是查看全体集群的场馆,能够看出整个集群状态平常
[[email protected]
~]# mmm_control show
master1(192.168.31.83) master/ONLINE. Roles: writer(192.168.31.2)
master2(192.168.31.141) master/ONLINE. Roles: reader(192.168.31.5)
slave1(192.168.31.250) slave/ONLINE. Roles: reader(192.168.31.4)
slave2(192.168.31.225) slave/ONLINE. Roles: reader(192.168.31.3)
模仿master1宕机,手动结束mysql服务,观看monitor日志,master1的日记如下:
[[email protected]
~]# tail -f /var/log/mysql-mmm/mmm_mond.log
2017/01/09 22:02:55 WARN Check ‘rep_threads’ on ‘master1’ is in unknown
state! Message: UNKNOWN: Connect error (host = 192.168.31.83:3306, user
= mmm_monitor)! Can’t connect to MySQL server on ‘192.168.31.83’
(111)
2017/01/09 22:02:55 WARN Check ‘rep_backlog’ on ‘master1’ is in unknown
state! Message: UNKNOWN: Connect error (host = 192.168.31.83:3306, user
= mmm_monitor)! Can’t connect to MySQL server on ‘192.168.31.83’
(111)
2017/01/09 22:03:05 ERROR Check ‘mysql’ on ‘master1’ has failed for 10
seconds! Message: ERROR: Connect error (host = 192.168.31.83:3306, user
= mmm_monitor)! Can’t connect to MySQL server on ‘192.168.31.83’
(111)
2017/01/09 22:03:07 FATAL State of host ‘master1’ changed from ONLINE to
HARD_OFFLINE (ping: OK, mysql: not OK)
2017/01/09 22:03:07 INFO Removing all roles from host ‘master1’:
2017/01/09 22:03:07 INFO Removed role ‘writer(192.168.31.2)’ from host
‘master1’
2017/01/09 22:03:07 INFO Orphaned role ‘writer(192.168.31.2)’ has been
assigned to ‘master2’
翻开集结的风行状态
[[email protected]
~]# mmm_control show
master1(192.168.31.83) master/HARD_OFFLINE. Roles:
master2(192.168.31.141) master/ONLINE. Roles: reader(192.168.31.5),
writer(192.168.31.2)
slave1(192.168.31.250) slave/ONLINE. Roles: reader(192.168.31.4)
slave2(192.168.31.225) slave/ONLINE. Roles: reader(192.168.31.3)
从展现结果能够看看master1的情况有ONLINE转变为HA君越D_OFFLINE,写VIP转移到了master2主机上。
检查有着的db服务器会集状态
[[email protected]
~]# mmm_control checks all
master1 ping [last change: 2017/01/09 21:31:47] OK
master1 mysql [last change: 2017/01/09 22:03:07] ERROR: Connect error
(host = 192.168.31.83:3306, user = mmm_monitor)! Can’t connect to MySQL
server on ‘192.168.31.83’ (111)
master1 rep_threads [last change: 2017/01/09 21:31:47] OK
master1 rep_backlog [last change: 2017/01/09 21:31:47] OK: Backlog is
null
slave1 ping [last change: 2017/01/09 21:31:47] OK
slave1mysql [last change: 2017/01/09 21:31:47] OK
slave1 rep_threads [last change: 2017/01/09 21:31:47] OK
slave1 rep_backlog [last change: 2017/01/09 21:31:47] OK: Backlog is
null
master2 ping [last change: 2017/01/09 21:31:47] OK
master2 mysql [last change: 2017/01/09 21:57:32] OK
master2 rep_threads [last change: 2017/01/09 21:31:47] OK
master2 rep_backlog [last change: 2017/01/09 21:31:47] OK: Backlog is
null
slave2 ping [last change: 2017/01/09 21:31:47] OK
slave2mysql [last change: 2017/01/09 21:31:47] OK
slave2 rep_threads [last change: 2017/01/09 21:31:47] OK
slave2 rep_backlog [last change: 2017/01/09 21:31:47] OK: Backlog is
null
从位置能够见见master1能ping通,表明只是劳动死掉了。

查看master2主机的ip地址:

[[email protected]
~]# ipaddr show dev eno16777736
eno16777736: <BROADCAST,MULTICAST,UP,LOWER_UP>mtu 1500
qdiscpfifo_fast state UP qlen 1000
link/ether 00:0c:29:75:1a:9c brdff:ff:ff:ff:ff:ff
inet 192.168.31.141/24 brd 192.168.31.255 scope global dynamic
eno16777736
valid_lft 35519sec preferred_lft 35519sec
inet 192.168.31.5/32 scope global eno16777736
valid_lft forever preferred_lft forever
inet 192.168.31.2/32 scope global eno16777736
valid_lft forever preferred_lft forever
inet6 fe80::20c:29ff:fe75:1a9c/64 scope link
valid_lft forever preferred_lft forever
slave1主机:
mysql> show slave status\G;
*************************** 1. row
***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.31.141
Master_User: rep
Master_Port: 3306
slave2主机:
mysql> show slave status\G;
*************************** 1. row
***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.31.141
Master_User: rep
Master_Port: 3306
开发银行master1主机的mysql服务,观看monitor日志,master1的日记如下:
[[email protected]
~]# tail -f /var/log/mysql-mmm/mmm_mond.log
2017/01/09 22:16:56 INFO Check ‘mysql’ on ‘master1’ is ok!
2017/01/09 22:16:56 INFO Check ‘rep_backlog’ on ‘master1’ is ok!
2017/01/09 22:16:56 INFO Check ‘rep_threads’ on ‘master1’ is ok!
2017/01/09 22:16:59 FATAL State of host ‘master1’ changed from
HARD_OFFLINE to AWAITING_RECOVERY
从地方能够见见master1的状态由hard_offline改变为awaiting_recovery状态
用如下命令将服务器上线:
[[email protected]
~]#mmm_controlset_onlinemaster1
查阅会集最新情状
[[email protected]
~]# mmm_control show
master1(192.168.31.83) master/ONLINE. Roles:
master2(192.168.31.141) master/ONLINE. Roles: reader(192.168.31.5),
writer(192.168.31.2)
slave1(192.168.31.250) slave/ONLINE. Roles: reader(192.168.31.4)
slave2(192.168.31.225) slave/ONLINE. Roles: reader(192.168.31.3)
能够见见主库运营不会接管主,只到现成的主再度宕机。
总结
(1)master2备选主节点宕机不影响集群的情事,正是移除了master2备选节点的读状态。
(2)master1主节点宕机,由master2备选主节点接管写剧中人物,slave1,slave2指向新master2主库进行理并答复制,slave1,slave2会自动change
master到master2.
(3)如果master1主库宕机,master2复制利用又落后于master1时就产生了主可写情形,那时的数据主不可能担保一致性。
如果master2,slave1,slave2延迟于master1主,那一个时master1宕机,slave1,slave2将会等待数据追上db1后,再另行指向新的主node2进行理并答复制操作,那时的数量也无从保险同步的一致性。
(4)借使利用MMM高可用架构,主,主备选节点机器配置同样,而且展开半一只进一步提升安全性或行使MariaDB/mysql5.7实行三二十四线程从复制,进步复制的习性。

附:

1、日志文件:
日志文件反复是深入分析错误的重要,所以要善用运用日志文件举办难题分析。
db端:/var/log/mysql-mmm/mmm_agentd.log
监控端:/var/log/mysql-mmm/mmm_mond.log
2、命令文件:
mmm_agentd:db代理进程的开发银行文件
mmm_mond:监察和控制进程的运行文件
mmm_backup:备份文件
mmm_restore:还最初的作品件
mmm_control:监察和控制操作命令文件
db服务器端只有mmm_agentd程序,别的的都以在monitor服务器端。
3、mmm_control用法
mmm_control程序能够用于监察和控制集结状态、切换writer、设置online\offline操作等。
Valid commands are:
help – show this message #支援音讯
ping – ping monitor #ping当前的集合是或不是健康
show – show status #会集在线状态检查
checks [<host>|all [<check>|all]] – show checks
status#实行监督检查操作
set_online<host> – set host <host> online
#将host设置为online
set_offline<host> – set host <host> offline
#将host设置为offline
mode – print current mode. #打字与印刷输出当前的mode
set_active – switch into active mode.

set_manual – switch into manual mode.
set_passive – switch into passive mode.
move_role [–force] <role><host> – move exclusive role
<role> to host <host>
#移除writer服务器为钦点的host服务器(Only use –force if you know what
you are doing!)
set_ip<ip><host> – set role with ip<ip> to host
<host>
自己斟酌有着的db服务器会集状态:
[[email protected]
~]# mmm_control checks all
自己商量项包蕴:ping、mysql是还是不是平常运营、复制线程是还是不是平常等
自己顶牛集结情形在线处境:
[[email protected]
~]# mmm_control show
对点名的host实行offline操作:
[[email protected]
~]# mmm_controlset_offline slave2
对点名的host实施onine操作:
[[email protected]
~]# mmm_controlset_online slave2
推行write切换(手动切换):
翻看当前的slave对应的master
[[email protected]
~]# mysql -uroot -p123456 -e ‘show slave status\G;’
mysql: [Warning] Using a password on the command line interface can be
insecure.
*************************** 1. row
***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.31.141
writer切换,要确保mmm_common.conf文件中的writer属性有安插相应的host,不然不能够切换
[[email protected]
~]# mmm_controlmove_role writer master1
OK: Role ‘writer’ has been moved from ‘master2’ to ‘master1’. Now you
can wait some time and check new roles info!
[[email protected]
~]# mmm_control show
master1(192.168.31.83) master/ONLINE. Roles: writer(192.168.31.2)
master2(192.168.31.141) master/ONLINE. Roles: reader(192.168.31.5)
slave1(192.168.31.250) slave/ONLINE. Roles: reader(192.168.31.4)
slave2(192.168.31.225) slave/ONLINE. Roles: reader(192.168.31.3)
save从库自动切换来了新的master
[[email protected]
~]# mysql -uroot -p123456 -e ‘show slave status\G;’
mysql: [Warning] Using a password on the command line interface can be
insecure.
*************************** 1. row
***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.31.83

4、其余管理难题

万一不想让writer从master切换来backup(包涵大旨的延时也会产生写VIP的切换),那么能够在配置/etc/mysql-mmm/mmm_common.conf时,去掉<role
write>中的backup
<role writer>#writer角色配置
hosts master1 #那边只安顿一个Hosts
ips 192.168.31.2#对外提供的写操作的虚构IP
mode exclusive
#exclusive代表只允许存在叁个主,也正是不得不提供二个写的IP
</role>
那样的话当master1现身故障了writer写操作不会切换成master2服务器,并且slave也不会针对新的master,此时当前的MMM从前对外提供写服务。

5、总结

1.对外提供读写的虚拟IP是由monitor程控。如若monitor未有运营那么db服务器不会被分配虚拟ip,可是假设已经分配好了虚拟ip,当monitor程序关闭了原本分配的杜撰ip不会应声关闭外部程序还足以连绵不断访问(只要不重启网络),那样的实惠正是对于monitor的可信赖性须求就能够低一些,可是要是这一年当中的某二个db服务器故障了就不能管理切换,约等于原先的虚拟ip依旧保持不改变,挂掉的那台DB的杜撰ip会变的不可访问。

2.agent程序受monitor程序的主宰管理write切换,从库切换等操作。如果monitor进度关闭了那么agent进程就起不到何等意义,它本人不能管理故障。

3.monitor顺序负责督察db服务器的意况,包罗Mysql数据库、服务器是不是运营、复制线程是还是不是正规、主从延时等;它还用于调节agent程序管理故障。

4.monitor会每隔几分钟监控db服务器的场馆,如果db服务器已经从故障形成了正规,那么monitor会自动在60s过后将其安装为online状态(私下认可是60s方可设为别的的值),有监控端的布署文件参数“auto_set_online”决定,集合服务器的景况有两种分别是:HAOdysseyD_OFFLINE→AWAITING_RECOVERY→online
5.默认monitor会控制mmm_agent会将writer
db服务器read_only修改为OFF,其它的db服务器read_only修改为ON,所认为了严酷能够在有着的服务器的my.cnf文件中参加read_only=1由monitor调节来决定writer和read,root用户和复制用户不受read_only参数的熏陶。

一、MMM简要介绍: MMM即Multi-Master Replication Manager for
MySQL:mysql多主复制管理器,基于perl完结…

1 install mysql

 

 图片 2
 

  请参考

 

———————[1] mmm_common.conf ——————–
 
主机:
功能                                         IP                         
主机名
monitor                                  192.168.9.159        
   proxy
DB     db1   Server-id=1          192.168.5.157           fbfldb01
         db2   Server-id=2          192.168.9.158           fbfldb02
虚拟ip:
IP0       192.168.9.154 (Write)
IP1       192.168.9.155 (Read)
IP2       192.168.9.156 (Read)
 
active_master_role writer
<host default>
cluster_interface eth0
pid_path /var/run/mysql-mmm/mmm_agentd.pid
bin_path /usr/libexec/mysql-mmm/
replication_user slave
replication_password slave
agent_user mmm_agent
agent_password RepAgent
</host>
<host db1>
ip 192.168.9.157
mode master
peer db2
</host>
<host db2>
ip 192.168.9.158
mode master
peer db1
</host>
#<host db3>
# ip 192.168.100.51
# mode slave
#</host>
<role writer>
hosts db1, db2 #可见作为writer的host
ips 192.168.100.250 #writer的杜撰IP,应用的写请求直接待上访问这里的ip就能够
mode exclusive
#只有两种格局:exclusive是排他,在这种形式下任曾几何时候只好一个host具有该角色,balanced情势下能够多个host同期持有此角色。一般writer是ex,reader是ba
</role>
<role reader>
hosts db1, db2 #可见作为reader的host
ips 192.168.100.251, 192.168.100.252
#reader的虚构IP,应用的读请求直接待上访问这里的ip就能够
mode balanced
</role>
 
 
——————–[2] mmm_mon.conf ———————–
include mmm_common.conf
<monitor>
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.9.157, 192.168.9.158,192.168.9.254
</monitor>
<host default>
monitor_user mmm_monitor
monitor_password RepMonitor
</host>
debug 0
 
————————[4] mmm_agent.conf  ——————–
include mmm_common.conf
this db1 #在DB1上就写成那样,DB2上就写成this
db2,写错了,将有一台DB的mysql-mmm-agent开不起来!切记!
 
翻开景况:
[[email protected]
~]# mmm_control show
db1(192.168.9.157) master/ONLINE. Roles: reader(192.168.9.156),
writer(192.168.9.154)
db2(192.168.9.158) master/ONLINE. Roles: reader(192.168.9.155)
[[email protected]
~]# mmm_control checks
db2 ping [last change: 2012/02/03 14:04:41] OK
db2 mysql [last change: 2012/02/03 14:04:41] OK
db2 rep_threads [last change: 2012/02/03 14:04:41] OK
db2 rep_backlog [last change: 2012/02/03 14:04:41] OK: Backlog is
null
db1 ping [last change: 2012/02/03 14:04:41] OK
db1 mysql [last change: 2012/02/03 14:04:41] OK
db1 rep_threads [last change: 2012/02/03 14:04:41] OK
db1 rep_backlog [last change: 2012/02/03 14:04:41] OK: Backlog is
null
[[email protected]
~]# mmm_control mode
ACTIVE
 
依傍宕机切换:
无论是找三个客户端,实施写操作:
@client[[email protected]
~]# vi /usr/local/mysql/binlog/inserting-into-db.sh
#!/bin/bash
while true;
do
mysql -ufbdb_user -pJc7ech-fB-me -h192.168.9.154 –database= fbdb -e
“insert into test values(null);”
sleep 1 ;
done;
[[email protected]
~]# ./inserting-into-db.sh &
 
能够看看七个db中的binlog展现的server
id都以1,也正是说当前情形下db1是作为写库。
停止db1
[[email protected]
~]# /etc/init.d/mysqld stop
Shutting down MySQL.. SUCCESS!
眼看过来DB1后proxy上查看mmm集群状态
 
检查mmmDB1的日志:
[[email protected]
binlog]# tail -f /var/log/mysql-mmm/mmm_agentd.log
2012/02/03 17:46:10 FATAL Couldn’t allow writes: ERROR: Can’t connect to
MySQL (host = 192.168.9.157:3306, user = mmm_agent)! Lost connection to
MySQL server at ‘reading initial communication packet’, system error:
111
2012/02/03 17:46:13 INFO We have some new roles added or old rules
deleted!
2012/02/03 17:46:13 INFO Deleted: reader(192.168.9.156),
writer(192.168.9.154)
2012/02/03 17:46:13 FATAL Couldn’t deny writes: ERROR: Can’t connect to
MySQL (host = 192.168.9.157:3306, user = mmm_agent)! Lost connection to
MySQL server at ‘reading initial communication packet’, system error:
111
2012/02/03 17:59:48 INFO We have some new roles added or old rules
deleted!
2012/02/03 17:59:48 INFO Added: reader(192.168.9.155)
2012/02/03 18:01:12 INFO We have some new roles added or old rules
deleted!
 
从日记能够见见,db1甘休现在,mmm提醒connect
error,由于近些日子的写库是db1,于是mmm感觉db2上的数目现已不可能和db1保持一致了,故把db2的读角色(reader)迁移到db1上。
产生了:
  db1(192.168.9.157) master/ONLINE. Roles: reader(192.168.9.155)
  db2(192.168.9.158) master/ONLINE. Roles: reader(192.168.9.156),
writer(192.168.9.154)
但是,若DB1未即时过来专门的学业,mmm的”mysql”检查项在10秒前面世报告警方,感觉db1已经深透失利,因而会把db1设置景况为hard_offline,把db2从replication_fail状态切换来online状态(因为db2的mysql至少还活着)同一时候把地点的有着剧中人物切换来db2上。状态最终变为:
[[email protected]
mysql-mmm]# mmm_control show
db1(192.168.9.157) master/HARD_OFFLINE. Roles:
db2(192.168.9.158) master/ONLINE. Roles: reader(192.168.9.155),
reader(192.168.9.156), writer(192.168.9.154)
 
很扎眼,当DB1或DB第22中学的个中一台宕机之后,mmm都会即时将宕机的主机的剧中人物全体转移到另一台DB。
仔细分析Mmm的拍卖步骤大概是:
db1的“mysql”check恢复生机正常,然后把db1切换成awaiting_recovery状态。然后mmm决断db6的宕机时间在常标准围内,不属于卓殊景况,因而活动切换为online状态。
把db第22中学的一个reader剧中人物迁移到db1上。
眼前写库是db2。
注:能够在exclusive 的<role
writer>中装置prefer=db1,那样在db1复苏不荒谬之后,就足以另行被切换为写库了。
 
调查整个切换进程意识,切换进程开销了15S!
 
 
本文出自 “Centi.Linux” 博客

  1. Basic configuration of master 1

  2. Create users
    GRANT REPLICATION CLIENT ON *.* TO ‘mmm_monitor’@’%’ IDENTIFIED BY
    ‘mmm_monitor’;
    GRANT SUPER, REPLICATION CLIENT, PROCESS ON *.* TO ‘mmm_agent’@’%’
    IDENTIFIED BY ‘mmm_agent’;
    GRANT REPLICATION SLAVE ON *.* TO ‘repl’@’10.88.49.%’ IDENTIFIED BY
    ‘repl’;
    grant insert,create,delete,update,select on *.* to ‘tim’@’%’
    identified by ‘tim’;
    Note: Don not use a replication_password longer than 32 characters

==============================================================
————-…

  1. Synchronisation of data between both databases
  1. Setup replication
    set m-s:
    change master to
    master_host=’10.88.49.119′,master_log_file=’mysql56-bin.000026′,master_log_pos=332,
    master_user=’repl’,master_password=’repl’;
  1. Install mmm
    6.1 download mmm.tar.gz
    wget
    6.2 mv :mmm2:mysql-mmm-2.2.1.tar.gz mysql-mmm-2.2.1.tar.gz
    tar -xvf mysql-mmm-2.2.1.tar.gz
    cd mysql-mmm-2.2.1
    make
    cmake
    [] don not require make and make install, there have *.conf in
    /etc/mysql-mmm folder.

  2. install lib package
    yum install -y perl-*
    yum install -y libart_lgpl.x86_64
    yum install -y mysql-mmm.noarch fail
    yum install -y rrdtool.x86_64
    yum install -y rrdtool-perl.x86_64
    7.1 []another way to install lib package in network
    cpan -i Algorithm::Diff Class::Singleton DBI DBD::mysql Log::Dispatch
    Log::Log4perl Mail::Send Net::Ping Proc::Daemon Time::HiRes
    Params::Validate Net::ARP

  1. Config MMM of DB host
    vim /etc/mysql-mmm/mmm_common.conf
    Don not forget to copy this file to all other hosts (including the
    monitoring host).
    #Bugsfor$
    scp /etc/mysql-mmm/mmm_common.conf 10.88.49.119:/etc/mysql-mmm/
    scp /etc/mysql-mmm/mmm_common.conf 10.88.49.122:/etc/mysql-mmm/
    scp /etc/mysql-mmm/mmm_common.conf 10.88.49.123:/etc/mysql-mmm/

On the database hosts we need to edit /etc/mysql-mmm/mmm_agent.conf.
Change “db1” accordingly on the other hosts:
chkconfig –add mysql-mmm-agent

  1. Config Monitor
    On the monitor host(10.88.49.123) we need to edit
    /etc/mysql-mmm/mmm_mon.conf:
    include mmm_common.conf
    <monitor>
    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
    auto_set_online 5
    ping_ips
    10.88.49.254,10.88.49.130,10.88.49.131,10.88.49.132,10.88.49.133,10.88.49.134
    </monitor>
    <host default>
    monitor_user mmm_monitor
    monitor_password mmm_monitor
    </host>
    debug 0
    ping_ips are some ips that are pinged to determine whether the network
    connection of the monitor is ok. I used my switch of GATEWAY
    (10.88.49.254) and the four database server.follow this
    [[email protected]
    mysql-mmm]# cat /etc/sysconfig/network-scripts/ifcfg-eth0
    DEVICE=eth0
    IPADDR=10.88.49.118
    NETWASK=255.255.254.0
    GATEWAY=10.88.49.254
    DNS1=10.106.185.143
    DNS2=10.106.185.138
    ONBOOT=yes
    BOOTPROTO=none
    TYPE=Ethernet
    HWADDR=00:15:5D:01:6A:0C
  1. Start in database hosts
    chkconfig –add mysql-mmm-agent
    [[email protected]
    ~]# mysql-mmm-agent start
    -bash: mysql-mmm-agent: command not found
    [[email protected]
    ~]# service mysql-mmm-agent start
    Daemon bin: ‘/usr/sbin/mmm_agentd’
    Daemon pid: ‘/var/run/mmm_agentd.pid’
    Starting MMM Agent daemon… Can not locate Proc/Daemon.pm in @INC (@INC
    contains: /usr/local/lib64/perl5 /usr/local/share/perl5
    /usr/lib64/perl5/vendor_perl /usr/share/perl5/vendor_perl
    /usr/lib64/perl5 /usr/share/perl5 .) at /usr/sbin/mmm_agentd line 7.
    BEGIN failed–compilation aborted at /usr/sbin/mmm_agentd line 7.
    [[email protected]
    ~]# cpan Proc::Daemon
    [[email protected]
    ~]# cpan Log::Log4perl
    [[email protected]
    ~]# /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