MySQL高可用解决方案MMM

xkorey 2017-04-08

一、MMM简介:

MMM即Multi-Master Replication Manager for MySQL:mysql多主复制管理器,基于perl实现,关于mysql主主复制配置的监控、故障转移和管理的一套可伸缩的脚本套件(在任何时候只有一个节点可以被写入),MMM也能对从服务器进行读负载均衡,所以可以用它来在一组用于复制的服务器启动虚拟ip,除此之外,它还有实现数据备份、节点之间重新同步功能的脚本。MySQL本身没有提供replication failover的解决方案,通过MMM方案能实现服务器的故障转移,从而实现mysql的高可用。MMM不仅能提供浮动IP的功能,如果当前的主服务器挂掉后,会将你后端的从服务器自动转向新的主服务器进行同步复制,不用手工更改同步配置。这个方案是目前比较成熟的解决方案。详情请看官网:http://mysql-mmm.org

MySQL高可用解决方案MMM

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

缺点: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了

相关推荐