ITPub博客

首页 > 数据库 > MySQL > mysql中pager和其它命令的一些小技巧介绍

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

原创 MySQL 作者:chenfeng 时间:2019-06-25 11:17:44 0 删除 编辑

一. 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


来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/15498/viewspace-2648645/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
交流MySQL,MongoDB和Redis技术。 微信或QQ:410294

注册时间:2015-12-07

  • 博文量
    744
  • 访问量
    1724834