mysql-mmm高可用群集
一、mysql-mmm集群技术概述
二、mysql-mmm优缺点
三、mysql-mmm内部工作架构
四、案例搭建mysql-mmm+mysql 5.6
一、mysql-mmm集群技术概述
概述:MMM(Master-Master replication manager for Mysql)是一套支持双主故障切换和双主日常管理的脚本程序。MMM使用perl语言开发，主要用来监控和管理MySQLMaster-Master(双主)复制,可以说是mysql主主复制管理器。
虽然叫做双主复制，但是业务上同一时刻只允许对一个主进行写入，另一台备选主上提供部分读服务，以加速在主主切换时刻备选主的预热，可以说MMM这套脚本程序一方面实现了故障切换的功能，另一方面其内部附加的工具脚本也可以实现多个slave的read负载均衡。
MMM提供了自动和手动两种方式移除一组服务器中复制延迟较高的服务器的虚拟ip，同时它可以备份数据，实现两节点之间的数据同步等。由于MMM无法完全的保证数据一致性，所以MMM适用于对数据的一致性要求不是很高，但是又想最大程度的保证业务可用性的场景。
二、MySQL-mmm优缺点
优点:高可用性,扩展性好,出现故障自动切换,对于主主同步,在同一时间只提供一台数据库写操作,保证的数据的一致性
缺点:Monitor节点是单点，可以结合keepalived实现高可用，对主机的数量有要求，需要实现读写分离，对程序来说是个挑战
三、mysql-mmm内部工作架构
进程类型:
mmm_mond:监控进程，负责所有的监控工作，决定和处理所有节点角色活动。此脚本需要在监管机上运行
mmm_agentd:运行在每个mysql服务器上(Master和Slave)的代理进程，完成监控的探针工作和执行简单的远端服务设置。此脚本需要在监管机上运行；
mmm_control:一个简单的脚本，提供管理mmm_mond进程的命令
工作架构:
工作原理:
mysql-mmm的监管端会提供多个虚拟ip(vip)包括一个可写vip,多个可读vip;通过监管的管理，这些ip会绑定在可用mysql之上；当某一台mysql宕机时，监管会将vip迁移至其他mysql；
四、案例搭建mysql-mmm+mysql5.6双主高可用集群
环境
CentOS 7.4 master1 192.168.10.205
CentOS 7.4 master2 192.168.10.213
CentOS 7.4 slave1 192.168.10.214
CentOS 7.4 slave2 192.168.10.215
CentOS 7.4 monitor 192.168.10.216
CentOS 7.4 client 192.168.10.217
master1 192.168.10.205
[root@localhost ~]# hostnamectl set-hostname master1
[root@localhost ~]# bash
[root@master1 ~]# cat <<END >>/etc/hosts
192.168.10.205 master1
192.168.10.213 master2
192.168.10.214 slave1
192.168.10.215 slave2
192.168.10.216 monitor
192.168.10.217 client
END
[root@master1 ~]# yum -y install ntp
[root@master1 ~]# sed -i '/^server/s/^/#/g' /etc/ntp.conf
[root@master1 ~]# cat <<END >>/etc/ntp.conf
server 127.127.1.0
fudge 127.127.1.0 stratum 8
END
[root@master1 ~]# systemctl start ntpd
[root@master1 ~]# systemctl enable ntpd
[root@master1 ~]# cat <<END >>/etc/my.cnf
server-id=1
log-bin=mysql-bin
log-slave-updates
sync_binlog=1
auto_increment_increment=2
auto_increment_offset=1
relay-log=relay1-log-bin
relay-log-index=slave-relay1-bin.index
END注解:
sync_binlog=1 ##主机每次提交事务的时候把二进制日志的内容同步到磁盘上，所以即使服务器崩溃，也会把时间写入到日志中；
auto_increment_increment=2 ##以下两参数用于主主复制中，用于错开增值，防止键值冲突
[root@master1 ~]# systemctl restart mysqld
[root@master1 ~]# mysql -u root -p123123
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000006 | 154 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
mysql> grant replication slave on *.* to 'master'@'192.168.10.%' identified by '123123';
mysql> flush privileges;
mysql>exit;
[root@master1 ~]# cat /usr/local/mysql/data/auto.cnf
[auto]
server-uuid=78af5c04-586c-11ec-a2b9-000c2974148e
[root@master1 ~]# mysql -u root -p123123
mysql> change master to master_host='192.168.10.213' ,master_user='master' ,master_password='123123' ,master_log_file='mysql-bin.000002' ,master_log_pos=612;
mysql> start slave;
mysql> show slave status\G;
mysql> create database qwe;
上传软件包Mysql_mmm_rpm
[root@moot@master1 ~]# ls
anaconda-ks.cfg boost_1_59_0.tar.gz mysql-5.7.12.tar.gz Mysql_mmm_rpm
[root@master1 ~]# scp -r Mysql_mmm_rpm root@192.168.10.213:/root/
[root@master1 ~]# scp -r Mysql_mmm_rpm root@192.168.10.214:/root/
[root@master1 ~]# scp -r Mysql_mmm_rpm root@192.168.10.215:/root/
[root@master1 ~]# scp -r Mysql_mmm_rpm root@192.168.10.216:/root/
[root@master1 ~]# cd /etc/yum.repos.d/
[root@master1 ~]# vim linux.repo
[linux]
name=linux
baseurl=file:///mnt/
enabled=1
gpgcheck=0
[Mysql_mmm_rpm]
name=Mysql_mmm_rpm
baseurl=file:///root/Mysql_mmm_rpm
enabled=1
gpgcheck=0
[root@master1 yum.repos.d]# scp linux.repo root@192.168.10.213:/etc/yum.repos.d/
[root@master1 yum.repos.d]# scp linux.repo root@192.168.10.214:/etc/yum.repos.d/
[root@master1 yum.repos.d]# scp linux.repo root@192.168.10.215:/etc/yum.repos.d/
[root@master1 yum.repos.d]# scp linux.repo root@192.168.10.216:/etc/yum.repos.d/
[root@master1 ~]# yum -y install mysql-mmm mysql-mmm-agent mysql-mmm-tools
[root@master1 ~]# mysql -uroot -p123123
mysql> grant replication client on *.* to 'mmm_agent'@'192.168.10.%' identified by 'agent';
mysql> grant super,replication client,process on *.* to 'mmm_agent'@'192.168.10.%' identified by 'agent';
mysql> flush privileges;权限注解:
replication client:权限用于执行show master status等命令。这些命令是用来查看复制状态的;
replicationslave:是用于连接主库从库进行读取二进制文件进而实现复制的;
super:杀死mysql中连接的进程，设置全局变量，重置主从配置的权限;
process:具有查看当前运行的sql的权限，以及explain执行计划;
mysql> exit;
[root@monitor ~]# systemctl start mysql-mmm-agent
[root@master1 ~]# netstat -utpln |grep mmm
tcp 0 0 192.168.10.205:9989 0.0.0.0:* LISTEN 2295/mmm_agentd
master2 192.168.10.213
[root@localhost ~]# hostnamectl set-hostname master2
[root@localhost ~]# bash
[root@master2 ~]# cat <<END >>/etc/hosts
> 192.168.10.205 master1
> 192.168.10.213 master2
> 192.168.10.214 slave1
> 192.168.10.215 slave2
> 192.168.10.216 monitor
> 192.168.10.217 client
> END
[root@master2 ~]# yum -y install ntpdate
[root@master2 ~]# ntpdate 192.168.10.205
[root@master2 ~]# cat<<END >>/etc/my.cnf
server-id=2
log-bin=mysql-bin
log-slave-updates
sync_binlog=1
auto_increment_increment=2
auto_increment_offset=1
relay-log=relay1-log-bin
relay-log-index=slave-relay2-bin.index
END
[root@master2 ~]# systemctl restart mysqld
[root@master2 ~]# mysql -u root -p123123
mysql> change master to master_host='192.168.10.205' ,master_user='master' ,master_password='123123' ,master_log_file='mysql-bin.000006' ,master_log_pos=154;
mysql> start slave;
mysql> show slave status\G;
mysql> exit
[root@master2 ~]# cat /usr/local/mysql/data/auto.cnf
[auto]
server-uuid=78af5c04-586c-11ec-a2b9-000c2974148e ##因为UUID一致导致IO为NO
[root@master2 ~]# rm -rf /usr/local/mysql/data/auto.cnf
[root@master2 ~]# systemctl restart mysqld
[root@master2 ~]# cat /usr/local/mysql/data/auto.cnf
[auto]
server-uuid=57033508-640d-11ec-8432-000c2976e730
[root@master2 ~]# mysql -u root -p123123
mysql> show slave status\G
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000002 | 612 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
mysql>grant replication slave on *.* to 'master'@'192.168.10.%' identified by '123123';
mysql> flush privileges;
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| qwe |
| sys |
+--------------------+
5 rows in set (0.05 sec)
[root@master2 ~]# vim /etc/mysql-mmm/mmm_agent.conf
this db2 ##更改为thisdb2
[root@master2 ~]# systemctl start mysql-mmm-agent
slave1 192.168.10.214
[root@localhost ~]# hostnamectl set-hostname slave1
[root@localhost ~]# bash
[root@slave1 ~]# cat <<END >>/etc/hosts
> 192.168.10.205 master1
> 192.168.10.213 master2
> 192.168.10.214 slave1
> 192.168.10.215 slave2
> 192.168.10.216 monitor
> 192.168.10.217 client
> END
[root@slave1 ~]# yum -y install ntpdate
[root@slave1 ~]# ntpdate 192.168.10.205
[root@slave1 ~]# rm -rf /usr/local/mysql/data/auto.cnf
[root@slave1 ~]# systemctl restart mysqld
[root@slave1 ~]#cat <<END >>/etc/my.cnf
server-id=4
relay-log=relay4-log-bin
relay-log-index=slave-relay4-bin.index
END
[root@slave1 ~]# systemctl restart mysqld
[root@slave1 ~]# mysql -u root -p123123
mysql> change master to master_host='192.168.10.205' ,master_user='master' ,master_password='123123' ,master_log_file='mysql-bin.000006' ,master_log_pos=154;
mysql> start slave;
mysql> show slave status\G;
[root@slave1 ~]# vim /etc/mysql-mmm/mmm_agent.conf
this db3 ##更改为thisdb3
[root@salve1~]# systemctl start mysql-mmm-agent
slave2 192.168.10.215
[root@localhost ~]# hostnamectl set-hostname slave2
[root@localhost ~]# bash
[root@slave2 ~]# cat <<END >>/etc/hosts
> 192.168.10.205 master1
> 192.168.10.213 master2
> 192.168.10.214 slave1
> 192.168.10.215 slave2
> 192.168.10.216 monitor
> 192.168.10.217 client
> END
[root@slave2 ~]# yum -y install ntpdate
[root@slave2 ~]# ntpdate 192.168.10.205
[root@slave2 ~]# rm -rf /usr/local/mysql/data/auto.cnf
[root@slave2 ~]# systemctl restart mysqld
[root@slave2 ~]# cat <<END >>/etc/my.cnf
server-id=5
relay-log=relay5-log-bin
relay-log-index=slave-relay5-bin.index
END
[root@slave2 ~]# systemctl restart mysqld
[root@slave2 ~]# mysql -u root -p123123
mysql> change master to master_host='192.168.10.205' ,master_user='master' ,master_password='123123' ,master_log_file='mysql-bin.000006' ,master_log_pos=154;
mysql> start slave;
mysql> show slave status\G;
[root@slave2 ~]# vim /etc/mysql-mmm/mmm_agent.conf
this db4 ##更改为thisdb4
[root@slave2 ~]# systemctl start mysql-mmm-agent
monitor 192.168.10.216[root@localhost ~]# hostnamectl set-hostname monitor
[root@localhost ~]# bash
[root@monitor ~]# cat <<END >>/etc/hosts
> 192.168.10.205 master1
> 192.168.10.213 master2
> 192.168.10.214 slave1
> 192.168.10.215 slave2
> 192.168.10.216 monitor
> 192.168.10.217 client
> END
[root@monitor ~]# yum -y install ntpdate
[root@monitor ~]# ntpdate 192.168.10.205
[root@monitor ~]# rm -rf /usr/local/mysql/data/auto.cnf
[root@monitor ~]# systemctl restart mysqld
[root@monitor ~]# yum -y install mysql-mmm mysql-mmm-tools mysql-mmm-monitor
[root@monitor ~]# vim /etc/mysql-mmm/mmm_common.conf
active_master_role writer
<host default>
cluster_interface ens33 ##修改对应的网卡类型
pid_path /run/mysql-mmm-agent.pid
bin_path /usr/libexec/mysql-mmm/
replication_user master ##集群授权的用户名
replication_password 123123 ##授权的密码
agent_user mmm_agent ##授权的用户名
agent_password agent ##授权的密码
</host>
<host db1>
ip 192.168.10.205 ##更改对应ip地址
mode master
peer db2 ##和db2互为主从
</host>
<host db2>
ip 192.168.10.213 ##更改对应ip地址
mode master
peer db1 ##和db1互为主从
</host>
<host db3>
ip 192.168.10.214 ##更改对应ip地址
mode slave
</host>
<host db4>
ip 192.168.10.215 ##更改对应ip地址
mode slave
</host>
<role writer> ##可写
hosts db1, db2
ips 192.168.100.250
mode exclusive ##同一时间只能存在一台主服务器
</role>
<role reader> ##可读
hosts db3, db4
ips 192.168.100.251, 192.168.100.252
mode balanced ##轮询
</role>
[root@monitor ~]# for i in 205 213 214 215;do scp /etc/mysql-mmm/mmm_common.conf root@192.168.10.$i:/etc/mysql-mmm/; done
[root@monitor ~]# vim /etc/mysql-mmm/mmm_mon.conf
include mmm_common.conf
<monitor>
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 192.168.100.205,192.168.100.213,192.168.100.214,192.168.100.215 ##修改地址
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 monitor ##对应的密码
</host>
debug 0
[root@monitor ~]# systemctl daemon-reload
[root@monitor ~]# systemctl start mysql-mmm-monitor
[root@monitor ~]# netstat -utpln |grep 9988
[root@monitor ~]# mmm_control show
db1(192.168.100.205) master/ONLTNE. Roles: writer(192. 168.100.250)
db2(192.168.100.213) master/ONLINE. Roles:
db3(192.168.100.214) slave/ONLINE. Roles: reader(192. 168. 100.251)
db4(192.168.100.215) slave/ONLINE. Roles: reader( 192.168. 100.252)client 192.168.10.217
[root@localhost ~]# hostnamectl set-hostname client
[root@localhost ~]# bash
[root@client ~]# cat <<END >>/etc/hosts
> 192.168.10.205 master1
> 192.168.10.213 master2
> 192.168.10.214 slave1
> 192.168.10.215 slave2
> 192.168.10.216 monitor
> 192.168.10.217 client
> END
[root@client ~]# yum -y install ntpdate
[root@client ~]# ntpdate 192.168.10.205
[root@client ~]# rm -rf /usr/local/mysql/data/auto.cnf
[root@client ~]# systemctl restart mysqld本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
