基于Keepalived搭建MySQL的高可用集群

[mysqld]
log-bin=mysql-bin
server-id=1
log_slave_updates=1

[mysqld]
log-bin=mysql-bin
server-id=2
log_slave_updates=1
read_only=1

CREATE USER 'repl'@'192.168.244.146' IDENTIFIED BY 'mysql';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.244.146';

CREATE USER 'repl'@'192.168.244.145' IDENTIFIED BY 'mysql';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.244.145';

CHANGE MASTER TO
  MASTER_HOST='192.168.244.146',
  MASTER_USER='repl',
  MASTER_PASSWORD='mysql',
  MASTER_LOG_FILE='mysql-bin.000004',
  MASTER_LOG_POS=64729;

CHANGE MASTER TO
  MASTER_HOST='192.168.244.145',
  MASTER_USER='repl',
  MASTER_PASSWORD='mysql',
  MASTER_LOG_FILE='mysql-bin.000003',
  MASTER_LOG_POS=68479;

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

vrrp_script chk_mysql {script "/etc/keepalived/check_mysql.sh"
    interval 30         #设置检查间隔时长，可根据自己的需求自行设定
}
vrrp_instance VI_1 {state BACKUP        #通过下面的 priority 来区分 MASTER 和 BACKUP，也只有如此，底下的 nopreempt 才有效
    interface eno16777736
    virtual_router_id 51
    priority 100
    advert_int 1
    nopreempt           #防止切换到从库后，主 keepalived 恢复后自动切换回主库
    authentication {
        auth_type PASS
        auth_pass 1111
    }
    track_script {chk_mysql}
     
    virtual_ipaddress {192.168.244.10/24
    }
}

#!/bin/bash
 
### 判断如果上次检查的脚本还没执行完，则退出此次执行
if [`ps -ef|grep -w "$0"|grep -v "grep"|wc -l` -gt 2 ];then
    exit 0
fi 
mysql_con='mysql -uroot -p123456'
error_log="/etc/keepalived/logs/check_mysql.err"
 
### 定义一个简单判断 mysql 是否可用的函数
function excute_query {${mysql_con} -e "select 1;" 2>> ${error_log}
}
 
### 定义无法执行查询，且 mysql 服务异常时的处理函数
function service_error {echo -e "`date "+%F  %H:%M:%S"`    -----mysql service error，now stop keepalived-----" >> ${error_log}
    service keepalived stop &>> ${error_log}
    echo "DB1 keepalived 已停止"|mail -s "DB1 keepalived 已停止, 请及时处理！" slowtech@126.com 2>> ${error_log}
    echo -e "\n---------------------------------------------------------\n" >> ${error_log}
}
 
### 定义无法执行查询, 但 mysql 服务正常的处理函数
function query_error {echo -e "`date "+%F  %H:%M:%S"`    -----query error, but mysql service ok, retry after 30s-----" >> ${error_log}
    sleep 30
    excute_query
    if [$? -ne 0 ];then
        echo -e "`date "+%F  %H:%M:%S"`    -----still can't execute query-----" >> ${error_log}
 
        ### 对 DB1 设置 read_only 属性
        echo -e "`date "+%F  %H:%M:%S"`    -----set read_only = 1 on DB1-----" >> ${error_log}
        mysql_con -e "set global read_only = 1;" 2>> ${error_log}
 
        ###kill 掉当前客户端连接
        echo -e "`date "+%F  %H:%M:%S"`    -----kill current client thread-----" >> ${error_log}
        rm -f /tmp/kill.sql &>/dev/null
        ### 这里其实是一个批量 kill 线程的小技巧
        mysql_con -e 'select concat("kill ",id,";") from  information_schema.PROCESSLIST where command="Query"or command="Execute"into outfile"/tmp/kill.sql";'
        mysql_con -e "source /tmp/kill.sql"
        sleep 2    ### 给 kill 一个执行和缓冲时间
        ### 关闭本机 keepalived       
        echo -e "`date "+%F  %H:%M:%S"`    -----stop keepalived-----" >> ${error_log}
        service keepalived stop &>> ${error_log}
        echo "DB1 keepalived 已停止"|mail -s "DB1 keepalived 已停止, 请及时处理！" slowtech@126.com 2>> ${error_log}
        echo -e "\n---------------------------------------------------------\n" >> ${error_log}
    else
        echo -e "`date "+%F  %H:%M:%S"`    -----query ok after 30s-----" >> ${error_log}
        echo -e "\n---------------------------------------------------------\n" >> ${error_log}
    fi
}
 
### 检查开始: 执行查询
excute_query
if [$? -ne 0 ];then
    service mysqld status &>/dev/null
    if [$? -ne 0 ];then
        service_error
    else
        query_error
    fi
fi

! Configuration File for keepalived
 
vrrp_instance VI_1 {
    state BACKUP
    interface eno16777736
    virtual_router_id 51
    priority 90
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass 1111
    }
    notify_master /etc/keepalived/notify_master_mysql.sh    #此条指令告诉 keepalived 发现自己转为 MASTER 后执行的脚本
    virtual_ipaddress {192.168.244.10/24
    }
}

#!/bin/bash
### 当 keepalived 监测到本机转为 MASTER 状态时，执行该脚本
 
change_log=/etc/keepalived/logs/state_change.log
mysql_con='mysql -uroot -p123456'
echo -e "`date "+%F  %H:%M:%S"`   -----keepalived change to MASTER-----" >> $change_log
 
slave_info() {
    ### 统一定义一个函数取得 slave 的 position、running、和 log_file 等信息
    ### 根据函数后面所跟参数来决定取得哪些数据
    if [$1 = slave_status ];then
        slave_stat=`${mysql_con} -e "show slave status\G;"|egrep -w "Slave_IO_Running|Slave_SQL_Running"`
        Slave_IO_Running=`echo $slave_stat|awk '{print $2}'`
        Slave_SQL_Running=`echo $slave_stat|awk '{print $4}'`
    elif [$1 = log_file -a $2 = pos ];then
        log_file_pos=`${mysql_con} -e "show slave status\G;"|egrep -w "Master_Log_File|Read_Master_Log_Pos|Relay_Master_Log_File|Exec_Master_Log_Pos"`
        Master_Log_File=`echo $log_file_pos|awk '{print $2}'`
        Read_Master_Log_Pos=`echo $log_file_pos|awk '{print $4}'`
        Relay_Master_Log_File=`echo $log_file_pos|awk '{print $6}'`
        Exec_Master_Log_Pos=`echo $log_file_pos|awk '{print $8}'`
    fi
}
 
action() {### 经判断 ' 应该 & 可以 ' 切换时执行的动作
    echo -e "`date "+%F  %H:%M:%S"`    -----set read_only = 0 on DB2-----" >> $change_log
 
    ### 解除 read_only 属性
    ${mysql_con} -e "set global read_only = 0;" 2>> $change_log
 
    echo "DB2 keepalived 转为 MASTER 状态，线上数据库切换至 DB2"|mail -s "DB2 keepalived change to MASTER"\
    slowtech@126.com 2>> $change_log
 
    echo -e "---------------------------------------------------------\n" >> $change_log
}
 
slave_info slave_status
if [$Slave_SQL_Running = Yes];then
    i=0    #一个计数器
    slave_info log_file pos
        ### 判断从 master 接收到的 binlog 是否全部在本地执行(这样仍无法完全确定从库已追上主库，因为无法完全保证 io_thread 没有延时(由网络传输问题导致的从库落后的概率很小)
    until [$Master_Log_File = $Relay_Master_Log_File -a $Read_Master_Log_Pos = $Exec_Master_Log_Pos ]
     do
        if [$i -lt 10 ];then    #将等待 exec_pos 追上 read_pos 的时间限制为 10s
            echo -e "`date "+%F  %H:%M:%S"`    -----Relay_Master_Log_File=$Relay_Master_Log_File,Exec_Master_Log_Pos=$Exec_Master_Log_Pos is behind Master_Log_File=$Master_Log_File,Read_Master_Log_Pos=$Read_Master_Log_Pos, wait......" >> $change_log    #输出消息到日志，等待 exec_pos=read_pos
            i=$(($i+1))
            sleep 1
            slave_info log_file pos
        else
            echo -e "The waits time is more than 10s,now force change. Master_Log_File=$Master_Log_File Read_Master_Log_Pos=$Read_Master_Log_Pos Relay_Master_Log_File=$Relay_Master_Log_File Exec_Master_Log_Pos=$Exec_Master_Log_Pos" >> $change_log
            action
            exit 0
        fi
    done
    action 
 
else
    slave_info log_file pos
    echo -e "DB2's slave status is wrong,now force change. Master_Log_File=$Master_Log_File Read_Master_Log_Pos=$Read_Master_Log_Pos Relay_Master_Log_File=$Relay_Master_Log_File Exec_Master_Log_Pos=$Exec_Master_Log_Pos" >> $change_log
    action
fi 

#!/bin/bash
### 手动执行将主库切换回 DB1 的操作
 
mysql_con='mysql -uroot -p123456'
 
echo -e "`date "+%F  %H:%M:%S"`    -----change to BACKUP manually-----" >> /etc/keepalived/logs/state_change.log
echo -e "`date "+%F  %H:%M:%S"`    -----set read_only = 1 on DB2-----" >> /etc/keepalived/logs/state_change.log
$mysql_con -e "set global read_only = 1;" 2>> /etc/keepalived/logs/state_change.log
 
###kill 掉当前客户端连接
echo -e "`date "+%F  %H:%M:%S"`    -----kill current client thread-----" >> /etc/keepalived/logs/state_change.log
rm -f /tmp/kill.sql &>/dev/null
### 这里其实是一个批量 kill 线程的小技巧
$mysql_con -e 'select concat("kill ",id,";") from  information_schema.PROCESSLIST where command="Query"or command="Execute"into outfile"/tmp/kill.sql";'
$mysql_con -e "source /tmp/kill.sql" 2>> /etc/keepalived/logs/state_change.log
sleep 2    ### 给 kill 一个执行和缓冲时间
 
### 确保 DB1 已经追上了, 下面的 repl 为复制所用的账户，- h 后跟 DB1 的内网 IP
log_file_pos=`mysql -urepl -pmysql -h192.168.244.145 -e "show slave status\G;"|egrep -w "Master_Log_File|Read_Master_Log_Pos|Relay_Master_Log_File|Exec_Master_Log_Pos"`
Master_Log_File=`echo $log_file_pos|awk '{print $2}'`
Read_Master_Log_Pos=`echo $log_file_pos|awk '{print $4}'`
Relay_Master_Log_File=`echo $log_file_pos|awk '{print $6}'`
Exec_Master_Log_Pos=`echo $log_file_pos|awk '{print $8}'`
until [$Read_Master_Log_Pos = $Exec_Master_Log_Pos -a $Master_Log_File = $Relay_Master_Log_File ]
do
    echo -e "`date "+%F  %H:%M:%S"`    -----DB1 Exec_Master_Log_Pos($exec_pos) is behind Read_Master_Log_Pos($read_pos), wait......" >> /etc/keepalived/logs/state_change.log
    sleep 1
done
 
### 然后解除 DB1 的 read_only 属性
echo -e "`date "+%F  %H:%M:%S"`    -----set read_only = 0 on DB1-----" >> /etc/keepalived/logs/state_change.log
ssh 192.168.244.145 'mysql -uroot -p123456 -e"set global read_only = 0;"&& /etc/init.d/keepalived start' 2>> /etc/keepalived/logs/state_change.log
 
### 重启 DB2 的 keepalived 使 VIP 漂移到 DB1
echo -e "`date "+%F  %H:%M:%S"`    -----make VIP move to DB1-----" >> /etc/keepalived/logs/state_change.log
/sbin/service keepalived restart &>> /etc/keepalived/logs/state_change.log
 
echo "DB2 keepalived 转为 BACKUP 状态，线上数据库切换至 DB1"|mail -s "DB2 keepalived change to BACKUP" slowtech@126.com 2>> /etc/keepalived/logs/state_change.log
 
echo -e "--------------------------------------------------\n" >> /etc/keepalived/logs/state_change.log

May 25 14:37:09 master1 Keepalived_vrrp[3165]: VRRP_Instance(VI_1) Entering BACKUP STATE
May 25 14:37:09 master1 Keepalived_vrrp[3165]: VRRP sockpool: [ifindex(2), proto(112), unicast(0), fd(10,11)]
May 25 14:37:50 master1 Keepalived_vrrp[3165]: VRRP_Instance(VI_1) Now in FAULT state

KEEPALIVED_OPTIONS="-D -d -S 0"

/usr/local/keepalived/sbin/keepalived -d -D -S 0

# keepalived -S 0 
local0.*                                                /var/log/keepalived.log