Use mysqladmin ext to understand MySQL run state turn

Https://yq.aliyun.com/articles/11210?spm=0.0.0.0.HpobXF1. Using the-r/-i parameter

With the Mysqladmin extended-status command, you get all the MySQL performance metrics, the output of show global status, but since most of these metrics are cumulative, a difference calculation is required if you want to know the current state. This is an extra feature of mysqladmin extended-status, very practical. By default, the use of Extended-status, see is also cumulative value, but, with the parameter-R (--relative), you can see the difference between the indicators, with the parameter-I (--sleep) can specify the frequency of the refresh, then like 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

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 to output time information:

Mysqladmin-uroot-p-h127.0.0.1-p3306-r-I 1 ext |awk-f "|"  ' {if ($ ~/variable_name/) {print "<-------------" Strftime ("%h:%m:%s") "------------->"; } if ($ ~/questions| queries| innodb_rows| Com_select | Com_insert | Com_update | Com_delete | innodb_buffer_pool_read_requests/) print $ $;} '                             <-------------12:38:49-------------> Com_delete 0 com_insert 0 Com_select 0 com_update 0 Innodb_buffer_pool_read_reque                       STS 589 innodb_rows_deleted 0 innodb_rows_inserted 2 Innodb_rows_read                              innodb_rows_updated Queries 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 Questions 1

4. With a more complex awk

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

Mysqladmin-p3306-uroot-p-h127.0.0.1-r-I 1 ext |awk-f "|" "Begin{count=0;}" ' {if ($ ~/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 ($/queries/) {queries=$3;} else if ($/com_select/) {com_select=$3;} else if ($/com_insert/) {com_insert=$3;} else if ($/com_update/) {com_update=$3;} else if ($/com_delete/) {com_delete=$3;} else if ($/innodb_rows_read/) {innodb_rows_read=$3;} else if ($/innodb_rows_deleted/) {innodb_rows_deleted=$3;} else if ($/innodb_rows_inserted/) {innodb_rows_inserted=$3;} else if ($/innodb_rows_updated/) {innodb_rows_updated=$3;} else if ($/innodb_buffer_pool_read_requests/) {innodb_lor=$3;} else if ($/innodb_buffer_pool_reads/) {innodb_phr=$3;} else if ($/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

In this way, these commands are used more by themselves and are readily shared.

Use mysqladmin ext to understand MySQL run state turn