一 MMM 高可用mysql简介
MMM(Master-Master Replication mananger for mysql),由一个管理端(monitor)和多个代理端(agent)构成。通过MMM可以实现监控和管理Mysql主主复制和服务状态,同时也可监控多个Slave节点的复制以及运行状态,并且可以做到任何节点发生故障时实现自动化切换的功能。
MMM套件三个主要脚本:
mmm_mond:监控进程,运行在管理节点,主要负责对所有数据库的监控工作,同时决定和处理所有节点的角色切换。
mmm_agent:代理进程,运行在每台Mysql服务器,完成监控的测试工作和执行远程服务设置。
mmm_control:管理脚本,查看和管理集群运行状态,同时管理mmm_mond进程。
二 试验架构图
三:环境摘要
1.系统版本:RHEL 7.4 (3.10.0-693.el7.x86_64)
2.Mysql: 5.7.17
3.Mysql-mmm: 2.2.1
4. 5台虚拟机eth3网卡桥接到eth0:macvtap
5.5台虚拟机桥接连网,配置网络源(mirrors.163.com ,mirrors.aliyun.com)
安装mysql-mmm包和依赖包perl
wget ftp://176.130.7.5/dvd.repo -O /root/桌面/a.repo
5台虚拟机安装以下安装包
mysql-mmm.noarch mysql-mmm-tools.noarch
mysql-mmm-agent.noarch mysql-mmm-monitor.noarch
6.Mmm双主多从架构配置
角色 物理IP Server
id vip Ip 功能
Master1 176.130.7.90 1 176.130.7.100 Write IP 写入VIP,单点写入
Master2 176.130.7.91 2 176.130.7.101
Reader IP 读查询VIP,每个节点1个读VIP,可实现读的负载均衡
Slave1 176.130.7.92 3 176.130.7.102
Slave2 176.130.7.96 4 176.130.7.103
monitor 176.130.7.93 176.130.7.104
7.关闭selinux和firewall (虚拟机默认关闭)
8.确保5台虚拟机时间同步 (虚拟机时间已经同步)
9.5台虚拟机/etc/hosts添加
176.130.7.90 db1
176.130.7.91 db2
176.130.7.92 db3
176.130.7.96 db4
四:数据库主从和一主多从配置(具体配置步骤省略)
Master1创建ok后同步数据(mysqldump)到其他3台mysql虚拟机
/etc/my.cnf 注意点(默认配置,仅添加以下参数):
2台主库开启log_id;
4台mysql均添加参数:read_only=1 和 log_slave_updates
#“real_only"mysql_mmm严格控制读写,此参数只会禁止普通用户权限的mysql写操作,不能限制super权限用户的写操作; 如果要想连super权限用户的写操作也禁止,就使用"flush tables with read lock;”,这样设置也会阻止主从同步复制!数据迁移时master可以做此操作;
log_slave_updates 保证数库的一致性
Master1 添加3个用户(其他mysql会同步)
Grant replication slave on . to ‘repluser’@’%’ identified by ‘123456’;
grant replication client on . to ‘mmm_monitor’@’%’ identified by ‘123456’;
grant super, replication client, process on . to ‘mmm_agent’@’%’ identified by ‘123456’;
五:配置mysql-mmm(vim /etc/mysql-mmm/mmm_common.conf)
1.在所有MMM节点配置mmm_common.conf
Master1更改后scp给其他4台虚拟机
active_master_role writer
cluster_interface eth3
pid_path /run/mysql-mmm-agent.pid
bin_path /usr/libexec/mysql-mmm/
replication_user repluser
replication_password 123456
agent_user mmm_agent
agent_password 123456
include mmm_common.conf
ip 127.0.0.1 pid_path /run/mysql-mmm-monitor.pid bin_path /usr/libexec/mysql-mmm status_path /var/lib/mysql-mmm/mmm_mond.status ping_ips 176.130.7.90 , 176.130.7.91 , 176.130.7.92 ,176.130.7.96 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_user mmm_monitor monitor_password 123456
debug 0
3.在所有Mysql节点设置mmm_agent.conf
vim /etc/mysql-mmm/mmm_agent.conf
include mmm_common.conf
this db1 #四台mysql节点上设置对应的db,分别为db1、db2、db3、db4
4.启动mmm服务
Monitor:systemctl restart mysql-mmm-monitor
systemctl enable mysql-mmm-monitor
Mysql节点:systemctl restart mysql-mmm-agent
systemctl enable mysql-mmm-agent
5.mmm管理基本命令
[root@monitor mysql-mmm]# mmm_conrtol show
db1(176.130.7.90) master/ONLINE. Roles: reader(176.130.7.104), writer(176.130.7.100)
db2(176.130.7.91) master/ONLINE. Roles: reader(176.130.7.103)
db3(176.130.7.92) slave/ONLINE. Roles: reader(176.130.7.101)
db4(176.130.7.96) slave/ONLINE. Roles: reader(176.130.7.102)
查看每个节点运行状态
[root@monitor mysql-mmm]# mmm_control checks all
db4 ping [last change: 2019/05/19 12:53:25] OK
db4 mysql [last change: 2019/05/19 12:53:25] OK
db4 rep_threads [last change: 2019/05/19 12:53:25] OK
db4 rep_backlog [last change: 2019/05/19 12:53:25] OK: Backlog is null
db2 ping [last change: 2019/05/19 12:53:25] OK
db2 mysql [last change: 2019/05/19 15:43:29] OK
db2 rep_threads [last change: 2019/05/19 12:53:25] OK
db2 rep_backlog [last change: 2019/05/19 12:53:25] OK: Backlog is null
db3 ping [last change: 2019/05/19 12:53:25] OK
db3 mysql [last change: 2019/05/19 12:53:25] OK
db3 rep_threads [last change: 2019/05/19 12:53:25] OK
db3 rep_backlog [last change: 2019/05/19 12:53:25] OK: Backlog is null
db1 ping [last change: 2019/05/19 12:53:25] OK
db1 mysql [last change: 2019/05/19 12:53:25] OK
db1 rep_threads [last change: 2019/05/19 12:53:25] OK
db1 rep_backlog [last change: 2019/05/19 12:53:25] OK: Backlog is null
看mysql每个节点vip绑定状态
[root@host52 ~]# ip a s eth35: eth3: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP qlen 1000
link/ether 52:54:00:bb:85:c0 brd ff:ff:ff:ff:ff:ff
inet 176.130.7.90/24 brd 176.130.7.255 scope global dynamic eth3
valid_lft 81836sec preferred_lft 81836sec
inet 176.130.7.104/32 scope global eth3
valid_lft forever preferred_lft forever
inet 176.130.7.100/32 scope global eth3
valid_lft forever preferred_lft forever
inet6 fe80::9987:ca73:c0e6:3b43/64 scope link
valid_lft forever preferred_lft forever
六:测试
Master1 : mysql>create database test;
mysql>create user test@"%" identified by ‘123456’;
mysql>grant all on test.* to test@"%";
1.VIP测试
#写VIP登录(创建表单,插入数据测试略)
Mysql -utest -p123456 -h176.130.7.100
use test;
create table mmm_test(id varchar(60));
insert into mmm_test (id) values (“maseter”);
Exit
#读VIP登录
mysql -utest -p -h176.130.7.101
select * from test.mmm_test;
±---------------+
| Tables_in_test |
±---------------+
| mmm_test |
±---------------+
mysql -utest -p -h176.130.7.102
select * from test.mmm_test;
2.故障测试
Master1: systemctl stop mysqld
查看mmm_control show(写vip自动切换到master2)
[root@monitor mysql-mmm]# mmm_control show
db1(176.130.7.90) master/ONLINE. Roles: reader(176.130.7.104)
db2(176.130.7.91) master/ONLINE. Roles: reader(176.130.7.103), writer(176.130.7.100)
db3(176.130.7.92) slave/ONLINE. Roles: reader(176.130.7.101)
db4(176.130.7.96) slave/ONLINE. Roles: reader(176.130.7.102)
Master1: systemctl restart mysqld
查看mmm_control show(写vip不会切换到master1)
Master2编写存储过程(测试slave能否自动同步数据)
mysql> delimiter //
mysql> create procedure aa.p1()
-> begin
-> while 1 = 1
-> do
-> insert into aa.t1 values(88);
-> end while;
-> end
-> //
systemctl stop mysqld
Slave1查看:#验证:slave能更新数,monitor自动找master1,无需手动
mysql> select * from aa.t1;
±-----+
| id |
±-----+
| 88 |
| 88 |
…
| 88 |
±-----+
1021 rows in set (0.00 sec)
排错
root@monitor mysql-mmm]# mmm_control show
db4(176.130.7.96) slave/HARD_OFFLINE. Roles:
2种排错思路:ping不通 或者mysql连接中断(查看mmm_agent mmm_monitor用户)
root@monitor mysql-mmm]# mmm_control show
ERROR: Can’t connect to monitor daemon!
配置文件中debug改为1 默认是0 或者等一会查看 原因:debug调式monitor没有完全stop
总结:mysql_mmm
优点:主库-----监控状态,和故障转移;从库----自动同步主库数库
缺点:数库一致性不是很高,monitor是单点,做HA增加成本