mysql-bench.sh

#!/bin/bash
export LANG=en_US.UTF-8

# ===== MySQL连接信息 =====
DB_USER="root"
DB_PASS="root"
DB_NAME="benchtmp"
DB_HOST="127.0.0.1"
DB_PORT=3306
# ===== 测试配置 =====
# 若本机是mysql服务器一般可以保持默认无需改动
# 配置参数越大测试越慢数据越精确
TABLES=2         #测试表数量
TABLE_SIZE=10000 #表数据量
RUN_TIME=10      #测试时间
CORES=1          #mysql服务器的并发数   #默认本机核心数

# 全局变量
selfversion='0.2'
installType='apt -y install'
removeType='apt -y remove'
upgrade="apt -y update"
release='linux'
# 目录和文件路径

LOG_FILE="$HOME/mysql_bench_tmp.log"

# 获取 CPU 核心数
CORES=$(nproc)
THREADS_LIST="1 $CORES"

# 颜色定义
_red() {
    printf '\033[0;31;31m%b\033[0m' "$1"
    echo
}
_green() {
    printf '\033[0;31;32m%b\033[0m' "$1"
    echo
}
_yellow() {
    printf '\033[0;31;33m%b\033[0m' "$1"
    echo
}
_blue() {
    printf '\033[0;31;36m%b\033[0m' "$1"
    echo
}

#检查系统
checkSystem() {
    if [[ -n $(find /etc -name "redhat-release") ]] || grep </proc/version -q -i "centos"; then
        release="centos"
        installType='yum -y install'
        removeType='yum -y remove'
        upgrade="yum update -y --skip-broken"
    elif grep -q -i "debian" /etc/issue || grep -q -i "debian" /proc/version || grep -q -i "ID=debian" /etc/os-release; then
        release="debian"
        installType='apt -y install'
        upgrade="apt update"
        removeType='apt -y autoremove'
    elif grep -q -i "ubuntu" /etc/issue || grep -q -i "ubuntu" /proc/version; then
        release="ubuntu"
        installType='apt -y install'
        upgrade="apt update"
        removeType='apt -y autoremove'
    elif grep -q -i "Alpine" /etc/issue || grep -q -i "Alpine" /proc/version; then
        release="alpine"
        installType='apk add'
        upgrade="apk update"
        removeType='apk del' # 修正错误的删除命令
    else
        _red "不支持此系统"
        exit 1
    fi
}
checkSystem

# 优化检查依赖函数，添加错误处理
check_deps() {
    local deps=("sysbench" "mysql")
    for dep in "${deps[@]}"; do
        if ! command -v "$dep" &>/dev/null; then
            _yellow "$dep 未安装，正在安装..."
            if ! ${upgrade} || ! ${installType} "$dep"; then
                _red "安装 $dep 失败，请检查网络或权限。"
                exit 1
            fi
        fi
    done
}
check_deps

# 优化清理函数，使用更安全的方式终止子进程
cleanup() {
    #_yellow "清理中，杀掉所有子进程..."
    local pids=$(jobs -p)
    [ -n "$pids" ] && kill $pids >/dev/null 2>&1
    echo '' >$LOG_FILE
}


# 加载动画
loading() {
    local pid="$1"
    local delay=0.1
    local spinstr='|/-\'
    tput civis # 隐藏光标

    while kill -0 "$pid" 2>/dev/null; do
        local temp=${spinstr#?}
        printf "\r\033[0;31;36m[ %c ] loading ...\033[0m" "$spinstr"
        spinstr=$temp${spinstr%"$temp"}
        sleep $delay
    done

    tput cnorm        # 恢复光标
    printf "\r\033[K" # 清除行
}

#开始测试

echo -e "                            _       _                     _     "
echo -e "  _ __ ___  _   _ ___  __ _| |     | |__   ___ _ __   ___| |__  "
echo -e " | '_ \` _ \\| | | / __|/ _\` | |_____| '_ \ / _ \ '_ \ / __| '_ \ " 
echo -e " | | | | | | |_| \__ \ (_| | |_____| |_) |  __/ | | | (__| | | |"
echo -e " |_| |_| |_|\__, |___/\__, |_|     |_.__/ \___|_| |_|\___|_| |_|"
echo -e "            |___/        |_|                                    "
_green "v: $selfversion"
echo


_blue "$(date +%Y-%m-%d_%H:%M:%S) 初始化数据库..."


mysql -u$DB_USER -p$DB_PASS -h $DB_HOST -P $DB_PORT -e "DROP DATABASE IF EXISTS $DB_NAME; CREATE DATABASE $DB_NAME;" >/dev/null 2>&1



# 检查数据库创建是否成功
if [ $? -ne 0 ]; then
    _red "数据库创建失败，请检查MySQL连接信息"
    exit 1
fi

_blue "$(date +%Y-%m-%d_%H:%M:%S) 准备测试数据..."


(
    sysbench /usr/share/sysbench/oltp_read_write.lua \
  --mysql-host=$DB_HOST --mysql-port=$DB_PORT \
  --mysql-user=$DB_USER --mysql-password=$DB_PASS \
  --mysql-db=$DB_NAME --tables=$TABLES --table-size=$TABLE_SIZE \
  prepare >/dev/null 2>&1

) &

# 等待子进程完成
loading $!
wait


RESULTS=()
TEST_MODES=("oltp_read_write" "oltp_read_only" "oltp_insert" "oltp_update_non_index")


_blue "$(date +%Y-%m-%d_%H:%M:%S) 开始性能测试...(大概需要2分钟)"

(
    for MODE in "${TEST_MODES[@]}"; do
        for THREADS in $THREADS_LIST; do
            RAW=$(sysbench /usr/share/sysbench/${MODE}.lua \
                --mysql-host=$DB_HOST --mysql-port=$DB_PORT \
                --mysql-user=$DB_USER --mysql-password=$DB_PASS \
                --mysql-db=$DB_NAME --tables=$TABLES --table-size=$TABLE_SIZE \
                --threads=$THREADS --time=$RUN_TIME \
                run 2>/dev/null)

            TPS=$(echo "$RAW" | grep "transactions:" | awk '{print $2}')
            RTIME=$(echo "$RAW" | grep "avg:" | awk '{print $2}')
            P95=$(echo "$RAW" | grep "95th percentile:" | awk '{print $3}')

            echo "$MODE,$THREADS,$TPS,$RTIME,$P95" >> "$LOG_FILE"

        done
    done
) &

# 等待子进程完成
loading $!
wait

_blue "$(date +%Y-%m-%d_%H:%M:%S) 清理测试数据..."

(
    sysbench /usr/share/sysbench/oltp_read_write.lua \
        --mysql-host=$DB_HOST --mysql-port=$DB_PORT \
        --mysql-user=$DB_USER --mysql-password=$DB_PASS \
        --mysql-db=$DB_NAME --tables=$TABLES --table-size=$TABLE_SIZE \
        cleanup >/dev/null 2>&1
) &

# 等待子进程完成
loading $!
wait


# ===== 打印最终报告 =====
_blue "$(date +%Y-%m-%d_%H:%M:%S) 完成测试"

_green "测试结果汇总"
printf "%-22s %-10s %-10s %-12s %-12s\n" "模式" "线程数" "TPS" "AvgRT(ms)" "P95RT(ms)"
echo "--------------------------------------------------------------------------"


# 模式列表
modes=("oltp_read_write" "oltp_read_only" "oltp_insert" "oltp_update_non_index")
weights=(80 5 60 50)  # 对应上面模式的权重
mode_scores=(0 0 0 0)  # 每个模式总得分
mode_counts=(0 0 0 0)  # 每个模式测试次数

while IFS= read -r line; do
    IFS=',' read -r MODE THREADS TPS RTIME P95 <<<"$line"
    printf "%-22s %-10s %-10s %-12s %-12s\n" "$MODE" "$THREADS" "$TPS" "$RTIME" "$P95"

     #计算得分 避免除以0或非法值
    if [[ "$RTIME" == "0" || -z "$RTIME" || -z "$TPS" ]]; then
        score=0
    else
        # 计算得分：扩大倍数 + 取整
        score=$(echo "$TPS / $RTIME * 100" | bc)
        score=${score%%.*}  # 去掉小数部分
    fi

     # 找到对应模式的索引
    for i in "${!modes[@]}"; do
        if [[ "${modes[$i]}" == "$MODE" ]]; then
            mode_scores[$i]=$((mode_scores[$i] + score))
            mode_counts[$i]=$((mode_counts[$i] + 1))
            break
        fi
    done

done < "$LOG_FILE"

echo
_yellow "得分情况（仅供参考！）"
echo "--------------------------------------------------------------------------"
final_score=0

for i in "${!modes[@]}"; do
    mode="${modes[$i]}"
    total=${mode_scores[$i]}
    count=${mode_counts[$i]}
    weight=${weights[$i]}

    if [[ $count -gt 0 ]]; then
        avg=$((total / count))
        weighted=$((avg * weight / 10))  # 权重总和是100，所以/10保持整数
    else
        avg=0
        weighted=0
    fi

    printf "%-22s 平均分: %-6s 权重: %-2s%% → 加权: %s\n" "$mode" "$avg" "$weight" "$weighted"
    final_score=$((final_score + weighted))
done
echo "--------------------------------------------------------------------------"
_green "总得分：$final_score 分"


# 脚本退出时清理
trap cleanup EXIT
echo '' >$LOG_FILE