您好,登錄后才能下訂單哦!
這篇文章主要介紹“如何使用mysqladmin ext了解MySQL運(yùn)行狀態(tài)”,在日常操作中,相信很多人在如何使用mysqladmin ext了解MySQL運(yùn)行狀態(tài)問(wèn)題上存在疑惑,小編查閱了各式資料,整理出簡(jiǎn)單好用的操作方法,希望對(duì)大家解答”如何使用mysqladmin ext了解MySQL運(yùn)行狀態(tài)”的疑惑有所幫助!接下來(lái),請(qǐng)跟著小編一起來(lái)學(xué)習(xí)吧!
mysqladmin是MySQL一個(gè)重要的客戶(hù)端,最常見(jiàn)的是使用它來(lái)關(guān)閉數(shù)據(jù)庫(kù),除此,該命令還可以了解MySQL運(yùn)行狀態(tài)、進(jìn)程信息、進(jìn)程殺死等。本文介紹一下如何使用mysqladmin extended-status(因?yàn)闆](méi)有"歧義",所以可以使用ext代替)了解MySQL的運(yùn)行狀態(tài)
使用mysqladmin extended-status命令可以獲得所有MySQL性能指標(biāo),即show global status的輸出,不過(guò),因?yàn)槎鄶?shù)這些指標(biāo)都是累計(jì)值,如果想了解當(dāng)前的狀態(tài),則需要進(jìn)行一次差值計(jì)算,這就是mysqladmin extended-status的一個(gè)額外功能,非常實(shí)用。默認(rèn)的,使用extended-status,看到也是累計(jì)值,但是,加上參數(shù)-r(--relative),就可以看到各個(gè)指標(biāo)的差值,配合參數(shù)-i(--sleep)就可以指定刷新的頻率,那么就有如下命令:
mysqladmin -uroot -r -i 1 -pxxx extended-status +------------------------------------------+----------------------+ | Variable_name | Value | +------------------------------------------+----------------------+ | Aborted_clients | 0 | | Com_select | 336 | | Com_insert | 243 | ...... | Threads_created | 0 | +------------------------------------------+----------------------+
配合grep使用,我們就有:
mysqladmin -uroot -r -i 1 -pxxx extended-status \ |grep "Questions\|Queries\|Innodb_rows\|Com_select \|Com_insert \|Com_update \|Com_delete " | Com_delete | 1 | | Com_delete_multi | 0 | | 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,同時(shí)輸出時(shí)間信息:
mysqladmin -uroot -p -h227.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 <------------- 12:38:50 -------------> Com_delete 0 Com_insert 0 Com_select 0 Com_update 0 Innodb_buffer_pool_read_requests 1814 Innodb_rows_deleted 0 Innodb_rows_inserted 0 Innodb_rows_read 8 Innodb_rows_updated 8 Queries 17 Questions 1
反正也不簡(jiǎn)單了,那就更復(fù)雜一點(diǎn),這樣讓輸出結(jié)果更友好點(diǎn),因?yàn)閍wk不支持動(dòng)態(tài)變量,所以代碼看起來(lái)比較復(fù)雜:
mysqladmin -P3306 -uroot -p -h227.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 10:37:15 | 2241| 271 217 66 0| 1160 129 66 0| 2935 0 10:37:17 | 2497| 299 224 97 0| 1141 149 95 0| 3831 0 10:37:18 | 2871| 352 304 74 23| 8202 226 73 23| 6167 0 10:37:19 | 2441| 284 233 82 0| 1099 121 78 0| 3292 0 10:37:20 | 2342| 279 242 61 0| 1083 224 61 0| 3366 0
到此,關(guān)于“如何使用mysqladmin ext了解MySQL運(yùn)行狀態(tài)”的學(xué)習(xí)就結(jié)束了,希望能夠解決大家的疑惑。理論與實(shí)踐的搭配能更好的幫助大家學(xué)習(xí),快去試試吧!若想繼續(xù)學(xué)習(xí)更多相關(guān)知識(shí),請(qǐng)繼續(xù)關(guān)注億速云網(wǎng)站,小編會(huì)繼續(xù)努力為大家?guī)?lái)更多實(shí)用的文章!
免責(zé)聲明:本站發(fā)布的內(nèi)容(圖片、視頻和文字)以原創(chuàng)、轉(zhuǎn)載和分享為主,文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng),如果涉及侵權(quán)請(qǐng)聯(lián)系站長(zhǎng)郵箱:is@yisu.com進(jìn)行舉報(bào),并提供相關(guān)證據(jù),一經(jīng)查實(shí),將立刻刪除涉嫌侵權(quán)內(nèi)容。