mysqladmin 監控 MySQL 運行狀態實戰：extended-status 進階用法

核心命令：差值模式即時監控

mysqladmin extended-status 相當於 SHOW GLOBAL STATUS，但配合 -r/--relative 可輸出差值（每秒變化量），配合 -i/--sleep 設定刷新頻率：

mysqladmin -uroot -p -r -i 1 extended-status

進階管線處理

1. 過濾關鍵指標

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

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

3. 完整儀表板格式輸出

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

輸出範例

---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
...

關鍵指標對照表