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;       3        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