MySQL纯Shell，主从切换脚本

#!/bin/bash
# Filename: coredb_3306_switch.sh


function log() {
    time_now=$(date +%F-%T)
    echo "$time_now  $1"
}

function log_red() {
    time_now=$(date +%F-%T)
    echo -e "$time_now \033[31;49;1m $1 \033[39;49;0m"
}

function log_blue() {
    time_now=$(date +%F-%T)
    echo -e "$time_now \033[34;49;1m $1 \033[39;49;0m"
}

function help() {
    log_blue "3306 主从切换脚本"
    log_blue "确保仔细阅读并理解该脚本，才能使用"
    log_blue "usage: bash -x $0 3306"
    log_blue "Exit"
}

function mysql_exec() {
    IP=$1
    SQL_STR=$2
    mysql -s -h "$IP" -P "$PORT" -uroot -p123456 -e "$SQL_STR"
}

if [ $# -eq 0 ]; then
    help
    exit
fi

if [ "$1" != "3306" ]; then
    help
    exit
fi


# 执行脚本 SLAVE1_IP 将被切换为主库，其它均作为 SLAVE1_IP 的从库
# 如果后面发现出现什么问题，希望回切，调换 MASTER_IP 与 SLAVE1_IP ，再次执行该脚本即可
MASTER_IP=iptest1  #192.168.1.1
SLAVE1_IP=iptest2  #192.168.1.2
SLAVE2_IP=iptest3  #192.168.1.3
SLAVE3_IP=iptest4  #192.168.1.4
SLAVE4_IP=iptest5  #192.168.1.5
PORT=$1


log_blue "STEP0 Port $PORT，开始主从切换"
log "当前的主从关系如下: "
log "MASTER $MASTER_IP"
log "$MASTER_IP: SHOW SLAVE HOSTS"
mysql_exec $MASTER_IP "SHOW SLAVE HOSTS"


log_blue "STEP1 SLAVE1: 检查 $SLAVE1_IP 延迟情况"
mysql_exec $SLAVE1_IP "SHOW SLAVE STATUS\G"
#slave状态如有异常，退出程序，此时可手动检查slave状态，待状态恢复正常后，重新执行该脚本
#check Slave_IO_Running: Yes
if ! mysql_exec $SLAVE1_IP "SHOW SLAVE STATUS\G" | grep "Slave_IO_Running: Yes"; then
    log_red "ERROR SLAVE1 $SLAVE1_IP 状态错误 EXIT"
    exit
fi
#check Slave_SQL_Running: Yes
if ! mysql_exec $SLAVE1_IP "SHOW SLAVE STATUS\G" | grep "Slave_SQL_Running: Yes"; then
    log_red "ERROR SLAVE1 $SLAVE1_IP 状态错误 EXIT"
    exit
fi
#check Seconds_Behind_Master: 0
if ! mysql_exec $SLAVE1_IP "SHOW SLAVE STATUS\G" | grep "Seconds_Behind_Master: 0"; then
    log_red "ERROR SLAVE1 $SLAVE1_IP 状态错误 EXIT"
    exit
fi

log_blue "STEP2 MASTER: 锁定 MASTER $MASTER_IP"
#为避免迁移过程中出现一些乱七八糟的问题，迁移过程中短暂锁表，这样主从均不可写，严格保证数据一致，几秒的主库不可用代价，可以接受
log "如果有大事务等正在执行，执行锁表语句，系统将持续等待这些大事务执行完毕，该脚本将持续夯住"
log "必要情况下 CTRL+C 退出该脚本，登录Master $MASTER_IP"
log "kill相关大事务，解锁表，恢复读写状态，UNLOCK TABLES; SET GLOBAL read_only = OFF"
log "之后根据情况，重新执行该脚本"
mysql_exec $MASTER_IP "FLUSH TABLES WITH READ LOCK"
mysql_exec $MASTER_IP "SET GLOBAL read_only = ON"
mysql_exec $MASTER_IP 'SHOW VARIABLES LIKE "%read_only%"'



log_blue "STEP3 MASTER: 拿到 $MASTER_IP binlog 位置"
mysql_exec $MASTER_IP "SHOW MASTER STATUS\G"
binlog_file=$(mysql_exec $MASTER_IP "SHOW MASTER STATUS\G" | grep "File:" | awk '{print $2}')
binlog_pos=$(mysql_exec $MASTER_IP "SHOW MASTER STATUS\G" | grep "Position:" | awk '{print $2}')
log "当前 MASTER $MASTER_IP BINLOG FILE $binlog_file"
log "当前 MASTER $MASTER_IP BINLOG POS  $binlog_pos"


log_blue "STEP4 SLAVE1: 让 $SLAVE1_IP 追上 MASTER $MASTER_IP"
#前面已经有 Seconds_Behind_Master: 0 检查，此处是进一步确保在主从切换过程中，主从数据完全一致
mysql_exec $SLAVE1_IP "SELECT MASTER_POS_WAIT('$binlog_file', $binlog_pos)"


log_blue "STEP5 SLAVE1: 清空 $SLAVE1_IP 原来的MASTER信息"
mysql_exec $SLAVE1_IP "RESET MASTER"


log_blue "STEP6 MASTER: 将 $MASTER_IP 设置为 SLAVE1 $SLAVE1_IP 的从库"
log "MASTER: 清空 $MASTER_IP SLAVE 信息"
#这一步其实是看情况的，MASTER_IP 可能根本没有从库信息，不过清理一下也无妨
#SLAVE1 上的 MASTER 信息已被 RESET，因此这里不需要指定文件位置
mysql_exec $MASTER_IP "STOP SLAVE"
mysql_exec $MASTER_IP "RESET SLAVE ALL"
mysql_exec $MASTER_IP "change master to master_host='$SLAVE1_IP', master_port=$PORT, master_user='replica', master_password='GgjnW3Uk'"
mysql_exec $MASTER_IP "START SLAVE"
mysql_exec $MASTER_IP "SHOW SLAVE STATUS\G"


log_blue "STEP7 SLAVE2、SLAVE3、SLAVE4: 将 $SLAVE2_IP、$SLAVE3_IP、$SLAVE4_IP $BACKUP_IP 设置为 SLAVE1 $SLAVE1_IP 的从库"
for SIP in $SLAVE2_IP $SLAVE3_IP $SLAVE4_IP; do
    mysql_exec $SIP "SELECT MASTER_POS_WAIT('$binlog_file', $binlog_pos)"
    mysql_exec $SIP "STOP SLAVE"
    mysql_exec $SIP "RESET SLAVE ALL"
    mysql_exec $SIP "change master to master_host='$SLAVE1_IP', master_port=$PORT, master_user='replica', master_password='GgjnW3Uk'"
    mysql_exec $SIP "START SLAVE"
    mysql_exec $SIP "SHOW SLAVE STATUS\G"
done


log_blue "STEP8 MASTER: $MASTER_IP 解锁"
#其实这一步可以不用执行，加锁是会话级别的，SQL执行结束终端退出，即解锁完成
#不过执行一遍解锁也无妨
mysql_exec $MASTER_IP "UNLOCK TABLES"


log_blue "STEP9 SLAVE1: $SLAVE1_IP 关闭只读"
mysql_exec $SLAVE1_IP "SET GLOBAL read_only = OFF"


log_blue "STEP10 SLAVE1: $SLAVE1_IP 取消双主模式"
sleep 2 #缓冲2秒
mysql_exec $SLAVE1_IP "STOP SLAVE"
mysql_exec $SLAVE1_IP "RESET SLAVE ALL"


log_blue "SWITCH MASTER SLAVE OK"
log "切换后主从关系如下:"
log "MASTER $SLAVE1_IP"
log "$SLAVE1_IP: SHOW SLAVE HOSTS"
mysql_exec $SLAVE1_IP "SHOW SLAVE HOSTS"