Shulou(Shulou.com)05/31 Report--

This article mainly introduces "how to use mysqladmin ext to understand the running status of MySQL". In daily operation, I believe many people have doubts about how to use mysqladmin ext to understand the running status of MySQL. The editor consulted all kinds of materials and sorted out simple and easy-to-use operation methods. I hope it will be helpful to answer the doubts of "how to use mysqladmin ext to understand the running status of MySQL". Next, please follow the editor to study!

Use mysqladmin ext to understand the running status of MySQL

Mysqladmin is an important client of MySQL, and it is most common to use it to shut down the database. In addition, this command can also know the running status of MySQL, process information, process killing, and so on. This article describes how to use mysqladmin extended-status (because there is no "ambiguity", so you can use ext instead) to understand the running state of MySQL

1. Use the-rhand Muri parameter

Using the mysqladmin extended-status command, you can get all the MySQL performance metrics, that is, the output of show global status, but because most of these metrics are cumulative, you need to do a difference calculation if you want to understand the current state, which is an additional feature of mysqladmin extended-status and is very useful. By default, if you use extended-status, you can see the cumulative value, but add the parameter-r (--relative) and you can see the difference of each metric. With the parameter-I (--sleep), you can specify the refresh frequency, so you can have the following command:

Mysqladmin-uroot-r-I 1-pxxx extended-status+--+--+ | Variable_name | Value | +- -- +-- + | Aborted_clients | 0 | | Com_select | 336 | | Com_insert | | 243 |. | Threads_created | 0 | +-- +-- + 2. Use with grep

When used with grep, we have:

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 | 3. Use with simple awk

Use awk while outputting time information:

Mysqladmin-uroot-p-h227.0.0.1-P3306-r-I 1 ext |\ awk-F "|" {{\ if ($2 ~ / Variable_name/) {\ print ";\}\ if ($2 ~ / Questions | Queries | Com_select | Com_insert | Com_update | Com_delete | Innodb_buffer_pool_read_requests/)\ print $2 $3 \} '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 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 14. Cooperate with a more complicated awk

It's not easy anyway, so it's a little more complex, which makes the output more friendly, because awk doesn't support dynamic variables, so the code looks more complex:

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% 6d", com_select,com_insert,com_update,com_delete) \ printf ("% 6d% 8d% 7d% 7d% 7d", innodb_rows_read,innodb_rows_inserted,innodb_rows_updated,innodb_rows_deleted);\ printf ("| d\ 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 | 274214 700 | 4811 16071 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 | 29922497 0 | 1141 14950 | 3831 0 10:37:18 | 2871 | 35230474 23 | 8202 22673 23 | 6167 0 10:37:19 | 2441 | 284,233,820 | 1099 121,780 | 3292 0 10:37:20 | 2342 | 279242 61 0 | 1083 224 61 0 | 3366 0 to here The study on "how to use mysqladmin ext to understand the running status of MySQL" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.