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