MySQL 系統效能狀態採集腳本：SHOW GLOBAL STATUS/Variables/InnoDB/Processlist 定時入庫

腳本用途

每 5 秒採集一次 MySQL 全域狀態、InnoDB 狀態、進程列表、系統負載，寫入時間序列檔案，事後用 pt-query-digest、Excel、Grafana 分析效能趨勢。

KSH 腳本完整版

#!/bin/ksh
INTERVAL=5
PREFIX=${INTERVAL}-sec-status
touch /tmp/running
RUNFILE=/tmp/running

# 固定變數快照
mysql -e 'SHOW GLOBAL VARIABLES' > mysql-variables

while test -e $RUNFILE; do
    file=$(date +%F_%H)
    sleep=$(date +%s.%N | awk "{print $INTERVAL - (\$1 % $INTERVAL)}")
    sleep $sleep
    ts="$(date +\"TS %s.%N %F %T\")"
    loadavg="$(uptime)"

    echo "$ts $loadavg" >> $PREFIX-${file}-status
    mysql -e 'SHOW GLOBAL STATUS' >> $PREFIX-${file}-status &

    echo "$ts $loadavg" >> $PREFIX-${file}-innodbstatus
    mysql -e 'SHOW ENGINE INNODB STATUS\G' >> $PREFIX-${file}-innodbstatus &

    echo "$ts $loadavg" >> $PREFIX-${file}-processlist
    mysql -e 'SHOW FULL PROCESSLIST\G' >> $PREFIX-${file}-processlist &

    echo $ts
done

echo "Exiting because $RUNFILE does not exist."

關鍵指標解讀

進階：自動化分析

# 產生報表
pt-query-digest 5-sec-status-2024-01-15-status --type slowlog --limit 20

# 導入 InfluxDB/Grafana 持續監控
# Telegraf + MySQL input plugin 替代手工腳本