环境: mysql version 5.7.22 on redhat6.4 64bit
master-db1 192.168.0.104
master-db2 192.168.0.105
slave-db3,mysql-mmm-monitor 192.168.0.106
mysql-mmm写VIP 192.168.0.110 读vip192.168.0.111, 192.168.0.112,192.168.0.113
1. 192.168.0.104 master-db1 与 192.168.0.105 master-db2 主主复制:略过
https://blog.csdn.net/aryoyo/article/details/80616396
192.168.0.104 master-db1 与 192.168.0.106 slave-db3 主从复制:略过
https://blog.csdn.net/aryoyo/article/details/80597947
2.在msater-db1上创建mmm_monitor和mmm_agent的账号,monitor主要是监控服务器的健康和复制状态的,
agent主要是做故障转移或主从切换,比如master-db1 crash后需要修改slave-db3的主为master-db2,所以需
赋予对应权限:
create user 'mmm_monitor'@'%' identified by '123456';
grant replication client on *.* to mmm_monitor;
create user 'mmm_agent'@'%' identified by '123456';
grant super,replication client,process on *.* to mmm_agent;
3.三台服务器分别安装mysql-mmm-agent,monitor 106上安装mysql-mmm-monitor
rpm -ivh http://mirrors.ustc.edu.cn/fedora/epel/6/x86_64/epel-release-6-8.noarch.rpm
rpm -ivh http://rpms.famillecollet.com/enterprise/remi-release-6.rpm
yum install mysql-mmm-agent
yum install mysql-mmm*
yum安装报错:
http://download.fedoraproject.org/pub/epel/6/x86_64/repodata/55a73bffdf44e8dfdb2066488144e71e23c8a5d7f727 38e8f935b2a6aa57764d-comps-Everything.x86_64.xml.gz: [Errno 14] problem making ssl connection
Trying other mirror.
解决办法是:
vim /etc/yum.repos.d/epel.repo,先把[epel]的enabled改成0,yum install ca-certificates
4.mysql-mmm的配置文件
1>三台服务器的/etc/hosts文件配置
192.168.0.104 master-db1
192.168.0.105 master-db2
192.168.0.106 slave-db3
2> 三台服务器上的/etc/mysql-mmm/mmm_common.conf
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 replicant
replication_password 123456
agent_user mmm_agent
agent_password 123456
</host>
<host master-db1>
ip 192.168.0.104
mode master
peer master-db2
</host>
<host master-db2>
ip 192.168.0.105
mode master
peer master-db1
</host>
<host slave-db3>
ip 192.168.0.106
mode slave
</host>
<role writer>
hosts master-db1,master-db2
ips 192.168.0.110
mode exclusive
</role>
<role reader>
hosts master-db1,master-db2,slave-db3
ips 192.168.0.111,192.168.0.112,192.168.0.113
mode balanced
</role>
3>3台服务器/etc/mysql-mmm/mmm_agent.conf,每台分别对应自己的名字:
this salve-db3
4>monitor服务器上/etc/mysql-mmm/mmm_mon.conf:
include mmm_common.conf
<monitor>
ip 192.168.0.106
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.104,192.168.0.105,192.168.0.106
auto_set_online 60
# 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>
5.三台服务分别启动mysql-mmm-agent服务
service mysql-mmm-agent start
monitor服务器上启动mysql-mmm-monitor服务
service mysql-mmm-monitor start
6.monitor服务器上:mmm_control show
[root@rhel64-64bit Desktop]# mmm_control show
master-db1(192.168.0.104) master/ONLINE. Roles: reader(192.168.0.111), writer(192.168.0.110)
master-db2(192.168.0.105) master/ONLINE. Roles: reader(192.168.0.112)
slave-db3(192.168.0.106) slave/ONLINE. Roles: reader(192.168.0.113)
在104的master-db1服务器上ip addr,读写VIP分配成功
2: eth0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP qlen 1000
link/ether 00:0c:29:22:98:4d brd ff:ff:ff:ff:ff:ff
inet 192.168.0.104/24 brd 192.168.0.255 scope global eth0
inet 192.168.0.111/32 scope global eth0
inet 192.168.0.110/32 scope global eth0
inet6 fe80::20c:29ff:fe22:984d/64 scope link
valid_lft forever preferred_lft forever
7.故障转移和切换测试:
1>104 master-db1上停止mysql
service mysqld stop
2>106 monitor上查看集群状态:
[root@rhel64-64bit Desktop]# mmm_control show
master-db1(192.168.0.104) master/HARD_OFFLINE. Roles:
master-db2(192.168.0.105) master/ONLINE. Roles: reader(192.168.0.112), writer(192.168.0.110)
slave-db3(192.168.0.106) slave/ONLINE. Roles: reader(192.168.0.111), reader(192.168.0.113)
可见写VIP已经到了master-db2 105服务器上了,应用不会有感知VIP的漂移的,因为mysql对应用的访问ip是VIP
3>查看slave-db3复制状态,发现master变成了105的master-db2了
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.0.105
Master_User: replicant
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mybinlogg.000014
Read_Master_Log_Pos: 678
Relay_Log_File: rhel64-64bit-relay-bin.000002
Relay_Log_Pos: 320
Relay_Master_Log_File: mybinlogg.000014
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
4>如果此时把104 服务器msater-db1重启启动,则60s后,查看:
[root@rhel64-64bit Desktop]# mmm_control show
master-db1(192.168.0.104) master/ONLINE. Roles: reader(192.168.0.113)
master-db2(192.168.0.105) master/ONLINE. Roles: reader(192.168.0.112), writer(192.168.0.110)
slave-db3(192.168.0.106) slave/ONLINE. Roles: reader(192.168.0.111)
master-db1重新加入集群,依旧和master-db2互为主主复制关系不需任何操作
8.总结:
mysql-mmm高可用特点:
1>当master-db1 down后,mysql-mmm的写VIP漂到master-db2上,并且自动完成slave-db3到master-db2的主从复制
更改,无需人为干预
2>可以监控从服务器的延迟,并发现超过某个值后,可以把读VIP迁移待其他延迟较小的服务器
mysql-mmm缺点:
1>只能是基于日志pos方式的复制,不支持基于GTID的复制也不支持多线程复制功能
2>master-db1 down后,只能选择master-db2作为新的主,可能master-db2的日志点的还没slave-db3的状态新,这样会导致数据的丢失,主从更改后还可能造成slave-db3的事务的重复提交等问题.