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

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

相關文章