MHA+keepalived架构优化

MHA+keepalived 架构优化

说明：之前使用的架构，keepalived负责VIP的切换（keepalived通过调用脚本监测本机MySQL server是否存活，如果server宕掉，自动切换VIP到备库），MHA负责后端架构的切换。
       这样的架构很可能出现，VIP漂移了，而MHA架构没有切换，或者MHA架构切换了，而VIP没有漂移
       因此对该架构进行优化，调整为：keepalived只负责VIP的挂载，不检查MySQL server是否存活，MHA负责架构的切换及VIP的切换（MHA判定MySQL server宕掉后，关闭老主库的keepalived服务，启动新主库的keepalived服务，从而达到VIP的切换）

所有操作使用work账号操作

一、MHA配置
MHA配置分为全局和局部配置，全局配置即所有的主从集群共用的配置，局部配置即每个实例自己的配置，具体参见如下:
全局配置存放位置为管理机的/etc/masterha_default.cnf
cat /etc/masterha_default.cnf
[server default]
user            = mha
password        = ********
ssh_user        = work
ssh_port        = 1234
repl_user       = repl
repl_password   = ********
ping_interval   = 5
ping_type       = SELECT

ssh_connection_timeout = 30

secondary_check_script = masterha_secondary_check -s 192.168.66.88 --port=1234
master_ip_failover_script="/mha/scripts/master_ip_failover.sh"
master_ip_online_change_script="/mha/scripts/master_ip_online_change.sh"

二、配置work账号及权限
vim /etc/sudoers
加入启动账号（配置work账号可以以root身份执行/sbin/service命令，并且不需要密码）
work ALL=(root)NOPASSWD:/sbin/service
Defaults:work !requiretty #指定work账号不需要tty执行命令

备注：主从都需要配置

三、master_ip_failover_script脚本
cat scripts/master_ip_failover.sh
#!/bin/bash
#2016-12-12

source /etc/profile
# Set some variables from command line arguments.
    arg=$@
    for arg do
        val=`echo "$arg" | sed 's/^--[^=]*=//'`
        optname=`echo "$arg" | sed 's/^\(--[^=]*\)=.*$/\1/'`
        case "$arg" in
            --command=*) command="$val";;
            --ssh_user=*) ssh_user="$val";;
            --orig_master_host=*) orig_master_host="$val";;
            --orig_master_ip=*) orig_master_ip="$val";;
            --orig_master_port=*) orig_master_port="$val";;
            --new_master_host=*) new_master_host="$val";;
            --new_master_ip=*) new_master_ip="$val";;
            --new_master_port=*) new_master_port="$val";;
            --new_master_user=*) new_master_user="$val";;
            --new_master_password=*) new_master_password="$val";;
            *) ;;
        esac
        shift
    done

ssh_user=${ssh_user:-work};
ssh_port=${ssh_port:-1234};
orig_master_user=${orig_master_user:-mha};
orig_master_password=${orig_master_password:-********};
mysql_path=/usr/bin

if [[ X"${command}" == X || X"${orig_master_host}" == X || X"${orig_master_ip}" == X || X"${orig_master_port}" == X ]] ;then
    echo "
    USAGE:  $0      --command=<command> \\
                        [--ssh_user=<user> \]
                        --orig_master_host=<hostname> \\
                        --orig_master_ip=<ip> \\
                        --orig_master_port=<port> [\]
                        [--new_master_host=<hostname> \]
                        [--new_master_ip=<ip> \]
                        [--new_master_port=<port> \]
                        [--new_master_user=<user>\ ]
                        [--new_master_password=<password>\ ]

    "
    exit -1;
fi

function protect_process (){
trap 'echo "Control-C disabled."' SIGINT
trap 'echo "Cannot terminate this script."'  SIGQUIT
trap 'echo "Control-Z disabled."' SIGTSTP
}

function start_keepalived() {
    DATE=`date "+%Y-%m-%d %H:%M:%S"`
    ssh_start_keepalived="sudo service keepalived start"
    ssh -p$ssh_port ${ssh_user}@${new_master_ip} ${ssh_start_keepalived}

    if [ $?  -eq 0 ];then
      echo "${DATE} start_keepalived successful!"
    else
     exit -1
    fi
}

function stop_keepalived() {
    DATE=`date "+%Y-%m-%d %H:%M:%S"`
    ssh_stop_keepalived="sudo service keepalived stop"
    ssh -p$ssh_port ${ssh_user}@${orig_master_ip} ${ssh_stop_keepalived}

    if [ $?  -eq 0 ];then
      echo "${DATE} stop_keepalived successful!"
    else
     exit -1
    fi
}

function stop_mysql(){
    DATE=`date "+%Y-%m-%d %H:%M:%S"`
    stop_mysql_command="${mysql_path}/mysqladmin --host=${orig_master_ip} --port=${orig_master_port} --user=${orig_master_user} --password=${orig_master_password} --connect-timeout=5 --shutdown-timeout=10 shutdown";
    ssh -p$ssh_port ${ssh_user}@${orig_master_ip} "${stop_mysql_command}" >/dev/null 2>&1

    if [ $?  -ne 0 ];then
      sleep 5
      mysql_pid=`ssh -p$ssh_port ${ssh_user}@${orig_master_ip} "ps -eo pid,cmd | grep -E \"mysqld_safe|mysqld\" | grep -v grep "| awk '{print $1}' | head -n 1`
 
      for((i=0;i<5;i++))
      do
        DATE=`date "+%Y-%m-%d %H:%M:%S"`
        if [ X"${mysql_pid}" == X ];then
          echo "${DATE} Does not need to kill any mysqld process!"
          break
        fi
       
        echo "${DATE} kill again the mysqld process at ${orig_master_ip} PID is: ${mysql_pid}"
          ssh -p$ssh_port ${ssh_user}@${orig_master_ip} "ps -eo pid,cmd | grep -E \"mysqld_safe|mysqld\" | grep -v grep "| awk '{print $1}' |xargs kill -9
          sleep 1
      done
    fi

}

case $command in

    status)
    exit 0
    ;;

    stopssh)
    protect_process
    stop_mysql
    stop_keepalived
    exit 0
    ;;

    start)
    protect_process
    start_keepalived
    exit 0
    ;;

    *)
    echo "Invalid input!"
    exit 1

esac

四、master_ip_online_change脚本
cat scripts/master_ip_online_change.sh
#!/bin/bash
#set -x

source /etc/profile
# Set some variables from command line arguments.
    arg=$@
    for arg do
        val=`echo "$arg" | sed 's/^--[^=]*=//'`
        optname=`echo "$arg" | sed 's/^\(--[^=]*\)=.*$/\1/'`
        case "$arg" in
            --command=*) command="$val";;
            --ssh_user=*) ssh_user="$val";;
            --orig_master_host=*) orig_master_host="$val";;
            --orig_master_ip=*) orig_master_ip="$val";;
            --orig_master_port=*) orig_master_port="$val";;
            --new_master_host=*) new_master_host="$val";;
            --new_master_ip=*) new_master_ip="$val";;
            --new_master_port=*) new_master_port="$val";;
            --new_master_user=*) new_master_user="$val";;
            --new_master_password=*) new_master_password="$val";;
            *) ;;
        esac
        shift
    done

ssh_user=${ssh_user:-work};
ssh_port=${ssh_port:-1234};
orig_master_user=${orig_master_user:-mha};
orig_master_password=${orig_master_password:-********};
mysql_path=/usr/bin

if [[ X"${command}" == X || X"${orig_master_host}" == X || X"${orig_master_ip}" == X || X"${orig_master_port}" == X ]] ;then
echo "
USAGE:  $0      --command=<command> \\
                        [--ssh_user=<user> \]
                        --orig_master_host=<hostname> \\
                        --orig_master_ip=<ip> \\
                        --orig_master_port=<port> [\]
                        [--new_master_host=<hostname> \]
                        [--new_master_ip=<ip> \]
                        [--new_master_port=<port> \]
                        [--new_master_user=<user>\ ]
                        [--new_master_password=<password>\ ]

"
exit -1;
fi

function protect_process (){
trap 'echo "Control-C disabled."' SIGINT
trap 'echo "Cannot terminate this script."'  SIGQUIT
trap 'echo "Control-Z disabled."' SIGTSTP
}

function start_keepalived() {
    DATE=`date "+%Y-%m-%d %H:%M:%S"`
    ssh_start_keepalived="sudo service keepalived start"
    ssh -p$ssh_port ${ssh_user}@${new_master_ip} ${ssh_start_keepalived}

    if [ $?  -eq 0 ];then
      echo "${DATE} start_keepalived successful!"
    else
     exit -1
    fi
}

function stop_keepalived() {
    DATE=`date "+%Y-%m-%d %H:%M:%S"`
    ssh_stop_keepalived="sudo service keepalived stop"
    ssh -p$ssh_port ${ssh_user}@${orig_master_ip} ${ssh_stop_keepalived}

    if [ $?  -eq 0 ];then
      echo "${DATE} stop_keepalived successful!"
    else
     exit -1
    fi
}

function stop_mysql(){
    DATE=`date "+%Y-%m-%d %H:%M:%S"`
    stop_mysql_command="${mysql_path}/mysqladmin --host=${orig_master_ip} --port=${orig_master_port} --user=${orig_master_user} --password=${orig_master_password} --connect-timeout=5 --shutdown-timeout=10 shutdown";
    ssh -p$ssh_port ${ssh_user}@${orig_master_ip} "${stop_mysql_command}" >/dev/null 2>&1

    if [ $?  -ne 0 ];then
      sleep 5
      mysql_pid=`ssh -p$ssh_port ${ssh_user}@${orig_master_ip} "ps -eo pid,cmd | grep -E \"mysqld_safe|mysqld\" | grep -v grep "| awk '{print $1}' | head -n 1`
 
      for((i=0;i<5;i++))
      do
        DATE=`date "+%Y-%m-%d %H:%M:%S"`
        if [ X"${mysql_pid}" == X ];then
          echo "${DATE} Does not need to kill any mysqld process!"
          break
        fi
       
        echo "${DATE} kill again the mysqld process at ${orig_master_ip} PID is: ${mysql_pid}"
          ssh -p$ssh_port ${ssh_user}@${orig_master_ip} "ps -eo pid,cmd | grep -E \"mysqld_safe|mysqld\" | grep -v grep "| awk '{print $1}' |xargs kill -9
          sleep 1
      done
    fi

}

function set_mysql_read_only(){
    DATE=`date "+%Y-%m-%d %H:%M:%S"`
    set_mysql_read_only="${mysql_path}/mysql --host=${orig_master_ip} --port=${orig_master_port} --user=${orig_master_user} --password=${orig_master_password} -e 'set  global read_only=1;'";
    ssh -p$ssh_port ${ssh_user}@${orig_master_ip} "${set_mysql_read_only}" >/dev/null 2>&1
   
    if [ $?  -eq 0 ];then
 echo "${DATE} set_mysql_read_only successful!"
    else
      exit -1
    fi
}

case $command in

    status)
    exit 0
    ;;

    stop)
    protect_process
    set_mysql_read_only
    stop_keepalived
    exit 0
    ;;

    start)
    protect_process
    start_keepalived
    exit 0
    ;;

    *)
    echo "Invalid input!"
    exit 1

esac

五、测试验证
1、故障转移切换
停止主库MySQL服务，观察MHA切换情况
备注：备库keepalived初始是停止状态

2、在线切换
直接运行
masterha_master_switch --master_state=alive --global_conf=/etc/masterha_default.cnf --conf=/mha/appl/appl.cnf
观察切换情况
备注：在线切换需要关闭数据库事件 set global event_scheduler = off;