mysqladmin 監控 MySQL 運行狀態實戰：extended-status、差值計算、AWK 實時儀表板

核心命令：mysqladmin extended-status

等價於 SHOW GLOBAL STATUS，但支援 -r/--relative（差值模式）+ -i/--sleep（輪詢間隔），**極適合寫監控腳本**。

基礎用法

# 每秒輸出一次差值
mysqladmin -uroot -p -r -i 1 extended-status

輸出所有 300+ 狀態變量的**每秒增量**。

配合 grep 過濾核心指標

mysqladmin -uroot -p -r -i 1 extended-status \
| 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 |

進階：AWK 實時儀表板（帶時間戳、QPS、行操作、緩存命中）

mysqladmin -uroot -p -h127.0.0.1 -P3306 -r -i 1 ext \
| awk -F"|" '
{
  if ($2 ~ /Variable_name/) {
    print " <------------- " strftime("%H:%M:%S") " ------------->"
  }
  if ($2 ~ /Questions|Queries|Innodb_rows|Com_select|Com_insert|Com_update|Com_delete|Innodb_buffer_pool_read_requests/)
    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 }
{
  if ($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"
  }
  else if ($2 ~ /Queries/) queries=$3
  else if ($2 ~ /Com_select/) com_select=$3
  else if ($2 ~ /Com_insert/) com_insert=$3
  else if ($2 ~ /Com_update/) com_update=$3
  else if ($2 ~ /Com_delete/) com_delete=$3
  else if ($2 ~ /Innodb_rows_read/) innodb_rows_read=$3
  else if ($2 ~ /Innodb_rows_deleted/) innodb_rows_deleted=$3
  else if ($2 ~ /Innodb_rows_inserted/) innodb_rows_inserted=$3
  else if ($2 ~ /Innodb_rows_updated/) innodb_rows_updated=$3
  else if ($2 ~ /Innodb_buffer_pool_read_requests/) innodb_lor=$3
  else if ($2 ~ /Innodb_buffer_pool_reads/) innodb_phr=$3
  else if ($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

關鍵指標解讀

總結

mysqladmin -r -i 1 extended-status + AWK 是**零依賴、輕量級、可嵌入告警系統**的 MySQL 實時監控利器，建議納入日常巡檢與自動化監控體系。