keepalived 做mysql的主从切换

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

一、过程简介：

?
1
2
3
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的配置文件

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
[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"
}

注：

?
1
2
3
4
5
6
7
8
9
10
11
12
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的配置文件

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
[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"
}

注：

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

 

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

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
[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 切换到从库上，进行的一系列操作

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
[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库上同步

 

posted @   MR__Wang  阅读(1937)  评论(1)  编辑  收藏  举报
编辑推荐：
· SQL Server 2025 AI相关能力初探
· Linux系列：如何用 C#调用 C方法造成内存泄露
· AI与.NET技术实操系列（二）：开始使用ML.NET
· 记一次.NET内存居高不下排查解决与启示
· 探究高空视频全景AR技术的实现原理
阅读排行：
· 阿里最新开源QwQ-32B，效果媲美deepseek-r1满血版，部署成本又又又降低了！
· AI编程工具终极对决：字节Trae VS Cursor，谁才是开发者新宠？
· 开源Multi-agent AI智能体框架aevatar.ai，欢迎大家贡献代码
· Manus重磅发布：全球首款通用AI代理技术深度解析与实战指南
· 被坑几百块钱后，我竟然真的恢复了删除的微信聊天记录！
历史上的今天：
2020-09-02 文件属性及find命令总结
2020-09-02 系统优化
2019-09-02 tesseract 3.04在centos6上安装
点击右上角即可分享
微信分享提示
AI IDE Trae