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
