MYSQL OPTIMIZE TABLE 找出生成的GTID

若水
2025-11-18 11:43:02
技术
MYSQL/MSSQ Ubuntu
MYSQL数据库主从同步中, 当进行 OPTIMIZE TABLE时, 快速的查找出生成的GTID, 可用于在从库在跳过, 避免再次执行.
这个问题主要是用于解决Xtrabackup8.0在进行数据热备时, 必须要进行的OPTIMIZE报错.
把下面的代码保存为file.sh格式, 进行chmod +x file.sh 授权后, 然后 file.sh 表名运行即可.
#!/bin/bash

# =============================================
# 自动 OPTIMIZE TABLE 并提取对应 GTID
# 用法: ./optimize_gtid.sh <表名>
# 示例: ./optimize_gtid.sh a_jk
# 要求: MySQL 启用 binlog + GTID
# =============================================

# ================== 配置区 ==================
MYSQL_USER="root"
MYSQL_PASS="你的密码"   # ← ← ← 请替换为你的实际密码
MYSQL_HOST="localhost"
MYSQL_PORT=3306
DB_NAME="数据库名"
BINLOG_DIR="/data/mysql"                # ← ← ← 确保这是你 binlog 的真实路径
# ===========================================

if [ "$#" -ne 1 ]; then
        echo "用法: $0 <表名>"
            echo "示例: $0 a_jk"
            exit 1
fi

TABLE_NAME="$1"
echo "开始优化表: ${DB_NAME}.${TABLE_NAME}"

MYSQL_CMD="mysql -h${MYSQL_HOST} -P${MYSQL_PORT} -u${MYSQL_USER} -p${MYSQL_PASS}"

# --- 获取并清理 server_uuid ---
SERVER_UUID_RAW=$($MYSQL_CMD -Nse "SELECT @@server_uuid;" 2>/dev/null)
if [ -z "$SERVER_UUID_RAW" ]; then
        echo "错误: 无法获取 server_uuid，请检查 MySQL 连接。"
            exit 1
fi
SERVER_UUID=$(echo "$SERVER_UUID_RAW" | tr '[:upper:]' '[:lower:]' | tr -d ' \t\n\r')
echo "Server UUID (cleaned): ${SERVER_UUID}"

# --- 获取 Executed_Gtid_Set ---
GTID_SET_LINE=$($MYSQL_CMD -e "SHOW MASTER STATUS\G" 2>/dev/null | grep "Executed_Gtid_Set")
if [ -z "$GTID_SET_LINE" ]; then
        echo "错误: SHOW MASTER STATUS 未返回 Executed_Gtid_Set，请确认 binlog 和 GTID 已启用。"
            exit 1
fi

GTID_SET=$(echo "$GTID_SET_LINE" | sed 's/.*Executed_Gtid_Set:[ \t]*//' | tr '[:upper:]' '[:lower:]' | tr -d ' ,')
echo "Executed_Gtid_Set (cleaned): ${GTID_SET}"

# --- 查找当前 UUID 对应的 GTID 区间 ---
FOUND=0
IFS=',' read -ra PARTS <<< "$(echo "$GTID_SET" | tr ',' ' ')"
for part in "${PARTS[@]}"; do
        if [[ "$part" == "${SERVER_UUID}:"* ]]; then
                    GTID_RANGE="$part"
                        FOUND=1
                            break
                            fi
                    done

                    if [ "$FOUND" -eq 0 ]; then
                            echo "错误: 未在 Executed_Gtid_Set 中找到当前 server_uuid 的记录。"
    exit 1
                    fi

BEFORE_NUM=$(echo "$GTID_RANGE" | cut -d: -f2 | awk -F- '{print $NF}')
echo "优化前 GTID 序号: ${BEFORE_NUM}"

# --- 执行 OPTIMIZE TABLE ---
echo "正在执行 OPTIMIZE TABLE ...（请等待）"
$MYSQL_CMD -e "OPTIMIZE TABLE \`${DB_NAME}\`.\`${TABLE_NAME}\`;" >/dev/null 2>&1

# --- 获取优化后的 GTID ---
GTID_SET_AFTER=$($MYSQL_CMD -e "SHOW MASTER STATUS\G" 2>/dev/null | grep "Executed_Gtid_Set" | sed 's/.*Executed_Gtid_Set:[ \t]*//' | tr '[:upper:]' '[:lower:]' | tr -d ' ,')
IFS=',' read -ra PARTS_AFTER <<< "$(echo "$GTID_SET_AFTER" | tr ',' ' ')"
for part in "${PARTS_AFTER[@]}"; do
        if [[ "$part" == "${SERVER_UUID}:"* ]]; then
                    GTID_RANGE_AFTER="$part"
                        break
                        fi
                done

                AFTER_NUM=$(echo "$GTID_RANGE_AFTER" | cut -d: -f2 | awk -F- '{print $NF}')
                echo "优化后 GTID 序号: ${AFTER_NUM}"

                # --- 判断是否新增一个事务 ---
                if [ $((AFTER_NUM - BEFORE_NUM)) -eq 1 ]; then
                        TARGET_GTID="${SERVER_UUID}:${AFTER_NUM}"
                            echo "成功定位 GTID: ${TARGET_GTID}"
                    else
                            echo "检测到并发写入，尝试从 binlog 精确查找..."

                                BINLOG_FILE=$($MYSQL_CMD -e "SHOW MASTER STATUS\G" 2>/dev/null | awk '/File:/ {print $2}')
                                if [ ! -f "${BINLOG_DIR}/${BINLOG_FILE}" ]; then
                                            echo "错误: binlog 文件不存在: ${BINLOG_DIR}/${BINLOG_FILE}"
        exit 1
    fi

    # 使用安全的 awk 提取 GTID（无引号冲突）
      TARGET_GTID=$(mysqlbinlog -v "${BINLOG_DIR}/${BINLOG_FILE}" 2>/dev/null | \
                awk -v tbl="$TABLE_NAME" '
                /GTID_NEXT.*:/ {
                            match($0, /'\''([a-f0-9-]+:[0-9]+)'\''/, arr)
                                    if (arr[1] != "") gtid = arr[1]
                                            }
                                            /OPTIMIZE TABLE/ && index($0, tbl) {
                                                        if (gtid != "") {
                                                                        print gtid
                                                                                exit
                                                                                      }
                                                                                  }
                                                                          ')
        
    echo "DEBUG: 搜索表名 = [$TABLE_NAME]"
    echo "DEBUG: 最后几行 binlog 相关内容:"
    mysqlbinlog -v "${BINLOG_DIR}/${BINLOG_FILE}" 2>/dev/null | grep -A3 -B3 -i "$TABLE_NAME" | tail -10    

    if [ -z "$TARGET_GTID" ]; then
        echo "错误: 未在 binlog 中找到对应的 ALTER 语句。"
        exit 1
    fi
    echo "从 binlog 定位到 GTID: ${TARGET_GTID}"
                fi

# --- 输出从库跳过命令 ---
echo ""
echo "请在从库执行以下命令："
echo ""
echo "STOP SLAVE;"
echo "SET GTID_NEXT = '${TARGET_GTID}';"
echo "BEGIN; COMMIT;"
echo "SET GTID_NEXT = 'AUTOMATIC';"
echo "START SLAVE;"
echo "SHOW SLAVE STATUS\\G"
echo ""
本文为宁若水!原创文章,转载无需和我联系,但请注明来自[若水]博客 www.lalaya.net