网路互联
虚拟化容器,大数据,DBA,中间件,监控。

keepalived 做mysql的主从切换

10 01月
作者:admin|分类:DBA运维

适用于mysql master-slave的主从架构

一、过程简介：

1、通过keepalived配置VIP高可用，keepalived均设置为BACKUP ,nopreempt非抢占模式。
2、master上监控mysql 3306端口的状态，当检测到3306 端口停止后，停止keepalived，vip自动转移到slave上。
3、slave获取到vip升级为master后，执行changemasterdb脚本，将本地mysql库升级为master库，同时将其他从库的主库修改为本库的从库。

二、主从架构图

2.1、切换前的

 

2.2、切换后的

 

三、主要配置文件

主要配置文件及作用说明

3.1 keepalived.conf.master 用于当前为master的数据库上的keepalived的配置文件

[root@t156 keepalived]# more keepalived.conf.master
! Configuration File for Keepalived

! ---------------------------------------------------------------------------
! GLOBAL
! ---------------------------------------------------------------------------

global_defs {
	! this is who emails will go to on alerts
	notification_email {
		wanghengzhi@hw801.com
		! add a few more email addresses here if you would like
	}
	notification_email_from wanghengzhi@hw801.com
	
	! mail relay server
	smtp_server 127.0.0.1
	smtp_connect_timeout 30
	
	! each load balancer should have a different ID
	! this will be used in SMTP alerts, so you should make
	! each router easily identifiable
	router_id LVS_170
	vrrp_mcast_group4 224.0.0.18
	lvs_sync_daemon eth1 VI1_LVS_DB
	script_user root
}
vrrp_script check_mysql {
	script "/etc/keepalived/mysql_check.sh"
	interval 10
}

vrrp_instance VI1_LVS_DB {

	!state MASTER
	state BACKUP
	interface eth1

	track_interface {
		eth1
	}
	
	! interface to run LVS sync daemon on

	! lvs_sync_daemon_interface eth1

	!mcast_src_ip 192.168.1.156

	! each virtual router id must be unique per instance name!

	virtual_router_id 170

	! MASTER and BACKUP state are determined by the priority
	! even if you specify MASTER as the state, the state will
	! be voted on by priority (so if your state is MASTER but your
	! priority is lower than the router with BACKUP, you will lose
	! the MASTER state)
	! I make it a habit to set priorities at least 50 points apart
	! note that a lower number is lesser priority - lower gets less vote

	priority 100

	! how often should we vote, in seconds?

	advert_int 1

	! send an alert when this instance changes state from MASTER to BACKUP

	smtp_alert
	
	! this authentication is for syncing between failover servers
	! keepalived supports PASS, which is simple password
	! authentication or AH, which is the IPSec authentication header.
	! Don't use AH yet as many people have reported problems with it

	authentication {
		auth_type PASS
		auth_pass 111111
	}

	! these are the IP addresses that keepalived will setup on this
	! machine. Later in the config we will specify which real
	! servers  are behind these IPs without this block, keepalived
	! will not setup and takedown any IP addresses

	virtual_ipaddress {
		192.168.1.170/24 dev eth1
	}
	nopreempt
	!preempt_delay 2
	track_script {
		check_mysql
	}
	!notify_master "/etc/keepalived/changemasterdb.sh"
}

注：

state BACKUP #state 全部为BACKUP

priority 100 #当前是master的优先级高
vrrp_script check_mysql {
script "/etc/keepalived/mysql_check.sh" #vrrp_script 检测脚本
interval 10
}
nopreempt # vip 设置为非抢占模式

track_script {
check_mysql # 调用 vrrp_script检测脚本
}

3.2 keepalived.conf.slave 用于当前为slave的数据库上的keepalived的配置文件

[root@t168 keepalived]# more keepalived.conf.backup 
! Configuration File for Keepalived

! ---------------------------------------------------------------------------
! GLOBAL
! ---------------------------------------------------------------------------

global_defs {
	! this is who emails will go to on alerts
	notification_email {
		wanghengzhi@hw801.com
		! add a few more email addresses here if you would like
	}
	notification_email_from wanghengzhi@hw801.com
	
	! mail relay server
	smtp_server 127.0.0.1
	smtp_connect_timeout 30
	
	! each load balancer should have a different ID
	! this will be used in SMTP alerts, so you should make
	! each router easily identifiable
	router_id LVS_170
	vrrp_mcast_group4 224.0.0.18
	lvs_sync_daemon eth1 VI1_LVS_DB
	script_user root
}

vrrp_instance VI1_LVS_DB {

	state BACKUP
	interface eth1

	track_interface {
		eth1
	}
	
	! interface to run LVS sync daemon on

	! lvs_sync_daemon_interface eth1

	!mcast_src_ip 192.168.1.168

	! each virtual router id must be unique per instance name!

	virtual_router_id 170

	! MASTER and BACKUP state are determined by the priority
	! even if you specify MASTER as the state, the state will
	! be voted on by priority (so if your state is MASTER but your
	! priority is lower than the router with BACKUP, you will lose
	! the MASTER state)
	! I make it a habit to set priorities at least 50 points apart
	! note that a lower number is lesser priority - lower gets less vote

	priority 90

	! how often should we vote, in seconds?

	advert_int 1

	! send an alert when this instance changes state from MASTER to BACKUP

	smtp_alert
	
	! this authentication is for syncing between failover servers
	! keepalived supports PASS, which is simple password
	! authentication or AH, which is the IPSec authentication header.
	! Don't use AH yet as many people have reported problems with it

	authentication {
		auth_type PASS
		auth_pass 111111
	}

	! these are the IP addresses that keepalived will setup on this
	! machine. Later in the config we will specify which real
	! servers  are behind these IPs without this block, keepalived
	! will not setup and takedown any IP addresses

	virtual_ipaddress {
		192.168.1.170/24 dev eth1
	}
	nopreempt
	notify_master "/etc/keepalived/changemasterdb.sh"
}

注：

state BACKUP #state 全部为BACKUP
priority 90 #当前是master的优先级高
nopreempt # vip 设置为非抢占模式
notify_master "/etc/keepalived/changemasterdb.sh" # 切换为master后执行的脚本

 

3.3 mysql_check.sh 用于主库上mysql 3306端口的检测

[root@t168 keepalived]# more mysql_check.sh 
#!/bin/bash
set -o nounset

#@Author  : wanghz
#@Time    : 2021/9/1 12:05

# define restricted path
PATH="/bin:/usr/bin:/sbin:/usr/sbin"

# adirname - return absolute dirname of given file
adirname() { odir=`pwd`; cd `dirname $1`; pwd; cd "${odir}"; }


PSW="123456"
PORT='3306'
MYSQL_BIN=`which mysql`
MYSQL_MASTER_BIN="${MYSQL_BIN} -uroot -p${PSW} -S /tmp/mysql${PORT}.sock"


count=1

while true
    do
    ${MYSQL_MASTER_BIN} -e "show status\G;" >/dev/null 2>&1
    Status=$?
    ps aux|grep mysqld|grep -v grep >/dev/null 2>&1
    Grep=$?
    if [ ${Status} = 0 ] && [ ${Grep} = 0 ] 
    then
        exit 0
    else
	if [ ${Status} = 1 ] && [ ${Grep} = 0 ] 
	then
        	exit 0
        else
         	if [ ${count} -gt 5 ] 
		then
			echo ${count}
                	break
                fi
     	let count+=1
     	continue
        fi
    fi
done

`which service` keepalived stop

3.4 changemasterdb.sh 用于到vip 切换到从库上，进行的一系列操作

[root@t168 keepalived]# more changemasterdb.sh 
#!/bin/bash
set -o nounset 
#数据库的端口
PORTS=( 3306 3308 )
PSW="123456"
REPL_USER="repl"
REPL_USER_PSW="repl123456"
#ANSIBLE_HOST_NAMES=( "168" )        #同 SLAVE_HOST_IP ，需要在/etc/ansible/hosts里配置
ANSIBLE_HOST_NAMES=( "159" )        #同 SLAVE_HOST_IP ，需要在/etc/ansible/hosts里配置
#[root@t168 ~]# more /etc/ansible/hosts
#[159]
#192.168.1.159


##############################################
#                                            #
#           配置修改开始                     #
#                                            #
##############################################

LOCAL_HOST_IP="192.168.1.168"   ##升级为主库的现有IP地址
MASTER_HOST_IP='192.168.1.170'  ## 主库的IP地址
SLAVE_HOST_IP="192.168.1.157"   ##原另一个从库的IP

MYSQL_BIN=`which mysql`
MYSQL_MASTER_BIN='${MYSQL_BIN} -uroot -p${PSW}'

##############################################
#                                            #
#           配置修改结束                       #
#                                            #
##############################################


# define restricted path
PATH="/usr/local/mysql/bin:/bin:/usr/bin:/sbin:/usr/sbin"

# adirname - return absolute dirname of given file
adirname() { odir=`pwd`; cd `dirname $1`; pwd; cd "${odir}"; }
MYNAM=`basename "$0"`
MYDIR=`adirname "$0"`
MYLOG_PATH="${MYDIR}/logs"
MYLOG="${MYLOG_PATH}/${MYNAM}_`date +%F`.log"
for D in ${MYLOG_PATH}
do
    if [ ! -d ${D} ] ; then
        mkdir -p ${D}
        echo -e "Mkdir ${D}" >> ${MYLOG}
    fi
done

# ---------
# functions
# ---------
#日志函数
function L(){
    message="$(date -d today +"%Y-%m-%d %H:%M:%S") - $1"
    echo -e "\033[34m  $message \033[0m" && echo $message >> ${MYLOG}
}

#主库挂，从库升级为主库
#1、keepalived 切vip到从库上
#2、脚本停止slave同步
#3、重看当前从库的pos和bin-logs记录
#4、修改从库为读写状态
#5、修改另一个从库来连接新的主库并查看同步状态

function CheckMasterIP(){
    #1、keepalived 切vip到主库上。keepalived为非抢占模式，切回来不自动切回去。
    #获取本地是否有vip
    VIP=`/bin/hostname -I|grep "${MASTER_HOST_IP}"`
    if [ "x${VIP}" == "x" ]; then
        L "vip${MASTER_HOST_IP}没有切到这个服务器上。"
        exit 1
    fi
    L "vip${MASTER_HOST_IP}切换到该服务器上成功"
    return 0
}

function StopSlave(){
    #2、停止当前slave同步
    ${MYSQL_MASTER_BIN} -e "stop slave"
    ${MYSQL_MASTER_BIN} -e "reset slave"
    L "${MYSQL_MASTER_BIN} -e \"stop slave\""
    SLAVESTATUS=`${MYSQL_MASTER_BIN} -e "show slave status\G"|grep "Slave_SQL_Running"|grep -v "State"|awk '{print $NF}'`
    L "${SLAVESTATUS} ${MYSQL_MASTER_BIN} -e \"show slave status\G\"|grep \"Slave_SQL_Running\"|grep -v \"State\"|awk '{print \$NF}'"
    if [ "${SLAVESTATUS}" != "No" ];then
        L "当前db${IPORT}停止slave失败。"
        exit 1
    fi
    L "停止当前DB${IPORT}的slave同步成功"
    return 0
}

function GetNewMasterPosNum(){
    #3、重看当前升级为主库的posnum记录
    #posnum
    Pos=`${MYSQL_MASTER_BIN} -e "show master status\G"|grep "Position"|awk -F " " '{print $2}'`
    L "新的master库${IPORT}的pos是${Pos}"
    return ${Pos}
}

function AlterReadOnlyStatus(){
    #4、修改当前库为读写状态
    ${MYSQL_MASTER_BIN} -e "set global read_only=0"
    #修改网卡配置文件，但不重启（防止该服务器重启后临时IP地址丢失）
    sed -i "s/read_only = 1/read_only = 0/g" /etc/my${IPORT}.cnf
    L "sed -i \"s/read_only = 1/read_only = 0/g\" /etc/my${IPORT}.cnf"
    ReadOnlyStatus=`${MYSQL_MASTER_BIN} -e "show variables like \"read_only\""|awk '{print $NF}'|sed -n '$p'`
    ReadOnlyConfig=`grep "read_only" /etc/my${IPORT}.cnf |awk '{print $NF}'`
    #echo "${ReadOnlyStatus}"
    if [ "${ReadOnlyStatus}" != "OFF" ] && [ "${ReadOnlyConfig}" != "0" ];then
        L "修改当前数据库${IPORT}的读写状态失败。"
        exit 1
    fi
    L "修改当前数据库${IPORT}的读写状态成功。"
    return 0
}

function AnsibleSlaveConnect(){
    #5、修改另一个从库来连接新的主库并查看同步状态
    for ANSIBLE_HOST_NAME in ${ANSIBLE_HOST_NAMES[@]}
    do
        ansible ${ANSIBLE_HOST_NAME} -m shell -a "${MYSQL_MASTER_BIN} -e \"stop slave\""
        L "ansible ${ANSIBLE_HOST_NAME} -m shell -a \"${MYSQL_MASTER_BIN} -e \"stop slave\"\""
        L "另一个从库${IPORT}停止原主从同步成功"
        ansible ${ANSIBLE_HOST_NAME} -m shell -a "${MYSQL_MASTER_BIN} -e \"reset slave\""
        L "另一个从库${IPORT}重置原主从同步成功"
        ansible ${ANSIBLE_HOST_NAME} -m shell -a "${MYSQL_MASTER_BIN} -e \"set global read_only=1\""
        L "获取新主库${IPORT}的bin-logs记录"
        LogFile=`${MYSQL_MASTER_BIN} -e "show master status\G"|egrep "File"|awk -F " " '{print $2}'`
        L "获取新主库${IPORT}的Pos记录"
        GetNewMasterPosNum
        ansible ${ANSIBLE_HOST_NAME} -m shell -a "${MYSQL_MASTER_BIN} -e \"change master to master_host='${MASTER_HOST_IP}',master_port=${IPORT},master_user='${REPL_USER}',master_password='${REPL_USER_PSW}',MASTER_LOG_FILE='${LogFile}',MASTER_LOG_POS=${Pos}\""
        L "另一个从库${IPORT}指定新的主库"
        L "ansible ${ANSIBLE_HOST_NAME} -m shell -a \"${MYSQL_MASTER_BIN} -e \"change master to master_host='${MASTER_HOST_IP}',master_port=${IPORT},master_user='${REPL_USER}',master_password='${REPL_USER_PSW}',MASTER_LOG_FILE=${LogFile},MASTER_LOG_POS=${Pos}\"\""
        ansible ${ANSIBLE_HOST_NAME} -m shell -a "${MYSQL_MASTER_BIN} -e \"start slave\""
        L "另一个从库${IPORT}开启同步"
        SlaveRsyncIp=`ansible ${ANSIBLE_HOST_NAME} -m shell -a "${MYSQL_MASTER_BIN} -e \"show slave status\G\""|grep "Master_Host"|awk '{print $NF}'`
        L "ansible ${ANSIBLE_HOST_NAME} -m shell -a "${MYSQL_MASTER_BIN} -e \"show slave status\G\""|grep "Master_Host"|awk '{print \$NF}'"
        L "SlaveRsyncIp ${SlaveRsyncIp} ,${MASTER_HOST_IP}"
        if [ "${SlaveRsyncIp}" != "${MASTER_HOST_IP}" ];then
            L "同步主库${IPORT}的IP错误"
            exit 1
        fi
        L "另一个从库${IPORT}配置完成，并开始同步"
        return 0
    done
}


##############################################
#                                            #
#           始设置本机为master db            #
#                                            #
##############################################
function MasterDB(){
    for IPORT in ${PORTS[@]}
    do	
        MYSQL_MASTER_BIN="`which mysql` -uroot -p${PSW} -P${IPORT} -S /tmp/mysql${IPORT}.sock"
        CheckMasterIP
        if [ $? != 0 ];then
            L "增加临时IP或修改IP配置文件失败."
            exit 1
        fi
        StopSlave
        if [ $? != 0 ];then
            L "停止${IPORT}的slave同步失败."
            exit 1
        fi 
        AlterReadOnlyStatus
        if [ $? != 0 ];then
            L "设置master${IPORT}库为读写库失败."
            exit 1
        fi 
        AnsibleSlaveConnect
        if [ $? != 0 ];then
            L "修改另一从库${IPORT}的master库为新的master库失败."
            exit 1
        fi 
    L "${IPORT}设置为新的主库完成！"
    done
}

##############################################
#                                            #
#              开始执行脚本                    #
#                                            #
##############################################

MasterDB

 

 至此，实现了mysql vip的自动切换，同时其他从库从新的master库上同步

 

更多文章推荐

历史上的今天
01月
10
浏览595 评论0
返回
目录 返回
首页
Centos7安装haproxy2.7实现4层与7层负载均衡 LVS 命令使用
↑