Files
oa-monitor 20fa48fb34 fix: check-alert v3 修复钉钉推送断档 P0 假阳性
根因:sqlite_exec 吞掉 stderr(2>/dev/null),查询失败返回空串,
0 默认为 0 导致误报

修复:业务层取数显式调用 sqlite3 + 检查退出码 + 正则校验,
查询失败跳过检测并 warn 日志
2026-11-04 19:41:18 +08:00

95 lines
3.6 KiB
Bash

#!/usr/bin/env bash
# ============================================================================
# OA Monitor · DB 层采集(每 30 秒)
# 在 DSH 宿主机上直接 mysql 连 OA DB(DSH 本身就是跳板,不需要再 ssh)
# ============================================================================
set -u
SCRIPT_DIR="$(cd "$(dirname "$0")" && pwd)"
source "${SCRIPT_DIR}/../lib/common.sh"
NOW=$(date +%s)
# ---- MySQL 直连(DSH 上直接连 OA DB 公网映射)----
DB_MYSQL() {
MYSQL_PWD="${DB_PASS}" timeout 12 mysql -h39.100.72.102 -P3306 -uecology ecology 2>/dev/null
}
db_exec() {
DB_MYSQL -e "$1"
}
# ---- 1. SHOW STATUS ----
log_info "[DB] SHOW STATUS"
STATUS=$(DB_MYSQL -e "SHOW GLOBAL STATUS" | grep -E '^(Threads_running|Threads_connected|Threads_idle|Uptime|Innodb_rows_read|Innodb_rows_inserted|Innodb_rows_updated|Innodb_rows_deleted|Slow_queries)\t')
THREADS_RUNNING=$(echo "${STATUS}" | awk -F'\t' '$1=="Threads_running"{print $2}')
THREADS_CONNECTED=$(echo "${STATUS}" | awk -F'\t' '$1=="Threads_connected"{print $2}')
THREADS_IDLE=$(echo "${STATUS}" | awk -F'\t' '$1=="Threads_idle"{print $2}')
UPTIME=$(echo "${STATUS}" | awk -F'\t' '$1=="Uptime"{print $2}')
INNODB_READ=$(echo "${STATUS}" | awk -F'\t' '$1=="Innodb_rows_read"{print $2}')
INNODB_INS=$(echo "${STATUS}" | awk -F'\t' '$1=="Innodb_rows_inserted"{print $2}')
INNODB_UPD=$(echo "${STATUS}" | awk -F'\t' '$1=="Innodb_rows_updated"{print $2}')
INNODB_DEL=$(echo "${STATUS}" | awk -F'\t' '$1=="Innodb_rows_deleted"{print $2}')
SLOW_QUERIES=$(echo "${STATUS}" | awk -F'\t' '$1=="Slow_queries"{print $2}')
INNODB_READ=${INNODB_READ:-0}
INNODB_INS=${INNODB_INS:-0}
INNODB_UPD=${INNODB_UPD:-0}
INNODB_DEL=${INNODB_DEL:-0}
INNODB_WRITE=$(( INNODB_INS + INNODB_UPD + INNODB_DEL ))
THREADS_RUNNING=${THREADS_RUNNING:-0}
THREADS_CONNECTED=${THREADS_CONNECTED:-0}
THREADS_IDLE=${THREADS_IDLE:-0}
UPTIME=${UPTIME:-0}
SLOW_QUERIES=${SLOW_QUERIES:-0}
# ---- 2. 上一次快照 → 差值 ----
PREV=$(sqlite_exec "SELECT * FROM db_metrics ORDER BY ts DESC LIMIT 1;")
QPS_READ=0; QPS_WRITE=0; SLOW_DELTA=0
if [[ -n "${PREV}" ]]; then
PREV_UPTIME=$(echo "${PREV}" | cut -d'|' -f5)
PREV_READ=$(echo "${PREV}" | cut -d'|' -f6)
PREV_WRITE=$(echo "${PREV}" | cut -d'|' -f7)
PREV_SLOW=$(echo "${PREV}" | cut -d'|' -f9)
PREV_UPTIME=${PREV_UPTIME:-0}; PREV_READ=${PREV_READ:-0}
PREV_WRITE=${PREV_WRITE:-0}; PREV_SLOW=${PREV_SLOW:-0}
DIFF_SEC=$(( NOW - PREV_UPTIME ))
if [[ ${DIFF_SEC} -gt 0 ]]; then
QPS_READ=$(( (INNODB_READ - PREV_READ) / DIFF_SEC ))
QPS_WRITE=$(( (INNODB_WRITE - PREV_WRITE) / DIFF_SEC ))
fi
SLOW_DELTA=$(( SLOW_QUERIES - PREV_SLOW ))
fi
# ---- 3. 写 SQLite ----
sqlite_exec "INSERT INTO db_metrics VALUES (
${NOW}, ${THREADS_RUNNING}, ${THREADS_CONNECTED}, ${THREADS_IDLE},
${UPTIME}, ${QPS_READ}, ${QPS_WRITE}, ${SLOW_DELTA}
);"
log_info "[DB] ✅ 写入:Threads_running=${THREADS_RUNNING} QPS_read=${QPS_READ} QPS_write=${QPS_WRITE}"
# ---- 4. 长查询 processlist ----
log_info "[DB] processlist"
PLIST=$(DB_MYSQL -e 'SHOW PROCESSLIST;')
echo "${PLIST}" | awk -F'\t' '
NR==1 { next }
NF < 8 { next }
{
pid=$1; user=$2; state=$6; time=$7; info=$8
if (time+0 >= 5 && pid+0 > 0) {
gsub(/\t/, " ", info)
printf "%d\t%d\t%s\t%s\t%s\n", pid, time, user, state, substr(info,1,300)
}
}
' | while IFS=$'\t' read -r pid time user state info; do
[[ -z "${pid}" ]] && continue
info_safe=$(echo "${info}" | sed "s/'/\"/g")
sqlite_exec "INSERT INTO db_processlist (ts, pid, user, state, time_sec, info)
VALUES (${NOW}, ${pid}, '${user}', '${state}', ${time}, '${info_safe}');"
done
log_info "[DB] ✅ 完成"