keepalived 做mysql的主从切换 |
您所在的位置：网站首页 › mysql主从切换脚本 › keepalived 做mysql的主从切换 |
|
适用于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 { [email protected] ! add a few more email addresses here if you would like } notification_email_from [email protected] ! 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 { [email protected] ! add a few more email addresses here if you would like } notification_email_from [email protected] ! 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库上同步
|
今日新闻 |
推荐新闻 |
| CopyRight 2018-2019 办公设备维修网 版权所有 豫ICP备15022753号-3 |