一、MMM简介:
MMM即Multi-Master Replication Manager for MySQL:mysql多主复制治理器,基于perl完成,关于mysql主主复制设置的监控、毛病转移和治理的一套可伸缩的剧本套件(在任何时刻只需一个节点能够被写入),MMM也能对从效劳器举行读负载平衡,所以能够用它来在一组用于复制的效劳器启动假造ip,除此之外,它另有完成数据备份、节点之间从新同步功用的剧本。MySQL自身没有供应replication failover的解决计划,经由过程MMM计划能完成效劳器的毛病转移,从而完成mysql的高可用。MMM不仅能供应浮动IP的功用,假如当前的主效劳器挂掉后,会将你后端的从效劳器自动转向新的主效劳器举行同步复制,不必手工变动同步设置。这个计划是现在比较成熟的解决计划。详情请看官网:http://mysql-mmm.org
长处:高可用性,扩展性好,涌现毛病自动切换,关于主主同步,在同一时刻只供应一台数据库写操纵,保证的数据的一致性。当主效劳器挂掉今后,另一个主马上接受,其他的从效劳器能自动切换,不必人工干预。
瑕玷: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(64位)数据库体系: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 rep@'192.168.31.%' identified by '123456';
在master2上受权:
mysql> grant replication slave on *.* to rep@'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 mmm_agent@192.168.31.% | +-----------------------------------------------------------------------------------------------------------------------------+ | GRANT PROCESS, SUPER, REPLICATION CLIENT ON *.* TO 'mmm_agent'@'192.168.31.%' | +-----------------------------------------------------------------------------------------------------------------------------+ mysql> show grants for 'mmm_monitor'@'192.168.31.%'; +-----------------------------------------------------------------------------------------------------------------------------+ | Grants for mmm_monitor@192.168.31.% | +-----------------------------------------------------------------------------------------------------------------------------+ | 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 http://pkgs.fedoraproject.org/repo/pkgs/mysql-mmm/mysql-mmm-2.2.1.tar.gz/f5f8b48bdf89251d3183328f0249461e/mysql-mmm-2.2.1.tar.gz tar -zxf mysql-mmm-2.2.1.tar.gz cd mysql-mmm-2.2.1 make install
在数据库效劳器(master1、master2、slave1、slave2)上装置代办
cd /tmp wget http://pkgs.fedoraproject.org/repo/pkgs/mysql-mmm/mysql-mmm-2.2.1.tar.gz/f5f8b48bdf89251d3183328f0249461e/mysql-mmm-2.2.1.tar.gz 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 [root@monitor1 ~]# /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 [root@monitor1 ~]# 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
搜检集群状态:
[root@monitor1 ~]# 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主机名
比方:[root@monitor1 ~]#mmm_controlset_onlinemaster1
从上面的显现能够看到,写要求的VIP在master1上,一切从节点也都把master1当做主节点。
检察是不是启用vip
[root@master1 ~]# 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 [root@master2 ~]# 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 [root@slave1 ~]# 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 [root@slave2 ~]# 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会漂移到别的节点,由别的节点供应效劳。
起首检察全部集群的状态,能够看到全部集群状态一般
[root@monitor1 ~]# 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的日记以下:
[root@monitor1 ~]# 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'
检察聚集的最新状态
[root@monitor1 ~]# 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转换为HARD_OFFLINE,写VIP转移到了master2主机上。
搜检一切的db效劳器聚集状态
[root@monitor1 ~]# 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地点:
[root@master2 ~]# 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的日记以下:
[root@monitor1 ~]# 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状态
用以下敕令将效劳器上线:
[root@monitor1 ~]#mmm_controlset_onlinemaster1
检察聚集最新状态
[root@monitor1 ~]# 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效劳器聚集状态:
[root@monitor1 ~]# mmm_control checks all
搜检项包括:ping、mysql是不是一般运转、复制线程是不是一般等
搜检聚集环境在线状态:
[root@monitor1 ~]# mmm_control show
对指定的host实行offline操纵:
[root@monitor1 ~]# mmm_controlset_offline slave2
对指定的host实行onine操纵:
[root@monitor1 ~]# mmm_controlset_online slave2
实行write切换(手动切换):
检察当前的slave对应的master
[root@slave2 ~]# 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,不然没法切换
[root@monitor1 ~]# 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!
[root@monitor1 ~]# 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
[root@slave2 ~]# 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”决议,聚集效劳器的状态有三种分别是:HARD_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参数的影响。
以上就是MySQL高可用解决计划MMM详解的细致内容,更多请关注ki4网别的相干文章!