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

5 5 hBlAVc rgIWKMELT2230353

6 6 yGNWtciFFlmDgWpH3941883

7 7 aRlDlsfzghrkbAAd7363753

8 8pWOiwGVJInoGrNP7648385

9 9 uJldIgGPfefqmltm866603

10 10 KnjeWwrsOUdIgGMS555015