#!/bin/sh
#****************************************************************#
# ScriptName: mysql_switch.sh
#***************************************************************#
# MySQL主從切換腳本
# 主庫：1.1.1.1，備庫：1.1.1.2
 
# 配置參數（請根據實際情況修改）
USER="xxx"               # 數據庫用户名
PASSWORD="xxx"  # 數據庫密碼

PORT="xxx"               # 數據庫端口，直連DB遷移需要確保端口一致
DOMAIN="xxx.com"
 
MASTER_IP="1.1.1.1"  # 原主庫IP
SLAVE_IP="1.1.1.2"   # 原備庫IP（切換後為主庫）
 
# 日誌輸出函數
log() {
    echo "[$(date +'%Y-%m-%d %H:%M:%S')] $1"
}
 
RESOLVED_IP1=$(ping -W 0.2 -c 1 ${DOMAIN} 2>/dev/null | grep -oP '\(\K[^)]+' | head -n 1)
#if [ "${RESOLVED_IP1}" != "${MASTER_IP}" ]; then
#    log "域名解析異常（主庫IP:${MASTER_IP} 當前解析IP：${RESOLVED_IP1}），退出"
#    exit 1
#fi
if [ "${RESOLVED_IP1}" != "${MASTER_IP}" ] && [ "${RESOLVED_IP1}" != "${SLAVE_IP}" ]; then
    log "域名解析異常（主庫IP:${MASTER_IP} 備庫IP:${SLAVE_IP} 當前解析IP：${RESOLVED_IP1}），退出"
    exit 1
fi
log "域名當前解析IP：${RESOLVED_IP1}"
 
# 驗證主從關係
log "開始驗證主從關係..."
verify_master_slave() {
    # 檢查是否互為主備
 
    MASTER_CHECK=$(mysql -h ${MASTER_IP} -u${USER} -p${PASSWORD} -P${PORT} -BNe "show global variables like 'read_only';" 2>/dev/null | awk '{print $2}')
    if [[ ${MASTER_CHECK} != "OFF" ]];then
        log "當前主庫IP不為主庫 read_only:${MASTER_CHECK}，退出"
        exit 1
    fi
 
    log "檢查 ${MASTER_IP} ${SLAVE_IP} 是否互為主備..."
    MASTER_IP_Master_Host=$(mysql -h ${MASTER_IP} -u${USER} -p${PASSWORD} -P${PORT} -e "SHOW SLAVE STATUS\G" 2>/dev/null  | grep "Master_Host" | awk '{print $2}')
    MASTER_IP_Master_Port=$(mysql -h ${MASTER_IP} -u${USER} -p${PASSWORD} -P${PORT} -e "SHOW SLAVE STATUS\G" 2>/dev/null  | grep "Master_Port" | awk '{print $2}')
    SLAVE_IP_Master_Host=$(mysql -h ${SLAVE_IP} -u${USER} -p${PASSWORD} -P${PORT} -e "SHOW SLAVE STATUS\G" 2>/dev/null  | grep "Master_Host" | awk '{print $2}')
    SLAVE_IP_Master_Port=$(mysql -h ${SLAVE_IP} -u${USER} -p${PASSWORD} -P${PORT} -e "SHOW SLAVE STATUS\G" 2>/dev/null  | grep "Master_Port" | awk '{print $2}')
    log "採集信息，原主庫IP:${MASTER_IP},slave:${MASTER_IP_Master_Host},port:${MASTER_IP_Master_Port}，原備庫IP:${SLAVE_IP},slave:${SLAVE_IP_Master_Host},port:${SLAVE_IP_Master_Port}"
 
    if [[ ${MASTER_IP_Master_Host} != ${SLAVE_IP} || ${SLAVE_IP_Master_Host} != ${MASTER_IP} || ${PORT} != ${MASTER_IP_Master_Port} || ${PORT} != ${SLAVE_IP_Master_Port} || ${MASTER_IP} == ${SLAVE_IP} ]]; then
        log "錯誤：未進行互為主備配置，原主庫IP:${MASTER_IP},slave:${MASTER_IP_Master_Host},port:${MASTER_IP_Master_Port}，原備庫IP:${SLAVE_IP},slave:${SLAVE_IP_Master_Host},port:${SLAVE_IP_Master_Port}"
        return 1
    fi
 
    return 0
}
 
# 執行主從關係驗證
if ! verify_master_slave; then
    log "主備關係驗證失敗，終止切換操作"
    exit 1
else
     log "主備關係驗證正常..."
fi
 
log "開始檢查主從延遲..."
check_delay1() {
 
    DELAY=$(mysql -h ${SLAVE_IP} -u${USER} -p${PASSWORD} -P${PORT} -e "SHOW SLAVE STATUS\G" 2>/dev/null | grep "Seconds_Behind_Master" | awk '{print $2}')
    echo "DELAY time:$DELAY "
    # 處理異常情況（延遲為NULL或未獲取到值）
    if [[ -z "${DELAY}" || "${DELAY}" = "NULL" || ${DELAY} -gt 10 ]]; then
        log "主從同步狀態異常活延遲時間過長 DELAY=${DELAY}，退出切換"
        return 1
    fi
 
    log "當前主從延遲：${DELAY}秒"
    return 0
}
# 執行延遲檢查並獲取等待時間
if ! check_delay1; then
    log "主從延遲時間異常或超過10s，終止切換操作"
    exit 1
fi
 
# 1. 檢查域名解析是否指向備庫IP
log "開始檢查域名解析（目標：${SLAVE_IP}）..."
while true; do
    # 獲取域名解析的IP（過濾本地地址，取第一個有效IP）
    PING_OUTPUT=$(ping -W 0.2 -c 1 ${DOMAIN} 2>/dev/null)
    RESOLVED_IP=$(echo "${PING_OUTPUT}" | grep -oP '\(\K[^)]+' | head -n 1)
 
    if [ "${RESOLVED_IP}" = "${SLAVE_IP}" ]; then
        log "域名解析變更，當前指向備庫IP: ${SLAVE_IP}"
        break
    elif [ "${RESOLVED_IP}" = "${MASTER_IP}" ]; then
        log "域名解析正常，域名：${DOMAIN}（當前解析主庫IP：${RESOLVED_IP}），解析備庫生效後進行切換，1秒後重試..."
        sleep 1
    else
        log "域名解析異常（當前解析：${RESOLVED_IP}），退出"
        exit 1
    fi
done
 
#log "sleep 10s"
#sleep 10
 
# 2. 檢查主從延遲並確定等待時間
log "開始檢查主從延遲..."
check_delay2() {
 
    DELAY=$(mysql -h ${SLAVE_IP} -u${USER} -p${PASSWORD} -P${PORT} -e "SHOW SLAVE STATUS\G" 2>/dev/null | grep "Seconds_Behind_Master" | awk '{print $2}')
    echo "DELAY time:$DELAY "
    log "當前主從延遲：${DELAY}秒"
    return ${DELAY}
}
# 執行延遲檢查並獲取等待時間
check_delay2
DELAY=$?
log "當前主從延遲：${DELAY}秒"
 
# 3. 處理主庫：設置只讀並清理連接
log "開始處理原主庫（${MASTER_IP}）..."
# 設置主庫只讀
log "設置主庫為只讀模式..."
mysql -h ${MASTER_IP} -u${USER} -p${PASSWORD} -P${PORT} -e "SET GLOBAL read_only = 1; SET GLOBAL super_read_only = 1;" 2>/dev/null
if [ $? -ne 0 ]; then
    log "主庫設置只讀失敗，切換終止"
    exit 1
fi
 
# 只讀後等待延遲追平
if [ ${DELAY} -eq 0 ]; then
    SLEEP_TIME=1
    log "無延遲，將等待${SLEEP_TIME}秒後開始切換"
else
    SLEEP_TIME=$((DELAY + 1))
    log "存在延遲，將等待${SLEEP_TIME}秒後開始切換"
fi
sleep ${SLEEP_TIME}
 
# 3. 切換後檢測延遲
log "切換後檢測延遲..."
check_delay3() {
 
    DELAY=$(mysql -h ${SLAVE_IP} -u${USER} -p${PASSWORD} -P${PORT} -e "SHOW SLAVE STATUS\G" 2>/dev/null | grep "Seconds_Behind_Master" | awk '{print $2}')
    echo "DELAY time:$DELAY "
    log "當前主從延遲：${DELAY}秒"
    return ${DELAY}
}
# 執行延遲檢查並獲取等待時間
check_delay3
DELAY3=$?
log "當前主從延遲：${DELAY}秒"
 
# 只讀後等待延遲追平
if [ ${DELAY3} -ne 0 ]; then
   log "存在延遲，將等待${DELAY3}秒後繼續後續步驟"
   sleep ${DELAY3}
fi
 
# 清理非系統用户連接
log "清理主庫非系統連接..."
KILL_IDS=$(mysql -h ${MASTER_IP} -u${USER} -p${PASSWORD} -P${PORT} -NBe "
    SELECT ID FROM INFORMATION_SCHEMA.PROCESSLIST
    WHERE USER NOT IN ('event_scheduler', 'root', 'system user', 'slave')
      AND ID != CONNECTION_ID();
" 2>/dev/null)
 
if [ -n "${KILL_IDS}" ]; then
    log "需要清理的連接ID：${KILL_IDS}"
    for ID in ${KILL_IDS}; do
        mysql -h ${MASTER_IP} -u${USER} -p${PASSWORD} -P${PORT} -e "KILL ${ID};" 2>/dev/null
        log "已終止連接ID: ${ID}"
    done
else
    log "主庫無需要清理的非系統連接"
fi
 
# 4. 處理備庫：關閉只讀
log "開始處理原備庫（${SLAVE_IP}）..."
log "關閉備庫只讀模式..."
mysql -h ${SLAVE_IP} -u${USER} -p${PASSWORD} -P${PORT} -e "SET GLOBAL read_only = 0; SET GLOBAL super_read_only = 0;" 2>/dev/null
if [ $? -ne 0 ]; then
    log "備庫關閉只讀失敗，請手動檢查"
    exit 1
fi
 
log "主從切換完成！新主庫：${SLAVE_IP}:${PORT}，原主庫已設為只讀"
 
# 5.原主庫查看連接
log "原主庫 ${MASTER_IP}:${PORT} 查看連接情況"
mysql -h ${MASTER_IP} -u${USER} -p${PASSWORD} -P${PORT} -e "show processlist;"
 
echo "mysql -h ${MASTER_IP} -u${USER} -p${PASSWORD} -P${PORT} -e \"show processlist;\""
 

exit 0