利用阿里云高可用虚拟ip+keepalived+mha实现两台mysql的高可用

准备：

1.两台数据库主备环境搭建好，准备一台单独的mha_master，创建阿里云高可用虚拟ip：10.51.52.36

主：10.51.52.224  安装keepalived、mha_node

备：10.51.52.220  安装keepalived、mha_node

mha_master：10.51.52.223  安装mha_master、mha_node

2.keepalived，mha_master配置文件分别如下

主：

keepalived.conf

# cat /etc/keepalived/keepalived.conf
! Configuration File for keepalived

global_defs {
   notification_email {
     root@localhost
   }
   notification_email_from Alexandre.Cassen@firewall.loc
   smtp_server 127.0.0.1
   smtp_connect_timeout 30
   router_id internel_connection
   vrrp_skip_check_adv_addr
   #vrrp_strict
   vrrp_iptables
   vrrp_garp_interval 0
   vrrp_gna_interval 0
}

vrrp_script chk_mysql {
    script /etc/keepalived/check_mysql.sh
    interval 1
    weight -20
    fall 3
    rise 5
    timeout 2
}

vrrp_instance VI_1 {
    state BACKUP    #设置ECS实例为backup状态
    interface eth0    #设置网卡名，本示例配置为eth0 
    virtual_router_id 99   #定义分组ID，同一组ID相同
    priority 100     #设置优先级，数字越大，优先级越高
    advert_int 1
    # preempt_delay 300
    nopreempt
    unicast_src_ip 10.51.52.224    #设置ECS实例的私网IP地址
    unicast_peer {
        10.51.52.220     #同组中另一台ECS实例的私网IP地址
    }
    authentication {
        auth_type PASS
        auth_pass 1111
    }
    virtual_ipaddress {
        10.51.52.36 dev eth0 label eth0:0   #设置虚拟IP(Vip)的IP地址
    }
    track_script {
        chk_mysql   //执行检测脚本
    }
}        

备:

keepalived.conf

# cat /etc/keepalived/keepalived.conf
! Configuration File for keepalived

global_defs {
   notification_email {
     root@localhost
   }
   notification_email_from Alexandre.Cassen@firewall.loc
   smtp_server 127.0.0.1
   smtp_connect_timeout 30
   router_id internel_connection
   vrrp_skip_check_adv_addr
   #vrrp_strict
   vrrp_iptables
   vrrp_garp_interval 0
   vrrp_gna_interval 0
}

vrrp_script chk_mysql {
    script /etc/keepalived/check_mysql.sh
    interval 1
    weight -20
    fall 3
    rise 5
    timeout 2
}

vrrp_instance VI_1 {
    state BACKUP
    interface eth0
    virtual_router_id 99
    priority 90
    advert_int 1
    # preempt_delay 300
    # nopreempt
    unicast_src_ip 10.51.52.220
    unicast_peer {
        10.51.52.224
    }
    authentication {
        auth_type PASS
        auth_pass 1111
    }
    virtual_ipaddress {
        10.51.52.36 dev eth0 label eth0:0
    }
    track_script {
        chk_mysql
    }
}

注意：

上面服务器的keepalived都设置为了BACKUP模式，在keepalived中2种模式，分别是master->backup模式和backup->backup模式。这两种模式有很大区别。在master->backup模式下，一旦主节点宕机，虚拟ip会自动漂移到从节点，当主节点修复后，keepalived启动后，还会把虚拟ip抢占过来，即使设置了非抢占模式（nopreempt）抢占ip的动作也会发生。在backup->backup模式下，当主节点故障后虚拟ip会自动漂移到从节点上，当原主节点恢复后，并不会抢占新主的虚拟ip，即使是优先级高于从库的优先级别，也不会发生抢占。为了减少ip漂移次数，通常是把修复好的主库当做新的备库。

检测脚本

check_mysql.sh

#!/bin/bash
MYSQL=/opt/mysql/bin/mysql
MYSQL_HOST=127.0.0.1
MYSQL_USER=xxx
MYSQL_PASSWORD=xxx
CHECK_TIME=3
#mysql is working MYSQL_OK is 1, mysql is down MYSQL_OK is 0
MYSQL_OK=1
function check_mysql_health(){
    $MYSQL -h $MYSQL_HOST -u $MYSQL_USER -p$MYSQL_PASSWORD -e "show status;" >/dev/null 2>&1
    if [ $? = 0 ]; then
        MYSQL_OK=1
    else
        MYSQL_OK=0
    fi
    return $MYSQL_OK
}
while [ $CHECK_TIME -ne 0 ]
do
    let "CHECK_TIME -= 1"
    check_mysql_health
    if [ $MYSQL_OK = 1 ]; then
        CHECK_TIME=0
        exit 0
    fi
    if [ $MYSQL_OK -eq 0 ] &&  [ $CHECK_TIME -eq 0 ]; then
        pkill keepalived
        exit 1
    fi
done

mha_master配置如下：

[server default]
user=root
password=xxx
manager_workdir=/etc/mha_master/app1
manager_log=/etc/mha_master/manager.log
remote_workdir=/data/mha_master/app1
ssh_user=root
repl_user=replication
repl_password=xxxxxx
ping_interval=1
master_binlog_dir=/opt/mysql/logs
[server1]
hostname=db1.danny.com
ssh_port=22
candidate_master=1
[server2]
hostname=db2.danny.com
ssh_port=22
candidate_master=1

启动mha方式：

nohup masterha_manager --conf=/etc/mha_master/mha.cnf >>/etc/mha_master/manager.log 2&>1 &

3.添加阿里云虚拟IP绑定ES实例教程

https://help.aliyun.com/document_detail/184485.html?spm=5176.11182181.0.dexternal.b3254882uw8PRe#task-1938181

4.原理解析：

1）阿里云高可用虚拟IP+keepalived实现VIP主备绑定偏移，当keepalived通过检测脚本发现mysql服务宕了之后，自杀，备keepalived接替VIP并绑定至备机；

2）此例mha检测对象为两台mysql，一主一备，当检测一台mysql服务出问题后可以通过VIP将请求切换至备机。

3）此例使用keepalived实现vip偏移，能实现的原因是备机只有一台，主机服务挂了后，能确定vip肯定能漂移至新的主机。

4）缺点：

　　master主机宕机后无法切换，以及无法补全binlog，但是master的mysqld进程crash后，还是可以切换成功，以及补全binlog的。