Linux 實戰：mysqladmin extended-status 搭配 watch/grep/awk 即時監控 MySQL 運行狀態

mysqladmin extended-status 等同 SHOW GLOBAL STATUS，輸出所有累計計數器。善用 -r（相對值/差值）、-i（間隔秒數）配合 Linux 管線工具，打造即時儀表板。

1. 基礎：相對值 + 固定間隔

mysqladmin -uroot -p -r -i 1 extended-status
# 每秒顯示各計數器增量

2. 篩選關鍵指標

mysqladmin -uroot -p -r -i 1 extended-status \
| grep -E "Questions|Queries|Innodb_rows|Com_select|Com_insert|Com_update|Com_delete"

3. awk 加入時間戳

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;
}'

4. 進階：自訂欄位對齊輸出（生產級單行腳本）

mysqladmin -P3306 -uroot -p -h127.0.0.1 -r -i 1 ext \
| awk -F"|" "
BEGIN { count=0; }
{
  if ($2 ~ /Variable_name/ && ++count == 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
...

指標說明：
• Queries/Questions：QPS 核心指標。
• Com_*：各類 SQL 執行次數增量。
• Innodb_rows_*：InnoDB 層面行級操作增量。
• Innodb_buffer_pool_read_requests（邏輯讀）vs Innodb_buffer_pool_reads（物理讀）：命中率 = 1 - 物理/邏輯。