mysql中pager和其它命令的一些小技巧介紹

2024-07-24 12:34:42

字體：大中小

來源：轉載

供稿：網友

  mysql中pager和其它命令的一些小技巧介紹:

　　一. pager less或pager more

　　說明：

　　less模式,可以使用空格到下一頁,q退出;

　　more模式，跟linux more命令一樣，按空格顯示到下一頁

　　例如：

　　mysql>pager less

　　PAGER set to 'less'

　　>show engine innodb status /G

　　*************************** 1. row ***************************

　　 Type: InnoDB

　　 Name:

　　Status:

　　=====================================

　　2019-06-25 09:58:27 0x7f326c3fb700 INNODB MONITOR OUTPUT

　　=====================================

　　Per second averages calculated from the last 7 seconds

　　-----------------

　　BACKGROUND THREAD

　　-----------------

　　srv_master_thread loops: 1644 srv_active, 0 srv_shutdown, 6045651 srv_idle

　　srv_master_thread log flush and writes: 6047099

　　----------

　　SEMAPHORES

　　----------

　　OS WAIT ARRAY INFO: reservation count 19543

　　OS WAIT ARRAY INFO: signal count 18271

　　RW-shared spins 0, rounds 1971, OS waits 762

　　RW-excl spins 0, rounds 15377, OS waits 200

　　RW-sx spins 365, rounds 7423, OS waits 99

　　Spin rounds per wait: 1971.00 RW-shared, 15377.00 RW-excl, 20.34 RW-sx

　　------------

　　TRANSACTIONS

　　------------

　　Trx id counter 3264932

　　Purge done for trx's n:o < 3264932 undo n:o < 0 state: running but idle

　　History list length 32

　　LIST OF TRANSACTIONS FOR EACH SESSION:

　　---TRANSACTION 421335447628512, not started

　　0 lock struct(s), heap size 1136, 0 row lock(s)

　　---TRANSACTION 421335447627600, not started

　　0 lock struct(s), heap size 1136, 0 row lock(s)

　　--------

　　FILE I/O

　　--------

　　I/O thread 0 state: waiting for completed aio requests (insert buffer thread)

　　I/O thread 1 state: waiting for completed aio requests (log thread)

　　I/O thread 2 state: waiting for completed aio requests (read thread)

　　I/O thread 3 state: waiting for completed aio requests (read thread)

　　I/O thread 4 state: waiting for completed aio requests (read thread)

　　I/O thread 5 state: waiting for completed aio requests (read thread)

　　I/O thread 6 state: waiting for completed aio requests (read thread)

　　I/O thread 7 state: waiting for completed aio requests (read thread)

　　I/O thread 8 state: waiting for completed aio requests (write thread)

　　I/O thread 9 state: waiting for completed aio requests (write thread)

　　I/O thread 10 state: waiting for completed aio requests (write thread)

　　I/O thread 11 state: waiting for completed aio requests (write thread)

　　I/O thread 12 state: waiting for completed aio requests (write thread)

　　I/O thread 13 state: waiting for completed aio requests (write thread)

　　Pending normal aio reads: [0, 0, 0, 0, 0, 0] , aio writes: [0, 0, 0, 0, 0, 0] ,

　　按回車繼續顯示，按q退出。

　　二.忽略中間過程輸出,只顯示執行結果：

　　mysql>pager cat > /dev/null

　　PAGER set to 'cat > /dev/null'

　　mysql>select * from test.test;

　　101000 rows in set (0.33 sec)

　　三.show processlist格式化輸出

　　mysql>pager awk -F '|' '{print $6}' | sort | uniq -c | sort -r

　　PAGER set to 'awk -F '|' '{print $6}' | sort | uniq -c | sort -r'

　　mysql>show processlist;

　　      1 Query

　　      1 Command

　　      1 Binlog Dump

　　    134 Sleep

　　136 rows in set (0.00 sec)

　　四.checksum用法：

　　checksum用來比較SQL結果是否相同:

　　mysql> pager md5sum

　　PAGER set to 'md5sum'

　　mysql>select count(*) from test.test;

　　009e5c78cbf36ce635cc26a4711edf6b -

　　1 row in set (0.11 sec)

　　刪除部分數據后:

　　mysql>select count(*) from test.test;

　　b092d86b9dad1070f9cd56786d1ac99a -

　　1 row in set (0.00 sec)

　　備注：刪除數據前后SQL語句的checksum的值不同

　　五.edit用法

　　root@localhost :(none)10:32:56>use test

　　Reading table information for completion of table and column names

　　You can turn off this feature to get a quicker startup with -A

　　Database changed

　　root@localhost :test10:32:57>show databases;

　　+--------------------+

　　| Database           |

　　+--------------------+

　　| information_schema |

　　| S121318            |

　　| S122036            |

　　| S122206            |

　　| S122443            |

　　| S122501            |

　　| S383               |

　　| U47032             |

　　| dsf                |

　　| impl               |

　　| monitor            |

　　| mysql              |

　　| performance_schema |

　　| slow_query_log     |

　　| sys                |

　　| test               |

　　| test_tb            |

　　| yqht               |

　　| yqms2              |

　　+--------------------+

　　19 rows in set (0.00 sec)

　　root@localhost :test10:33:02>edit

　　//敲回車

　?。ㄔ诖蜷_的vi中編輯，編輯完然后x退出）

　　        >;                            //退出后再敲上分號。來執行這條編輯后的語句。

　　show tables

　　"/tmp/sqlizwJXA" 1L, 12C written

　　    -> ;

　　+---------------------+

　　| Tables_in_test      |

　　+---------------------+

　　| aaa                 |

　　| dsf                 |

　　| dsf_old             |

　　| peihy               |

　　| sq_prebycollecttime |

　　| t                   |

　　| t1                  |

　　| test                |

　　+---------------------+

　　8 rows in set (0.01 sec)

　　六. tee命令用法

　　tee命令可以把結果輸出到文件:

　　root@localhost :test10:36:25>tee /tmp/aaa.txt

　　Logging to file '/tmp/aaa.txt'

　　root@localhost :test10:36:31>select * from t;

　　+----+------+

　　| id | name |

　　+----+------+

　　| 1 | dsf |

　　| 2 | dsf |

　　| 5 | dsf |

　　| 6 | liu |

　　| 7 | pei |

　　+----+------+

　　5 rows in set (0.00 sec)

　　root@localhost :test10:36:34>notee

　　Outfile disabled.

　　/tmp/aaa.txt內容如下：

　　# cat /tmp/aaa.txt

　　mysql>select * from t;

　　+----+------+

　　| id | name |

　　+----+------+

　　| 1 | dsf |

　　| 2 | dsf |

　　| 5 | dsf |

　　| 6 | liu |

　　| 7 | pei |

　　+----+------+

　　5 rows in set (0.00 sec)

　　mysql>notee

　　七.echo命令用法：

　　# echo "select * from t;" | mysql test

　　id      name

　　1       dsf

　　2       dsf

　　5       dsf

　　6       liu

　　7       pei

　　八. 不顯示表的列頭部:

　　# mysql --skip-column-names -e "select * from test.user limit 10;"

　　+----+------------------+---------+

　　| 1 |             小明 |       1 |

　　| 2 |             小紅 |       1 |

　　| 3 |             涵涵 |       2 |

　　| 4 | BBfSaxkHIuXDbvXA | 7394002 |

　　| 5 | hBlAVc rgIWKMELT | 2230353 |

　　| 6 | yGNWtciFFlmDgWpH | 3941883 |

　　| 7 | aRlDlsfzghrkbAAd | 7363753 |

　　| 8 | pWOiwGVJInoGrNP | 7648385 |

　　| 9 | uJldIgGPfefqmltm | 866603 |

　　| 10 | KnjeWwrsOUdIgGMS | 555015 |

　　+----+------------------+---------+

　　# mysql --skip-column-names -e "select * from test.user limit 10;" | cat -n

　　     1 1       小明    1

　　     2 2       小紅    1

　　     3 3       涵涵    2

　　     4 4       BBfSaxkHIuXDbvXA        7394002

　　     5 5       hBlAVc rgIWKMELT        2230353

　　     6 6       yGNWtciFFlmDgWpH        3941883

　　     7 7       aRlDlsfzghrkbAAd        7363753

　　     8 8        pWOiwGVJInoGrNP        7648385

　　     9 9       uJldIgGPfefqmltm        866603

　　    10 10      KnjeWwrsOUdIgGMS        555015

（編輯：武林網）

上一篇：如何快速找到MYSQL binlog中的大事物以及生成量分布

下一篇：MySQL進門 MySQL復制技術之主從復制