mysqladmin extended-status 是 DBA 即時掌握 MySQL 運行指標的核心工具，相當於 SHOW GLOBAL STATUS 但支援 -r (--relative) 差值模式 與 -i (--sleep) 週期輪詢，配合 grep/awk 可構建輕量級監控儀表板。本文從基礎用法進階到自動化報表腳本。
# 累計值（預設）
mysqladmin -uroot -p extended-status
# 差值模式：每秒刷新一次，顯示增量
mysqladmin -uroot -p -r -i 1 extended-status
# 縮寫：ext 等同 extended-status
mysqladmin -uroot -p -r -i 1 ext
mysqladmin -uroot -p -r -i 1 ext | grep -E "Questions|Queries|Innodb_rows|Com_select|Com_insert|Com_update|Com_delete"
| Com_delete | 1 |
| Com_insert | 321 |
| Com_select | 286 |
| Com_update | 63 |
| Innodb_rows_deleted | 1 |
| Innodb_rows_inserted | 207 |
| Innodb_rows_read | 5211 |
| Innodb_rows_updated | 65 |
| Queries | 2721 |
| Questions | 2721 |
mysqladmin -uroot -p -h127.0.0.1 -P3306 -r -i 1 ext |
awk -F"|" '
$2 ~ /Variable_name/ { print " <------------- " strftime("%H:%M:%S") " ------------->"; }
$2 ~ /Questions|Queries|Innodb_rows|Com_select|Com_insert|Com_update|Com_delete|Innodb_buffer_pool_read_requests/ {
gsub(/^[ \t]+|[ \t]+$/, "", $2); gsub(/^[ \t]+|[ \t]+$/, "", $3);
print $2 " " $3;
}
'
<------------- 12:38:49 ------------->
Com_delete 0
Com_insert 0
Com_select 0
Com_update 0
Innodb_buffer_pool_read_requests 589
Innodb_rows_deleted 0
Innodb_rows_inserted 2
Innodb_rows_read 50
Innodb_rows_updated 50
Queries 105
Questions 1
mysqladmin -P3306 -uroot -p -h127.0.0.1 -r -i 1 ext |
awk -F"|" '
BEGIN { count = 0; }
$2 ~ /Variable_name/ && ((++count) % 20 == 1) {
print "----------|---------|--- MySQL Command Status --|----- Innodb row operation ----|-- Buffer Pool Read --";
print "---Time---|---QPS---|select insert update delete| read inserted updated deleted| logical physical";
}
$2 ~ /Queries/ { queries = $3; }
$2 ~ /Com_select/ { com_select = $3; }
$2 ~ /Com_insert/ { com_insert = $3; }
$2 ~ /Com_update/ { com_update = $3; }
$2 ~ /Com_delete/ { com_delete = $3; }
$2 ~ /Innodb_rows_read/ { innodb_rows_read = $3; }
$2 ~ /Innodb_rows_deleted/ { innodb_rows_deleted = $3; }
$2 ~ /Innodb_rows_inserted/ { innodb_rows_inserted = $3; }
$2 ~ /Innodb_rows_updated/ { innodb_rows_updated = $3; }
$2 ~ /Innodb_buffer_pool_read_requests/ { innodb_lor = $3; }
$2 ~ /Innodb_buffer_pool_reads/ { innodb_phr = $3; }
$2 ~ /Uptime/ && count >= 2 {
printf(" %s |%9d", strftime("%H:%M:%S"), queries);
printf("|%6d %6d %6d %6d", com_select, com_insert, com_update, com_delete);
printf("|%6d %8d %7d %7d", innodb_rows_read, innodb_rows_inserted, innodb_rows_updated, innodb_rows_deleted);
printf("|%10d %11d\n", innodb_lor, innodb_phr);
}
'
----------|---------|--- MySQL Command Status --|----- Innodb row operation ----|-- Buffer Pool Read --
---Time---|---QPS---|select insert update delete| read inserted updated deleted| logical physical
10:37:13 | 2231| 274 214 70 0| 4811 160 71 0| 4146 0
10:37:14 | 2972| 403 256 84 23| 2509 173 85 23| 4545 0
10:37:15 | 2334| 282 232 66 1| 1266 154 67 1| 3543 0
| 指標 | 含義 | 異常徵兆 |
|---|---|---|
Questions | 客戶端發送的查詢數 | 暴增可能為慢查詢堆積 |
Queries | 伺服器執行的總指令數（含內部） | Queries > Questions 正常 |
Com_select/insert/update/delete | 各類 SQL 執行次數 | 寫入比例過高需關注鎖爭用 |
Innodb_rows_read/inserted/updated/deleted | 引擎層行操作計數 | Read 遠大於 Select 可能全表掃描 |
Innodb_buffer_pool_read_requests | 邏輯讀請求（記憶體） | 基準指標 |
Innodb_buffer_pool_reads | 物理讀（磁碟） | 物理讀/邏輯讀 > 1% 需擴充 Buffer Pool |
Uptime | 伺服器運行秒數 | 若重置需檢查是否自動重啟 |
#!/bin/bash
# monitor_mysql.sh
HOST=127.0.0.1
PORT=3306
USER=monitor
PASS=xxx
INTERVAL=5
mysqladmin -h$HOST -P$PORT -u$USER -p$PASS -r -i $INTERVAL ext |
awk -F"|" -v interval=$INTERVAL '
# ... 版本 2 awk 程式碼 ...
' >> /var/log/mysql_qps.log 2>&1 &
配合 logrotate 切割日誌，systemd 管理服務，構建零依賴監控代理。
mysqladmin -r -i + awk 是零安裝、零配置、即時可見的 MySQL 監控鐵三角。掌握差值統計邏輯與關鍵指標解讀，即可在無 Prometheus/Grafana 環境下快速建立可用性監控。進階場景建議整合 pmm、prometheus-mysqld-exporter 做長期趨勢分析。
