分享一个MySQL 8.0复制架构主从自动切换脚本

/* by 01022.hk - online tools website : 01022.hk/zh/formatc.html */ #!/bin/bash # ============================================================================= # 基于GTID的MySQL 8.0 一主一从架构主从切换脚本 # 版本: 3.0 # 优化内容: 全变量配置、增强可配置性、改进错误处理 # ============================================================================= # >>>>>>>>>>>> 第一部分：脚本配置区域 (使用前请务必修改) <<<<<<<<<<<< # 数据库连接凭证 MYSQL_USER="repluser" MYSQL_PASS="repluser" # 当前主从节点IP地址及端口 CURRENT_MASTER_HOST="192.168.1.1" CURRENT_MASTER_PORT="3306" CURRENT_SLAVE_HOST="192.168.1.2" CURRENT_SLAVE_PORT="3307" # 日志文件配置 LOG_FILE="/var/log/mysql_switchover.log" LOCK_FILE="/tmp/mysql_switchover.lock" LOG_MAX_SIZE=10485760 # 10MB LOG_BACKUP_COUNT=5 # 连接超时时间（秒） MYSQL_CONNECT_TIMEOUT=5 MYSQL_QUERY_TIMEOUT=10 # 复制延迟配置 MAX_DELAY=60 # 最大容忍延迟秒数 WAIT_TIMEOUT=300 # 等待追平超时秒数 WAIT_STEP=5 # 检查间隔秒数 # 切换验证配置 VERIFY_TEST_DB_PREFIX="test_switchover_" VERIFY_CREATE_TABLE_SQL="CREATE TABLE verify_switchover (id INT PRIMARY KEY, test_time TIMESTAMP)" VERIFY_INSERT_SQL="INSERT INTO verify_switchover VALUES (1, NOW())" # 连接检查配置 WRITE_CONN_THRESHOLD=0 # 写连接数阈值 EXCLUDE_USERS="('system user','event_scheduler')" EXCLUDE_COMMANDS="('Sleep','Binlog Dump','Binlog Dump GTID')" # 脚本行为配置 ENABLE_AUTO_CONFIRM=false # 是否自动确认警告 SKIP_DELAY_CHECK=false # 是否跳过延迟检查 SKIP_CONSISTENCY_CHECK=false # 是否跳过一致性检查 FORCE_SWITCHOVER=false # 是否强制切换 # >>>>>>>>>>>> 第二部分：核心函数定义 <<<<<<<<<<<< # 日志轮转函数 rotate_log() { if [ -f "$LOG_FILE" ] && [ $(stat -c%s "$LOG_FILE") -gt $LOG_MAX_SIZE ]; then for i in $(seq $((LOG_BACKUP_COUNT-1)) -1 1); do [ -f "${LOG_FILE}.${i}" ] && mv "${LOG_FILE}.${i}" "${LOG_FILE}.$((i+1))" done [ -f "$LOG_FILE" ] && mv "$LOG_FILE" "${LOG_FILE}.1" touch "$LOG_FILE" fi } # 日志记录函数 log() { rotate_log local LEVEL=$1 local MSG=$2 local TIMESTAMP=$(date "+%Y-%m-%d %H:%M:%S") echo "[$TIMESTAMP] [$LEVEL] $MSG" | tee -a "$LOG_FILE" } # 发送邮件通知 send_email() { local SUBJECT=$1 local BODY=$2 if [ "$ENABLE_EMAIL_NOTIFY" = true ]; then echo "$BODY" | mail -s "${EMAIL_SUBJECT_PREFIX} $SUBJECT" "$EMAIL_RECIPIENTS" 2>/dev/null || \ log "WARNING" "邮件发送失败" fi } # 错误处理与退出函数 error_exit() { local MSG=$1 local EXIT_CODE=${2:-1} log "ERROR" "$MSG" send_email "切换失败" "MySQL主从切换失败: $MSG" cleanup_lockfile exit $EXIT_CODE } # 通用的MySQL连接执行函数 mysql_exec() { local HOST=$1 local PORT=$2 local SQL=$3 mysql -h"$HOST" -P"$PORT" -u"$MYSQL_USER" -p"$MYSQL_PASS" \ --connect-timeout=$MYSQL_CONNECT_TIMEOUT \ --max-allowed-packet=64M \ -e "$SQL" 2>/dev/null } # 获取MySQL单值结果 get_mysql_value() { local HOST=$1 local PORT=$2 local SQL=$3 mysql -h"$HOST" -P"$PORT" -u"$MYSQL_USER" -p"$MYSQL_PASS" \ --connect-timeout=$MYSQL_CONNECT_TIMEOUT \ -N -s -e "$SQL" 2>/dev/null | tail -1 } # 检查MySQL实例是否可连接 check_mysql_connectivity() { local HOST=$1 local PORT=$2 if mysql_exec "$HOST" "$PORT" "SELECT 1;" &> /dev/null; then log "INFO" "成功连接到MySQL实例: $HOST:$PORT" return 0 else log "ERROR" "无法连接到MySQL实例: $HOST:$PORT" return 1 fi } # 创建锁文件防止脚本重复运行 create_lockfile() { if [ -f "$LOCK_FILE" ]; then local LOCK_PID=$(cat "$LOCK_FILE") if ps -p "$LOCK_PID" > /dev/null 2>&1; then log "ERROR" "脚本已在运行中 (PID: $LOCK_PID)，请勿重复执行" exit 1 else log "WARNING" "发现残留锁文件，清理后继续" rm -f "$LOCK_FILE" fi fi echo $$ > "$LOCK_FILE" } # 清理锁文件 cleanup_lockfile() { [ -f "$LOCK_FILE" ] && rm -f "$LOCK_FILE" } # 检查复制延迟并等待追平 wait_for_replica_catchup() { [ "$SKIP_DELAY_CHECK" = true ] && return 0 local REPLICA_HOST=$1 local REPLICA_PORT=$2 local TOTAL_WAIT=0 log "INFO" "检查副本延迟并等待追平，最大容忍延迟: ${MAX_DELAY}秒，超时: ${WAIT_TIMEOUT}秒" while [ $TOTAL_WAIT -lt $WAIT_TIMEOUT ]; do local REPLICA_STATUS=$(mysql_exec "$REPLICA_HOST" "$REPLICA_PORT" "SHOW REPLICA STATUS\G") local DELAY=$(echo "$REPLICA_STATUS" | grep -i "Seconds_Behind_Source:" | awk '{print $2}') if [ "$DELAY" == "NULL" ] || [ -z "$DELAY" ]; then log "ERROR" "无法获取副本延迟信息或复制已停止" return 1 fi if ! [[ "$DELAY" =~ ^[0-9]+$ ]]; then log "ERROR" "获取的延迟值非数字: $DELAY" return 1 fi if [ "$DELAY" -eq 0 ]; then log "INFO" "副本已完全追平，延迟: 0秒" return 0 elif [ "$DELAY" -le $MAX_DELAY ]; then log "INFO" "副本当前延迟: ${DELAY}秒，在容忍范围内" break else log "INFO" "副本当前延迟: ${DELAY}秒，等待追平... 已等待 ${TOTAL_WAIT}秒" sleep $WAIT_STEP TOTAL_WAIT=$((TOTAL_WAIT + WAIT_STEP)) fi done if [ $TOTAL_WAIT -ge $WAIT_TIMEOUT ]; then log "WARNING" "等待副本追平超时，当前延迟: ${DELAY}秒" if [ "$FORCE_SWITCHOVER" = true ]; then log "WARNING" "强制切换模式，继续执行" return 0 fi return 2 fi return 0 } # 检查复制状态 check_replica_status() { local HOST=$1 local PORT=$2 local STATUS=$(mysql_exec "$HOST" "$PORT" "SHOW REPLICA STATUS\G") if [ -z "$STATUS" ]; then log "WARNING" "未找到复制状态信息，主机可能不是副本角色: $HOST:$PORT" return 2 fi local IO_THREAD=$(echo "$STATUS" | grep -i "Replica_IO_Running:" | awk '{print $2}') local SQL_THREAD=$(echo "$STATUS" | grep -i "Replica_SQL_Running:" | awk '{print $2}') local SECONDS_BEHIND=$(echo "$STATUS" | grep -i "Seconds_Behind_Source:" | awk '{print $2}') if [[ "$IO_THREAD" == "Yes" ]] && [[ "$SQL_THREAD" == "Yes" ]]; then log "INFO" "副本复制线程运行正常: $HOST:$PORT, 延迟: ${SECONDS_BEHIND:-N/A} 秒" return 0 else log "ERROR" "副本复制线程异常 - IO线程: $IO_THREAD, SQL线程: $SQL_THREAD" return 1 fi } # 检查主从数据一致性 check_gtid_consistency() { [ "$SKIP_CONSISTENCY_CHECK" = true ] && return 0 local MASTER_HOST=$1 local MASTER_PORT=$2 local SLAVE_HOST=$3 local SLAVE_PORT=$4 log "INFO" "开始检查主从GTID一致性..." local MASTER_GTID_SET=$(get_mysql_value "$MASTER_HOST" "$MASTER_PORT" "SELECT @@GLOBAL.GTID_EXECUTED;") local SLAVE_GTID_SET=$(get_mysql_value "$SLAVE_HOST" "$SLAVE_PORT" "SELECT @@GLOBAL.GTID_EXECUTED;") if [ -z "$MASTER_GTID_SET" ] || [ -z "$SLAVE_GTID_SET" ]; then log "ERROR" "获取主库或从库的GTID_EXECUTED失败" return 1 fi local GTID_DIFF=$(get_mysql_value "$SLAVE_HOST" "$SLAVE_PORT" \ "SELECT GTID_SUBTRACT('$MASTER_GTID_SET', '$SLAVE_GTID_SET') AS DIFF;") if [ -n "$GTID_DIFF" ] && [ "$GTID_DIFF" != "" ]; then log "WARNING" "主从数据存在GTID差异: $GTID_DIFF" return 2 else log "INFO" "主从GTID一致性检查通过" return 0 fi } # 设置实例为只读模式 set_read_only() { local HOST=$1 local PORT=$2 local MODE=$3 if [ "$MODE" == "on" ]; then log "INFO" "设置实例为只读模式: $HOST:$PORT" mysql_exec "$HOST" "$PORT" "SET GLOBAL super_read_only=1, read_only=1;" local WRITE_CONNS=$(get_mysql_value "$HOST" "$PORT" \ "SELECT COUNT(*) FROM performance_schema.processlist WHERE command NOT IN $EXCLUDE_COMMANDS AND USER NOT IN $EXCLUDE_USERS AND id <> CONNECTION_ID();") if [ "$WRITE_CONNS" -gt $WRITE_CONN_THRESHOLD ] 2>/dev/null; then log "WARNING" "发现 $WRITE_CONNS 个活跃写连接，超过阈值 $WRITE_CONN_THRESHOLD" else log "INFO" "活跃写连接数检查通过" fi else log "INFO" "关闭实例只读模式: $HOST:$PORT" mysql_exec "$HOST" "$PORT" "SET GLOBAL read_only=0, super_read_only=0;" fi } # 停止并重置复制 stop_and_reset_replica() { local HOST=$1 local PORT=$2 log "INFO" "在主机上停止并重置复制: $HOST:$PORT" mysql_exec "$HOST" "$PORT" "STOP REPLICA;" mysql_exec "$HOST" "$PORT" "RESET REPLICA ALL;" if [ $? -eq 0 ]; then log "INFO" "成功停止并重置复制: $HOST:$PORT" return 0 else log "ERROR" "停止或重置复制操作失败: $HOST:$PORT" return 1 fi } # 提升从库为新主库 promote_replica_to_source() { local REPLICA_HOST=$1 local REPLICA_PORT=$2 log "INFO" "开始提升副本为新主库: $REPLICA_HOST:$REPLICA_PORT" stop_and_reset_replica "$REPLICA_HOST" "$REPLICA_PORT" || return 1 set_read_only "$REPLICA_HOST" "$REPLICA_PORT" "off" log "INFO" "副本提升为新主库操作完成: $REPLICA_HOST:$REPLICA_PORT" return 0 } # 配置旧主库作为新主库的从库 setup_old_source_as_replica() { local OLD_MASTER_HOST=$1 local OLD_MASTER_PORT=$2 local NEW_MASTER_HOST=$3 local NEW_MASTER_PORT=$4 log "INFO" "配置旧主库作为新主库的副本: $OLD_MASTER_HOST:$OLD_MASTER_PORT -> $NEW_MASTER_HOST:$NEW_MASTER_PORT" check_mysql_connectivity "$OLD_MASTER_HOST" "$OLD_MASTER_PORT" || return 1 stop_and_reset_replica "$OLD_MASTER_HOST" "$OLD_MASTER_PORT" local CHANGE_SOURCE_SQL="CHANGE REPLICATION SOURCE TO SOURCE_HOST='$NEW_MASTER_HOST', SOURCE_PORT=$NEW_MASTER_PORT, SOURCE_USER='$MYSQL_USER', SOURCE_PASSWORD='$MYSQL_PASS', SOURCE_AUTO_POSITION=1, GET_SOURCE_PUBLIC_KEY=1, SOURCE_CONNECT_RETRY=10, SOURCE_RETRY_COUNT=10;" mysql_exec "$OLD_MASTER_HOST" "$OLD_MASTER_PORT" "$CHANGE_SOURCE_SQL" mysql_exec "$OLD_MASTER_HOST" "$OLD_MASTER_PORT" "START REPLICA;" if [ $? -eq 0 ]; then log "INFO" "成功在旧主库上配置指向新主库的复制" sleep 5 if check_replica_status "$OLD_MASTER_HOST" "$OLD_MASTER_PORT"; then log "INFO" "旧主库到新主库的复制状态正常" return 0 else log "WARNING" "旧主库到新主库的复制状态异常" return 2 fi else log "ERROR" "在旧主库上配置复制关系失败" return 1 fi } # 切换后验证函数 verify_switchover_result() { local NEW_SOURCE_HOST=$1 local NEW_SOURCE_PORT=$2 local OLD_SOURCE_HOST=$3 local OLD_SOURCE_PORT=$4 log "INFO" "开始验证切换结果..." # 创建临时测试数据库 local TEST_DB="${VERIFY_TEST_DB_PREFIX}$(date +%s)" local TEST_TABLE="${TEST_DB}.verify_switchover" # 验证1: 创建数据库 if ! mysql_exec "$NEW_SOURCE_HOST" "$NEW_SOURCE_PORT" "CREATE DATABASE IF NOT EXISTS $TEST_DB;"; then log "ERROR" "新主库创建数据库测试失败" return 1 fi # 验证2: 创建表 if ! mysql_exec "$NEW_SOURCE_HOST" "$NEW_SOURCE_PORT" \ "CREATE TABLE $TEST_TABLE (id INT PRIMARY KEY, test_time TIMESTAMP);"; then log "ERROR" "新主库创建表测试失败" mysql_exec "$NEW_SOURCE_HOST" "$NEW_SOURCE_PORT" "DROP DATABASE IF EXISTS $TEST_DB;" return 1 fi # 验证3: 插入数据 if ! mysql_exec "$NEW_SOURCE_HOST" "$NEW_SOURCE_PORT" \ "INSERT INTO $TEST_TABLE VALUES (1, NOW());"; then log "ERROR" "新主库插入数据测试失败" mysql_exec "$NEW_SOURCE_HOST" "$NEW_SOURCE_PORT" "DROP DATABASE IF EXISTS $TEST_DB;" return 1 fi # 验证4: 查询数据 local RESULT=$(get_mysql_value "$NEW_SOURCE_HOST" "$NEW_SOURCE_PORT" \ "SELECT COUNT(*) FROM $TEST_TABLE WHERE id = 1;") if [ "$RESULT" != "1" ]; then log "ERROR" "新主库查询数据测试失败" mysql_exec "$NEW_SOURCE_HOST" "$NEW_SOURCE_PORT" "DROP DATABASE IF EXISTS $TEST_DB;" return 1 fi # 清理测试数据 mysql_exec "$NEW_SOURCE_HOST" "$NEW_SOURCE_PORT" "DROP DATABASE IF EXISTS $TEST_DB;" log "INFO" "新主库写测试完全通过: $NEW_SOURCE_HOST:$NEW_SOURCE_PORT" # 验证旧主库复制状态 if check_mysql_connectivity "$OLD_SOURCE_HOST" "$OLD_SOURCE_PORT"; then sleep 3 if check_replica_status "$OLD_SOURCE_HOST" "$OLD_SOURCE_PORT"; then log "INFO" "旧主库到新主库的复制状态正常" else log "WARNING" "旧主库到新主库的复制状态异常，需人工检查" return 2 fi else log "INFO" "旧主库当前不可达，复制状态略过检查" fi log "INFO" "切换验证完成" return 0 } # 用户确认函数 user_confirm() { local MESSAGE=$1 local DEFAULT=${2:-"N"} if [ "$ENABLE_AUTO_CONFIRM" = true ]; then log "INFO" "自动确认模式: $MESSAGE" return 0 fi if [ "$DEFAULT" = "Y" ]; then read -p "$MESSAGE (Y/n): " -t 30 user_input user_input=${user_input:-"Y"} else read -p "$MESSAGE (y/N): " -t 30 user_input user_input=${user_input:-"N"} fi [[ "$user_input" =~ ^[Yy]$ ]] return $? } # 显示配置信息 show_configuration() { log "INFO" "========== 切换配置信息 ==========" log "INFO" "原主库: $CURRENT_MASTER_HOST:$CURRENT_MASTER_PORT" log "INFO" "原从库: $CURRENT_SLAVE_HOST:$CURRENT_SLAVE_PORT" log "INFO" "最大延迟: ${MAX_DELAY}秒" log "INFO" "等待超时: ${WAIT_TIMEOUT}秒" log "INFO" "自动确认: $ENABLE_AUTO_CONFIRM" log "INFO" "强制模式: $FORCE_SWITCHOVER" log "INFO" "==================================" } # >>>>>>>>>>>> 第三部分：切换主流程 <<<<<<<<<<<< # 切换函数 switchover() { log "INFO" "开始主从切换流程..." show_configuration # 1. 前置检查 log "INFO" "步骤1: 执行前置检查" check_mysql_connectivity "$CURRENT_MASTER_HOST" "$CURRENT_MASTER_PORT" || \ error_exit "原主库无法连接，切换中止" check_mysql_connectivity "$CURRENT_SLAVE_HOST" "$CURRENT_SLAVE_PORT" || \ error_exit "从库无法连接，切换中止" check_replica_status "$CURRENT_SLAVE_HOST" "$CURRENT_SLAVE_PORT" || \ error_exit "从库复制状态异常，切换中止" # 2. 检查数据一致性 if [ "$SKIP_CONSISTENCY_CHECK" = false ]; then log "INFO" "步骤2: 检查主从数据一致性" check_gtid_consistency "$CURRENT_MASTER_HOST" "$CURRENT_MASTER_PORT" \ "$CURRENT_SLAVE_HOST" "$CURRENT_SLAVE_PORT" local CONSISTENCY_RESULT=$? if [ $CONSISTENCY_RESULT -eq 1 ]; then error_exit "GTID一致性检查失败，切换中止" elif [ $CONSISTENCY_RESULT -eq 2 ]; then if ! user_confirm "主从数据存在差异，请确认是否继续切换?"; then log "INFO" "用户取消切换操作" exit 1 fi log "INFO" "用户确认继续切换" fi fi # 3. 设置原主库为只读 log "INFO" "步骤3: 设置原主库为只读模式" set_read_only "$CURRENT_MASTER_HOST" "$CURRENT_MASTER_PORT" "on" # 4. 等待从库追平延迟 if [ "$SKIP_DELAY_CHECK" = false ]; then log "INFO" "步骤4: 检查从库复制延迟" wait_for_replica_catchup "$CURRENT_SLAVE_HOST" "$CURRENT_SLAVE_PORT" local CATCHUP_RESULT=$? if [ $CATCHUP_RESULT -eq 1 ]; then set_read_only "$CURRENT_MASTER_HOST" "$CURRENT_MASTER_PORT" "off" error_exit "检查副本延迟时发生错误" elif [ $CATCHUP_RESULT -eq 2 ]; then if ! user_confirm "从库延迟较大，请确认是否继续切换?"; then set_read_only "$CURRENT_MASTER_HOST" "$CURRENT_MASTER_PORT" "off" log "INFO" "用户取消切换操作，已恢复原主库写权限" exit 1 fi log "INFO" "用户确认继续切换" fi fi # 5. 提升从库为新主库 log "INFO" "步骤5: 提升从库为新主库" promote_replica_to_source "$CURRENT_SLAVE_HOST" "$CURRENT_SLAVE_PORT" || \ error_exit "提升从库失败" # 6. 配置旧主库为新从库 log "INFO" "步骤6: 配置旧主库为新从库" if ! setup_old_source_as_replica "$CURRENT_MASTER_HOST" "$CURRENT_MASTER_PORT" \ "$CURRENT_SLAVE_HOST" "$CURRENT_SLAVE_PORT"; then log "WARNING" "配置旧主库为新从库失败，尝试备用方案..." local NEW_MASTER_GTID=$(get_mysql_value "$CURRENT_SLAVE_HOST" "$CURRENT_SLAVE_PORT" \ "SELECT @@GLOBAL.GTID_EXECUTED;") if [ -n "$NEW_MASTER_GTID" ]; then log "INFO" "尝试备用GTID同步方案" mysql_exec "$CURRENT_MASTER_HOST" "$CURRENT_MASTER_PORT" \ "STOP REPLICA; RESET REPLICA ALL;" mysql_exec "$CURRENT_MASTER_HOST" "$CURRENT_MASTER_PORT" \ "SET GLOBAL gtid_purged='$NEW_MASTER_GTID';" if setup_old_source_as_replica "$CURRENT_MASTER_HOST" "$CURRENT_MASTER_PORT" \ "$CURRENT_SLAVE_HOST" "$CURRENT_SLAVE_PORT"; then log "INFO" "备用方案配置成功" else log "ERROR" "备用方案也失败，需要手动干预" return 2 fi else log "ERROR" "无法获取新主库GTID，备用方案无法执行" return 2 fi fi # 7. 最终验证 log "INFO" "步骤7: 执行切换后验证" if verify_switchover_result "$CURRENT_SLAVE_HOST" "$CURRENT_SLAVE_PORT" \ "$CURRENT_MASTER_HOST" "$CURRENT_MASTER_PORT"; then log "INFO" "切换验证通过" send_email "切换成功" "MySQL主从切换成功完成。新主库: $CURRENT_SLAVE_HOST:$CURRENT_SLAVE_PORT" else error_exit "切换验证失败，新主库可能不可用" fi log "INFO" "主从切换流程全部完成！新主库: $CURRENT_SLAVE_HOST:$CURRENT_SLAVE_PORT" return 0 } # >>>>>>>>>>>> 第四部分：脚本入口 <<<<<<<<<<<< # 解析命令行参数 parse_arguments() { while [[ $# -gt 0 ]]; do case $1 in --auto-confirm) ENABLE_AUTO_CONFIRM=true shift ;; --skip-delay-check) SKIP_DELAY_CHECK=true shift ;; --skip-consistency-check) SKIP_CONSISTENCY_CHECK=true shift ;; --force) FORCE_SWITCHOVER=true shift ;; --master-host=*) CURRENT_MASTER_HOST="${1#*=}" shift ;; --master-port=*) CURRENT_MASTER_PORT="${1#*=}" shift ;; --slave-host=*) CURRENT_SLAVE_HOST="${1#*=}" shift ;; --slave-port=*) CURRENT_SLAVE_PORT="${1#*=}" shift ;; --help) echo "Usage: $0 [OPTIONS]" echo "Options:" echo " --auto-confirm 自动确认所有警告" echo " --skip-delay-check 跳过延迟检查" echo " --skip-consistency-check 跳过一致性检查" echo " --force 强制切换模式" echo " --master-host=HOST 指定原主库主机" echo " --master-port=PORT 指定原主库端口" echo " --slave-host=HOST 指定原从库主机" echo " --slave-port=PORT 指定原从库端口" echo " --help 显示帮助信息" exit 0 ;; *) echo "未知参数: $1" echo "使用 --help 查看帮助信息" exit 1 ;; esac done } # 主执行逻辑 main() { parse_arguments "$@" # 创建锁文件 create_lockfile trap cleanup_lockfile EXIT log "INFO" "==== MySQL主从切换脚本开始执行 ====" log "INFO" "脚本版本: 2.0" log "INFO" "执行时间: $(date)" switchover local result=$? if [ $result -eq 0 ]; then log "INFO" "脚本执行成功" else log "ERROR" "脚本执行过程中遇到错误" fi log "INFO" "==== MySQL主从切换脚本执行结束 ====" exit $result } # 脚本执行入口 main "$@"