MySQL 巡檢腳本 V1：從系統到引擎層全維度監控

MySQL 巡檢腳本 V1：從系統到引擎層全維度監控

生產環境 MySQL 需要定期「體檢」。本文提供一個功能完整的 Shell 巡檢腳本，覆蓋主機資源、MySQL 運行狀態、關鍵性能指標，輸出彩色報告，便於人工閱讀與自動化告警對接。

腳本功能清單

1. 主機基礎信息：主機名、IP、網關、CPU、內存、磁盤、負載、服務
2. MySQL 基礎信息：版本、端口、進程數
3. 連接層：客戶端連接數、ESTABLISHED 狀態分布
4. 吞吐指標：QPS、TPS（Commit+Rollback）
5. 緩衝池命中率：Key Buffer（MyISAM）、InnoDB Buffer Pool、Query Cache

完整腳本（可直接保存為 mysql_inspect.sh）

#!/bin/bash
# MySQL 巡檢腳本 V1
# 用法: chmod +x mysql_inspect.sh && ./mysql_inspect.sh
# 依賴: bc, mysql client, 標準 Linux 工具

# ====== 配置區 ======
MYSQL_USER="inspector"
MYSQL_PASS="InspectorPassw0rd!"
MYSQL_HOST="127.0.0.1"
MYSQL_PORT="3306"
# ====================

# 顏色定義
RED='\033[1;31m'; GREEN='\033[1;32m'; YELLOW='\033[1;33m'; BLUE='\033[1;34m'; NC='\033[0m'
cecho() { echo -e "${2}${1}${NC}"; }
cechon() { echo -ne "${2}${1}${NC}"; }

mysql_exec() {
    mysql -h"$MYSQL_HOST" -P"$MYSQL_PORT" -u"$MYSQL_USER" -p"$MYSQL_PASS" -sN -e "$1" 2>/dev/null
}

# ---- 1. 主機信息 ----
cecho "========== 主機基礎信息 ==========" "$BLUE"
hostname=$(hostname)
ip=$(ip route get 1 | awk '{print $7; exit}')
gw=$(ip route | awk '/^default/ {print $3}')
cpu=$(lscpu | awk -F: '/Model name/ {print $2}' | xargs)
mem_total=$(free -m | awk '/Mem:/ {print $2}')
mem_used=$(free -m | awk '/Mem:/ {print $3}')
mem_pct=$(echo "scale=2; $mem_used*100/$mem_total" | bc)
disk=$(df -hP | awk 'NR>1 {printf "%s %s %s %s %s\n", $1,$2,$3,$4,$5}')
load=$(uptime | awk -F'load average:' '{print $2}')
svcs=$(systemctl list-units --type=service --state=running --no-legend | wc -l)

cecho "主機名: $hostname" "$GREEN"
cecho "IP: $ip" "$GREEN"
cecho "網關: $gw" "$GREEN"
cecho "CPU: $cpu" "$GREEN"
cecho "內存: ${mem_used}MB / ${mem_total}MB (${mem_pct}%)" "$GREEN"
cecho "磁盤:" "$GREEN"; echo "$disk"
cecho "負載: $load" "$GREEN"
cecho "運行服務數: $svcs" "$GREEN"

# ---- 2. MySQL 基礎 ----
cecho "\n========== MySQL 基礎信息 ==========" "$BLUE"
ver=$(mysql_exec "SELECT @@version;")
port=$(mysql_exec "SHOW VARIABLES LIKE 'port';" | awk '{print $2}')
proc_cnt=$(ps -ef | grep -c '[m]ysqld')
cecho "版本: $ver" "$GREEN"
cecho "端口: $port" "$GREEN"
cecho "進程數: $proc_cnt" "$GREEN"

# ---- 3. 連接層 ----
cecho "\n========== 連接層 ==========" "$BLUE"
conn_cnt=$(mysql_exec "SHOW PROCESSLIST;" | wc -l)
cecho "客戶端連接數: $conn_cnt" "$GREEN"
cecho "ESTABLISHED 連接分布:" "$GREEN"
netstat -an -t | grep ":$MYSQL_PORT" | grep ESTABLISHED | awk '{print $5}' | cut -d: -f1 | sort | uniq -c | sort -nr

# ---- 4. QPS ----
cecho "\n========== QPS ==========" "$BLUE"
questions=$(mysql_exec "SHOW GLOBAL STATUS LIKE 'Questions';" | awk '{print $2}')
uptime_sec=$(cat /proc/uptime | awk '{print int($1)}')
qps=$(echo "scale=2; $questions/$uptime_sec" | bc)
cecho "QPS (啟動以來平均): $qps" "$GREEN"

# ---- 5. TPS ----
cecho "\n========== TPS ==========" "$BLUE"
com_commit=$(mysql_exec "SHOW GLOBAL STATUS LIKE 'Com_commit';" | awk '{print $2}')
com_rollback=$(mysql_exec "SHOW GLOBAL STATUS LIKE 'Com_rollback';" | awk '{print $2}')
tps=$(echo "scale=2; ($com_commit+$com_rollback)/$uptime_sec" | bc)
cecho "TPS (啟動以來平均): $tps" "$GREEN"

# ---- 6. Key Buffer 命中率 (MyISAM) ----
cecho "\n========== Key Buffer (MyISAM) ==========" "$BLUE"
key_reads=$(mysql_exec "SHOW GLOBAL STATUS LIKE 'Key_reads';" | awk '{print $2}')
key_read_req=$(mysql_exec "SHOW GLOBAL STATUS LIKE 'Key_read_requests';" | awk '{print $2}')
if [ "$key_read_req" -gt 0 ]; then
    hit=$(echo "scale=2; (1-$key_reads/$key_read_req)*100" | bc)
    cecho "讀命中率: ${hit}%" "$GREEN"
else
    cecho "無數據" "$YELLOW"
fi
key_writes=$(mysql_exec "SHOW GLOBAL STATUS LIKE 'Key_writes';" | awk '{print $2}')
key_write_req=$(mysql_exec "SHOW GLOBAL STATUS LIKE 'Key_write_requests';" | awk '{print $2}')
if [ "$key_write_req" -gt 0 ]; then
    hit=$(echo "scale=2; (1-$key_writes/$key_write_req)*100" | bc)
    cecho "寫命中率: ${hit}%" "$GREEN"
else
    cecho "無數據" "$YELLOW"
fi

# ---- 7. InnoDB Buffer Pool 命中率 ----
cecho "\n========== InnoDB Buffer Pool ==========" "$BLUE"
ibuf_reads=$(mysql_exec "SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_reads';" | awk '{print $2}')
ibuf_read_req=$(mysql_exec "SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read_requests';" | awk '{print $2}')
if [ "$ibuf_read_req" -gt 0 ]; then
    hit=$(echo "scale=2; (1-$ibuf_reads/$ibuf_read_req)*100" | bc)
    cecho "讀命中率: ${hit}%" "$GREEN"
else
    cecho "無數據" "$YELLOW"
fi

# ---- 8. Query Cache 命中率 (5.7 已廢棄，8.0 移除) ----
cecho "\n========== Query Cache ==========" "$BLUE"
qc_hits=$(mysql_exec "SHOW GLOBAL STATUS LIKE 'Qcache_hits';" | awk '{print $2}')
qc_inserts=$(mysql_exec "SHOW GLOBAL STATUS LIKE 'Qcache_inserts';" | awk '{print $2}')
qc_not_cached=$(mysql_exec "SHOW GLOBAL STATUS LIKE 'Qcache_not_cached';" | awk '{print $2}')
if [ "$qc_hits" -gt 0 ] 2>/dev/null; then
    total=$(echo "$qc_hits+$qc_inserts+$qc_not_cached" | bc)
    hit=$(echo "scale=2; $qc_hits*100/$total" | bc)
    cecho "命中率: ${hit}%" "$GREEN"
else
    cecho "Query Cache 未啟用或版本不支持" "$YELLOW"
fi

cecho "\n========== 巡檢完成 $(date) ==========" "$BLUE"

部署與自動化

1. 創建巡檢賬號：GRANT PROCESS, REPLICATION CLIENT, SELECT ON performance_schema.* TO 'inspector'@'10.0.1.%' IDENTIFIED BY '...';
2. 分發腳本到所有 DB 節點
3. Crontab 定時執行：0 */6 * * * /opt/scripts/mysql_inspect.sh >> /var/log/mysql_inspect.log 2>&1
4. 對接告警：解析日誌關鍵指標（QPS 突降、命中率 < 90%、連接數 > 80% max_connections）推送至企業微信/釘釘/PostgreSQL

進階擴展建議

• 增加 慢查詢統計：SELECT * FROM performance_schema.events_statements_summary_by_digest ORDER BY AVG_TIMER_WAIT DESC LIMIT 10;
• 增加 複製延遲：SHOW SLAVE STATUS\G 解析 Seconds_Behind_Master
• 增加 表碎片率：information_schema.TABLES.DATA_FREE 計算
• 輸出 JSON 格式：便於 Prometheus/Grafana 採集
• 集成 pt-query-digest、mysqld_exporter 構建完整觀測體系

關鍵指標健康基線（參考）

將巡檢腳本納入標準化運維體系，讓數據庫健康狀態「可視、可量、可控」。