MySQL mha 高可用集群搭建

【mha】

MHA作为MySQL故障切换和主从提升的高可用软件,在故障切换过程中，MHA能做到在0~30秒之内自动完成数据库的故障切换操作，并且在进行故障切换的过程中，MHA能在最大程度上保证数据的一致性，以达到真正意义上的高可用。

从宕机崩溃的master保存二进制日志事件（binlog events）;
识别含有最新更新的slave；
应用差异的中继日志（relay log）到其他的slave；
应用从master保存的二进制日志事件（binlog events）；
提升一个slave为新的master；
使其他的slave连接新的master进行复制；

一、架构概况
192.168.56.101   master
192.168.56.102   slave master
192.168.56.103   slave/mha manager
OS为centos 7.2

版本：MySQL 5.7.18

架构图：

二、安装步骤

1、安装MySQL【略】
2、配置基于组提交的半同步复制
3、配置mha
4、配置keepalive

三、配置基于组提交的半同步复制

master:
mysql> install plugin rpl_semi_sync_master soname 'semisync_master.so';  --安装 semisync_master.so插件 
master my.cnf
[mysqld]
rpl_semi_sync_master_enabled = 1 
rpl_semi_sync_master_timeout = 1000

slave:
root@localhost [zw3306]>install plugin rpl_semi_sync_slave soname 'semisync_slave.so'; --安装 semisync_slave.so插件
slave  my.cnf
[mysqld]
rpl_semi_sync_slave_enabled = 1


GRANT  REPLICATION SLAVE ON *.* TO 'repluser'@'192.168.56.102' IDENTIFIED  BY 'XXX';
GRANT  REPLICATION SLAVE ON *.* TO 'repluser'@'192.168.56.103' IDENTIFIED  BY 'XXX';


#MTS
slave_parallel_type=LOGICAL_CLOCK
slave_parallel_workers=4
master_info_repository=TABLE
relay_log_info_repository=TABLE
relay_log_recovery=ON

四、配置mha

1、三节点 ssh 互信配置(root用户)
2、192.168.56.101/192.168.56.102/192.168.56.103

yum install mha4mysql-node-0.54-0.el6.noarch.rpm
ln -s /app/mysql/bin/mysql /usr/bin/mysql
ln -s /app/mysql/bin/mysqlbinlog /usr/bin/mysqlbinlog

GRANT  REPLICATION SLAVE ON *.* TO 'repluser'@'192.168.56.%' IDENTIFIED  BY 'repluser';
grant all privileges on *.* to mha_check@'10.78.220.%' identified by 'mha_check';
flush privileges;

3、192.168.56.103

yum install mha4mysql-manager-0.55-0.el6.noarch.rpm
mkdir -p /etc/masterha/
vi /etc/masterha/mha.cnf 
--参考附件样例
vi /etc/masterha/master_ip_failover 
--参考附件样例

4、启动

验证
masterha_check_ssh --conf=/etc/masterha/mha.conf 
masterha_check_status --conf=/etc/masterha/mha.conf 
masterha_check_repl --conf=/etc/masterha/mha.conf 

启动MHA
nohup masterha_manager --conf=/etc/masterha/mha.conf  --ignore_fail_on_start >/etc/masterha/mha_manager.log 2>&1 &

五、配置keepalive

安装依赖包 :
yum install -y openssl openssl-devel
yum install popt-devel -y
yum -y install gcc automake autoconf libtool make
-----------------------------------------------------------------------------------------------
安装：
tar -zxvf keepalived-1.2.7.tar.gz  
cd keepalived-1.2.7  
  
./configure --prefix=/usr/local/keepalived    

make && make install  

copy文件到指定目录
cp /usr/local/keepalived/sbin/keepalived /usr/sbin/  
cp /usr/local/keepalived/etc/sysconfig/keepalived /etc/sysconfig/  
cp /usr/local/keepalived/etc/rc.d/init.d/keepalived /etc/init.d/ 

配置文件
mkdir /etc/keepalived  
cd /etc/keepalived 

[root@master ~]# vi /etc/keepalived/keepalived.conf
! Configuration File forkeepalived
global_defs {
router_id MYSQL_HA      #标识，双主相同
 }
vrrp_instance VI_1 {
 state BACKUP           #两台都设置BACKUP
 interface eth0
 virtual_router_id 101       #主备相同
 priority 100           #优先级，backup设置90
 advert_int 1
 nopreempt             #不主动抢占资源，只在master这台优先级高的设置，backup不设置
 authentication {
 auth_type PASS
 auth_pass 1111
 }
 virtual_ipaddress {
 10.70.195.20
 }
}

[root@master ~]# /etc/init.d/keepalived start